MySQL：終于為OS層面的線程命名了
作者：高鵬（八怪）
如果某個(gè)線程的CPU高或者IO高我們就能夠知道是什么線程。當(dāng)然你也可以和information_schema.processlist做join得到process id和state等有用的信息。
一、問(wèn)題來(lái)源
最近在檢查某個(gè)數(shù)據(jù)庫(kù)性能的時(shí)候，通過(guò)top -Hu mysql看到了一個(gè)特別奇怪的現(xiàn)象，線程有了自己的名字，我開(kāi)始以為是哪個(gè)大廠自己維護(hù)的版本，如下：
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8146 mysql 20 0 4164720 734540 26624 S 0.0 9.9 0:00.96 mysqld
8159 mysql 20 0 4164720 734540 26624 S 0.0 9.9 0:00.02 ib_io_ibuf
8160 mysql 20 0 4164720 734540 26624 S 0.0 9.9 0:00.02 ib_io_log
8161 mysql 20 0 4164720 734540 26624 S 0.0 9.9 0:00.04 ib_io_rd-1
8162 mysql 20 0 4164720 734540 26624 S 0.0 9.9 0:00.03 ib_io_rd-2
...
后來(lái)裝了一個(gè)8.0.28才發(fā)現(xiàn)確實(shí)是官方版本的新玩意。但是雖然能夠猜到一些線程的功能，可還是很陌生的樣子，因?yàn)檫@個(gè)名字和performance_schema.thread中的名字并不一樣。
這里我們就來(lái)看看它的做法和對(duì)應(yīng)關(guān)系。不過(guò)這一小步，卻是DBA的一大步，我們以往在看Oracle的進(jìn)程的時(shí)候都習(xí)慣了有命名的進(jìn)程名字，這帶來(lái)的好處是直接從OS層面就能判斷大概哪個(gè)功能的壓力增高。
二、以往的對(duì)應(yīng)方法
我們知道以前在獲取到線程的LWP號(hào)后需要到performance_schema.thread通過(guò)lwp和thread_os_id 對(duì)應(yīng)，得到如下結(jié)果：
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5524 mysql 20 0 4052588 792400 11676 S 0.0 27.4 0:03.82 mysqld
5533 mysql 20 0 4052588 792400 11676 S 0.0 27.4 0:00.00 mysqld
5556 mysql 20 0 4052588 792400 11676 S 0.0 27.4 0:00.00 mysqld
5557 mysql 20 0 4052588 792400 11676 S 0.0 27.4 0:00.00 mysqld
...
----------------------------------------+--------------+
| name | thread_os_id |
+----------------------------------------+--------------+
| thread/sql/main | 5524 |
| thread/sql/thread_timer_notifier | 5533 |
| thread/innodb/io_ibuf_thread | 5556 |
| thread/innodb/io_read_thread | 5558 |
| thread/innodb/io_log_thread | 5557 |
...
如果某個(gè)線程的CPU高或者IO高我們就能夠知道是什么線程。當(dāng)然你也可以和information_schema.processlist做join得到process id和state等有用的信息。
三、簡(jiǎn)單的實(shí)現(xiàn)方法討論
比如以innodb為例，所有的線程的OS thread name都放到了all_innodb_threads這個(gè)一個(gè)數(shù)組中，其中每個(gè)元素是一個(gè)結(jié)構(gòu)體，結(jié)構(gòu)體中包含了我們OS thread name這個(gè)元素給予了大量的代碼注釋?zhuān)揖头乓稽c(diǎn)我們?nèi)菀卓炊模?/p>
typedef struct PSI_thread_info_v5 PSI_thread_info;
The thread name to advertise to the operating system.
This feature is optional, and improves
observability for platforms that support
a flavor of pthread_setname_np().
這里我們也看到需要支持pthread_setname_np函數(shù)才行。在調(diào)用register_thread_class注冊(cè)所有的class的時(shí)候會(huì)將這些OS thread name放到一個(gè)叫做thread_class_array全局內(nèi)存中，這樣再建立線程應(yīng)該能輕松的從全局內(nèi)存中拿到每個(gè)線程的OS thread name(當(dāng)然我沒(méi)去細(xì)看了)。接著，在建立線程的時(shí)候我們調(diào)用my_thread_self_setname設(shè)置OS thread name就可以了，實(shí)際上就是調(diào)用pthread_setname_np。
四、新的Linux OS thread name和performance_schema.threads中name的對(duì)應(yīng)
為了快速的得到對(duì)應(yīng)的辦法，我稍微加了點(diǎn)輸出內(nèi)容，這樣只要有線程啟動(dòng)，就會(huì)打印到error日志，因?yàn)槿绻粋€(gè)一個(gè)去看每個(gè)線程啟動(dòng)的時(shí)候帶入的OS thread name實(shí)在太慢了，耗不起，得到的結(jié)果如下：
cat mysql3380.err |grep -w 'init threads'
[init threads] os name:boot -- mysql name:thread/sql/bootstrap
[init threads] os name:ib_io_ibuf -- mysql name:thread/innodb/io_ibuf_thread
[init threads] os name:ib_io_log -- mysql name:thread/innodb/io_log_thread
[init threads] os name:ib_io_rd-1 -- mysql name:thread/innodb/io_read_thread
[init threads] os name:ib_io_rd-2 -- mysql name:thread/innodb/io_read_thread
[init threads] os name:ib_io_rd-3 -- mysql name:thread/innodb/io_read_thread
[init threads] os name:ib_io_rd-4 -- mysql name:thread/innodb/io_read_thread
[init threads] os name:ib_io_rd-5 -- mysql name:thread/innodb/io_read_thread
[init threads] os name:ib_io_rd-6 -- mysql name:thread/innodb/io_read_thread
[init threads] os name:ib_io_rd-7 -- mysql name:thread/innodb/io_read_thread
[init threads] os name:ib_io_rd-8 -- mysql name:thread/innodb/io_read_thread
[init threads] os name:ib_io_wr-1 -- mysql name:thread/innodb/io_write_thread
[init threads] os name:ib_io_wr-2 -- mysql name:thread/innodb/io_write_thread
[init threads] os name:ib_io_wr-3 -- mysql name:thread/innodb/io_write_thread
[init threads] os name:ib_io_wr-4 -- mysql name:thread/innodb/io_write_thread
[init threads] os name:ib_pg_flush_co -- mysql name:thread/innodb/page_flush_coordinator_thread
[init threads] os name:ib_pg_flush-1 -- mysql name:thread/innodb/page_flush_thread
[init threads] os name:ib_pg_flush-2 -- mysql name:thread/innodb/page_flush_thread
[init threads] os name:ib_pg_flush-3 -- mysql name:thread/innodb/page_flush_thread
[init threads] os name:ib_recv_write -- mysql name:thread/innodb/recv_writer_thread
[init threads] os name:ib_log_checkpt -- mysql name:thread/innodb/log_checkpointer_thread
[init threads] os name:ib_log_fl_notif -- mysql name:thread/innodb/log_flush_notifier_thread
[init threads] os name:ib_log_flush -- mysql name:thread/innodb/log_flusher_thread
[init threads] os name:ib_log_wr_notif -- mysql name:thread/innodb/log_write_notifier_thread
[init threads] os name:ib_log_writer -- mysql name:thread/innodb/log_writer_thread
[init threads] os name:ib_par_rseg-0 -- mysql name:thread/innodb/parallel_rseg_init_thread
[init threads] os name:ib_par_rseg-0 -- mysql name:thread/innodb/parallel_rseg_init_thread
[init threads] os name:ib_srv_lock_to -- mysql name:thread/innodb/srv_lock_timeout_thread
[init threads] os name:ib_srv_err_mon -- mysql name:thread/innodb/srv_error_monitor_thread
[init threads] os name:ib_srv_mon -- mysql name:thread/innodb/srv_monitor_thread
[init threads] os name:ib_buf_resize -- mysql name:thread/innodb/buf_resize_thread
[init threads] os name:ib_src_main -- mysql name:thread/innodb/srv_master_thread
[init threads] os name:ib_dict_stats -- mysql name:thread/innodb/dict_stats_thread
[init threads] os name:ib_fts_opt -- mysql name:thread/innodb/fts_optimize_thread
[init threads] os name:xpl_worker-1 -- mysql name:thread/mysqlx/worker
[init threads] os name:xpl_worker-2 -- mysql name:thread/mysqlx/worker
[init threads] os name:xpl_accept-1 -- mysql name:thread/mysqlx/acceptor_network
[init threads] os name:ib_buf_dump -- mysql name:thread/innodb/buf_dump_thread
[init threads] os name:ib_clone_gtid -- mysql name:thread/innodb/clone_gtid_thread
[init threads] os name:ib_srv_purge -- mysql name:thread/innodb/srv_purge_thread
[init threads] os name:ib_srv_wkr-1 -- mysql name:thread/innodb/srv_worker_thread
[init threads] os name:ib_srv_wkr-2 -- mysql name:thread/innodb/srv_worker_thread
[init threads] os name:ib_srv_wkr-3 -- mysql name:thread/innodb/srv_worker_thread
[init threads] os name:sig_handler -- mysql name:thread/sql/signal_handler
[init threads] os name:xpl_accept-2 -- mysql name:thread/mysqlx/acceptor_network
[init threads] os name:xpl_accept-3 -- mysql name:thread/mysqlx/acceptor_network
[init threads] os name:gtid_zip -- mysql name:thread/sql/compress_gtid_table
[init threads] os name:connection -- mysql name:thread/sql/one_connection
可以看到建立的線程非常的多，但是我們得到它們的對(duì)應(yīng)關(guān)系這就夠了。這里不一一討論每個(gè)線程的功能了，不過(guò)大部分我們都非常熟悉了，比如purge線程/cleaner線程 ，這里我列出一些，其他的就自己看看吧。
- purge線程 srv_purge_thread 主要用于清理delete flag和釋放undo表空間；
- clean線程 page_cleaner_thread DBWR 主要用于進(jìn)行臟數(shù)據(jù)的刷盤(pán)和LRU鏈表的管理；
- 異步IO線程 io_read_thread/io_write_thread 通常數(shù)據(jù)預(yù)讀和刷臟會(huì)使用到異步AIO,用于合并可能的散列IO為連續(xù)IO提高性能；
- 字典收集線程 dict_stats_thread 數(shù)據(jù)修改的10%后會(huì)觸發(fā)統(tǒng)計(jì)數(shù)據(jù)的收集；
- 鎖超時(shí)監(jiān)控線程srv_lock_timeout_thread 用于監(jiān)控innodb行鎖的超時(shí)，超時(shí)進(jìn)行事務(wù)回退；
- GTID壓縮線程compress_gtid_table 將歷史的gtid壓縮為范圍，避免gtid_executed表過(guò)長(zhǎng)
- slave io線程 slave_io 從庫(kù)接收來(lái)自DUMP線程的binlog Event。將這些Event寫(xiě)入到relay log；
- slave dump線程 這是前臺(tái)線程 主庫(kù)監(jiān)控binlog的變化，發(fā)送binlog Event
- slave sql線程 slave_sql 從庫(kù)負(fù)責(zé)執(zhí)行binlog Event；
- 用戶(hù)線程one_connection 一個(gè)session就是一個(gè)用戶(hù)線程，對(duì)于用戶(hù)線程而言。可以通過(guò)processlist_id和show processlist；
- srv_error_monitor_thread 所謂的信號(hào)量監(jiān)控線程，注意這個(gè)信號(hào)量不是OS的信號(hào)量。是Innodb內(nèi)部的rw lock和mutex；
- srv_monitor_thread 這個(gè)線程當(dāng)參數(shù)innodb_status_output打開(kāi)的情況下，每15秒輸出一個(gè)show engine innodb status信息到日志文件。也會(huì)自動(dòng)開(kāi)啟比如Innodb內(nèi)存不足會(huì)自動(dòng)開(kāi)啟。
- log_writer線程：將redo寫(xiě)到redo文件；
- log_write_notifier線程：通知用戶(hù)會(huì)話(huà)寫(xiě)入redo文件結(jié)束；
- log_flusher線程：將redo fsync到redo文件；
- log_flush_notifier線程：通知用戶(hù)會(huì)話(huà)fsync結(jié)束；
- log_checkpointer線程：定期檢查臟數(shù)據(jù)寫(xiě)盤(pán)的redo 位置。
五、用pthread_setname_np為線程命名
這里我就隨便寫(xiě)了4個(gè)循環(huán)的線程調(diào)用這個(gè)函數(shù)為我的線程命名為D-GPWK，需要耗用較高的CPU，看到的結(jié)果如下：
image.png
如果這個(gè)線程是MySQL的線程，當(dāng)看到這個(gè)結(jié)果，我們就能明白大概的方向了。
《MySQL主從原理》作者：高鵬（八怪）
責(zé)任編輯：武曉燕
來(lái)源：
MySQL學(xué)習(xí)
