建表和sql不规范 导致 服务器几乎没反应

建表和sql不规范 导致 服务器几乎没反应

问题描述：

1）研发人员说测试库session满了，请求调大最大连接数。

数据库中发现大量(1944个)的session，语句基本相同，SELECT * FROM ali_phone_info where phone=13004669173 ， 处于sending data状态。

服务器连ssh登陆都很困难。

mysql> show processlist;

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

| 413853 | sx_pac | 180.169.233.185:46268 | sx_pac | Query | 461 | Sending data | SELECT * FROM ali_phone_info where phone=13004669173 |

| 413588 | sx_pac | 180.169.233.185:27527 | sx_pac | Query | 438 | Sending data | SELECT * FROM ali_phone_info where phone=15577861724 |

| 413589 | sx_pac | 180.169.233.185:27526 | sx_pac | Query | 438 | Sending data | SELECT * FROM ali_phone_info where phone=13175885461 |

| 413590 | sx_pac | 180.169.233.185:27520 | sx_pac | Query | 429 | Sending data | SELECT * FROM ali_phone_info where phone=15578128274 |

...

| 74405 | sx_pac | 180.169.233.185:11350 | sx_pac | Query | 0 | query end | create table IF NOT EXISTS o2o_huangye_info(

primary_key VARCHAR (255) not null pri |

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

1944 rows in set (0.01 sec)

开发同学说这些查询的sql，是在insert之前的验证步骤，如果有就不用再insert了。

error log 很多如下信息：

2018-09-04T13:17:43.990352+08:00 21201 [Note] Aborted connection 21201 to db: 'sx_pac' user: 'sx_pac' host: '180.169.150.211' (Got an error reading communication packets)

2018-09-04T13:18:26.263859+08:00 21220 [Note] Aborted connection 21220 to db: 'sx_pac' user: 'sx_pac' host: '180.169.150.211' (Got an error reading communication packets)

2018-09-04T13:32:24.931968+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4900ms. The settings might not be optimal. (flushed=5 and evicted=0, during the time.)

2）服务器配置 2c4G， centos7.x

mysql相关参数：

innodb_buffer_pool_size | 5G //阿里云rds 25G / 12G

innodb_buffer_pool_instances | 2 //阿里云rds 8 / 4

innodb_io_capacity | 200 //阿里云rds 是 2000 ，都是固态硬盘啊

innodb_io_capacity_max | 2000 //阿里云rds 是 4000

innodb_read_io_threads | 4

innodb_write_io_threads | 4

max_connections | 2000 // 阿里云rds 是 8512 / 4512

log_warnings | 2

interactive_timeout | 28800 // 服务器关闭交互式连接前等待活动的秒数，默认值:28800秒(8小时)

wait_timeout | 28800 //

innodb_page_cleaners | 2 // 一般建议设置和innodb_buffer_pool_instances一致，5.6只有一个page_cleaner线程，5.7可以有多个

innodb_log_file_size | 536870912 // 512M ,阿里云rds 1.5G

innodb_log_files_in_group | 2

innodb_lru_scan_depth | 1024

3）其它信息展示：

mysql> analyze table ali_phone_info;

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

| Table | Op | Msg_type | Msg_text |

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

| sx_pac.ali_phone_info | analyze | status | OK |

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

1 row in set (0.03 sec)

mysql> show table status like 'ali_phone_info';

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

| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |

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

| ali_phone_info | InnoDB | 10 | Dynamic | 752640 | 56 | 42532864 | 0 | 20512768 | 4194304 | 787894 | 2018-09-21 19:30:27 | 2018-09-21 18:22:05 | NULL | utf8_general_ci | NULL | | |

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

1 row in set (0.00 sec)

mysql> show create table ali_phone_info;

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

| Table | Create Table

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

| ali_phone_info | CREATE TABLE `ali_phone_info` (

`primary_key` int(11) NOT NULL AUTO_INCREMENT,

`phone` varchar(255) NOT NULL,

`plat_code` varchar(255) NOT NULL,

`crawl_time` timestamp NULL DEFAULT NULL,

`jrjt_del_dt` varchar(255) DEFAULT NULL,

PRIMARY KEY (`primary_key`)

) ENGINE=InnoDB AUTO_INCREMENT=787894 DEFAULT CHARSET=utf8 |

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

1 row in set (0.00 sec)

问题定位：

1.建表上

2.sql语句上

3.索引上

处理：

1.phone字段使用255个字符，完全没有必要

2.phone字段是字段类型，sql使用时 却是数字类型的值 "SELECT * FROM ali_phone_info where phone=15578128274"

3.这里用 "select * " 验证不符合规范，应该改写 SELECT phone from ali_phone_info where phone='15578128274' ,

4.直接ddl方式建索引，锁表时间长，57w行的数据，建索引进2个小数，线上环境是不行的。

mysql> alter table ali_phone_info add key idx_ali_phone_info01(phone(11));

Query OK, 0 rows affected (1 hour 54 min 30.51 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table ali_phone_info;

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

| Table | Create Table

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

| ali_phone_info | CREATE TABLE `ali_phone_info` (

`primary_key` int(11) NOT NULL AUTO_INCREMENT,

`phone` varchar(255) NOT NULL,

`plat_code` varchar(255) NOT NULL,

`crawl_time` timestamp NULL DEFAULT NULL,

`jrjt_del_dt` varchar(255) DEFAULT NULL,

PRIMARY KEY (`primary_key`),

KEY `idx_ali_phone_info01` (`phone`(11))

) ENGINE=InnoDB AUTO_INCREMENT=787894 DEFAULT CHARSET=utf8 |

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

1 row in set (0.00 sec)

5.大量的session，说明程序没有使用连接池机制

即使是爬虫，前端发起大量session，但后端操作数据库也是可以走连接池完成的，

6.没使用缓存，做批量插入

7.在程序中建表

8.error log信息说明

[Note] Aborted connection 21201 to db: ... (Got an error reading communication packets)

这种是网络等原因导致，这里是session过多，导致系统资源耗尽，

如果是"Got timeout reading communication packets" ,则是会话的idle时间达到了数据库指定的timeout时间

[Note] InnoDB: page_cleaner: 1000ms intended loop took 4900ms. The settings might not be optimal. (flushed=5 and evicted=0, during the time.)

Innodb page cleaner线程刷新策略

有以下几个参数会影响到Page cleaner的行为：

innodb_lru_scan_depth

innodb_adaptive_flushing_lwm

innodb_max_dirty_pages_pct_lwm

innodb_io_capacity_max

innodb_flushing_avg_loops

参考：

https://blog.csdn.net/jc_benben/article/details/82251891

https://www.cnblogs.com/yuyue2014/p/5553820.html