MySQL 线程池[2021-06-26]
文章目录一.引入1.mysqlslap结合mysqladmin2.秒杀模拟二、线程池1.mysql线程池2.如何开启线程池3.线程池参数一.引入1.mysqlslap结合mysqladminstep1：准备数据## 自建一个库和表，表中有id即可；写一个查询sql脚本，如下：[root@k8s101 ~]# cat sbtest.sqlset @id=floor(rand()*2000000+1)
·
一.引入
1.mysqlslap结合mysqladmin
- step1：准备数据
## 自建一个库和表，表中有id即可；写一个查询sql脚本，如下：
[root@k8s101 ~]# cat sbtest.sql
set @id=floor(rand()*2000000+1);
select * from sysbench.sbtest1 where id=@id;
## 测试脚本
[root@k8s101 ~]# mysql < sbtest.sql
id k c pad
796943 999008 04776247908-39159432861-68542247261-30978582973-93414058449-84873004715-33799745922-13233722164-78399932182-79481095076 25155346000-93752087602-90510925010-53244682843-83774199527
- step2：执行mysqlslap
## 执行mysqlslap（前提，创建一个空的mysqlslap库，否则报错）
## 该mysqlslap线程将会一直执行，没有任何输出，因此要结合mysqladmin查看运行结果
[root@k8s101 ~]# mysqlslap --query=sbtest.sql -c 4 --number-of-queries=1000000000
该mysqlslap线程将会一直执行，没有任何输出，因此要结合mysqladmin查看mysql性能结果。
- step3：执行mysqladmin进行监控
在另一个服务器连接客户端上执行：
## Questions统计qps
[root@k8s101 ~]# mysqladmin extended-status -r -i 1 | grep Questions
| Questions | 584644 |
| Questions | 63559 |
| Questions | 66115 |
| Questions | 64859 |
| Questions | 64876 |
| Questions | 63957 |
| Questions | 64454 |
| Questions | 63476
......
## Com_select统计查询执行的次数
[root@k8s101 ~]# mysqladmin extended-status -r -i 1 | grep Com_select
| Com_select | 704766 |
| Com_select | 34051 |
| Com_select | 32985 |
| Com_select | 32264 |
| Com_select | 33661 |
| Com_select | 31717 |
| Com_select | 32695
......
2.秒杀模拟
- step1：准备数据
use mysqlslap;
DROP TABLE IF EXISTS `stock`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `stock` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`sku_id` bigint(20) DEFAULT NULL COMMENT 'sku_id',
`ware_id` bigint(20) DEFAULT NULL COMMENT '仓库id',
`stock` int(11) DEFAULT NULL COMMENT '库存数',
`sku_name` varchar(200) DEFAULT NULL COMMENT 'sku_name',
`stock_locked` int(11) DEFAULT '0' COMMENT '锁定库存',
PRIMARY KEY (`id`) USING BTREE,
KEY `sku_id` (`sku_id`) USING BTREE,
KEY `ware_id` (`ware_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='商品库存';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `stock`
--
LOCK TABLES `stock` WRITE;
/*!40000 ALTER TABLE `stock` DISABLE KEYS */;
INSERT INTO `stock` VALUES (1,1,1,99999999,'华为 HUAWEI P40 5G 套餐1',0),(3,2,1,50,'华为 HUAWEI P40 Pro+ 麒麟990 5G 流光幻镜 套餐三',0),(4,3,1,35,'华为 HUAWEI P40 Pro+ 麒麟990 5G 流光幻镜 套餐一',0),(5,4,1,60,'华为 HUAWEI P40 Pro+ 麒麟990 5G 霓影紫 套餐二',0),(6,5,1,125,'华为 HUAWEI P40 Pro+ 麒麟990 5G 霓影紫 套餐三',0);
/*!40000 ALTER TABLE `stock` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
- step2：准备秒杀脚本
## 最简单的秒杀脚本sql，其实核心sql就这一句。
[root@k8s101 ~]# cat stock.sql
UPDATE stock SET stock=stock-1 WHERE sku_id=1 and stock>0;
-
step3：执行mysqlslap
-c num 表示模拟num个客户端发送sql请求
## 执行下列mysqlslap的时候，观察step4：mysqladmi的监控输出。 ^C表示 按下ctrl+c，中断命令
[root@k8s101 ~]# mysqlslap --query=stock.sql -c 4 --number-of-queries=1000000000
^C
[root@k8s101 ~]# mysqlslap --query=stock.sql -c 8 --number-of-queries=1000000000
^C
[root@k8s101 ~]# mysqlslap --query=stock.sql -c 16 --number-of-queries=1000000000
^C
[root@k8s101 ~]# mysqlslap --query=stock.sql -c 32 --number-of-queries=1000000000
^C
[root@k8s101 ~]# mysqlslap --query=stock.sql -c 64 --number-of-queries=1000000000
^C
[root@k8s101 ~]# mysqlslap --query=stock.sql -c 1024 --number-of-queries=1000000000
^C
- step4：执行mysqladmin进行监控
[root@k8s101 ~]# mysqladmin extended-status -r -i 1 | grep Questions
| Questions | 9730823 |
| Questions | 1 |
| Questions | 1 |
| Questions | 1 |
| Questions | 4783 ## -c 4 |
| Questions | 7172 |
| Questions | 7558 |
| Questions | 7641 |
| Questions | 7665
| Questions | 7579 |
| Questions | 1 |
| Questions | 1 |
| Questions | 1326 ## -c 8 |
| Questions | 11927 |
| Questions | 11495 |
| Questions | 11257 |
| Questions | 11370 |
| Questions | 10830 |
| Questions | 11159 |
| Questions | 11451 |
| Questions | 1 |
| Questions | 1 |
| Questions | 1 |
| Questions | 3150 ## -c 16 |
| Questions | 11955 |
| Questions | 12681 |
| Questions | 13188 |
| Questions | 13278 |
| Questions | 12765 |
| Questions | 12151 |
| Questions | 12453 |
| Questions | 5906 |
| Questions | 1 |
| Questions | 1 |
| Questions | 1 |
| Questions | 2446 ## -c 32 |
| Questions | 12068 |
| Questions | 11763 |
| Questions | 11999 |
| Questions | 11339 |
| Questions | 11744 |
| Questions | 11399 |
| Questions | 1909 |
| Questions | 1 |
| Questions | 1 |
| Questions | 1 |
| Questions | 2960 ## -c 64 |
| Questions | 9848 |
| Questions | 10197 |
| Questions | 9393 |
| Questions | 9283 |
| Questions | 10050 |
| Questions | 9793
| Questions | 464 |
| Questions | 1 |
| Questions | 1 |
| Questions | 1 |
| Questions | 2739 ## -c 1024 |
| Questions | 127 |
| Questions | 129 |
| Questions | 122 |
| Questions | 123 |
| Questions | 127 |
| Questions | 128 |
......
## 查看mysql连接数
(root@localhost)[(none)]> show processlist;
......
| 2911 | root | localhost | mysqlslap | Query | 8 | updating | UPDATE stock SET stock=stock-1 WHERE sku_id=1 and stock>0 | 0 | 0 |
| 2912 | root | localhost | mysqlslap | Query | 8 | updating | UPDATE stock SET stock=stock-1 WHERE sku_id=1 and stock>0 | 0 | 0 |
| 2913 | root | localhost | mysqlslap | Query | 7 | updating | UPDATE stock SET stock=stock-1 WHERE sku_id=1 and stock>0 | 0 | 0 |
+------+-----------------+-----------+-----------+---------+-------+------------------------+-----------------------------------------------------------+-----------+---------------+
1023 rows in set (0.00 sec) ##mysql连接数1023个
说明：并不是发送sql请求的客户端越多，qps就越高。
原因：CPU处理请求是有性能瓶颈的。
解决方法：限流。两种方案，业务限流和mysql数据库限流。
- 业务限流：在业务层面对可能需要限流的业务（比如秒杀）使用一些中间件（比如消息队列）进行限流。
- 数据库限流：为了使得数据库有一个稳定的qps，建议开启线程池。
二、线程池
1.mysql线程池
MySQL常用（目前线上使用）的线程调度方式是one-thread-per-connection（每连接一个线程），server为每一个连接创建一个线程来服务，连接断开后，这个线程进入thread_cache或者直接退出（取决于thread_cache设置及系统当前已经cache的线程数目）。one-thread-per-connection调度的好处是实现简单，而且能够在系统没有遇到瓶颈之前保证较小的响应时间，比较适合活跃的长连接的应用场景，而在大量短连接或者高并发情况下，one-thread-per-connection需要创建/调度大量的线程，产生较高的的context-switch代价，从而使得系统性能下降。
(root@localhost)[mysqlslap]> show variables like 'thread_hand%';
+-------------------------------+---------------------------+
| Variable_name | Value |
+-------------------------------+---------------------------+
| thread_handling | one-thread-per-connection |
+-------------------------------+---------------------------+
1 rows in set (0.00 sec)
2.如何开启线程池
方案一：插件启动。只支持mysql企业版本（收费）。
方案二：直接使用percona版本的mysql。
MySQLl安装percona版本见链接：https://blog.csdn.net/qq_41822345/article/details/117779815
-
step1：安装percon版本的mysql。
-
step2：修改my.cnf
[mysqld] thread_handling=pool-of-threads ## 默认one-thread-per-connection max_connections=2048
验证：
## 客户端1执行mysqlsalp
[root@k8s101 ~]# mysqlslap --query=stock.sql -c 1024 --number-of-queries=1000000000
## 客户端2执行mysqladmin进行监控
[root@k8s101 ~]# mysqladmin extended-status -r -i 1 | grep Questions
| Questions | 12432 |
| Questions | 12006 |
| Questions | 11583 |
| Questions | 11685 |
| Questions | 11163 |
| Questions | 11459 |
......
结论：即使开启了1024个客户端，同时发送sql请求，mysql实例的qps也能稳定在最高值12000左右，相比于原来未开启线程池时的qps在120左右，提高了9倍。
附注：阿里云rds版本更新中有说明，本次更新数据库实例qps提高了数十倍，实际上就是开启了线程池，在多客户端请求下，qps稳定在最大值。
所以说开启线程池很重要。
为什么一定强调开启线程池？分析业务的时候，已经把需要进行限流的地方都限流了，为甚么还需要开启线程池进行数据库限流？因为我们不知道哪些场景下可能会出现热点数据，比如网易云音乐宕机事件（某个歌星发布新歌后，用户评论导致宕机）；微博王宝强事件，微博第一反应就是后台扩资源，否则就会宕机（仅仅是一个热点事件就需要整个服务服务进行升级）。开启线程池可以很大程度上能够避免因无法预测的热点数据导致宕机的事件发生。
开启线程池的需求也是90%的公司倾向于选择使用percona版本mysql的原因。因为开启线程池很方便。
3.线程池参数
[root@k8s101 ~]# mysql -uroot -p***
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1027 ##Percona 版本
Server version: 5.7.34-37-log Percona Server (GPL), Release 37, Revision 7c516e9
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
## 开启线程池后的参数
(root@localhost)[(none)]> show variables like 'thread%';
+-------------------------------+-----------------+
| Variable_name | Value |
+-------------------------------+-----------------+
| thread_cache_size | 28 |
| thread_handling | pool-of-threads |
| thread_pool_high_prio_mode | transactions |
| thread_pool_high_prio_tickets | 4294967295 |
| thread_pool_idle_timeout | 60 |
| thread_pool_max_threads | 100000 |
| thread_pool_oversubscribe | 3 |
| thread_pool_size | 4 |
| thread_pool_stall_limit | 500 |
| thread_stack | 262144 |
| thread_statistics | OFF |
+-------------------------------+-----------------+
11 rows in set (0.10 sec)
## 查看开启线程池后的status
(root@localhost)[(none)]> show global status like 'thread%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Threadpool_idle_threads | 22 |
| Threadpool_threads | 34 |
| Threads_cached | 0 |
| Threads_connected | 1022 |
| Threads_created | 52 |
| Threads_running | 13 |
+-------------------------+-------+
6 rows in set (0.10 sec)
-
thread_cache_size
线程缓冲池大小。
-
thread_handling
one-thread-per-connection：表示一个每连接一个客户端，server为每一个连接创建一个线程来服务。
pool-of-threads：表示从线程池中拿线程处理客户端连接。
-
thread_pool_high_prio_mode
有三个取值：transactions / statements / none
transactions(default): 使用优先队列和普通队列，对于事务已经开启的statement，放到优先队列中，否则放到普通队列中。
statements：只使用优先队列
none: 只是用普通队列，本质上和statements相同，都是只是用一个队列 -
thread_pool_high_prio_tickets
取值0~4294967295，当开启了优先队列模式后（thread_pool_high_prio_mode=transactions），每个连接最多允许thread_pool_high_prio_tickets次被放到优先队列中，之后放到普通队列中，默认为4294967295
-
thread_pool_idle_timeout
worker线程最大空闲时间，单位为秒，超过限制后会退出，默认60。
-
thread_pool_max_threads
线程池中最大线程数目，所有group中worker线程总数超过该限制后不能继续创建更多线程，默认100000。
-
thread_pool_size
threadpool中group数量，默认为cpu核心数，server启动时自动计算
mysql线程池原理
根据连接id将连接分配到线程组中，线程组中的listener线程监控任务并将事务类型的任务放入优先队列，非事务任务放入普通队列，worker线程优先将优先队列中的任务处理掉再处理普通队列中的任务，线程组中worker线程循环取出任务，执行，返回结果。
通过这种方式，避免了线程和内存对象的频繁创建和释放，降低了服务端的并发度，减少了上下文切换和资源的竞争，提高资源利用效率。
三、MySQL后台线程
MySQL5.7有以下9组后台线程。
分别为1个主线程，4组IO线程，1个锁线程，1个错误线程，1个purge线程，1个page cleaner线程。
master thread : 主要负责将脏缓存页刷新到数据文件中，执行purge操作，触发检查点，合并插入缓冲区等。
insert buffer thread : 主要负责插入缓冲区的合并操作。
read thread ： 负责数据库的读取操作，可配置多个读线程。
write thread ： 负责数据库的写操作，可配置多个写线程。
log thread ： 用于将重做日志刷新到logfile中。
purge thread ： MySQL5.5 之后用单独的purge thread执行purge操作。
page cleaner thread : MySQL 5.6 之后，用来执行buffer pool 中脏页的flush 操作。
lock thread ： 负责锁控制和死锁检测等。
错误监控线程：主要负责错误监控和错误处理。
MySQL是典型单进程多线程的应用。如何查看其后台线程？
- step1：/etc/mycnf配置如下
## innodb
innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=4
innodb_log_file_size=128M
# 在线修改表schema产生的日志缓存内存大小（默认128M，线上可以调大为1G）
innodb_online_alter_log_max_size=128M
innodb_write_io_threads=16
innodb_flush_neighbors=0
innodb_log_file_size=1G
#innodb_flush_method=0_DIRECT
innodb_io_capacity=4000
innodb_page_cleaners=16
- step2：重启
[root@k8s101 mysql_test_data]# /etc/init.d/mysql.server restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
- step3：MySQL实例中查看后台线程参数
(root@localhost)[performance_schema]> select NAME,TYPE,THREAD_OS_ID from threads where name like 'thread/innodb%';
+----------------------------------------+------------+--------------+
| NAME | TYPE | THREAD_OS_ID |
+----------------------------------------+------------+--------------+
| thread/innodb/io_ibuf_thread | BACKGROUND | 5446 |
| thread/innodb/io_read_thread | BACKGROUND | 5448 |
| thread/innodb/io_log_thread | BACKGROUND | 5447 |
| thread/innodb/io_read_thread | BACKGROUND | 5451 |
| thread/innodb/io_read_thread | BACKGROUND | 5449 |
| thread/innodb/io_read_thread | BACKGROUND | 5450 |
| thread/innodb/io_write_thread | BACKGROUND | 5452 |
| thread/innodb/io_write_thread | BACKGROUND | 5453 |
| thread/innodb/io_write_thread | BACKGROUND | 5454 |
| thread/innodb/io_write_thread | BACKGROUND | 5455 |
| thread/innodb/io_write_thread | BACKGROUND | 5457 |
| thread/innodb/io_write_thread | BACKGROUND | 5456 |
| thread/innodb/io_write_thread | BACKGROUND | 5458 |
| thread/innodb/io_write_thread | BACKGROUND | 5459 |
| thread/innodb/io_write_thread | BACKGROUND | 5460 |
| thread/innodb/io_write_thread | BACKGROUND | 5462 |
| thread/innodb/io_write_thread | BACKGROUND | 5461 |
| thread/innodb/io_write_thread | BACKGROUND | 5463 |
| thread/innodb/io_write_thread | BACKGROUND | 5464 |
| thread/innodb/io_write_thread | BACKGROUND | 5465 |
| thread/innodb/io_write_thread | BACKGROUND | 5466 |
| thread/innodb/io_write_thread | BACKGROUND | 5467 |
| thread/innodb/page_cleaner_thread | BACKGROUND | 5470 |
| thread/innodb/page_cleaner_thread | BACKGROUND | 5471 |
| thread/innodb/page_cleaner_thread | BACKGROUND | 5469 |
| thread/innodb/page_cleaner_thread | BACKGROUND | 5468 |
| thread/innodb/buf_lru_manager_thread | BACKGROUND | 5472 |
| thread/innodb/buf_lru_manager_thread | BACKGROUND | 5473 |
| thread/innodb/buf_lru_manager_thread | BACKGROUND | 5474 |
| thread/innodb/buf_lru_manager_thread | BACKGROUND | 5475 |
| thread/innodb/srv_monitor_thread | BACKGROUND | 5476 |
| thread/innodb/srv_lock_timeout_thread | BACKGROUND | 5478 |
| thread/innodb/srv_error_monitor_thread | BACKGROUND | 5479 |
| thread/innodb/srv_master_thread | BACKGROUND | 5480 |
| thread/innodb/srv_worker_thread | BACKGROUND | 5482 |
| thread/innodb/srv_worker_thread | BACKGROUND | 5484 |
| thread/innodb/srv_worker_thread | BACKGROUND | 5483 |
| thread/innodb/srv_purge_thread | BACKGROUND | 5481 |
| thread/innodb/buf_dump_thread | BACKGROUND | 5485 |
| thread/innodb/dict_stats_thread | BACKGROUND | 5486 |
+----------------------------------------+------------+--------------+
40 rows in set (0.00 sec)
- step4：服务器中查看后台线程，
输入top -H。
[root@k8s101 ~]# top -H
输出如下。
top - 10:47:29 up 1:09, 4 users, load average: 0.21, 0.15, 0.14
Threads: 247 total, 2 running, 245 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.6 us, 1.8 sy, 0.0 ni, 97.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 4026384 total, 686504 free, 601716 used, 2738164 buff/cache
KiB Swap: 2097148 total, 2097148 free, 0 used. 3096684 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5446 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.06 mysqld
5447 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.10 mysqld
5448 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.04 mysqld
5449 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.05 mysqld
5450 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.06 mysqld
5451 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.05 mysqld
5452 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.07 mysqld
5453 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.05 mysqld
5454 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.07 mysqld
5455 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.05 mysqld
5456 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.06 mysqld
5457 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.05 mysqld
5458 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.05 mysqld
5459 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.05 mysqld
5460 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.05 mysqld
5461 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.05 mysqld
5462 mysql 20 0 1910596 358776 10268 S 0.0 8.9 0:00.06 mysqld
K8S/Kubernetes社区为您提供最前沿的新闻资讯和知识内容
更多推荐
0
0- 0
已为社区贡献11条内容
所有评论(0)