多线程读写mysql_记录一次多线程写入MySQL分表性能分析

最新推荐文章于 2024-08-18 11:29:29 发布

weixin_39569753

最新推荐文章于 2024-08-18 11:29:29 发布

阅读量795

点赞数

文章标签：多线程读写mysql

本文链接：https://blog.csdn.net/weixin_39569753/article/details/113337403

版权

本文详细记录了在同步4亿+数据时，通过多线程实现MySQL分库提升性能的过程。通过监控分析发现IO瓶颈在于磁盘，进而从MySQL服务器的CPU、IO、连接数等方面进行性能优化，揭示了数据库性能优化的重要性。

摘要由CSDN通过智能技术生成

笔者需从阿里云RDS同步单表4亿+的数据(泪崩)，立刻用写了一段同步代码，周一到周五跑5天发现才同步到1200w数据，一天不到300w，照这个速度下去，同步过来要130天，还不能出现socket超时，想想也是醉了。所以决定上多线程分段同步，充分利用多核CPU的优势。

吃出缺少多线程架构图。

线上从上周五晚跑到周一。不到三天时间，同步了2.7亿。效果显著，下面开始分析多线程的性能。

A服务器跑tomcat，B服务器跑MySQL。

先分析B服务器的各项指标和性能。

1.top

6ef0fddfa3e3

top_b.jpg

进入交互模式，输入1查看各cpu指标

6ef0fddfa3e3

topb1.jpg

其中关键列释义如下：

us, user：运行(未调整优先级的) 用户进程的CPU时间

sy，system: 运行内核进程的CPU时间

ni，niced：运行已调整优先级的用户进程的CPU时间

wa，IO wait: 用于等待IO完成的CPU时间

很明显，图中cpu占比非常低，其中几个cpu的IO等待时间过长。猜测瓶颈在IO上。继续往下看。

iostat -x 1

6ef0fddfa3e3

iostat-x.jpg

关键列释义：

avgrq-sz：每个IO的平均扇区数，即所有请求的平均大小，以扇区(512字节)为单位。

avgqu-sz：平均等待处理的IO请求队列长度。

%util：工作时间或者繁忙时间占总时间的百分比。在统计时间内所有处理IO时间除以总共统计时间。例如，如果统计间隔1秒，该设备有0.8秒在处理IO，而0.2秒闲置，那么该设备的%util = 0.8/1 = 80%，所以该参数暗示了设备的繁忙程度。一般地，如果该参数是100%表示设备已经接近满负荷运行了(当然如果是多磁盘，即使%util是100%，因为磁盘的并发能力，所以磁盘使用未必就到了瓶颈)。

很明显磁盘IO已经接近满负荷。

Device释义：

dm-0、dm-1、dm-2的主设备号是253(是linux内核留给本地使用的设备号)，次设备号分别是0、1、2，这类设备在/dev/mapper中

[root@sell180 conf]# ll /dev/mapper/

总用量 0

lrwxrwxrwx 1 root root 7 5月 13 14:07 centos-data -> ../dm-2

lrwxrwxrwx 1 root root 7 5月 13 14:07 centos-root -> ../dm-0

lrwxrwxrwx 1 root root 7 5月 13 14:07 centos-swap -> ../dm-1

crw------- 1 root root 10, 236 5月 13 14:07 control

lrwxrwxrwx 1 root root 7 10月 10 09:03 docker-253:2-4301390401-109409ce8ed2759e6483127271832fc510f87bc42ec8069879e3ccb18ab1fcba -> ../dm-6

lrwxrwxrwx 1 root root 7 10月 9 15:58 docker-253:2-4301390401-69803b40f7be3af3cefd966f6dd616b735d8be9f8cc994031c94201d283c45fb -> ../dm-7

lrwxrwxrwx 1 root root 7 10月 10 09:03 docker-253:2-4301390401-ac3f4238962d8c9a53d05f1b0901317934e5a92f81f8124d639eb7c4d0b61ff8 -> ../dm-5

lrwxrwxrwx 1 root root 7 10月 9 15:58 docker-253:2-4301390401-d19b04116d5fe13131838b0395dd7015e7844e2c85131879dc43cd2c7bdf557e -> ../dm-8

lrwxrwxrwx 1 root root 7 10月 10 09:03 docker-253:2-4301390401-dbd692e604bf93d5bb4556af88c38310a24815a4234b25b114da12c06ddc80bb -> ../dm-4

lrwxrwxrwx 1 root root 7 5月 13 14:08 docker-253:2-4301390401-pool -> ../dm-3

IO负荷是有MySQL写入数据造成，从MySQL角度分析。

mysql> show processlist;

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

, meid, nerwork, network_o |

, meid, nerwork, network_o |

, meid, nerwork, network_o |

, meid, nerwork, network_o |

, meid, nerwork, network_o |

, meid, nerwork, network_o |

, meid, nerwork, network_o |

, meid, nerwork, network_o |

, meid, nerwork, network_o |

