mysql - Waiting for table level lock on a table

Question

I have a problem with my website.. The last few days, at random times the website has become very slow. I started to investigate the best I could. I saw that the MySQL process was using 85 - 95% of the available memory of my server. (Should I upgrade my memory also?)

I checked my MySQL process log, and I noticed a huge list of queries with: Waiting for table level lock

But what I also noticed, what that ALL of these queries with "table level lock", was only queries which had something to do with my table called "users".

I have 20 other tables, with constant queries, but I don't see them on the list.. So I guess the problem is with the users table?

I want to know how I can improve the table, and eventually remove the table level lock?

I also ran this:

SHOW VARIABLES LIKE 'query_cache%';

Which resulted in this:

query_cache_limit
1048576
query_cache_min_res_unit
4096
query_cache_size
33554432
query_cache_type
ON
query_cache_wlock_invalidate
OFF

Please let me know what I can do to improve my database/mysql.

Answer 1

Perhaps the users table is type/storage engine MyISAM and the others are InnoDB or perhaps the other tables aren't subject to many INSERT, UPDATE, or DELETE queries (IIRC, locking isn't used for SELECT).

See also:

• way to prevent queries from waiting for table level lock
• https://stackoverflow.com/questions/6543624/what-is-waiting-for-table-level-lock
• https://dev.mysql.com/doc/refman/5.0/en/table-locking.html