MySQL需要太多的CPU

下面是我的.cnf文件，如果我看到我的CPU消耗99.99％的我的mysqld命令。 MySQL服务器是从远程机器连接的，它经常更新数据，但是我确保远程服务器打开连接，读/写/更新，然后closures它。 另外远程服务器读取很多。

我可以做些什么来减less我的CPU消耗。 仅供参考，我正在使用4GB RAM的2核心CPU。

[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysqluser 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 wait_timeout = 20 interactive_timeout = 60 bind-address = <IP-ADDRESS> key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP max_connections = 300 query_cache_limit = 20M query_cache_size = 128M log_error = /var/log/mysql/error.log log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 4 log-queries-not-using-indexes expire_logs_days = 10 max_binlog_size = 100M [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M 

也从mysqltuner粘贴输出。

  >> MySQLTuner 1.4.0 - Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Logged in using credentials from debian maintenance account. [OK] Currently running supported MySQL version 5.5.38-0ubuntu0.14.04.1-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in InnoDB tables: 2G (Tables: 26) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 26 -------- Performance Metrics ------------------------------------------------- [--] Up for: 21m 51s (37K q [28.525 qps], 31K conn, TX: 6M, RX: 7M) [--] Reads / Writes: 97% / 3% [--] Total buffers: 304.0M global + 2.7M per thread (5000 max threads) [!!] Maximum possible memory usage: 13.4G (347% of installed RAM) [!!] Slow queries: 18% (6K/37K) [OK] Highest usage of available connections: 0% (16/5000) [OK] Key buffer size / total MyISAM indexes: 16.0M/100.0K [OK] Query cache efficiency: 20.2% (7K cached / 36K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts) [OK] Temporary tables created on disk: 25% (54 on disk / 215 total) [OK] Thread cache hit rate: 99% (16 created / 31K connections) [OK] Table cache hit rate: 25% (74 open / 289 opened) [OK] Open file limit used: 0% (49/25K) [OK] Table locks acquired immediately: 100% (29K immediate / 29K locks) [!!] InnoDB buffer pool / data size: 128.0M/2.9G [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** innodb_buffer_pool_size (>= 2G) 

我没有得到这个问题，但是当我开始连接10个远程服务器到这个MySQL服务器，当这十个服务器开始在这个单一的MySQL服务器上进行SELECT和UPDATE查询时，我看到这个CPU消耗的增加。

• PHP + Apache很长的等待时间
• 回写模式下的LVMcaching相当于外部的ext4日志？
• 我是否推动了当前VPS的限制，还是有优化的余地？
• 在Nginxconfiguration中设置fastcgi_buffers值的任何指南？
• configurationapache的〜125 django网站来优化内存使用（mod_python v mod_wsgi; worker vs prefork;静态文件）

 innodb_buffer_pool_size = 3000M 

 . . . . . . . . . . [mysqld] user = mysqluser pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock log_error = /var/log/mysql/error.log log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 10 log-queries-not-using-indexes expire_logs_days = 10 max_binlog_size = 100M port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking wait_timeout = 20 interactive_timeout = 60 bind-address = <IP-ADDRESS> myisam-recover = BACKUP ## REASONABLE MEMORY PARAMETERS max_connections = 64 thread_cache_size = 2 thread_concurrency = 4 key_buffer_size = 64M join_buffer_size = 2M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 16M query_cache_limit = 2M query_cache_size = 32M table_open_cache = 512 max_allowed_packet = 1M . . . . . . . . . .