溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
建表和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
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
