2

I'm trying to troubleshoot my MYSQL in a LAMP and have an interesting situation. At first I thought MYSQL itself was locking up, but it turns out to be ONLY innodb databases that are locking up.

These sites are WordPress. I converted one to Myisam and now when the lockups happen that one is still good, so in my mind this rules out site specific issues.

Any suggestions?

This is my my.cnf

[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
query_cache_size=128M
tmp_table_size=32M
thread_cache_size=4
innodb_buffer_pool_size=400M
key_buffer_size=60M
max_heap_table_size=32M
tmp_table_size=64M
join_buffer_size=1M

migrated from serverfault.com Dec 26 '14 at 2:49

This question came from our site for system and network administrators.

  • Run mytop or mysqladmin processlist in a while loop to try and catch the queries in the act. – Stefan Lasiewski Dec 25 '14 at 21:42
1

InnoDB

InnoDB has to lock up because you do not have it configured properly.

You have to realize the framework behind the InnoDB

Here is a Pictorial Representation of InnoDB from Percona CTO Vadim Tkachenko

InnoDB Architecture

Looking at your my.cnf, there are many settings you do not have enabled or is underconfigured

The list can go on. The main point is that you cannot leave InnoDB in its default settings and expect good performance. You must configure InnoDB properly. Left unconfigured, oldver version of MySQL run better than newer versions (As an example, see my old post Why mysql 5.5 slower than 5.1 (linux,using mysqlslap))

You can read many posts in the DBA StackExchange from me (just another MySQL DBA) and a whole host of others MySQL and Percona Experts.

MyISAM

As regards MyISAM, you have key_buffer_size=60M. That's probably OK if just one Wordpress site is all MyISAM. If you really want to know how to set up caching for InnoDB and MyISAM, please read my old post What are the main differences between InnoDB and MyISAM?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.