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.
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).