mysql性能调优

mysql  优化的思路：（是关键的灵魂）

 

优化的目的：、

为了快速响应客户端的响应；

 

优化手段：

1.硬优化

升级硬件（硬盘   内存 ）  

2.软优化

优化服务运行的参数

 

影响数据库服务的性能的因数有那些：

1.网络带宽   解决办法（网络测速软件） 卖带宽

2.服务器的硬件配置低   解决办法    查看硬件的使用率（CPU 内存    存储设备）

3.数据库服务软件版本低，导致性能低下 解决办法     （查看数据库服务，运行时的运行参数（查看变量值））

mysql> show variables\G;

mysql> show variables  like "%password%";

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

| Variable_name                         | Value  |

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

| default_password_lifetime             | 0      |

| disconnect_on_expired_password        | ON     |

| log_builtin_as_identified_by_password | OFF    |

| mysql_native_password_proxy_users     | OFF    |

| old_passwords                         | 0      |

| report_password                       |        |

| sha256_password_proxy_users           | OFF    |

| validate_password_check_user_name     | OFF    |

| validate_password_dictionary_file     |        |

| validate_password_length              | 8      |

| validate_password_mixed_case_count    | 1      |

| validate_password_number_count        | 1      |

| validate_password_policy              | MEDIUM |

| validate_password_special_char_count  | 1      |

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

14 rows in set (0.00 sec)

 

 

2.硬件的查看思路：

CPU //查看命令

 top  //每5秒钟刷新一次

[root@host2 ~]# uptime

 10:30:35 up  1:47,  1 user,  load average: 0.00, 0.01, 0.05

0.00, 0.01, 0.05//越少越好，占用率越少

内存

[root@host2 ~]# free  -m

              total        used        free      shared  buff/cache   available

Mem:            992         320         364           6         307         482  

Swap:          2047           0        2047

//要保持有20%的使用的遇留空间，不然访风值就会出问题

 

Mem  //物理内存的使用率

Swap   //交换空间的使用率

 

存储设备：

转速15000转/分钟

固体硬盘

top查看：  0.0 wa, 数值越大说明等待硬盘的处理的时间就越长

 

云主机：16cpu 128G 1000T

 

 

 

修改永久的密码配置：

vim /etc/my.cnf

[mysqld]

validate_password_length=6

validate_password_policy=0

:wq

[root@host51 ~]# systemctl  restart  mysqld

[root@host51 ~]# mysql -u root -p

mysql> show  variables like"%password%";

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

| Variable_name                         | Value |

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

| default_password_lifetime             | 0     |

| disconnect_on_expired_password        | ON    |

| log_builtin_as_identified_by_password | OFF   |

| mysql_native_password_proxy_users     | OFF   |

| old_passwords                         | 0     |

| report_password                       |       |

| sha256_password_proxy_users           | OFF   |

| validate_password_check_user_name     | OFF   |

| validate_password_dictionary_file     |       |

| validate_password_length              | 6     |

| validate_password_mixed_case_count    | 1     |

| validate_password_number_count        | 1     |

| validate_password_policy              | LOW   |

| validate_password_special_char_count  | 1     |

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

14 rows in set (0.01 sec)

 

临时修改密码规则：

set global validate_password_policy=2;

 

mysql> set global validate_password_policy=2;

Query OK, 0 rows affected (0.01 sec)

 

mysql> set validate_password_policy=2;

ERROR 1229 (HY000): Variable 'validate_password_policy' is a GLOBAL variable and should be set with SET GLOBAL  //报错原因是：这个变量是全局变量 

 

设置会影响服务性能参数的值：

1、目的：

通过根据服务器目前状况，修改Mysql的系统参数，达到合理利用服务器现有资源，最大合理的提高MySQL性能。

2.并发连接数量   //mysql> set global max_connections = 300;

mysql> show variables like "%conn%";   

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

| Variable_name                                 | Value           |

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

| character_set_connection                      | utf8            |

| collation_connection                          | utf8_general_ci |

| connect_timeout                               | 10              |

| disconnect_on_expired_password                | ON              |

| init_connect                                  |                 |

| max_connect_errors                            | 100             |

| max_connections                               | 151             |

| max_user_connections                          | 0               |

| performance_schema_session_connect_attrs_size | 512             |

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

9 rows in set (0.00 sec)

             

 

 

mysql> show  global status ;  //查看状态

mysql> show  global status  like "%conn%"；    //查看连接状态

mysql> show   global  status  like  "Max_used_connections";       //查看最大的连接的最大数量

曾经有过的最大连接数/并发连接数 = 0.85  * 100% 

Max_used_connections/max_connections=0.85

 

3.连接超时时间

 

mysql> show variables  like "%time%";

| connect_timeout                 | 10                 //客户端与服务器建立连接时，三次握手的超时时间

