MySQL優化（mysql ERROR 1040 (00000): Too many connections）

mysql

afgn 2011-12-29 13:40:26 ‧ 18038 瀏覽

分享至

請教一下各位,

小弟公司的 MySQL資料庫會出現 mysql ERROR 1040 (00000): Too many connections

我有用過一些調校工具，像是 tuning-primer.sh ，可是還是怪怪的...

我們公司主機環境：

CPU： Intel(R) Xeon(R) CPU E5520 @ 2.27GHz (16核心)
Memory： 12GB

列出 my.cnf 供大家參考：

[client]
host = 127.0.0.1
port = 3306
socket = /tmp/mysql.sock

[mysqld]
log-bin = /var/log/mysql/binlog.log
log-error = /var/log/mysql/error.log
log-slow-queries = /var/log/mysql/slow.log
port = 3306
socket = /tmp/mysql.sock
datadir = /var/lib/mysql
user=mysql
skip-locking
set-variable = key_buffer=64M
set-variable = max_allowed_packet=50M
set-variable = max_connections=1000
set-variable = max_connect_errors=800
set-variable = join_buffer_size=2M
set-variable = table_definition_cache=10240
set-variable = back_log=65535
set-variable = table_cache=10240
set-variable = tmp_table_size=256M
set-variable = max_heap_table_size=256M
set-variable = max_tmp_tables=32
set-variable = sort_buffer_size=24M
set-variable = net_buffer_length=16K
set-variable = myisam_sort_buffer_size=96M
read_buffer_size = 8M
read_rnd_buffer_size = 100M
query_cache_size = 128MB
query_cache_type = 1
query_cache_limit = 8MB
query_cache_min_res_unit = 1874
thread_concurrency = 32
thread_cache_size = 8192
thread_stack = 256K
wait_timeout = 28800
long_query_time = 3
server-id = 1

[mysql]
no-auto-rehash

[myisamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout
set-variable = log_slow_queries = ON
set-variable = log_slow_queries = /tmp/mysql.log
set-variable = long_query_time = 5

[mysqld_safe]
log-error = /var/log/mysql/mysqld.log
pid-file = /var/lib/mysql/mysql.pid

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

ghost

iT邦新手 2 級 ‧ 2011-12-29 16:35:48

最佳解答

看起來你好像還在用很舊版本在用的參數.
應該改一改了(難道你還在用 MySQL 5.0 以前的版本?)

你的 sql 是在做什麼用的?使用者會有多少人?
自己測測看,把這個設大一點

afgn提到：
max_connections=1000

這個設小一點

wait_timeout = 28800

& interactive_timeout

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

afgn iT邦新手 5 級 ‧ 2011-12-30 10:00:08 檢舉

MySQL Version 5.1.30-enterprise-gpl-log x86_64

這是我現在用的版本 :)

max_connections 大是大多少? :) 我們網站用的是 Apache 2.2 (兩台), 平常每分鐘流量約 150人 (兩台加總).

wait_timeout = 28800 應該改多小比較合適? :)

ghost iT邦新手 2 級 ‧ 2011-12-30 22:56:57 檢舉

一分鐘 150 人,那五分鐘就 750 人了.如果一個人要開一個 connection. 你一千個夠撐幾分鐘?

同理,如果 wait_timeout 太久,開了都不關,那 connection 就是一直開上去.
所以你覺得應該多久把沒用的 connection 給 close 掉,比較不會浪費 connection?

ghost iT邦新手 2 級 ‧ 2011-12-30 22:58:42 檢舉

對了.
set-variable 不是正規的 my.cnf 參數.
少這樣用.

afgn iT邦新手 5 級 ‧ 2012-01-02 10:08:17 檢舉

我已經把 max_connections 改為300, 但 wait_timeout 預設值是 28800 (8小時??), 請問建議改多少比較好?

登入發表回應

wiseguy

iT邦超人 1 級 ‧ 2011-12-29 17:22:14

你要先弄清楚 mysql 各個參數是做什麼的，亂設一通的話，效能不升反降。
tuning-primer.sh 已告訴你，你這份設定的記憶體最大會用到 131 G，而且 mysql 最大曾使用到 29 G 記憶體，遠遠超過這台主機所裝的實體記憶體 12 G。這樣子效能怎麼會好？tuning-primer.sh 告訴你這設定使用記憶體超過實體容量的 90%，這效能鐵定不良的。因為一定開始在使用 swap 虛擬記憶體了。

mysql 的參數中，有關 buffer 的部份，分為 global 的與 per connection 的。所以所需記憶體大概是 GLOBAL + max_connection * PER_CONN 的大小。

你的設定裡面，
join_buffer_size=2M
sort_buffer_size=24M
myisam_sort_buffer_size=96M
read_buffer_size = 8M
read_rnd_buffer_size = 100M
這些都是 by connection 的參數。你想想看，你的程式中有一個 select SQL 就會回傳好幾 MB 的 recordset 嗎？你設定這麼大，是不是都浪費掉了？

