分析 MySQL 表出现错误 1040 - 连接数过多
为什么当我运行时
ANALYZE TABLE table_name_here
MySQL 服务器开始发出此错误：
1040 - Too much links
我已经通过 PHPMyAdmin 运行了这个错误。顺便说一句
，该表包含超过 1500 万行数据。有办法解决这个问题吗？
MySQLTuner 结果：
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.91-rs-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 10G (Tables: 192)
[!!] Total fragmented tables: 14
-------- Security Recommendations -------------------------------------------
ERROR 1142 (42000) at line 1: SELECT command denied
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 21m 37s (134K q [103.756 qps], 982 conn, TX: 267M, RX: 20M)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 1.2G global + 22.2M per thread (120 max threads)
[!!] Maximum possible memory usage: 3.8G (99% of installed RAM)
[OK] Slow queries: 0% (4/134K)
[OK] Highest usage of available connections: 14% (17/120)
[OK] Key buffer size / total MyISAM indexes: 1.0G/2.9G
[OK] Key buffer hit rate: 97.7% (1M cached / 25K reads)
[OK] Query cache efficiency: 72.7% (92K cached / 127K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[!!] Joins performed without indexes: 492
[!!] Temporary tables created on disk: 44% (490 on disk / 1K total)
[OK] Thread cache hit rate: 98% (17 created / 982 connections)
[OK] Table cache hit rate: 97% (262 open / 268 opened)
[OK] Open file limit used: 0% (463/65K)
[OK] Table locks acquired immediately: 99% (78K immediate / 78K locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
join_buffer_size (> 10.0M, or always use indexes with joins)
tmp_table_size (> 192M)
max_heap_table_size (> 192M)
最初我的密钥缓冲区大小仅设置为 64MB。当我将密钥缓冲区大小设置为 1GB 时，运行该命令时我的最大连接用户数仅达到 17 个峰值。当它仅设置为 64MB 时，它总是达到允许的最大连接用户数。我无法将其设置为更高的值，因为我的服务器仅限于 4GB 内存。
Why is it that when I run
ANALYZE TABLE table_name_here
The MySQL server starts giving out this error:
1040 - Too many connections
I've run this thru PHPMyAdmin btw..
And the table contains over 15 million rows of data. Is there a way to fix this?
MySQLTuner results:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.91-rs-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 10G (Tables: 192)
[!!] Total fragmented tables: 14
-------- Security Recommendations -------------------------------------------
ERROR 1142 (42000) at line 1: SELECT command denied
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 21m 37s (134K q [103.756 qps], 982 conn, TX: 267M, RX: 20M)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 1.2G global + 22.2M per thread (120 max threads)
[!!] Maximum possible memory usage: 3.8G (99% of installed RAM)
[OK] Slow queries: 0% (4/134K)
[OK] Highest usage of available connections: 14% (17/120)
[OK] Key buffer size / total MyISAM indexes: 1.0G/2.9G
[OK] Key buffer hit rate: 97.7% (1M cached / 25K reads)
[OK] Query cache efficiency: 72.7% (92K cached / 127K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[!!] Joins performed without indexes: 492
[!!] Temporary tables created on disk: 44% (490 on disk / 1K total)
[OK] Thread cache hit rate: 98% (17 created / 982 connections)
[OK] Table cache hit rate: 97% (262 open / 268 opened)
[OK] Open file limit used: 0% (463/65K)
[OK] Table locks acquired immediately: 99% (78K immediate / 78K locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
join_buffer_size (> 10.0M, or always use indexes with joins)
tmp_table_size (> 192M)
max_heap_table_size (> 192M)
Originally my key buffer size was only set to 64MB. When I set the key buffer size to 1GB my maxed number of connected user only peaked at 17 while running that command. And when it was only set to 64MB it was always reaching the maximum allowed connected user. I can't set this to anything higher as my server is only limited to 4GB ram.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我将我的评论移至此处的完整答案。这是一个 MySQL 配置问题，您可能会在服务器故障上得到更好的答案。
ANALYZE TABLE 执行的两件事可能会导致服务器出现问题。首先，它是一个需要很长时间才能运行的命令。根据您对问题的过于简短的描述，我猜测您的应用程序正在与数据库进行很多短连接。由于 ANALYZE 需要很长时间，因此运行时使用的连接被锁定。如果应用程序正在使用连接池或对其可以建立的连接数有特定于应用程序的限制，我会将其设置为比 MySQL 连接限制少 3 个，以允许您执行这样的工作。
其次，ANALYZE TABLE，为MyISAM表重建索引。这意味着MySQL尝试将整个表加载到内存中（或读取整个表）以重新构建索引。这会对表发出表锁并占用大量内存，从而干扰 MySQL 执行其他工作（例如运行应用程序）的能力。
我真正的建议是转向 InnoDB 而不是 MyISAM。它在管理内存、索引和数据方面做得更好。对于您正在处理的表大小来说，它比 MyISAM 更快，而且更少的麻烦。
I'm moving my comment to a full answer here. This is a MySQL configuration issue and you may get a better answer on server fault.
The ANALYZE TABLE does two things that would cause your server problems. First is it is a command that takes a long time to run. From your too brief description of the problem I guessed that your application is doing very many short connections to the database. Since the ANALYZE takes a long time, the connection used while this is running is locked down. If the application is using connection pooling or has a application specific limit to number of connections it can make, I would set this to 3 short of the MySQL connection limit to allow you do perform work like this.
Second, ANALYZE TABLE, for MyISAM table rebuilds the indexes. Which means MySQL attempts to load the entire table into memory (or read the entire table) to build the indexes anew. This issues table locks against the table and takes a huge chunk of memory, which interferes with MySQL's ability to do other work (like run your application).
My real suggestion would be move to InnoDB rather than MyISAM. It does a much better job of managing memory, indexes, and data. It's faster than MyISAM for the table sizes you are dealing with and fewer headaches.