【记录】再去Linode上的优化crifan.com的wordpress的mysql的配置

【背景】

当前的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 <major@mhtx.net>
 >>  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:~$ 

如图:

run mysqltuner pl result again on linode vps

【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:~$ 

sudo systemctl restart mysqld and mariadb

 

希望之后,效果会好点:

能使得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


发表评论

电子邮件地址不会被公开。 必填项已用*标注

无觅相关文章插件,快速提升流量