我已经对MySQLconfiguration做了很多调整，但不幸的是，我总是会重新利用非常高的CPU。
多个MySQL进程部分在200-750％的CPU / RAM的使用率是50％左右。
系统信息：
根：HP ProLiant DL120 1Gbps / Intel四核至强X3440 – 16GB Ram / Ubuntu服务器操作系统
有3个数据库（3个站点），每个数据库有16个表| DB_1 = 35MB – 560MB DB_2 = – = DB_3 3,4GB
网站被优化（数据库查询/caching等）| 每天访问者每天约15K /天
在根目录下只运行Nginx + PHP5-FPM和mySQL，而且所有的8个处理器都被完全重载。
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 MySQLconfiguration我经常改变，想不到当前的“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/
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 | +-------+---------+-----------+-------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+