【背景】
当前的Linode的VPS的物理上的硬件配置已经还可以了。
但是现在CPU占用率感觉还是不小。
打开网页，时不时的还是比较慢。
感觉高峰期（最多是100多在线）时，尤其明显。
而且SSH登陆远程操作，经常很卡，卡到输入一个字符，好多秒后才能反应过来。
实在受不了了，所以再想办法去优化。
看看MySQL还能否继续优化。
【折腾过程】
1.继续去用那个mysqltuner.pl：
crifan@crifan:~$ ll total 20316 drwxrwxr-x 2 crifan crifan 4096 May 16 23:08 cpanel -rw-r--r-- 1 crifan crifan 11909 May 16 10:08 httpd.conf.backup drwxr-xr-x 2 crifan crifan 4096 Jul 23 09:14 mediawiki-1.24.1 -rw-rw-r-- 1 crifan crifan 20725572 Jul 23 09:09 mediawiki-1.24.1.tar.gz -rw-r--r-- 1 crifan crifan 570 May 16 14:42 my.conf.backup-20150516 -rw-rw-r-- 1 crifan crifan 47679 May 25 10:07 mysqltuner.pl crifan@crifan:~$ perl mysqltuner.pl >> 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 Please enter your MySQL administrative login: root Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.5.41-MariaDB-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 127M (Tables: 14) [--] Data in InnoDB tables: 2M (Tables: 52) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 0B (Tables: 1) [!!] Total fragmented tables: 56 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 28d 21h 13m 57s (77M q [31.045 qps], 1M conn, TX: 719B, RX: 11B) [--] Reads / Writes: 97% / 3% [--] Total buffers: 400.0M global + 2.6M per thread (75 max threads) [OK] Maximum possible memory usage: 596.9M (15% of installed RAM) [OK] Slow queries: 0% (458K/77M) [OK] Highest usage of available connections: 69% (52/75) [OK] Key buffer size / total MyISAM indexes: 32.0M/9.3M [OK] Key buffer hit rate: 100.0% (377M cached / 18K reads) [OK] Query cache efficiency: 43.1% (52M cached / 121M selects) [!!] Query cache prunes per day: 116635 [OK] Sorts requiring temporary tables: 0% (14 temp sorts / 7M sorts) [!!] Temporary tables created on disk: 74% (1M on disk / 1M total) [OK] Thread cache hit rate: 99% (2K created / 1M connections) [!!] Table cache hit rate: 1% (32 open / 1K opened) [OK] Open file limit used: 3% (34/1K) [OK] Table locks acquired immediately: 99% (18M immediate / 18M locks) [OK] InnoDB buffer pool / data size: 32.0M/2.3M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Variables to adjust: query_cache_size (> 64M) table_open_cache (> 32) crifan@crifan:~$
如图：
【Total fragmented tables: 56】
mysqltuner.pl Total fragmented tables: 56
【配置文件】
vi /etc/my.cnf
Variables to adjust:
query_cache_size (> 64M)
table_open_cache (> 32)
crifan@crifan:~$ cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
max_connections = 75
key_buffer = 32M
max_allowed_packet = 1M
thread_stack = 128K
table_cache = 32
innodb_buffer_pool_size = 32M
#log-slow-queries=/var/lib/mysql/slow.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 10
log_queries_not_using_indexes = 1
query_cache_size = 64M
tmp_table_size = 256M
max_heap_table_size = 256M
thread_cache_size = 8
bind-address=127.0.0.1
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
crifan@crifan:~$
修改配置。
先去查查：
table_open_cache
搜：
mysql table_open_cache
参考：
table_open_cache参数对mysql性能的影响-myownstars-ITPUB博客
自己此处：
GoogleAnalytics分析出来的：
最高峰有100多人同时在线
最少也有30多人在线
所以：
应该设置为100*N
N==2？？？
其中提到了：max_connections
而看到此处的我的：
max_connections = 75
所以，待会也要提高max_connections的值，比如设置为200
Optimizing table_open_cache – MariaDB Knowledge Base
去看看当前的自己的table_open_cache配置：
当前果然是32：
crifan@crifan:~$ sudo mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 1462337 Server version: 5.5.41-MariaDB-log MariaDB Server Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> select @@table_open_cache; +--------------------+ | @@table_open_cache | +--------------------+ | 32 | +--------------------+ 1 row in set (0.00 sec) MariaDB [(none)]>
->所以调校测试后推荐大于32
opened_tables
可以去设置了：
query_cache_size =256M
max_connections = 200
table_open_cache = 200
先去看看那个：
opened_tables
show global status like ‘opened_tables’;
crifan@crifan:~$ sudo mysql -u root -p [sudo] password for crifan: Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 1462641 Server version: 5.5.41-MariaDB-log MariaDB Server Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> select @@table_open_cache; +--------------------+ | @@table_open_cache | +--------------------+ | 32 | +--------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> show global status like 'opened_tables'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Opened_tables | 2359 | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]>
感觉此处的opened_tables数值还好，没有太大。
是可以去增加那个table_open_cache的。
crifan@crifan:~$ sudo vi /etc/my.cnf [sudo] password for crifan: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd max_connections = 200 key_buffer = 64M max_allowed_packet = 1M thread_stack = 128K table_cache = 64 table_open_cache = 200 innodb_buffer_pool_size = 32M #log-slow-queries=/var/lib/mysql/slow.log slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 10 log_queries_not_using_indexes = 1 query_cache_size = 256M tmp_table_size = 256M max_heap_table_size = 256M thread_cache_size = 8 bind-address=127.0.0.1 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/my.cnf.d
继续优化mysql
再去重启mysql：
crifan@crifan:~$ sudo systemctl restart mysqld
Failed to issue method call: Unit mysqld.service failed to load: No such file or directory.
crifan@crifan:~$ sudo systemctl restart mariadb
crifan@crifan:~$ systemctl status mariadb
mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled)
Active: active (running) since Tue 2015-08-04 21:57:28 CST; 8s ago
Process: 10684 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 10653 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 10683 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─10683 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─11032 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/...
crifan@crifan:~$ 
希望之后，效果会好点：
能使得CPU占用率能再低点
能让远程ssh操作不要那么卡
能打开页面速度再快点
Opened_tables
其实还需要再参考其他的：
mysql5.6下table_open_cache参数合理配置详解_秋雨_新浪博客
抽空再去用：
show status like ‘open%tables’;
去看Open_tables和Opened_tables的值
如果：发现open_tables等于table_open_cache
那么就继续去增加table_open_cache
MySQL :: MySQL 5.1 Reference Manual :: 8.4.3.1 How MySQL Opens and Closes Tables
其中：
“table_open_cache was known as table_cache in MySQL 5.1.2 and earlier. ”
所以：
此处应该
先去确认mysql的版本，
如果是5.1.2之后的，那么就，去除掉table_cache
而之前登陆mysql时就看到有：
Server version: 5.5.41-MariaDB-log MariaDB Server
所以是5.1.2之后的，所以就去去掉table_cache。
crifan@crifan:~$ sudo vi /etc/my.cnf [sudo] password for crifan: table_cache = 64 [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd max_connections = 200 key_buffer = 64M max_allowed_packet = 1M thread_stack = 128K table_open_cache = 200 innodb_buffer_pool_size = 32M #log-slow-queries=/var/lib/mysql/slow.log slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 10 log_queries_not_using_indexes = 1 query_cache_size = 256M tmp_table_size = 256M max_heap_table_size = 256M thread_cache_size = 8 bind-address=127.0.0.1 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/my.cnf.d
至此，此次的优化，到此为止。
希望以后能有点效果。
【总结】
此处继续通过mysqltuner.pl去执行推荐的优化参数。
主要是有如下改动：
- max_connections = 200
- 去掉table_cache
- table_open_cache = 200
- query_cache_size =256M