| interactive_timeout             | 28800             |     //3.2建立连接后，服务器等待客户输入sql命令的超时时间

 

connect_timeout这两个的设置的作用：拒绝服务攻击,这个值设置得太长也不好，设置得太短也不好

过大容易：拒绝服务攻击  过短容易：消耗内存资源

interactive_timeout 

[root@host53 ~]# echo  $[28800/3600]

8

 

知识补充：

三次握手：是在tcp协议下才可以进行

ss ntulp | grep mysql //半开式扫描   ——就是利用三次握手的原理

 

 

4.可以重复使用的线程数量

mysql> show variables like "%thread%";

| thread_cache_size                       | 9      

 

mysql 默认就会开9个线程，等待客户端的响应，系统不会杀死这些进程，在内存等待客户端进行访问，这样响应的速度就会加快。

如果设置太大，就会占用内存资源，

如果过少 ，就会让客户端的等待时间过长

 

5.所有mysql程序打开表的数量

mysql> show variables like "%table%";

| table_open_cache                       | 2000     |

客户端访问数据库，有可能访问同一张表，或者不同的表

如果设置太少，服务器会把表调到内存，（内存的速度，要比硬盘的速度快）

 

6.缓存的设置

6.1.管理工具：提供服务的软件

软件安装后，提供的管理命令 （软件自带的命令）

连接池：检查服务器是否有资源响应客户端有

是否有空闲的线程，等待连接请求

连接成功后要输入sql命令

sql接口：把sql命令传递给mysql程序

mysql> select * from t1;

分析器：检查sql命令是否正确；（输入命令的报错机制）

优化器： 对用户执行的sql命令进行优化；（自动完成，不需要人为干预）

查询缓存：存储曾经查找过的结果，（存储空间时mysql服务启动时从物理主机的物理内存划分出来）

存储引擎：软件自带的管理工具：表的处理器，不同的存储引擎支持不同的功能和数据存储方式 myisam innodb

文件系统： 数据服务用来存储数据的硬盘

 /var/lib/mysql

[root@host2 ~]# df -h

文件系统               容量  已用  可用 已用% 挂载点

/dev/mapper/rhel-root   17G  7.3G  9.8G   43% /              //这是一个逻辑卷

 

[root@host2 ~]# lvscan                                                       //查看逻辑卷

  ACTIVE            '/dev/rhel/swap' [2.00 GiB] inherit

  ACTIVE            '/dev/rhel/root' [<17.00 GiB] inherit

//所有的数据存储到/var/lib/mysql  实质时存储到  /的逻辑卷中

 

mysql处理查询的过程：

1.首先要跟服务器建立，连接请求

2.建立连接请求，到库里表里查询数据

3.把查询结果返回给查询缓存空间

4.查询缓存空间返回给客户端

 

mysql> show variables  like  "�che%";      //查询缓存的种类

mysql> show variables  like  "query_cache_%";     //查询缓存的参数

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

| Variable_name                | Value   |

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

| query_cache_limit            | 1048576 |

| query_cache_min_res_unit     | 4096    |

| query_cache_size             | 1048576 |

| query_cache_type             | OFF     |

| query_cache_wlock_invalidate | OFF     |

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

5 rows in set (0.01 sec)

 

query_cache_wlock_invalidate | OFF

当客户端查询mysqlSam 存储引擎的表时，由此时有客户端对表执行写操作的话，数据库不好从查询缓存里查找结果返回给客户端，而是等写操作完成后，从新

 

 

 

从查询缓存里，直接查找结果返回给客户端，如果没有就到库里表里查询结果，再主动的将结果返回给查询缓存，查询缓存再把结果返回给客户端。

如果有客户执行查询操作，数据库就会进行写锁，不能写进数据，等客户执行网查询操作，才能执行写入操作，

这样保证了数据的一致性。

 

| query_cache_type             | OFF     |    //查询缓存的开关    on 就是相当于0；默认是关的

 

| query_cache_type   0|1|2

0 禁止使用查询缓存

1 若查询结果没有超过查询缓存大少的设置 ，查询结果会自动存储到查询缓存里

2 明确指定要把查询结果存储

 

| query_cache_min_res_unit     | 4096    //查询缓存的最少存储大少，最少是4k  （4096是字节单位）存储过数据就不能再存储数据，必须等清空才可以再一次存储数据，这样保证数据的一致性

 

 

生产环境中：

把访问次数比较多的数据放到缓存服务器里，热度不高的放到数据库里

 

查询结果的统计信息：

mysql> show global status like "qcache%";

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

| Variable_name           | Value   |

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

| Qcache_free_blocks      | 1       |

| Qcache_free_memory      | 1031832 |

| Qcache_hits             | 0       |         //当前查询结果是查询缓存里找到，此变量值就会自动加1

| Qcache_inserts          | 0       |      //服务器接收到一次查询请求，此变量就会自动加1

