等待MySQL表上的表级别锁定 [英] Waiting for table level lock on a MySQL table

问题描述

最近几天，随机地，我的网站变得非常慢.我开始尽力调查.我看到MySQL进程正在使用服务器可用内存的85％-95％(我也应该升级我的内存吗?).

The last few days, at random times my 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?).

我检查了我的MySQL进程日志，并发现了一个庞大的查询列表:

I checked my MySQL process log, and I noticed a huge list of queries with:

等待表级锁定

Waiting for table level lock

但是我还注意到，所有这些带有表级锁定"的查询都是那些与我的表有关的查询，称为users.

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.

我还有20个具有恒定查询的表，但我在列表中没有看到它们.所以我猜问题出在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?

我也跑了这个

SHOW VARIABLES LIKE 'query_cache%';

这是什么原因

query_cache_limit
1048576
query_cache_min_res_unit
4096
query_cache_size
33554432
query_cache_type
ON
query_cache_wlock_invalidate
OFF

请让我知道我可以做些什么来改善我的数据库/mysql.

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

这是进程列表:

   | 228 | db_user | localhost | db_db| Query          |    5 | Waiting for table level lock | SELECT count(*) FROM users WHERE createtime>'1396411200' OR createtime='1396411200'                  |
    | 229 | db_user | localhost | db_db| Query          |    4 | Waiting for table level lock | UPDATE users SET upline_clicks=upline_clicks+'1', upline_earnings=upline_earnings+'0.0000' WHERE use |
| 203 | db_user | localhost | db_db| Query          |    6 | Waiting for table level lock | SELECT SUM(cashedout) FROM users                                                                     |
| 204 | db_user | localhost | db_db| Query          |    4 | Waiting for table level lock | UPDATE users SET upline_clicks=upline_clicks+'1', upline_earnings=upline_earnings+'0.0000' WHERE use |
| 205 | db_user | localhost | db_db| Query          |    1 | Waiting for table level lock | SELECT * FROM users WHERE id='12055'                                                                 |
| 206 | db_user | localhost | db_db| Query          |    2 | Waiting for table level lock | SELECT * FROM users WHERE id='22530'                                                                 
| 197 | db_user | localhost | db_db| Query          |    3 | Waiting for table level lock | SELECT * FROM `users` WHERE `username` = 'ptc4life123' LIMIT 1                                       |
| 200 | db_user | localhost | db_db| Query          |    3 | Waiting for table level lock | UPDATE users SET upline_clicks=upline_clicks+'1', upline_earnings=upline_earnings+'0.0050' WHERE use |

基本上，这就是所有锁定过程的样子.

This is basically what all the locked processes looks like.

推荐答案

在其中一个较慢的时间段内，运行以下命令:

During one of the slow periods, run this command:

show processlist;

这将向您显示正在运行的实际SQL命令，以及在添加索引方面要查找的位置.如果可能，请发布运行时间最长的SQL.

This will show you the actual SQL commands that are running, and the places to look for in terms of adding indexes. Post the longest running SQL if possible.

添加索引将如下所示:

 ALTER TABLE MYTABLE ADD INDEX idx_columnname (COLUMN_NAME ASC) ;

但是您要小心，不要在生产期间这样做.乍一看，您想对用户表中的ID和用户名列进行操作.

But you want to be careful not to do that during a production period. At first glance, you want to do that on ID and username columns in the user table.

这篇关于等待MySQL表上的表级别锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！