RE: problems with INNODB tables
Some mixture. Perhaps 35% of RAM for buffer_pool and 10% for key_buffer. It depends on which needs more caching. Note: The key_buffer does not need to be bigger than the total of all MyISAM indexes (Index_length in SHOW TABLE STATUS, or size of .MYI files). The buffer_pool does not need to be bigger than the total of data+index for InnoDB files. -Original Message- From: Malka Cymbalista [mailto:malki.cymbali...@weizmann.ac.il] Sent: Wednesday, April 25, 2012 3:15 AM To: Rick James; Andrés Tello Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: RE: problems with INNODB tables Thanks for your answer. I read http://mysql.rjweb.org/doc.php/memory where it tells you to do one thing if using MYIASM tables and another if using INNODB tables. We are using both. Any suggestions? Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.il 08-9343036 -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Monday, April 23, 2012 9:42 PM To: Andrés Tello; Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: RE: problems with INNODB tables Check your memory usage according to http://mysql.rjweb.org/doc.php/memory -Original Message- From: Andrés Tello [mailto:mr.crip...@gmail.com] Sent: Monday, April 23, 2012 9:00 AM To: Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: Re: problems with INNODB tables Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql process with pkill -9 -f mysql Y suppose the way drupal is being programed. PHP open and closes database connections each time a webpage with db access is issued. When a php exceution ends and the apache webserver have fullfilled the http request, again, php memory is freed and connections closed... UNLESS:.. you are using a mem cached db connection, wich I doubt it since drupal doens't requiere one, or using persistent connections, again, I doubt it, because persistante database connections aren't recommended to innodb tables... Mysql server by default can handles 100 conections, if you get to thata limit you need to fine tune the number of connections allowed. show full processlist can give you a better idea of what is going on, connections with the sleep status, are open connections with no currently no transacctions... I never use script based stop, I always use mysqladmin -u root -p -h localhost shutdown which properly tells mysql to flush tables and terminate. I can almost bet that you are using Ubuntu... ubuntu had given me sometimes very hard times because of the edgy code they use to use, ext4 last version, and so on... what can you tell us about that? How much amount of memory you have? How much concurrent apache/php users you have? Can you provide more cuantitive data please? Hardware, php version, distro, kernel... Cheers... To start, 100 process is quite a lot, something isn't fine. Each time On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: We are running MySQL version 5.0.45 on a Linux machine. Over the past few months we have been having several problems: 1. Our mysql processes have increased the memory used from about .3% per process to 8% per process 2. We sometimes can have over 100 processes running which brings the machine to its knees and we have to stop and start MySQL in order to kill all the processes. We think that maybe the processes are not finishing normally and are just hanging around. 3. The machine is a web server and in the last few months we are moving over to drupal 7 to build our sites and Drupal 7 requires INNODB tables. Sometimes, when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. In order for the INNODB tables to work, we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and then restart mysql. Otherwise the INNODB tables are not accessable. In the past all our tables were MYIASM. Our problems started as we started using more and more INNODB tables. Is there anything special that has to be done to configure MySQL when using INNODB tables? We clearly have a problem but we have no idea where to start looking. Our error logs don't show anything. If anyone has any suggestions, we will be happy to hear them. We are considering hiring a consultant who is an expert in MySQL. We are in Israel and we are open to suggestions. Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il 08-9343036 -- MySQL General
RE: problems with INNODB tables
Thanks for your answer. I read http://mysql.rjweb.org/doc.php/memory where it tells you to do one thing if using MYIASM tables and another if using INNODB tables. We are using both. Any suggestions? Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.il 08-9343036 -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Monday, April 23, 2012 9:42 PM To: Andrés Tello; Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: RE: problems with INNODB tables Check your memory usage according to http://mysql.rjweb.org/doc.php/memory -Original Message- From: Andrés Tello [mailto:mr.crip...@gmail.com] Sent: Monday, April 23, 2012 9:00 AM To: Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: Re: problems with INNODB tables Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql process with pkill -9 -f mysql Y suppose the way drupal is being programed. PHP open and closes database connections each time a webpage with db access is issued. When a php exceution ends and the apache webserver have fullfilled the http request, again, php memory is freed and connections closed... UNLESS:.. you are using a mem cached db connection, wich I doubt it since drupal doens't requiere one, or using persistent connections, again, I doubt it, because persistante database connections aren't recommended to innodb tables... Mysql server by default can handles 100 conections, if you get to thata limit you need to fine tune the number of connections allowed. show full processlist can give you a better idea of what is going on, connections with the sleep status, are open connections with no currently no transacctions... I never use script based stop, I always use mysqladmin -u root -p -h localhost shutdown which properly tells mysql to flush tables and terminate. I can almost bet that you are using Ubuntu... ubuntu had given me sometimes very hard times because of the edgy code they use to use, ext4 last version, and so on... what can you tell us about that? How much amount of memory you have? How much concurrent apache/php users you have? Can you provide more cuantitive data please? Hardware, php version, distro, kernel... Cheers... To start, 100 process is quite a lot, something isn't fine. Each time On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: We are running MySQL version 5.0.45 on a Linux machine. Over the past few months we have been having several problems: 1. Our mysql processes have increased the memory used from about .3% per process to 8% per process 2. We sometimes can have over 100 processes running which brings the machine to its knees and we have to stop and start MySQL in order to kill all the processes. We think that maybe the processes are not finishing normally and are just hanging around. 3. The machine is a web server and in the last few months we are moving over to drupal 7 to build our sites and Drupal 7 requires INNODB tables. Sometimes, when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. In order for the INNODB tables to work, we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and then restart mysql. Otherwise the INNODB tables are not accessable. In the past all our tables were MYIASM. Our problems started as we started using more and more INNODB tables. Is there anything special that has to be done to configure MySQL when using INNODB tables? We clearly have a problem but we have no idea where to start looking. Our error logs don't show anything. If anyone has any suggestions, we will be happy to hear them. We are considering hiring a consultant who is an expert in MySQL. We are in Israel and we are open to suggestions. Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il 08-9343036 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: problems with INNODB tables
switch to innodb... and use one_file_per_table I use both, but I try to use myisam for cataloges. Innodb and myisam are truly different engines, they do things completely different, for example, with myisam you have parameters to configure the size of the memory for the indexes, and several others, meanwhile most of innodb performase is bound to innodb buffer pools, and with the newerst mysql version, yo u can have several innodb buffer pools lowering your mutex wait a lot... Also you can switch from myisam to innodb quickly, for that type of tasks, I do a mysqldump with tab formatted texts because it gives 2 files per table, 1 file with the sql query to create de database and other, tab delimited file with all the data of that table, to be used with mysqlimport For a properly recommendation, we would need to know much more about the system using the database, some statistics... What is the database used for? On Wed, Apr 25, 2012 at 5:14 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: Thanks for your answer. I read http://mysql.rjweb.org/doc.php/memorywhere it tells you to do one thing if using MYIASM tables and another if using INNODB tables. We are using both. Any suggestions? Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.il 08-9343036 -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Monday, April 23, 2012 9:42 PM To: Andrés Tello; Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: RE: problems with INNODB tables Check your memory usage according to http://mysql.rjweb.org/doc.php/memory -Original Message- From: Andrés Tello [mailto:mr.crip...@gmail.com] Sent: Monday, April 23, 2012 9:00 AM To: Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: Re: problems with INNODB tables Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql process with pkill -9 -f mysql Y suppose the way drupal is being programed. PHP open and closes database connections each time a webpage with db access is issued. When a php exceution ends and the apache webserver have fullfilled the http request, again, php memory is freed and connections closed... UNLESS:.. you are using a mem cached db connection, wich I doubt it since drupal doens't requiere one, or using persistent connections, again, I doubt it, because persistante database connections aren't recommended to innodb tables... Mysql server by default can handles 100 conections, if you get to thata limit you need to fine tune the number of connections allowed. show full processlist can give you a better idea of what is going on, connections with the sleep status, are open connections with no currently no transacctions... I never use script based stop, I always use mysqladmin -u root -p -h localhost shutdown which properly tells mysql to flush tables and terminate. I can almost bet that you are using Ubuntu... ubuntu had given me sometimes very hard times because of the edgy code they use to use, ext4 last version, and so on... what can you tell us about that? How much amount of memory you have? How much concurrent apache/php users you have? Can you provide more cuantitive data please? Hardware, php version, distro, kernel... Cheers... To start, 100 process is quite a lot, something isn't fine. Each time On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: We are running MySQL version 5.0.45 on a Linux machine. Over the past few months we have been having several problems: 1. Our mysql processes have increased the memory used from about .3% per process to 8% per process 2. We sometimes can have over 100 processes running which brings the machine to its knees and we have to stop and start MySQL in order to kill all the processes. We think that maybe the processes are not finishing normally and are just hanging around. 3. The machine is a web server and in the last few months we are moving over to drupal 7 to build our sites and Drupal 7 requires INNODB tables. Sometimes, when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. In order for the INNODB tables to work, we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and then restart mysql. Otherwise the INNODB tables are not accessable. In the past all our tables were MYIASM. Our problems started as we started using more and more INNODB tables. Is there anything special that has to be done to configure MySQL when using INNODB tables? We clearly have a problem but we have no idea where to start looking. Our error logs don't show anything. If anyone has
Re: problems with INNODB tables
Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql process with pkill -9 -f mysql Y suppose the way drupal is being programed. PHP open and closes database connections each time a webpage with db access is issued. When a php exceution ends and the apache webserver have fullfilled the http request, again, php memory is freed and connections closed... UNLESS:.. you are using a mem cached db connection, wich I doubt it since drupal doens't requiere one, or using persistent connections, again, I doubt it, because persistante database connections aren't recommended to innodb tables... Mysql server by default can handles 100 conections, if you get to thata limit you need to fine tune the number of connections allowed. show full processlist can give you a better idea of what is going on, connections with the sleep status, are open connections with no currently no transacctions... I never use script based stop, I always use mysqladmin -u root -p -h localhost shutdown which properly tells mysql to flush tables and terminate. I can almost bet that you are using Ubuntu... ubuntu had given me sometimes very hard times because of the edgy code they use to use, ext4 last version, and so on... what can you tell us about that? How much amount of memory you have? How much concurrent apache/php users you have? Can you provide more cuantitive data please? Hardware, php version, distro, kernel... Cheers... To start, 100 process is quite a lot, something isn't fine. Each time On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: We are running MySQL version 5.0.45 on a Linux machine. Over the past few months we have been having several problems: 1. Our mysql processes have increased the memory used from about .3% per process to 8% per process 2. We sometimes can have over 100 processes running which brings the machine to its knees and we have to stop and start MySQL in order to kill all the processes. We think that maybe the processes are not finishing normally and are just hanging around. 3. The machine is a web server and in the last few months we are moving over to drupal 7 to build our sites and Drupal 7 requires INNODB tables. Sometimes, when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. In order for the INNODB tables to work, we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and then restart mysql. Otherwise the INNODB tables are not accessable. In the past all our tables were MYIASM. Our problems started as we started using more and more INNODB tables. Is there anything special that has to be done to configure MySQL when using INNODB tables? We clearly have a problem but we have no idea where to start looking. Our error logs don't show anything. If anyone has any suggestions, we will be happy to hear them. We are considering hiring a consultant who is an expert in MySQL. We are in Israel and we are open to suggestions. Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il 08-9343036
RE: problems with INNODB tables
Check your memory usage according to http://mysql.rjweb.org/doc.php/memory -Original Message- From: Andrés Tello [mailto:mr.crip...@gmail.com] Sent: Monday, April 23, 2012 9:00 AM To: Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: Re: problems with INNODB tables Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql process with pkill -9 -f mysql Y suppose the way drupal is being programed. PHP open and closes database connections each time a webpage with db access is issued. When a php exceution ends and the apache webserver have fullfilled the http request, again, php memory is freed and connections closed... UNLESS:.. you are using a mem cached db connection, wich I doubt it since drupal doens't requiere one, or using persistent connections, again, I doubt it, because persistante database connections aren't recommended to innodb tables... Mysql server by default can handles 100 conections, if you get to thata limit you need to fine tune the number of connections allowed. show full processlist can give you a better idea of what is going on, connections with the sleep status, are open connections with no currently no transacctions... I never use script based stop, I always use mysqladmin -u root -p -h localhost shutdown which properly tells mysql to flush tables and terminate. I can almost bet that you are using Ubuntu... ubuntu had given me sometimes very hard times because of the edgy code they use to use, ext4 last version, and so on... what can you tell us about that? How much amount of memory you have? How much concurrent apache/php users you have? Can you provide more cuantitive data please? Hardware, php version, distro, kernel... Cheers... To start, 100 process is quite a lot, something isn't fine. Each time On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: We are running MySQL version 5.0.45 on a Linux machine. Over the past few months we have been having several problems: 1. Our mysql processes have increased the memory used from about .3% per process to 8% per process 2. We sometimes can have over 100 processes running which brings the machine to its knees and we have to stop and start MySQL in order to kill all the processes. We think that maybe the processes are not finishing normally and are just hanging around. 3. The machine is a web server and in the last few months we are moving over to drupal 7 to build our sites and Drupal 7 requires INNODB tables. Sometimes, when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. In order for the INNODB tables to work, we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and then restart mysql. Otherwise the INNODB tables are not accessable. In the past all our tables were MYIASM. Our problems started as we started using more and more INNODB tables. Is there anything special that has to be done to configure MySQL when using INNODB tables? We clearly have a problem but we have no idea where to start looking. Our error logs don't show anything. If anyone has any suggestions, we will be happy to hear them. We are considering hiring a consultant who is an expert in MySQL. We are in Israel and we are open to suggestions. Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il 08-9343036 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
replication problems with InnoDB tables...?
So I have been reading a lot lately about replication but I can't find a good summary/explanation of MySQL's replication features regarding InnoDB tables and how their transactional properties may cause problems in replication. I have seen suggestions that InnoDB tables should be converted to MyISAM tables before being loaded and then converted back to InnoDB tables. I have also seen many others here confused as to what problems there are between InnoDB and replication. So, could some friendly MySQL guru please explain to me, and many others, what the replication issues with InnoDB tables really are...please? The docs state that InnoDB is replication safe yet there seem to be so many problems with them when updates fail, roll back or are partially executed because some constraint conflict that causes replication to stop. Sincerely Kris Karski _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication problems with InnoDB tables...?
Kris, - Original Message - From: Krzysztof Karski [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, January 21, 2003 11:35 AM Subject: replication problems with InnoDB tables...? So I have been reading a lot lately about replication but I can't find a good summary/explanation of MySQL's replication features regarding InnoDB tables and how their transactional properties may cause problems in replication. transactions should not cause problems in replication. However, a relevant bug fix is this: MySQL/InnoDB-4.0.6, December 19, 2002 ... Fixed a bug: in replication issuing SLAVE STOP in the middle of a multi-statement transaction could cause that SLAVE START would only perform part of the transaction. A similar error could occur if the slave crashed and was restarted. I have seen suggestions that InnoDB tables should be converted to MyISAM tables before being loaded and then converted back to InnoDB tables. LOAD TABLE FROM MASTER only works for MyISAM tables. To set up a new replication slave you should use a cold backup, or a hot backup made with the non-free InnoDB tool. I have also seen many others here confused as to what problems there are between InnoDB and replication. So, could some friendly MySQL guru please explain to me, and many others, what the replication issues with InnoDB tables really are...please? The docs state that InnoDB is replication safe yet there seem to be so many problems with them when updates fail, roll back or are partially executed because some constraint conflict that causes replication to stop. A relevant bug fix: MySQL/InnoDB-4.0.6, December 19, 2002 ... Fixed two bugs introduced in 4.0.4: in AUTO_INCREMENT, REPLACE could cause the counter to be left 1 too low. A deadlock or a lock wait timeout could cause the same problem. MySQL does not write to the binlog SQL statements which fail. It does not write to the binlog any SQL statements from a transaction which is rolled back. Thus, these are never replicated, and in the slave there should not be any constraint violation errors. Sincerely Kris Karski Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