| 1439 | root | 10.206.16.88:59693 | cdc_test | Sleep | 12446 | | NULL |

| 1440 | root | 10.206.16.88:59696 | cdc_test | Sleep | 22184 | | NULL |

, meid, nerwork, network_o |

24 rows in set (0.00 sec)

有不少insert语句还在执行中，说明写表语句是很慢的。

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----

r b swpd free buff cache si so bi bo in cs us sy id wa st

1 5 4051600 298140 1096 50074544 0 0 1 27 0 0 0 0 100 0 0

0 3 4051616 307452 1096 50065236 0 16 12188 23371 6291 16749 1 0 91 8 0

0 2 4051616 294860 1096 50078100 0 0 12640 25342 6156 16550 1 0 93 6 0

4 2 4051640 305124 1096 50068740 0 24 11728 26707 7131 20233 1 1 91 7 0

1 6 4051640 293792 1096 50080464 0 0 12172 29240 6995 19640 1 1 91 7 0

1 4 4051668 308576 1096 50064232 0 28 14344 27741 7403 19994 1 1 90 8 0

0 4 4051668 295648 1096 50077824 0 0 14060 26221 6816 18383 1 1 91 8 0

0 3 4051696 305264 1096 50067612 0 28 12044 26940 13189 19431 1 1 91 8 0

0 8 4051696 297200 1096 50075552 0 0 8880 28778 7318 20127 1 1 91 7 0

0 9 4051708 306360 1096 50067192 0 16 12924 28790 9666 30541 2 1 90 7 0

其中:

procs--内核进程的状态

--r 运行队列中的进程数,在一个稳定的工作量下,应该少于5

--b 等待队列中的进程数(等待I/O),通常情况下是接近0的.

[root@sell180 conf]# sar -u 1

Linux 3.10.0-327.el7.x86_64 (sell180) 2018年11月05日 _x86_64_ (40 CPU)

18时55分59秒 CPU %user %nice %system %iowait %steal %idle

18时56分00秒 all 1.10 0.00 0.78 7.67 0.00 90.44

18时56分01秒 all 1.05 0.00 1.15 8.72 0.00 89.08

18时56分02秒 all 0.98 0.00 0.73 7.67 0.00 90.62

18时56分03秒 all 1.08 0.00 0.65 8.58 0.00 89.69

18时56分04秒 all 0.60 0.00 0.40 8.39 0.00 90.61

18时56分05秒 all 0.83 0.00 0.58 7.10 0.00 91.50

18时56分06秒 all 0.78 0.00 0.48 8.02 0.00 90.72

18时56分07秒 all 0.73 0.00 0.68 7.52 0.00 91.07

18时56分08秒 all 0.58 0.00 0.58 8.72 0.00 90.12

18时56分09秒 all 0.65 0.00 0.53 8.72 0.00 90.10

18时56分10秒 all 1.00 0.00 0.63 8.36 0.00 90.01

18时56分11秒 all 0.68 0.00 0.50 5.39 0.00 93.44

18时56分12秒 all 1.35 0.00 0.80 6.57 0.00 91.28

18时56分13秒 all 0.80 0.00 0.53 7.60 0.00 91.07

18时56分14秒 all 0.58 0.00 0.53 6.49 0.00 92.40

18时56分15秒 all 0.83 0.00 0.45 7.85 0.00 90.87

--%user

在用户模式中运行进程所花的时间的百分比

--%nice

运行正常进程所花的时间的百分比

--%system

在内核模式(系统)中运行进程所花的时间的百分比

--%iowait

没有进程在该CPU上执行时，处理器等待I/O完成的时间的百分比 --这个值过高，表示硬盘存在I/O瓶颈

--%idle

CPU空闲时间百分比 ---如果这个值很高但是系统响应慢这时候应该加大内存如果这个值持续太低说明系统缺少cpu资源

连接数

show variables like 'max_connections'

show status like 'max_used_connections'

max_used_connections / max_connections * 100% (理想值 ≈ 85%)

如果max_used_connections跟max_connections相同那么就是max_connections设置过低或者超过服务器负载上限了

请求队列长度back_log

MySQL能够暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，他就会起作用。如果MySQL的连接数据达到max_connections时，新的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈数量即back_log，如果等待连接的数量超过back_log，将不被接受连接资源。

缓存簇

show status like 'key_blocks_u%';使用和未使用缓存簇(blocks)数

show variables like '%Key_cache%';

show variables like '%Key_buffer_size%';

如果Key_blocks_used * key_cache_block_size 远小于key_buffer_size 那么就意味着内存浪费了应该调大key_buffer_size值

key_buffer_size

