Problemas com my.cnf e php.ini - Usando o sintonizador mysql

Question

Meu servidor estava funcionando bem até uma atualização recente no Plesk e agora está lento. Minha tecnologia de servidor teve que reconstruir o arquivo de configuração do Apache e desde que os sites foram lentos.

O servidor é a caixa dedicada XL8 de 1and1. tem 16gb de ram e uma CPU de 8 núcleos. O servidor contém 2 fóruns de vbulletin bastante ocupados e alguns sites menores.

Aqui está o my.cnf atual como eu configurei -

 [mysqld]
#bind-address=127.0.0.1
#skip-bdb
local-infile=0
max_connections=90
open_files_limit=2050
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
query_cache_limit = 1M
query_cache_size = 15M
join_buffer_size = 512K
read_buffer_size = 1M
tmp_table_size = 3024M
thread_cache_size = 8
table_cache = 450
innodb_buffer_pool_size = 540M
key_buffer_size = 32M
table_definition_cache = 4024
max_allowed_packet = 35M
max_heap_table_size = 3024M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

E aqui estão os últimos resultados do sintonizador mysql

Last login: Tue Sep 24 19:28:02 2013 from ip70-181-17-116.ri.ri.cox.net
[root@u16557714 ~]# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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.69
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 3316)
[--] Data in InnoDB tables: 111M (Tables: 487)
[--] Data in MEMORY tables: 372K (Tables: 329)
[!!] Total fragmented tables: 612

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4m 15s (10K q [42.020 qps], 506 conn, TX: 286M, RX: 2M)
[--] Reads / Writes: 80% / 20%
[--] Total buffers: 4.5G global + 3.1M per thread (90 max threads)
[OK] Maximum possible memory usage: 4.8G (30% of installed RAM)
[OK] Slow queries: 0% (1/10K)
[OK] Highest usage of available connections: 13% (12/90)
[OK] Key buffer size / total MyISAM indexes: 1.0G/504.2M
[OK] Key buffer hit rate: 98.0% (131K cached / 2K reads)
[OK] Query cache efficiency: 71.4% (5K cached / 8K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 429 sorts)
[!!] Joins performed without indexes: 6
[!!] Temporary tables created on disk: 34% (109 on disk / 320 total)
[OK] Thread cache hit rate: 95% (25 created / 506 connections)
[OK] Table cache hit rate: 97% (330 open / 337 opened)
[OK] Open file limit used: 4% (509/12K)
[OK] Table locks acquired immediately: 99% (4K immediate / 4K locks)
[OK] InnoDB data size / buffer pool: 111.0M/540.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with joins)

[root@u16557714 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@u16557714 ~]# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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.69
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 3316)
[--] Data in InnoDB tables: 111M (Tables: 487)
[--] Data in MEMORY tables: 124K (Tables: 329)
[!!] Total fragmented tables: 613

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 52s (683 q [13.135 qps], 39 conn, TX: 27M, RX: 229K)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 4.5G global + 3.1M per thread (90 max threads)
[OK] Maximum possible memory usage: 4.8G (30% of installed RAM)
[OK] Slow queries: 0% (0/683)
[OK] Highest usage of available connections: 2% (2/90)
[OK] Key buffer size / total MyISAM indexes: 1.0G/504.3M
[!!] Key buffer hit rate: 92.2% (11K cached / 928 reads)
[OK] Query cache efficiency: 38.5% (194 cached / 504 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 54 sorts)
[!!] Joins performed without indexes: 1
[OK] Temporary tables created on disk: 19% (8 on disk / 41 total)
[OK] Thread cache hit rate: 94% (2 created / 39 connections)
[OK] Table cache hit rate: 94% (113 open / 120 opened)
[OK] Open file limit used: 1% (212/12K)
[OK] Table locks acquired immediately: 100% (656 immediate / 656 locks)
[OK] InnoDB data size / buffer pool: 111.0M/540.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with joins)

