筆者需從阿里云RDS同步單表4億+的數(shù)據(jù)（淚崩），立刻用寫(xiě)了一段同步代碼，周一到周五跑5天發(fā)現(xiàn)才同步到1200w數(shù)據(jù)，一天不到300w，照這個(gè)速度下去，同步過(guò)來(lái)要130天，還不能出現(xiàn)socket超時(shí)，想想也是醉了。所以決定上多線(xiàn)程分段同步，充分利用多核CPU的優(yōu)勢(shì)。
吃出缺少多線(xiàn)程架構(gòu)圖。
線(xiàn)上從上周五晚跑到周一。不到三天時(shí)間，同步了2.7億。效果顯著，下面開(kāi)始分析多線(xiàn)程的性能。
A服務(wù)器跑tomcat，B服務(wù)器跑MySQL。
先分析B服務(wù)器的各項(xiàng)指標(biāo)和性能。
1.top
-
進(jìn)入交互模式，輸入1查看各cpu指標(biāo)
topb1.jpg
其中關(guān)鍵列釋義如下：
- us, user： 運(yùn)行(未調(diào)整優(yōu)先級(jí)的) 用戶(hù)進(jìn)程的CPU時(shí)間
- sy，system: 運(yùn)行內(nèi)核進(jìn)程的CPU時(shí)間
- ni，niced：運(yùn)行已調(diào)整優(yōu)先級(jí)的用戶(hù)進(jìn)程的CPU時(shí)間
- wa，IO wait: 用于等待IO完成的CPU時(shí)間
很明顯，圖中cpu占比非常低，其中幾個(gè)cpu的IO等待時(shí)間過(guò)長(zhǎng)。猜測(cè)瓶頸在IO上。繼續(xù)往下看。
-
iostat -x 1
iostat-x.jpg
關(guān)鍵列釋義：
- avgrq-sz：每個(gè)IO的平均扇區(qū)數(shù)，即所有請(qǐng)求的平均大小，以扇區(qū)（512字節(jié)）為單位。
- avgqu-sz：平均等待處理的IO請(qǐng)求隊(duì)列長(zhǎng)度。
- %util： 工作時(shí)間或者繁忙時(shí)間占總時(shí)間的百分比。在統(tǒng)計(jì)時(shí)間內(nèi)所有處理IO時(shí)間除以總共統(tǒng)計(jì)時(shí)間。例如，如果統(tǒng)計(jì)間隔1秒，該設(shè)備有0.8秒在處理IO，而0.2秒閑置，那么該設(shè)備的%util = 0.8/1 = 80%，所以該參數(shù)暗示了設(shè)備的繁忙程度。一般地，如果該參數(shù)是100%表示設(shè)備已經(jīng)接近滿(mǎn)負(fù)荷運(yùn)行了（當(dāng)然如果是多磁盤(pán)，即使%util是100%，因?yàn)榇疟P(pán)的并發(fā)能力，所以磁盤(pán)使用未必就到了瓶頸）。
很明顯磁盤(pán)IO已經(jīng)接近滿(mǎn)負(fù)荷。
Device釋義：
dm-0、dm-1、dm-2的主設(shè)備號(hào)是253（是linux內(nèi)核留給本地使用的設(shè)備號(hào)），次設(shè)備號(hào)分別是0、1、2，這類(lèi)設(shè)備在/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負(fù)荷是有MySQL寫(xiě)入數(shù)據(jù)造成，從MySQL角度分析。
mysql> show processlist;
+------+-----------------+---------------------------+------------+---------+---------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+---------------------------+------------+---------+---------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 6 | system user | | NULL | Connect | 1740864 | Waiting for master to send event | NULL |
| 7 | system user | | NULL | Connect | 6 | Slave has read all relay log; waiting for more updates | NULL |
| 202 | event_scheduler | localhost | NULL | Daemon | 862768 | Waiting on empty queue | NULL |
| 1058 | root | localhost:52655 | report_his | Query | 0 | update | INSERT INTO tbl_report_201808 (city_id, county_id, imei, imei2, location
, meid, nerwork, network_o |
| 1107 | root | localhost:53006 | report_his | Query | 0 | update | INSERT INTO tbl_report_201808 (city_id, county_id, imei, imei2, location
, meid, nerwork, network_o |
| 1176 | root | localhost:56576 | report_his | Query | 0 | update | INSERT INTO tbl_report_201806 (city_id, county_id, imei, imei2, location
, meid, nerwork, network_o |
| 1177 | root | localhost:56577 | report_his | Query | 0 | update | INSERT INTO tbl_report_201807 (city_id, county_id, imei, imei2, location
, meid, nerwork, network_o |
| 1178 | root | localhost:56578 | report_his | Query | 0 | update | INSERT INTO tbl_report_201804 (city_id, county_id, imei, imei2, location
, meid, nerwork, network_o |
| 1180 | root | localhost:56650 | report_his | Query | 0 | update | INSERT INTO tbl_report_201804 (city_id, county_id, imei, imei2, location
, meid, nerwork, network_o |
| 1182 | root | localhost:56721 | report_his | Query | 0 | update | INSERT INTO tbl_report_201804 (city_id, county_id, imei, imei2, location
, meid, nerwork, network_o |
| 1183 | root | localhost:56792 | report_his | Query | 0 | update | INSERT INTO tbl_report_201801 (city_id, county_id, imei, imei2, location
, meid, nerwork, network_o |
| 1300 | root | localhost:50137 | report_his | Query | 0 | update | INSERT INTO tbl_report_201804 (city_id, county_id, imei, imei2, location
, meid, nerwork, network_o |
| 1305 | root | localhost:51841 | report_his | Sleep | 2 | | NULL |
| 1439 | root | 10.206.16.88:59693 | cdc_test | Sleep | 12446 | | NULL |
| 1440 | root | 10.206.16.88:59696 | cdc_test | Sleep | 22184 | | NULL |
| 1496 | root | localhost:52007 | report_his | Sleep | 28 | | NULL |
| 1497 | root | localhost:52026 | report_his | Sleep | 22 | | NULL |
| 1498 | root | localhost:52027 | report_his | Sleep | 29 | | NULL |
| 1499 | root | localhost:52028 | report_his | Query | 0 | update | INSERT INTO tbl_report_201802 (city_id, county_id, imei, imei2, location
, meid, nerwork, network_o |
| 1500 | root | localhost:52099 | report_his | Sleep | 2 | | NULL |
| 1501 | root | localhost:52100 | report_his | Sleep | 29 | | NULL |
| 1502 | root | localhost:52171 | report_his | Sleep | 29 | | NULL |
| 1503 | root | localhost:52242 | report_his | Sleep | 12 | | NULL |
|
| 1516 | root | localhost | NULL | Query | 0 | starting | show processlist |
+------+-----------------+---------------------------+------------+---------+---------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
24 rows in set (0.00 sec)
有不少insert語(yǔ)句還在執(zhí)行中，說(shuō)明寫(xiě)表語(yǔ)句是很慢的。
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--內(nèi)核進(jìn)程的狀態(tài)
--r 運(yùn)行隊(duì)列中的進(jìn)程數(shù),在一個(gè)穩(wěn)定的工作量下,應(yīng)該少于5
--b 等待隊(duì)列中的進(jìn)程數(shù)(等待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時(shí)55分59秒 CPU %user %nice %system %iowait %steal %idle
18時(shí)56分00秒 all 1.10 0.00 0.78 7.67 0.00 90.44
18時(shí)56分01秒 all 1.05 0.00 1.15 8.72 0.00 89.08
18時(shí)56分02秒 all 0.98 0.00 0.73 7.67 0.00 90.62
18時(shí)56分03秒 all 1.08 0.00 0.65 8.58 0.00 89.69
18時(shí)56分04秒 all 0.60 0.00 0.40 8.39 0.00 90.61
18時(shí)56分05秒 all 0.83 0.00 0.58 7.10 0.00 91.50
18時(shí)56分06秒 all 0.78 0.00 0.48 8.02 0.00 90.72
18時(shí)56分07秒 all 0.73 0.00 0.68 7.52 0.00 91.07
18時(shí)56分08秒 all 0.58 0.00 0.58 8.72 0.00 90.12
18時(shí)56分09秒 all 0.65 0.00 0.53 8.72 0.00 90.10
18時(shí)56分10秒 all 1.00 0.00 0.63 8.36 0.00 90.01
18時(shí)56分11秒 all 0.68 0.00 0.50 5.39 0.00 93.44
18時(shí)56分12秒 all 1.35 0.00 0.80 6.57 0.00 91.28
18時(shí)56分13秒 all 0.80 0.00 0.53 7.60 0.00 91.07
18時(shí)56分14秒 all 0.58 0.00 0.53 6.49 0.00 92.40
18時(shí)56分15秒 all 0.83 0.00 0.45 7.85 0.00 90.87
--%user
在用戶(hù)模式中運(yùn)行進(jìn)程所花的時(shí)間的百分比
--%nice
運(yùn)行正常進(jìn)程所花的時(shí)間的百分比
--%system
在內(nèi)核模式（系統(tǒng)）中運(yùn)行進(jìn)程所花的時(shí)間的百分比
--%iowait
沒(méi)有進(jìn)程在該CPU上執(zhí)行時(shí)，處理器等待I/O完成的時(shí)間的百分比 --這個(gè)值過(guò)高，表示硬盤(pán)存在I/O瓶頸
--%idle
CPU空閑時(shí)間百分比 ---如果這個(gè)值很高 但是系統(tǒng)響應(yīng)慢 這時(shí)候應(yīng)該加大內(nèi)存 如果這個(gè)值持續(xù)太低 說(shuō)明系統(tǒng)缺少cpu資源
連接數(shù)
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設(shè)置過(guò)低或者超過(guò)服務(wù)器負(fù)載上限了
請(qǐng)求隊(duì)列長(zhǎng)度back_log
MySQL能夠暫存的連接數(shù)量。當(dāng)主要MySQL線(xiàn)程在一個(gè)很短時(shí)間內(nèi)得到非常多的連接請(qǐng)求，他就會(huì)起作用。如果MySQL的連接數(shù)據(jù)達(dá)到max_connections時(shí)，新的請(qǐng)求將會(huì)被存在堆棧中，以等待某一連接釋放資源，該堆棧數(shù)量即back_log，如果等待連接的數(shù)量超過(guò)back_log，將不被接受連接資源。
緩存簇
show status like 'key_blocks_u%';使用和未使用緩存簇(blocks)數(shù)
show variables like '%Key_cache%';
show variables like '%Key_buffer_size%';
如果Key_blocks_used * key_cache_block_size 遠(yuǎn)小于key_buffer_size 那么就意味著內(nèi)存浪費(fèi)了 應(yīng)該調(diào)大key_buffer_size值
key_buffer_size
key_buffer_size指定索引緩沖區(qū)的大小，他決定索引的處理速度，尤其是索引讀的速度。通過(guò)檢查狀態(tài)值 key_read_requests和key_reads，可以知道key_buffer_size設(shè)置是否合理。比例key_reads/key_read_requests應(yīng)該盡可能的低，至少是1：100，1：1000更好（上述狀態(tài)值可以使用show status like ‘key_read%'獲得）
未命中緩存的概率：
key_cache_miss_rate = key_reads/key_read_requests*100%
key_buffer_size只對(duì)MAISAM表起作用。
如何調(diào)整key_buffer_size的值
默認(rèn)的配置數(shù)時(shí)8388608（8M），主機(jī)有4G內(nèi)存可以調(diào)優(yōu)值為268435456（256M）
線(xiàn)程使用情況
show status like 'Thread%';如果發(fā)現(xiàn)Threads_created值過(guò)大的話(huà)，可以適當(dāng)增加配置文件中thread_cache_size值
Threads_cached用來(lái)緩存線(xiàn)程
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)
打開(kāi)的文件數(shù)
show status like '%open_file%';
show variables like '%open_file%';
如果Open_files和open_files_limit接近 就應(yīng)該增加open_files_limit的大小
不過(guò)mysql打開(kāi)的文件描述符限制都是OS的文件描述符限制，和配置文件中open_files_limit的設(shè)置沒(méi)有關(guān)系
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)
全聯(lián)接
show status like '%select_full__%';
全鏈接是無(wú)索引鏈接 最好避免
如果Select_full_range_join過(guò)高 說(shuō)明系統(tǒng)運(yùn)行了很多范圍查詢(xún)聯(lián)接表
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)
打開(kāi)表情況
show tatus like 'open%tables%';
如果open_tables接近table_cache的時(shí)候，并且Opened_tables這個(gè)值在逐步增加，說(shuō)明table_cache不夠用 表緩存沒(méi)有完全用上 那就要考慮增加table_cache的大小了。還有就是Table_locks_waited比較高的時(shí)候，也需要增加table_cache
mysql> show status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 1555 |
| Opened_tables | 2 |
+---------------+-------+
2 rows in set (0.00 sec)
查詢(xún)緩存
show status like 'qcache%';
show variables like 'query_cache%';察看query_cache的配置
query_cache_limit：超過(guò)此大小的查詢(xún)將不緩存
query_cache_min_res_unit：緩存塊的最小大小
query_cache_size：查詢(xún)緩存大小
query_cache_type：緩存類(lèi)型，決定緩存什么樣的查詢(xún)，示例中表示不緩存 select sql_no_cache 查詢(xún)
query_cache_wlock_invalidate：當(dāng)有其他客戶(hù)端正在對(duì)MyISAM表進(jìn)行寫(xiě)操作時(shí)，如果查詢(xún)?cè)趒uery cache中，是否返回cache結(jié)果還是等寫(xiě)操作完成再讀表獲取結(jié)果
排序情況
show status like 'sort%';
Sort_merge_passes過(guò)大 就要增加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 存儲(chǔ)引擎鍵高速緩存 對(duì)MyISAM表性能影響很大大
show status like 'key_read%';磁盤(pán)讀取索引的請(qǐng)求次數(shù)
索引未命中緩存的概率=Key_reads / Key_read_requests * 100%
不能以Key_read_requests / Key_reads原則來(lái)設(shè)置key_buffer_size
Key_reads 將這個(gè)值和系統(tǒng)的i/o做對(duì)比
表鎖情況
show status like 'table_locks%';
Table_locks_waited顯示了多少表被鎖住并導(dǎo)致了mysql的鎖等待 可以開(kāi)啟慢查詢(xún)看一下
mysql> show status like 'table_locks%';
表掃描情況
show status like 'handler_read%';
show status like 'com_select';
如果Handler_read_rnd_next /Handler_read_rnd 的值過(guò)大 那么就應(yīng)該優(yōu)化索引、查詢(xún)
臨時(shí)表情況
show status like 'created_tmp%';
show variables like 'tmp_table%';
show variables like 'max_heap%';
如果Created_tmp_disk_tables值較高 則有可能是因?yàn)椋簍mp_table_size或者max_heap_table_size太小
或者是選擇blob、text屬性的時(shí)候創(chuàng)建了臨時(shí)表
Created_tmp_tables 過(guò)高的話(huà) 那么就有話(huà)查詢(xún)吧
二進(jìn)制日志緩存
show status like'%binlog%';
show variables like'%binlog%';
如果Binlog_cache_disk_use 和 Binlog_cache_use 比例很大 那么就應(yīng)該增加binlog_cache_size的值
還有一個(gè)問(wèn)題就是web服務(wù)器有406個(gè)線(xiàn)程并發(fā)執(zhí)行，web服務(wù)器的各項(xiàng)指標(biāo)如IO、磁盤(pán)、CPU、網(wǎng)卡、上下文切換均保持正常，jstack -l pid發(fā)現(xiàn)非常多線(xiàn)程wait在saveBatch，即保存到MySQL服務(wù)器上，如上檢查，MySQL服務(wù)器的連接、線(xiàn)程、均正常，寫(xiě)入磁盤(pán)負(fù)荷已滿(mǎn)，但是MySQL的的連接卻不超過(guò)10個(gè)，web服務(wù)器的那么多并發(fā)線(xiàn)程都在哪里呢，經(jīng)排查，原來(lái)web服務(wù)器的連接池滿(mǎn)了，最大連接配置為10個(gè)，406個(gè)線(xiàn)程均在等待連接釋放，而這10個(gè)連接寫(xiě)入MySQL已經(jīng)把磁盤(pán)懟到滿(mǎn)負(fù)荷，所以即使加增加連接池連接會(huì)讓MySQL阻塞寫(xiě)入，并把寫(xiě)入請(qǐng)求加入等待隊(duì)列，并不能提高磁盤(pán)寫(xiě)入。
這種情況與朋友討論后，說(shuō)有兩個(gè)途徑可以提高磁盤(pán)寫(xiě)入瓶頸，第一、增加數(shù)據(jù)庫(kù)節(jié)點(diǎn)；第二、利用數(shù)據(jù)空間把數(shù)據(jù)放在不同的文件系統(tǒng)。