key_buffer_size指定索引缓冲区的大小，他决定索引的处理速度，尤其是索引读的速度。通过检查状态值 key_read_requests和key_reads，可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低，至少是1：100，1：1000更好(上述状态值可以使用show status like ‘key_read%'获得)

未命中缓存的概率：

key_cache_miss_rate = key_reads/key_read_requests*100%

key_buffer_size只对MAISAM表起作用。

如何调整key_buffer_size的值

默认的配置数时8388608(8M)，主机有4G内存可以调优值为268435456(256M)

线程使用情况

show status like 'Thread%';如果发现Threads_created值过大的话，可以适当增加配置文件中thread_cache_size值

Threads_cached用来缓存线程

mysql> show status like 'Thread%';

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

| Variable_name | Value |

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

| Threads_cached | 8 |

| Threads_connected | 24 |

| Threads_created | 811 |

| Threads_running | 5 |

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

4 rows in set (0.01 sec)

打开的文件数

show status like '%open_file%';

show variables like '%open_file%';

如果Open_files和open_files_limit接近就应该增加open_files_limit的大小

不过mysql打开的文件描述符限制都是OS的文件描述符限制，和配置文件中open_files_limit的设置没有关系

mysql> show status like '%open_file%';

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

| Variable_name | Value |

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

| Innodb_num_open_files | 299 |

| Open_files | 48 |

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

2 rows in set (0.00 sec)

mysql> show variables like '%open_file%';

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

| Variable_name | Value |

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

| innodb_open_files | 2000 |

| open_files_limit | 5000 |

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

2 rows in set (0.00 sec)

全联接

show status like '%select_full__%';

全链接是无索引链接最好避免

如果Select_full_range_join过高说明系统运行了很多范围查询联接表

mysql> show status like '%select_full__%';

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

| Variable_name | Value |

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

| Select_full_join | 0 |

| Select_full_range_join | 0 |

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

2 rows in set (0.00 sec)

打开表情况

show tatus like 'open%tables%';

如果open_tables接近table_cache的时候，并且Opened_tables这个值在逐步增加，说明table_cache不够用表缓存没有完全用上那就要考虑增加table_cache的大小了。还有就是Table_locks_waited比较高的时候，也需要增加table_cache

mysql> show status like 'open%tables%';

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

| Variable_name | Value |

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

| Open_tables | 1555 |

| Opened_tables | 2 |

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

2 rows in set (0.00 sec)

查询缓存

show status like 'qcache%';

show variables like 'query_cache%';察看query_cache的配置

query_cache_limit：超过此大小的查询将不缓存

query_cache_min_res_unit：缓存块的最小大小

query_cache_size：查询缓存大小

query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询

query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果

排序情况

show status like 'sort%';

Sort_merge_passes过大就要增加Sort_buffer_size 但是盲目的增加 Sort_buffer_size 并不一定能提高速度

mysql> show status like 'sort%';

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

| Variable_name | Value |

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

| Sort_merge_passes | 0 |

| Sort_range | 0 |

| Sort_rows | 0 |

| Sort_scan | 0 |

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

4 rows in set (0.00 sec)

高速缓存

show variables like 'key_buffer_size';MyISAM 存储引擎键高速缓存对MyISAM表性能影响很大大

show status like 'key_read%';磁盘读取索引的请求次数

索引未命中缓存的概率=Key_reads / Key_read_requests * 100%

不能以Key_read_requests / Key_reads原则来设置key_buffer_size

Key_reads 将这个值和系统的i/o做对比

表锁情况

show status like 'table_locks%';

Table_locks_waited显示了多少表被锁住并导致了mysql的锁等待可以开启慢查询看一下

mysql> show status like 'table_locks%';

表扫描情况

show status like 'handler_read%';

show status like 'com_select';

如果Handler_read_rnd_next /Handler_read_rnd 的值过大那么就应该优化索引、查询

临时表情况

show status like 'created_tmp%';

show variables like 'tmp_table%';

show variables like 'max_heap%';

如果Created_tmp_disk_tables值较高则有可能是因为：tmp_table_size或者max_heap_table_size太小

或者是选择blob、text属性的时候创建了临时表

Created_tmp_tables 过高的话那么就有话查询吧

二进制日志缓存

show status like'%binlog%';

show variables like'%binlog%';

如果Binlog_cache_disk_use 和 Binlog_cache_use 比例很大那么就应该增加binlog_cache_size的值

还有一个问题就是web服务器有406个线程并发执行，web服务器的各项指标如IO、磁盘、CPU、网卡、上下文切换均保持正常，jstack -l pid发现非常多线程wait在saveBatch，即保存到MySQL服务器上，如上检查，MySQL服务器的连接、线程、均正常，写入磁盘负荷已满，但是MySQL的的连接却不超过10个，web服务器的那么多并发线程都在哪里呢，经排查，原来web服务器的连接池满了，最大连接配置为10个，406个线程均在等待连接释放，而这10个连接写入MySQL已经把磁盘怼到满负荷，所以即使加增加连接池连接会让MySQL阻塞写入，并把写入请求加入等待队列，并不能提高磁盘写入。

这种情况与朋友讨论后，说有两个途径可以提高磁盘写入瓶颈，第一、增加数据库节点；第二、利用数据空间把数据放在不同的文件系统。