MySQL Forums
Forum List  »  MyISAM

evaluating table locking
Posted by: Evgeny Anisiforov
Date: May 19, 2008 10:32AM

I have a database using myisam as storage engine. The database is beeing using to make a lot of selects(with joins), but there are also a lot of update-statements beeing executed. I would say that updates are at least 25% of all statements.

updates change in the most cases only one line using primary key id.
selects are randomly sorted (sort by rand()), choose 5 of about 1000-2000 lines
(limit 5).

the server has often a load of 4.0-5.0 (linix top command) on two-core cpu.
at the moment the performance is acceptable, but i did have performance problems on a smaller server, and i think i would get them again, if my website will continue to grow.

today i've read about myisam and figured out, that myisam locks the whole table, when executing a statement.

so my question is:
would it be usefull to change to InnoDB? I don't need transactions, but its because of the row level locking.

Is there a way to get some statistics about how much the locking influences the performance. i tried SHOW PROCESSLIST and see that some queries are beeeing locked, but can not figure out, how much performance it really costs.

someone an idea?

Options: ReplyQuote

Written By
evaluating table locking
May 19, 2008 10:32AM
May 21, 2008 09:04PM
May 30, 2008 08:41AM
August 30, 2008 10:55AM
January 21, 2009 03:29AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.