Only InnoDB databases locking up

Question

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

Answer 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

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

• innodb_buffer_pool_size : 400M is your setting right now. If the DB Server is dedicated to just the database, you need to scale this up to 60-75% of the installed RAM.
• innodb_log_file_size : For MySQL 5.6 it's 48M. Before MySQL 5.6, it's 5M.
• innodb_log_buffer_size : This is 8M by default. A larger log buffer makes for reduced I/O
• innodb_read_io_threads and innodb_write_io_threads are both 4 by default (Percona Server's default is 8). Increase this for more CPU engagement (See my old post Possible to make MySQL use more than one core?)

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?