[root@u16557714 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@u16557714 ~]# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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.69
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 3316)
[--] Data in InnoDB tables: 111M (Tables: 487)
[--] Data in MEMORY tables: 124K (Tables: 329)
[!!] Total fragmented tables: 612

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 25s (411 q [16.440 qps], 25 conn, TX: 14M, RX: 152K)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 4.0G global + 3.1M per thread (90 max threads)
[OK] Maximum possible memory usage: 4.3G (27% of installed RAM)
[OK] Slow queries: 0% (0/411)
[OK] Highest usage of available connections: 3% (3/90)
[!!] Key buffer size / total MyISAM indexes: 500.0M/504.2M
[!!] Key buffer hit rate: 92.5% (8K cached / 610 reads)
[OK] Query cache efficiency: 29.7% (91 cached / 306 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 39 sorts)
[!!] Joins performed without indexes: 1
[OK] Temporary tables created on disk: 15% (3 on disk / 19 total)
[OK] Thread cache hit rate: 88% (3 created / 25 connections)
[OK] Table cache hit rate: 92% (81 open / 88 opened)
[OK] Open file limit used: 7% (148/2K)
[OK] Table locks acquired immediately: 100% (381 immediate / 381 locks)
[OK] InnoDB data size / buffer pool: 111.0M/540.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
Variables to adjust:
    key_buffer_size (> 504.2M)
    join_buffer_size (> 512.0K, or always use indexes with joins)

[root@u16557714 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@u16557714 ~]# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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.69
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 3316)
[--] Data in InnoDB tables: 111M (Tables: 487)
[--] Data in MEMORY tables: 1M (Tables: 329)
[!!] Total fragmented tables: 613

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 13s (262 q [20.154 qps], 24 conn, TX: 15M, RX: 64K)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 3.5G global + 4.0M per thread (90 max threads)
[OK] Maximum possible memory usage: 3.9G (24% of installed RAM)
[OK] Slow queries: 0% (0/262)
[OK] Highest usage of available connections: 2% (2/90)
[!!] Key buffer size / total MyISAM indexes: 32.0M/504.2M
[!!] Key buffer hit rate: 89.5% (2K cached / 240 reads)
[OK] Query cache efficiency: 46.4% (78 cached / 168 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 13 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 5 total)
[OK] Thread cache hit rate: 91% (2 created / 24 connections)
[OK] Table cache hit rate: 88% (54 open / 61 opened)
[OK] Open file limit used: 5% (103/2K)
[OK] Table locks acquired immediately: 100% (197 immediate / 197 locks)
[OK] InnoDB data size / buffer pool: 111.0M/540.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
Variables to adjust:
    key_buffer_size (> 504.2M)

Qualquer conselho ou assistência seria muito apreciado.

Muito obrigado !!

Aqui está meu último

    [mysqld]
#bind-address=127.0.0.1
#skip-bdb
local-infile=0
max_connections=90
open_files_limit=2050
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
query_cache_limit = 1M
query_cache_size = 15M
join_buffer_size = 512K
read_buffer_size = 1M
tmp_table_size = 100M
thread_cache_size = 8
table_cache = 450
innodb_buffer_pool_size = 540M
key_buffer_size = 32M
table_definition_cache = 4024
max_allowed_packet = 35M
max_heap_table_size = 100M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

    

Answer 1

eu executaria uma "lista de processos completa" quando a execução fosse lenta, bem como a ativação do log lento.

log-slow-queries = slow.log
long_query_time = 20
log-queries-not-using-indexes

isso é para ter uma ideia de quais consultas estão matando a caixa, já que pode ser uma junção sem situação de indexação.

Além disso, algumas outras otimizações podem estar reduzindo seu buffer pool de innodb, já que sua maioria não é usada

[OK] InnoDB data size / buffer pool: 111.0M/5.0G

aumentando o tamanho do seu buffer de chave:

[OK] Key buffer size / total MyISAM indexes: 525.0M/504.2M

aumentando seu tmp_table_size para que as tabelas tmp não sejam gravadas no disco

tmp_table_size (> 100M)
max_heap_table_size (> 100M)

reduza a alocação do buffer, pois é muito alto e pode pendurar sua caixa sob carga

[!!] Maximum possible memory usage: 1960.1G (12495% of installed RAM)

no download da comunidade mysql, pelo menos o tarball de origem, há exemplos de exemplos my.cnf small / medium / large / huge, e eu provavelmente substituiria alguns dos seus valores de buffer existentes pelo my.cnf "large" (ou "enorme" se seu mysql dedicado). lembre-se de fazer as alterações uma de cada vez e, em seguida, carregar o teste.