查看MySQL伺服器執行緒數的方法
- 小云云原創
- 2018-03-26 09:35:181743瀏覽
本文主要和大家介紹了MySQL伺服器線程數的查看方法,結合實例形式分析了mysql線程數查看的相關命令、配置、參數及相關使用技巧,需要的朋友可以參考下，希望能幫助到大家。
mysql重啟指令：
/etc/init.d/mysql restart
MySQL伺服器的執行緒數需要在一個合理的範圍之內，這樣才能保證MySQL伺服器健康平穩地運作。 Threads_created表示已建立的執行緒數，透過查看Threads_created就可以查看MySQL伺服器的進程狀態。
mysql> show global status like 'Thread%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 46 | | Threads_connected | 2 | | Threads_created | 570 | | Threads_running | 1 | +-------------------+-------+
如果我們在MySQL伺服器設定檔中設定了thread_cache_size，當客戶端斷開之後，伺服器處理此客戶的執行緒將會快取起來回應下一個客戶而不是銷毀(前提是快取數未達上限)。
Threads_created表示創建過的線程數，如果發現Threads_created值過大的話，表示MySQL伺服器一直在創建線程，這也是比較耗資源，可以適當增加設定檔中thread_cache_size值，查詢伺服器
thread_cache_size設定：
mysql> show variables like 'thread_cache_size'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | thread_cache_size | 64 | +-------------------+-------+
範例中的伺服器還挺健康的。
解析MySQL與連接數相關的幾個參數
MySQL的variables和status是管理維護的利器，就類似Oracle的spfile和v$表。
MySQL透過系統變數記錄很多組態訊息，例如最大連線數max_connections：
mysql> show variables like '%connect%'; +--------------------------+-----------------+ | Variable_name | Value | +--------------------------+-----------------+ | character_set_connection | utf8 | | collation_connection | utf8_general_ci | | connect_timeout | 10 | | init_connect | SET NAMES utf8 | | max_connect_errors | 10 | | max_connections | 200 | | max_user_connections | 0 | +--------------------------+-----------------+ 7 rows in set (0.00 sec)
這個參數是指同時連線上來的客戶端數量，在5.1版本裡預設的值是151，那麼實際支援的連線數是這個值加一，也就是152，因為要為系統管理員登入上來查看資訊保留一個連線。這個參數的大小要綜合很多因素來考慮，例如使用的平台所支援的線程庫數量（windows只能支援到2048）、伺服器的配置（特別是記憶體大小）、每個連接佔用資源（記憶體和負載）的多少、系統所需的回應時間等。一般Linux系統支援到幾百併發是沒有任何問題的。可以在global或session範圍內修改這個參數：
mysql> set global max_connections=151; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%connect%'; +--------------------------+-----------------+ | Variable_name | Value | +--------------------------+-----------------+ | character_set_connection | utf8 | | collation_connection | utf8_general_ci | | connect_timeout | 10 | | init_connect | SET NAMES utf8 | | max_connect_errors | 10 | | max_connections | 151 | | max_user_connections | 0 | +--------------------------+-----------------+ 7 rows in set (0.00 sec)
但是要注意的是，連接數的增加會帶來很多連鎖反應，需要在實際中避免由此產生的負面影響。
首先我們來看看status的輸出：
mysql> status -------------- mysql Ver 14.14 Distrib 5.1.49, for pc-linux-gnu (i686) using readline 5.1 Connection id: 255260 Current database: mysql Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.49-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 161 days 3 hours 42 min 38 sec Threads: 14 Questions: 160655492 Slow queries: 71 Opens: 8124 Flush tables: 3 Open tables: 64 Queries per second avg: 11.538 --------------
這裡有個Open tables輸出時64，這就是說目前資料庫打開的表的數量是64個，要注意的是這個64並不是實際的64個表，因為MySQL是多線程的系統，幾個不同的並發連接可能打開同一個表，這就需要為不同的連接session分配獨立的記憶體空間來儲存這些資訊以避免衝突。因此連接數的增加會導致MySQL所需的 檔案描述符數目的增加。另外對於MyISAM表，也會建立一個共享的索引檔案描述符。
那麼在MySQL資料庫層面，有幾個系統參數決定了可同時開啟的表的數量和要使用的檔案描述符，那就是table_open_cache、max_tmp_tables和open_files_limit.
mysql> show variables like 'table_open%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | table_open_cache | 64 | +------------------+-------+ 1 row in set (0.00 sec)
這裡的table_open_cache 參數是64，這就是說所有的MySQL線程一共能同時打開64個表，我們可以蒐集系統的打開表的數量的歷史記錄和這個參數來對比，決定是否要增加這個參數的大小。查看目前的開啟表的數目的方法一個是用上邊提到過的status指令，另外可以直接查詢這個系統變數的值：
##
mysql> show status like 'open%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Open_files | 3 | | Open_streams | 0 | | Open_table_definitions | 8 | | Open_tables | 8 | | Opened_files | 91768 | | Opened_table_definitions | 0 | | Opened_tables | 0 | +--------------------------+-------+ 7 rows in set (0.00 sec) mysql> show global status like 'open%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Open_files | 3 | | Open_streams | 0 | | Open_table_definitions | 10 | | Open_tables | 11 | | Opened_files | 91791 | | Opened_table_definitions | 1211 | | Opened_tables | 8158 | +--------------------------+-------+ 7 rows in set (0.00 sec)這裡有Open_tables就是目前開啟表的數目，透過flush tables指令可以關閉目前開啟的表。而全域範圍內查看的Opened_tables是個歷史累計值。 這個值如果過大，而且如果沒有經常的執行
flush tables指令，可以考慮增加table_open_cache參數的大小。
mysql> show variables like 'max_tmp%'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | max_tmp_tables | 32 | +----------------+-------+ 1 row in set (0.00 sec)這個參數指定的是單一客戶端連線能開啟的暫存表數目。查看目前已經開啟的臨時表資訊：
mysql> show global status like '%tmp%table%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 10478 | | Created_tmp_tables | 25860 | +-------------------------+-------+ 2 rows in set (0.00 sec)也可以比較這兩個值來判斷臨時表的建立位置，一般選取BLOB和TEXT列、Group by 和Distinct語句的資料量超過512 bytes，或是union的時候select某列的資料超過512 bytes的時候，就直接在磁碟上建立臨時表了，另外記憶體中的臨時表變大的時候，也可能被MySQL自動轉移到磁碟上（由tmp_table_size和max_heap_table_size參數決定）。
继续原来的讨论，增加table_open_cache或 max_tmp_tables 参数的大小后，从操作系统的角度看，mysqld进程需要使用的文件描述符的个数就要相应的增加，这个是由 open_files_limit参数控制的。但是这个参数是OS限制的，所以我们设定的值并不一定总是生效。如果OS限制MySQL不能修改这个值，那 么置为0。如果是专用的MySQL服务器上，这个值一般要设置的尽量大，就是没有报Too many open files错误的最大值，这样就能一劳永逸了。当操作系统无法分配足够的文件描述符的时候，mysqld进程会在错误日志里记录警告信息。
mysql> show variables like 'open_files%';+------------------+-------+| Variable_name | Value |+------------------+-------+| open_files_limit | 1024 |+------------------+-------+1 row in set (0.00 sec) mysql> show variables like 'open_files%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | open_files_limit | 1024 | +------------------+-------+ 1 row in set (0.00 sec)
对应的，有两个状态变量记录了当前和历史的文件打开信息：
mysql> show global status like '%open%file%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Open_files | 3 | | Opened_files | 91799 | +---------------+-------+ 2 rows in set (0.01 sec)
MySQL为每个连接分配线程来处理，可以通过threads_connected参数查看当前分配的线程数量：
mysql> show status like '%thread%'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | Delayed_insert_threads | 0 | | Slow_launch_threads | 0 | | Threads_cached | 0 | | Threads_connected | 14 | | Threads_created | 255570 | | Threads_running | 2 | +------------------------+--------+ 6 rows in set (0.00 sec)
比较这个threads_connected参数和前面提到的max_connections参数，也可以作为目前的系统负载的参照，决定是否需要修改连接数。
如果查看每个thread的更详细的信息，可以使用processlist命令：
mysql> show processlist; +--------+-----------+--------------------+----------+-------------+----------+----------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+-----------+--------------------+----------+-------------+----------+----------------------------------------------------------------+------------------+ | 8293 | repl | 192.168.0.33:47208 | NULL | Binlog Dump | 11574424 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | | 140991 | mogile | 192.168.0.33:41714 | mogilefs | Sleep | 0 | | NULL | | 140992 | mogile | 192.168.0.33:41715 | mogilefs | Sleep | 3 | | NULL | | 140993 | mogile | 192.168.0.33:41722 | mogilefs | Sleep | 2 | | NULL | | 140994 | mogile | 192.168.0.33:41723 | mogilefs | Sleep | 1 | | NULL | | 140995 | mogile | 192.168.0.33:41724 | mogilefs | Sleep | 3 | | NULL | | 254914 | mogile | 192.168.0.33:43028 | mogilefs | Sleep | 11074 | | NULL | | 254915 | mogile | 192.168.0.33:43032 | mogilefs | Sleep | 11091 | | NULL | | 255144 | mogile | 192.168.0.33:47514 | mogilefs | Sleep | 11090 | | NULL | | 255157 | mogile | 192.168.0.33:47535 | mogilefs | Sleep | 11087 | | NULL | | 255162 | mogile | 192.168.0.33:47549 | mogilefs | Sleep | 11074 | | NULL | | 255260 | root | localhost | mysql | Query | 0 | NULL | show processlist | | 255352 | maopaodev | 192.168.0.78:55399 | maopaodb | Sleep | 3172 | | NULL | | 255353 | maopaodev | 192.168.0.78:55400 | NULL | Sleep | 8926 | | NULL | +--------+-----------+--------------------+----------+-------------+----------+----------------------------------------------------------------+------------------+ 14 rows in set (0.00 sec)
执行这个命令需要有Process_priv权限，具体的权限分配信息可以查看mysql.user表。
对于影响系统运行的thread，可以狠一点，用kill connection|query threadid的命令杀死它。
相关推荐：
以上是查看MySQL伺服器執行緒數的方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！