Eu já fiz muitos ajustes na configuração do MySQL, mas infelizmente eu sempre volto a utilizar muito a CPU.
Múltiplos processos MySQL parcialmente entre 200-750% de CPU / uso de RAM estão ok cerca de 50%.
Informações sobre o sistema:
Raiz: HP ProLiant DL120 de 1 Gbps / Intel Xeon Quad-Core X3440 - 16 GB sistema operacional Ram / Ubuntu Server
Existem 3 bases de dados (3 sites), cada uma com 16 tabelas | DB_1 = 35MB - 560MB DB_2 = - DB_3 3,4GB
Sites são otimizados (consultas de banco de dados / cache, etc.) | visitantes por dia cerca de 15K / dia por página
Na raiz, são executados apenas Nginx + PHP5-FPM e mySQL, e todos os 8 processadores estão completamente sobrecarregados.
Edição do MySQLTuner:
>> MySQLTuner 1.1.1 - 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.5.44-0ubuntu0.14.04.1
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 65K (Tables: 2)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in InnoDB tables: 3G (Tables: 44)
[!!] Total fragmented tables: 18
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 4s (991 q [247.750 qps], 73 conn, TX: 8M, RX: 91K)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 12.5G global + 3.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 12.8G (81% of installed RAM)
[OK] Slow queries: 0% (8/991)
[OK] Highest usage of available connections: 28% (28/100)
[OK] Key buffer size / total MyISAM indexes: 256.0M/120.0K
[!!] Query cache efficiency: 17.8% (155 cached / 873 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 26 sorts)
[OK] Temporary tables created on disk: 20% (61 on disk / 302 total)
[OK] Thread cache hit rate: 61% (28 created / 73 connections)
[OK] Table cache hit rate: 95% (138 open / 145 opened)
[OK] Open file limit used: 5% (52/1K)
[OK] Table locks acquired immediately: 100% (712 immediate / 712 locks)
[OK] InnoDB data size / buffer pool: 3.4G/7.8G
-------- 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
MySQL Configuration Eu sempre mudei, não consigo pensar no atual "my.cnf":
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 127.0.0.1
# Tunning #
tmp_table_size = 4000M
max_heap_table_size = 4200M
max_connections = 100
max_allowed_packet = 16M
thread_cache_size = 32M
query_cache_size = 512M
query_cache_min_res_unit = 4K
sort_buffer_size = 2M
join_buffer_size = 128K
preload_buffer_size = 32K
key_buffer_size = 256M
read_buffer_size = 1M
read_rnd_buffer_size = 256K
low_priority_updates = 1
concurrent_insert = ALWAYS
# INNODB #
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_file_per_table = 1
innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 8000M
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_thread_concurrency = 0
# Logging
log_warnings = 2
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 20
# Binary Log / Replication
server_id = 1
binlog_cache_size = 1M
sync_binlog = 8
binlog_format = row
expire_logs_days = 7
max_binlog_size = 128M
relay-log = /var/log/mysql/slave-relay.log
relay-log-index = /var/log/mysql/slave-relay-log.index
[mysqldump]
quick
single-transaction
max_allowed_packet = 8M
[mysql]
no_auto_rehash
[myisamchk]
key_buffer = 265M
read_buffer = 8M
write_buffer = 8M
[mysqld_safe]
open-files-limit = 8192
log-error = /var/log/mysql/error.log
!includedir /etc/mysql/conf.d/
key_buffer = 8M
max_allowed_packet = 8M
thread_stack = 192K
thread_cache_size = 4
[mysqldump]
quick
quote-names
max_allowed_packet = 8M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 8M
!includedir /etc/mysql/conf.d/
Saída de PROCESSLIST:
mysql> SHOW PROCESSLIST;
+-------+---------+-----------+-------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+---------+-----------+-------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 251 | pb_root | localhost | db_name | Sleep | 1 | | NULL |
| 48726 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
| 48775 | pb_root | localhost | db_name | Sleep | 0 | | NULL |
| 48838 | ww_root | localhost | db_name | Query | 1 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts |
| 48845 | ww_root | localhost | db_name | Query | 0 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts |
| 48846 | ww_root | localhost | db_name | Query | 0 | preparing | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND (wp_posts.ID NOT IN ( |
| 48847 | ww_root | localhost | db_name | Sleep | 0 | | NULL |
| 48854 | pb_root | localhost | db_name | Sleep | 1 | | NULL |
| 48735 | pb_root | localhost | db_name | Sleep | 0 | | NULL |
| 48878 | ww_root | localhost | db_name | Query | 1 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts |
| 48845 | pb_root | localhost | db_name | Query | 0 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts |
| 48827 | ww_root | localhost | db_name | Sleep | 0 | | NULL |
+-------+---------+-----------+-------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+