Ubuntu | Mysql 服务器 CPU 300-750%

Ubuntu | Mysql 服务器 CPU 300-750%

我已经对 MySQL 配置做了很多调整,但不幸的是我总是回到 CPU 利用率非常高的情况。

多个 MySQL 进程的 CPU / Ram 使用率部分在 200-750% 之间,大约为 50%。

系统信息:

Root:HP ProLiant DL120 1Gbps / Intel 四核 Xeon X3440 - 16GB Ram / Ubuntu 服务器操作系统

有 3 个数据库(3 个站点),每个数据库有 16 个表 | DB_1 = 35MB - 560MB DB_2 = - = DB_3 3,4GB

网站已优化(数据库查询/缓存等)| 每日访客约 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

MySQL 配置我经常更改,想不起当前的“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/

进程列表输出:

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                                                                                                 |

+-------+---------+-----------+-------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+

相关内容