InnnoDb - change innodb_buffer_pool_size ?
Hello, I am running mysql 4.0.20 with Innodb and we have upgrade grade the RAM in the system from 1GB to 4GB :). Now If I change the: innodb_buffer_pool_size From 500M to 3G the service will not start. I remember reading something about this before and I believe I need to delete the ib_logfile* files or something .. and they will get recreated on start up ?? Any help is appreciated :) Here is my mysql.log output: 050614 19:50:50 mysqld started 050614 19:50:50 Warning: Asked for 196608 thread stack, but got 126976 InnoDB: Fatal error: cannot allocate 3221241856 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 20288332 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=67108864 read_buffer_size=2093056 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3546735 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8424780 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffeb88, stack_bottom=0x20386365, thread_stack=126976, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x64207369 is invalid pointer thd-thread_id=484 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 050614 19:50:50 mysqld ended Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnnoDb - change innodb_buffer_pool_size ?
Hello, I have finished reading something similer on the web, why is there a 2GB limit ? If I can compile the kernel to say eys there is 4GB of memory why can mysql not use 3GB of it ? For know I have set the limit at 2GB. Michael Partha Dutta wrote: If you are running on a 32 bit operating system, (e.g. Linux) you can not allocate that much memory for InnoDB. The max you could allocate would be 2GB, but that would be pushing it. You also have to account for MyISAM memory usage (key_buffer_size) and per thread memory allocations as well. Partha -- Partha Dutta, Senior Consultant MySQL Inc, NY, USA, www.mysql.com Are you MySQL certified? www.mysql.com/certification -Original Message- From: Michael Gale [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 14, 2005 10:04 PM To: mysql@lists.mysql.com Subject: InnnoDb - change innodb_buffer_pool_size ? Hello, I am running mysql 4.0.20 with Innodb and we have upgrade grade the RAM in the system from 1GB to 4GB :). Now If I change the: innodb_buffer_pool_size From 500M to 3G the service will not start. I remember reading something about this before and I believe I need to delete the ib_logfile* files or something .. and they will get recreated on start up ?? Any help is appreciated :) Here is my mysql.log output: 050614 19:50:50 mysqld started 050614 19:50:50 Warning: Asked for 196608 thread stack, but got 126976 InnoDB: Fatal error: cannot allocate 3221241856 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 20288332 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=67108864 read_buffer_size=2093056 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3546735 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8424780 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffeb88, stack_bottom=0x20386365, thread_stack=126976, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x64207369 is invalid pointer thd-thread_id=484 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 050614 19:50:50 mysqld ended Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnnoDb - change innodb_buffer_pool_size ?
Hello, Even if I change the value to 2G it still will not load ? Because the value was 500M before do I not have to remove the ib* files and have them recreated ? Michael Michael Gale wrote: Hello, I am running mysql 4.0.20 with Innodb and we have upgrade grade the RAM in the system from 1GB to 4GB :). Now If I change the: innodb_buffer_pool_size From 500M to 3G the service will not start. I remember reading something about this before and I believe I need to delete the ib_logfile* files or something .. and they will get recreated on start up ?? Any help is appreciated :) Here is my mysql.log output: 050614 19:50:50 mysqld started 050614 19:50:50 Warning: Asked for 196608 thread stack, but got 126976 InnoDB: Fatal error: cannot allocate 3221241856 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 20288332 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=67108864 read_buffer_size=2093056 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3546735 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8424780 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffeb88, stack_bottom=0x20386365, thread_stack=126976, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x64207369 is invalid pointer thd-thread_id=484 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 050614 19:50:50 mysqld ended Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
data length vs index length ??
Hello, Currently I have a large mysql table (36 million rows) and according to the mysql-admin the data length is 6.5GB and my index length is 8.8GB. I am new to mysql, but I would assume that my index should not consume more space then my data ? But in order to provide the data in a timely manner I do not believe I can remove any of my indexes as there were all added as a result of a query taking 30-40 minutes. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index help ?
Hello, I have the following table setup: IDhostnamefacilityprioritydatemessage ID is auto incrementing. This is used to store all of the syslog messages, currently there are over 7 million: The following query takes forever: Select machine,count(*) from syslog WHERE date1 (NOW() - INTERVAL 24 hour) AND message LIKE 'sshd%' GROUP BY machine; I have created the following indexs but when I use Explain it says that the query has to search all the rows: datehostfacility 1 date1 A352489 datehostfacility 2 machine A 1409956 datehostfacility 3 facility A 1409956 datemesghost 1 date1 A640889 datemesghost 2 message(15) A 7049783 datemesghost 3 machine A 7049783 datemesghost 4 facility A 7049783 datemesghost 5 priority A 7049783 What would the proper index be ? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index problem ?
Hello, Currently I am using sysklogd-sql to store syslog messages in a MySQL 4.0.24. The current number of rows is around 3,799,700. The table layout is as follows: ID machine facility priority date1 message Now ID is the primary key and it had one index called hostname containing (machine). I do a lot of queries based off information on the machine name and date1 field. So I created the following index: `CREATE INDEX hostdate ON syslog (machine,date1);` This index has a Cardinality of 1,899,353 - this number is about half of the total number of entries. When I run the following: `Select DISTINCT machine from syslog WHERE date1 (NOW() - INTERVAL 1 hour);' it takes 9min to complete. If I use Explain it says the query is using index hostname, should it not be using the index hostdate which contains fields machine and date1 since those are the fields I am using in my query ? Thanks. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 2013: Lost connection to MySQL server during query
Hello, Thanks to all who applied but I was able to get access to the log file today. The server was shutting down and it seemed to be a performance problem. I increased the RAM in the server and adjusted the Innodb settings to take advantage of this. Now everything is much better. Michael Daniel Kasak wrote: Michael Gale wrote: Hello, Earlier today I tried to make some table alterations on a mysql server (4.0.24) and kept on receiving this error: ERROR 2013: Lost connection to MySQL server during query I would login to the server, either through the socket or using the IP. Then connect to a database and try the following: CREATE INDEX hostdate ON syslog (machine,date1); There are about 3 million rows .. about a minute or two into the command I would get disconnected. Sometimes I could log back but not others, I tried seaching google and the only answer I found was This is fix the in the next release of 5.X.X. This does not help as I am stuck with 4.0.X right now. How can I fix this ? Michael Is the server crashing? What does your error log say? If you're using 4.0.x, then my first hunch would be table corruption. Have you checked for that yet? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 2013: Lost connection to MySQL server during query
Hello, Earlier today I tried to make some table alterations on a mysql server (4.0.24) and kept on receiving this error: ERROR 2013: Lost connection to MySQL server during query I would login to the server, either through the socket or using the IP. Then connect to a database and try the following: CREATE INDEX hostdate ON syslog (machine,date1); There are about 3 million rows .. about a minute or two into the command I would get disconnected. Sometimes I could log back but not others, I tried seaching google and the only answer I found was This is fix the in the next release of 5.X.X. This does not help as I am stuck with 4.0.X right now. How can I fix this ? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database load and table design ?
Hello, Currently I am using syslog-sql to store syslog data in a mysql database. The table format is something like: ID hostnamefacilityprioritydatemessage ID is auto incrementing Now I am writting a perl app to calculate stats based on the data in the table which will get run everyday. It is currently doing a loop, here is an example: Select count(facility) from syslog WHERE machine = '$srv' AND facility = '$fac' AND date1 (NOW() - INTERVAL 24 hour) Now $srv is the name of the host and $fac is the facility name. This select statement is in a loop that loops through each server and each facility. When this is running it puts a load on the DB, since there could be about 20 host, each with 6 facilities, which equals about: 600 - The number of times that the select statement would be run I suppose that running the query: select host,facility from WHERE date1 (NOW() - INTERVAL 24 hour) and letting perl do the math on the selected results would be less of a system load ? Is there a better way ? Also I am going to display the results using the following format: hostnamehost A host B ... Facility name facility A X XX facility B X xX facility C X XX Is there an easy way to put that into a mysql table ?? Thanks. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Better option then polling ?
Hello, I am fairly new to mysql and have a strange request I am not sure if stored procedures will solve this. I have a table in a database that is constantly being updated with new information. I would like to be notified if certain information gets committed. Lets say for example the table gets updated every time a login attempt happens regardless if it is successful or not. So I want an app to watch the table for incorrect logins and if found to send an e-mail. Now there are several problems with this ... first... there could be hundreds of logins so we do not want the same app to spawn multiple processes each notifying the same incorrect login attempt. I was thinking about a cron that would run every X minute searching any newly added rows and keeping the last row id in a tmp file. Is there a better way ? Thanks. Michael. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql bin files
Hello, I am new to mysql and am in the middle of upgrading our mysql server to the latest release. Upon viewing our currently data directory I noticed a large amount of hostname-bin-### files. Some of hostname-bin-### have time stamps of January -- from the documentation I have read I can uses these to restore mysql data. So am I correct in thinking that I can safely backup any hostname-bin-### files older then let's say a week to a backup server and then delete them from the data directory ? We are running mysql-3.23 build 50. -- Michael Gale -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data loading
Hello, I have a question about data loading using mysql 4.0.20. If you need to load let's say 50,000 items into a database. Now 50,000 is not a lot for a DB. So my question is would it be fast to load the file using the local infile which is a security concern or should I be using a loop with INSERTS ? -- Michael Gale Network Administrator Utilitran Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IMPORTANT: Please Verify Your Message
Hello, I got a error the first time ... but is seems to be working. Michael. On Thu, 22 Jul 2004 07:04:37 -0400 [EMAIL PROTECTED] wrote: Verify Email Hello [EMAIL PROTECTED] , [EMAIL PROTECTED] is currently protecting themselves from receiving junk mail using Spamcease Just this once, click the link below so I can receive your emails. You won't have to do this again. http://www.tgpwizards.com/spamcease2/verify.php?id=1094016 -- Michael Gale Network Administrator Utilitran Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld being killed by VM
Hello, I have a mysql server that VM is killing about once every two weeks. Now my sar utility shows that the memory on the box continues to grow at a small rate from start to stop. But I am not sure how accurate that is. Now I am running slackware 9.1 with kernel 2.4.23 with mysql 3.23 build 50 with InnoDB support. I checked the my.cnf file and found the following: The box is a dual xeon 2.4Ghz with 2GB of RAM __alloc_pages: 0-order allocation failed (gfp=0x1d2/0) VM: killing process mysqld-max Where do I start to find out if the problem is with mysql ? or our application ? It appears that the mysqld-max process will try and use all the memory in the box and so the kernel will kill the process. Also if I used mysqld_safe would it restart after this ? -- Michael Gale Network Administrator Utilitran Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 4.1 cluster
Hello, Anyone running mysql 4.1 with cluster support ? Is it sort of production ready ? -- Michael Gale -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Master and slave with different versions
Hello, Right now we are running a mysql-max db with InnoDb for transaction tracking. This is a older version of mysql running mysql-3.23 build 50. We are planning on upgrading to mysql 4.0 latest in a couple of weeks (hopefully). Anyways in order to do proper backups with InnoDB I am going to use the following method: 1. Make current mysql server master 2. Setup secondary mysql server as a slave so nightly I can bring down the slave to do binary backups. The slave server will be on a backup box and is only for backup purposes. It will not be uses by any production system. The question for the list is ... can I use the latest version of mysql on the slave server so when we upgrade the master in a few weeks / month the master and slave will be at the same version ? Or does the slave have to be version 3.23 build 50 ? Thanks Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
