Lots of locked state mysql inserts - slow updates and selects

el930692 used Ask the Experts™
For some reason all the time when on my processes send selects and updates to a mysql table I see a lot of  inserts to the same table in the locked sate.

That makes the initial selects and updates very slow like 40 seconds to a minute for every select or update.

How can I fix this?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You probably are using the default storage engine type MyISAM which tends to do a poor job with locking tables from other users. Turn your tables into InnoDB which ensures much better locking management (e.g. lock only rows being updated, don't lock entire table when doing a query on it, etc.)

Say your table is called "fubar", here is a simple command to turn it into InnoDB (assuming you have InnoDB configured for your MySQL server):

  alter table fubar engine=innodb


Can you have mixed table types like MyISAM and InnoDB?
The majority of my tables are MyISAM.
You can indeed have a mix. The important thing with InnoDB -- to ensure good
performance -- is to make sure that the innodb_buffer_pool_size system
configuration variable is set suitably high enough to take advantage of
high speed memory which makes up the RAM in your server.

Example, if you have a 4 gig RAM server, have a line like this in the my.cnf
file (leaving 1 gig for the operating system and 1 gig for other stuff):
    innodb_buffer_pool_size = 2G


Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial