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 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.il<mailto:malki.cymbali...@weizmann.ac.il > > > > > > > 08-9343036 > > > > > > >