| Qcache_lowmem_prunes    | 0       |

| Qcache_not_cached       | 4       |

| Qcache_queries_in_cache | 0       |

| Qcache_total_blocks     | 1       |

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

8 rows in set (0.00 sec)

 

| Qcache_hits  这个值越高，表示查询缓存的性能就越高。

 

锁引缓存：

mysql> mysql> show global status like "%key%";

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

| Variable_name          | Value |

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

| Com_assign_to_keycache | 0     |

| Com_preload_keys       | 0     |

| Com_show_keys          | 0     |

| Handler_read_key       | 6     |

| Key_blocks_not_flushed | 0     |

| Key_blocks_unused      | 6695  |

| Key_blocks_used        | 3     |

| Key_read_requests      | 6     |

| Key_reads              | 3     |

| Key_write_requests     | 0     |

| Key_writes             | 0     |

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

11 rows in set (0.00 sec)

 

 

mysql> show variables  like "key_buffer_size";

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

| Variable_name   | Value   |

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

| key_buffer_size | 8388608 |

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

1 row in set (0.01 sec)

 

索引缓存默认是8M，设置索引缓存，就会表目录下生成相关的索引文件，存放索引信息（存储的排队信息）

索引缓存就是把排队信息放在内存里，如果没有就放在硬盘里，（这样的查找速度比较慢）

性能优化

 

 

mysql> explain select user  from mysql.user where user="mysql"\G;        \\查看表中的索引信息（也可以查看看表的查看速度）

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: user

   partitions: NULL

         type: index

possible_keys: NULL

          key: PRIMARY

      key_len: 276

          ref: NULL

         rows: 2

     filtered: 50.00

        Extra: Using where; Using index

1 row in set, 1 warning (0.00 sec)

 

ERROR: 

No query specified

 

 

key_buffer-size 用于 MyISAM 引擎的关键索引缓存大小

sort_buffer_size 为每个要排序的线程分配此大小的缓存空间

read_buffer_size 为顺序读取表记录保留的缓存大小

thread_cache_size 允许保存在缓存中被重用的线程数量

table_open_cache 为所有线程缓存的打开的表的数量

 

 

[root@host51 ~]# vim /etc/my.cnf

[mysqld]

general-log

：wq

 

[root@host51 ~]# systemctl  restart  mysqld

[root@host51 ~]# ls /var/lib/mysql

 host51.log

[root@host51 ~]# vim  /var/lib/mysql/host51.log

生产环境中：根据业务需求进行配置

 

启动慢查询日志：记录超过超时时间显示查询结果的sql命令。（默认没有启用）

[root@host51 ~]# vim /etc/my.cnf

[mysql]

slow-query-log

:wq

[root@host51 ~]# systemctl  restart  mysqld

[root@host51 ~]# ls /var/lib/mysql

 host51-slow.log

：wq

[root@host51 ~]# vim /var/lib/mysql/host51-slow.log 

Time                 Id Command    Argument

 

模拟一个报错信息

mysql> select sleep(11),"name";       //模拟报错                  //sleep(11) 等待多长时间再执行命令

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

| sleep(11) | name |

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

|         0 | name |

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

1 row in set (11.00 sec)

[root@host51 ~]# vim /var/lib/mysql/host51-slow.log      //查看错误信息

Time                 Id Command    Argument

# Time: 2018-07-23T07:59:10.198806Z

# User@Host: root[root] @ localhost []  Id:     3

# Query_time: 11.000477  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0

SET timestamp=1532332750;

select sleep(11),"name";

mysql> select sleep(3) "abc","bcd";

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

| abc | bcd |

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

|   0 | bcd |

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

1 row in set (3.00 sec)

 

[root@host51 ~]# vim /var/lib/mysql/host51-slow.log     //默认值是是10秒，3秒不会报错

# User@Host: root[root] @ localhost []  Id:     3

# Query_time: 11.000477  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0

SET timestamp=1532332750;

select sleep(11),"name"

 

[root@host51 ~]# mysqldumpslow  /var/lib/mysql/host51-slow.log    //mysql软件自带命令查看统计结果

 

Reading mysql slow query log from /var/lib/mysql/host51-slow.log

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts

  # Time: N-N-23T07:N:N.198806Z

  # User@Host: root[root] @ localhost []  Id:     N

  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N

  SET timestamp=N;

  select sleep(N),"S"

 

[root@host51 ~]# mysqldumpslow  /var/lib/mysql/host51-slow.log   > /root/sql.txt

将错误信息统计结果另存为，发给程序员，让程序员修改

 

数据存储架构不合理：数据传输有瓶颈

查看数据架构；这个就是非常大的问题

 

 

总结：mysql性能要结合zabbix一起使用；

  env                     是linux 的系统表量

  variables            mysql的数据库表量