雖然主機錯誤是 Too many connections 也就是 max_connections 不夠。但是如果程式使用 pconnect 去連，而 wait_timeout 設太長，就會一直等到 wait_timeout 到了之後，connection 才會回收使用。因此 wait_timeout 必須設定短一點，比如 wait_timeout = 300 (五分鐘) 甚至可以更短。
之後再觀察線上人數，到底有多少，需不需要再把 1000 往上調。tuning-primer.sh 應該會顯示最大連線數。

回應 8
分享
檢舉

看更多先前的回應...收起先前的回應...

afgn iT邦新手 5 級 ‧ 2012-01-02 09:44:37 檢舉

我已把 max_connections= 從 1000改為 300

[root@www /]# perl mysqltuner.pl

MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
Bug reports, feature requests, and downloads at http://mysqltuner.com/
Run with '--help' for additional options and output filtering

-------- General Statistics
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.30-enterprise-gpl-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 708M (Tables: 132)
[!!] Total fragmented tables: 6

-------- Security Recommendations
[!!] User '@localhost' has no password set.
[!!] User '@www.houseweb.com.tw' has no password set.
[!!] User 'mysqltop@%' has no password set.
[!!] User 'mysqltop@localhost' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@www.houseweb.com.tw' has no password set.

afgn iT邦新手 5 級 ‧ 2012-01-02 09:45:26 檢舉

-------- Performance Metrics
[--] Up for: 6m 34s (324K q [824.678 qps], 7K conn, TX: 586M, RX: 26M)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 448.0M global + 134.2M per thread (300 max threads)
[!!] Maximum possible memory usage: 39.8G (339% of installed RAM)
[OK] Slow queries: 0% (0/324K)
[OK] Highest usage of available connections: 33% (99/300)
[OK] Key buffer size / total MyISAM indexes: 64.0M/105.5M
[OK] Key buffer hit rate: 99.8% (6M cached / 10K reads)
[OK] Query cache efficiency: 70.5% (201K cached / 285K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 11K sorts)
[!!] Temporary tables created on disk: 34% (4K on disk / 12K total)
[OK] Thread cache hit rate: 98% (99 created / 7K connections)
[OK] Table cache hit rate: 40% (216 open / 532 opened)
[OK] Open file limit used: 1% (374/20K)
[OK] Table locks acquired immediately: 97% (97K immediate / 100K locks)

afgn iT邦新手 5 級 ‧ 2012-01-02 09:45:41 檢舉

-------- 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
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses

afgn iT邦新手 5 級 ‧ 2012-01-02 09:48:58 檢舉

[root@www /]# ./tuning-primer.sh

-- MYSQL PERFORMANCE TUNING PRIMER --

By: Matthew Montgomery -

MySQL Version 5.1.30-enterprise-gpl-log x86_64

Uptime = 0 days 0 hrs 21 min 53 sec
Avg. qps = 745
Total Questions = 978466
Threads Connected = 72

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 3.000000 sec.
You have 0 out of 978509 that take longer than 3.000000 sec. to complete
Your long_query_time seems to be fine

afgn iT邦新手 5 級 ‧ 2012-01-02 09:55:29 檢舉

BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
Setting expire_logs_days will allow you to remove old binary logs automatically
Binlog sync is not enabled, you could lose binlog records during a server crash

WORKER THREADS
Current thread_cache_size = 8192
Current threads_cached = 35
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 300
Current threads_connected = 82
Historic max_used_connections = 117
The number of used connections is 39% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 15.53 G
Configured Max Per-thread Buffers : 39.34 G
Configured Max Global Buffers : 192 M
Configured Max Memory Limit : 39.52 G
Physical Memory : 11.72 G

Max memory limit exceeds 90% of physical memory

afgn iT邦新手 5 級 ‧ 2012-01-02 09:56:22 檢舉

KEY BUFFER
Current MyISAM index space = 105 M
Current key_buffer_size = 64 M
Key cache miss rate is 1 : 1687
Key buffer free ratio = 61 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 42 M
Current query_cache_limit = 8 M
Current Query cache Memory fill ratio = 33.26 %
Current query_cache_min_res_unit = 1 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 24 M
Current read_rnd_buffer_size = 100 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 2.00 M
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly

OPEN FILES LIMIT
Current open_files_limit = 20790 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

afgn iT邦新手 5 級 ‧ 2012-01-02 09:57:31 檢舉

TABLE CACHE
Current table_open_cache = 10240 tables
Current table_definition_cache = 5120 tables
You have a total of 155 tables
You have 225 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 256 M
Current tmp_table_size = 256 M
Of 29955 temp tables, 34% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 8 M
Current table scan ratio = 363 : 1
read_buffer_size seems to be fine

afgn iT邦新手 5 級 ‧ 2012-01-02 09:57:44 檢舉

TABLE LOCKING
Current Lock Wait ratio = 1 : 27
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

登入發表回應