建表和sql不規(guī)范導(dǎo)致服務(wù)器幾乎無反應(yīng)

2024-09-07 22:12:41

字體：大中小

供稿：網(wǎng)友

建表和sql不規(guī)范導(dǎo)致服務(wù)器幾乎沒反應(yīng)

問題描述：

1）研發(fā)人員說測(cè)試庫session滿了，請(qǐng)求調(diào)大最大連接數(shù)。

數(shù)據(jù)庫中發(fā)現(xiàn)大量(1944個(gè))的session，語句基本相同，SELECT * FROM ali_phone_info where phone=13004669173 ，處于sending data狀態(tài)。

服務(wù)器連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)

開發(fā)同學(xué)說這些查詢的sql，是在insert之前的驗(yàn)證步驟，如果有就不用再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）服務(wù)器配置 2c4G， centos7.x

mysql相關(guān)參數(shù)：

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

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

innodb_io_capacity           | 200     //阿里云rds 是 2000 ，都是固態(tài)硬盤啊

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    //   服務(wù)器關(guān)閉交互式連接前等待活動(dòng)的秒數(shù)，默認(rèn)值:28800秒(8小時(shí))

wait_timeout                 | 28800    //

innodb_page_cleaners         | 2        //   一般建議設(shè)置和innodb_buffer_pool_instances一致，5.6只有一個(gè)page_cleaner線程，5.7可以有多個(gè)

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個(gè)字符，完全沒有必要

2.phone字段是字段類型，sql使用時(shí) 卻是數(shù)字類型的值 “SELECT * FROM ali_phone_info where phone=15578128274”

3.這里用 “select * ” 驗(yàn)證不符合規(guī)范，應(yīng)該改寫 SELECT phone from ali_phone_info where phone='15578128274' ,

4.直接ddl方式建索引，鎖表時(shí)間長(zhǎng)，57w行的數(shù)據(jù)，建索引進(jìn)2個(gè)小數(shù)，線上環(huán)境是不行的。

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，說明程序沒有使用連接池機(jī)制

  即使是爬蟲，前端發(fā)起大量session，但后端操作數(shù)據(jù)庫也是可以走連接池完成的，

6.沒使用緩存，做批量插入

7.在程序中建表

8.error log信息說明

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

這種是網(wǎng)絡(luò)等原因?qū)е拢@里是session過多，導(dǎo)致系統(tǒng)資源耗盡，

如果是“Got timeout reading communication packets” ,則是會(huì)話的idle時(shí)間達(dá)到了數(shù)據(jù)庫指定的timeout時(shí)間

[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線程刷新策略

有以下幾個(gè)參數(shù)會(huì)影響到Page cleaner的行為：

innodb_lru_scan_depth

innodb_adaptive_flushing_lwm

innodb_max_dirty_pages_pct_lwm

innodb_io_capacity_max

innodb_flushing_avg_loops

（編輯：武林網(wǎng)）

上一篇：關(guān)于修測(cè)驗(yàn)UTO_INCREMENT列的默認(rèn)值的測(cè)試

下一篇：InnoDB關(guān)鍵特征之自適應(yīng)hash索引