Posted by mmilano on April 29, 2010 at 5:02pm
After reading djudd's post, I went and found the video of kbahey's drupalcon session. Great stuff!
It was mentioned to set tables to innodb if they are experiencing locks. How do I monitor for locks?
Comments
SHOW PROCESSLIST
For MyISAM tables there will be a lot of processes in Locked state (SHOW PROCESSLIST command).
Sometimes it's hard to find the process which holds the lock, since there is no SQL command for that, but you can use debug command for that (prints information about locks into the server's error_log).
Example: mysqladmin debug
There will be a lot of info, but near the end you should find the process which holds the lock.
Article
Good place to start in terms of finding out what tables lock
http://tag1consulting.com/MySQL_Monitoring_and_Tuning
In my experience it's not
In my experience it's not worth the time picking individual tables. If you've got free memory, I'd recommend switching everything to InnoDB and increasing the InnoDB buffer pool enough to fit your whole dataset in memory. The performance improvements over MyISAM is significant.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Thanks for the info and
Thanks for the info and tools.
This really isn't critical as I'm just finding my way around the tools, but this confused me a bit.
I run mysqladmin debug, I get the following in the mysqld log:
Current locks:
lock: 0x78fb9a0:
lock: 0x78f9460:
lock: 0x78f6ad0:
(then a bunch more of these lock lines)
but when I run show processlist, I just get the one row of my show processlist command.
Are the current locks from the debug anything to be concerned about if there's no locks appearing in the show processlist?
Mike Milano
Perhaps the user that you are
Perhaps the user that you are running SHOW PROCESSLIST with doesn't have permissions to view all connections on the server. Try running as the MySQL root user.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Usually you need debug if you
Usually you need debug if you want to kill process which holds the lock for a long time. In order to kill it - you need to know which process holds the lock.
SHOW PROCESSLIST can help you to spot the problem in the first place.
By the way you can configure MyISAM to support concurrent INSERT and SELECT statements. To do that you can set concurrent_insert variable to 2 (starting MySQL 5.0.6). concurrent_insert = 2 enables concurrent inserts for all MyISAM tables, even those that have holes in the middle.
If you have a small number of DELETE or UPDATE statements + big number of SELECTS and concurrent INSERTS - MyISAM maybe a good fit for you.
Basically you have to measure your workload first.
If you need more details about mysql locks, you can find them in here:
http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html