php - Mysql配置优化建议

Question

我有8 GB RAM和8核的服务器，运行一个网站，我已经完成了mysql配置设置，在该服务器上正常工作，但在某些时候(24小时内1到2次)，它需要很大的负载。运行 CentOS 的操作系统

需要建议。

[mysqld]
connection settings
max_connect_errors=10
max_connections=500
max_user_connections=700
wait_timeout=60
connect_timeout=10
interactive_timeout=60
innodb_buffer_pool_size=6GB

cache settings
query_cache_limit=128M
query_cache_size=10M
query_cache_type=1
table_open_cache=5000
thread_cache_size=250

buffer sizes
key_buffer=1288M
sort_buffer_size=20M
read_buffer_size=20M
join_buffer_size=20M


tmpdir / temp table sizes
tmp_table_size=256M
max_heap_table_size=256M

misc. settings
default-storage-engine = MYISAM
datadir=/var/lib/mysql
skip-external-locking

server-id = 1
open-files-limit = 96384
max_allowed_packet = 300M

innodb settings
innodb_data_file_path = ibdata1:10M:autoextend
innodb_thread_concurrency = 10
innodb_buffer_pool_size = 1000M
innodb_log_buffer_size = 500M
innodb_file_per_table = 1

[mysqld_safe]
open-files-limit = 16384

[mysqldump]
quick
max_allowed_packet=600M

[myisamchk]
key_buffer = 128M
sort_buffer = 128M
read_buffer = 128M
write_buffer = 16M

[mysql]
no-auto-rehash

Answer 1

现在，您为 mysql 分配的 RAM 比服务器上的物理内存还要多。请检查我下面针对变量的评论-

    [mysqld]

    #connection setting
    max_connect_errors=10
    max_connections=500 #each connection consumes server resources, so keep how many required.
    max_user_connections=700 #It should be always less than max_connections,even you should remove it.
    wait_timeout=60
    connect_timeout=10
    interactive_timeout=60
    innodb_buffer_pool_size=6GB #duplicate as already mentioned below

    #cache setting
    query_cache_limit=128M #this should be less than query_cache_size, 2M will be sufficient we should not cache heavy queries as it will slow the process.
    query_cache_size=10M #can keep 20M or increase as per requirement.
    query_cache_type=1
    table_open_cache=5000
    thread_cache_size=250 --even not impact much to performance but still should not more than 100.

    #buffer sizes
    key_buffer=1288M # keep it 20M as your server is innodb
    sort_buffer_size=20M #this variable consumes server resources for each connection, 2M should be sufficient but can increase based on your queries.
    read_buffer_size=20M #this variable consumes server resources for each connection, 2M should be sufficient but can increase based on your queries.
    join_buffer_size=20M #this variable consumes server resources for each connection, 2M should be sufficient but can increase based on your queries.

    #tmpdir / temp table sizes
    tmp_table_size=256M
    max_heap_table_size=256M

    #misc. settings
    default-storage-engine = MYISAM # change to innoDB
    datadir=/var/lib/mysql
    skip-external-locking

    server-id = 1
    open-files-limit = 96384
    max_allowed_packet = 300M # You can reduce it to 64M or how much really required, fine if your application is sending large packet. 

    #innodb settings
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_thread_concurrency = 0 #mysql can utilize it better read this > http://dba.stackexchange.com/questions/5666/possible-to-make-mysql-use-more-than-one-core
    innodb_buffer_pool_size = 1000M # It should be how much you can increase in innodb upto 80% of RAM.
    innodb_log_buffer_size = 500M #8M is fine can increase as per server write in your environment but not 500M, as consumes server resources based on no of connections.
    innodb_file_per_table = 1

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

    [mysqldump]
    quick

注意:您可以保留除[mysqld]、[mysqld_Safe]和[mysqldump]之外的其他部分

最后您可以使用以下配置测试您的服务器-

[mysqld]

#connection setting
max_connect_errors=10
max_connections=400
wait_timeout=60
connect_timeout=10
interactive_timeout=60

#cache setting
query_cache_limit=2M
query_cache_size=50M
query_cache_type=1
table_open_cache=5000
thread_cache_size=100

#buffer sizes
key_buffer_size=20M
sort_buffer_size=2M
read_buffer_size=2M
join_buffer_size=2M

#tmpdir / temp table sizes
tmp_table_size=256M
max_heap_table_size=256M

#misc. settings
default-storage-engine = innoDB
datadir=/var/lib/mysql
skip-external-locking

server-id = 1
open-files-limit = 65535
max_allowed_packet = 64M 

#innodb settings
innodb_data_file_path = ibdata1:10M:autoextend
innodb_thread_concurrency = 0
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 8M 
innodb_file_per_table = 1

[mysqldump]
quick

注意:如果您可以减少total_connections，那么您可以增加innodb_buffer_pool_size并可以获得更好的性能。如果有 250 个用户，您可以将 innodb_buffer_pool_size 从 4GB 增加到 5GB。

首先您需要测试您的服务器，并且可以根据要求/性能进行+/-调整。