php - Mysql my.cnf 设置为具有 8GB RAM、2 个处理器和 1000 个并发连接的服务器

Question

我有一台带有 2 个处理器和 8GB 内存 + 8GB 交换内存的服务器。

我有这个“my.cnf”配置:

skip-looking
skip-name-resolve
table_cache = 2048 ( max_connections * a query that joins x tables, ex: 500 * 4 = 2000) - seems to be ok (source: http://serverfault.com/a/78810/201894 )
thread_cache_size = 32 (detailed here: http://serverfault.com/a/408936/201894 - should be greater than 500? Read also: http://www.epigroove.com/blog/optimize-mysql-the-thread-cache)
back_log = 100 ( http://www.percona.com/blog/2012/01/06/mysql-high-number-connections-per-secon/ , should be at least the same as max_connections ? )
max_connect_errors = 10000 ( If there is more than this number of interrupted connections from a host this host will be blocked for further connections. You can unblock a host with the command FLUSH HOSTS. The value seems to be good ? )
open-files=10000 ( use the command: ulimit -n to see the system's limits, seems to be ok? maybe a little too high?)
interactive_timeout = 400 ( good enough, because standard is: 28800 = 8 hours)
wait_timeout = 300 ( The number of seconds the server waits for activity on a connection before closing it. Standard value is 28800. - Should be reduced !? If yes, what about complex db queries? )
max_connections = 500 ( I want to raise it to 1000 )
long_query_time = 2 ( Above option will set the slow or long query limit as 2 seconds, which means MySQL will capture and log all SQL queries that took longer than 5 seconds to execute and finish running to the log file. I guess it's ok.)
log-queries-not-using-indexes ( http://stackoverflow.com/questions/6428532/log-queries-not-using-index-mysql )
max_allowed_packet = 128M ( The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries. Value seems to be good. )
tmp_table_size = 256M ( should be ram / max_connections , ex: 8gb ram / 500 = 13.6 . So should be change this value to a lower one?)
max_heap_table_size = 256M ( same as above )
query_cache_size = 23M ( is the maximum amount of data that may be stored in the cache and. This value could/should be bigger? If the value it's too big, there will be performance issues. ).
query_cache_limit = 6M ( is the maximum size of a single resultset in the cache. This value seems to be ok? )
sort_bufer_size = 4M (should be lower? 2M? )
read_buffer_size = 4M ( should be lower? 2M? )
read_rnd_buffer_size = 16M (I've read somewhere that if it's set to a big value it is good for big queries. Still, I guess the value is too big. ) 
join_buffer_size = 2M ( Is it too big? What about 1M/256k or 128k? )
key_buffer_size = 128M ( Could be 64M, but I guess that the value is good)
innodb_log_file_size = 100M
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 20M
innodb_support_xa = 0

symbolic-links = 0

这是 500 个并发连接的 my.cnf 配置，我想更改它以允许最多 1000 个并发用户。

我添加了与几乎所有设置相关的注释。请告诉我应该如何更改配置才能使 mysql 服务器消耗低于 8GB 的​​内存。

Answer 1

你为什么不使用perl mysqltunner并检查一下你需要改进什么？