使用mysqladmin ext 瞭解MySQL運行狀態 轉

標籤：

https://yq.aliyun.com/articles/11210?spm=0.0.0.0.HpobXF1. 使用-r/-i參數

使用mysqladmin extended-status命令可以獲得所有MySQL效能指標，即show global status的輸出，不過，因為多數這些指標都是累計值，如果想瞭解當前的狀態，則需要進行一次差值計算，這就是mysqladmin extended-status的一個額外功能，非常實用。預設的，使用extended-status，看到也是累計值，但是，加上參數-r(--relative)，就可以看到各個指標的差值，配合參數-i(--sleep)就可以指定重新整理的頻率，那麼就有如下命令：

mysqladmin -uroot -r -i 1 -pxxx extended-status+------------------------------------------+----------------------+| Variable_name                            | Value                |+------------------------------------------+----------------------+| Aborted_clients                          | 0                    || Com_select                               | 336                  || Com_insert                               | 243                  |......| Threads_created                          | 0                    |+------------------------------------------+----------------------+

 2. 配合grep使用

配合grep使用，我們就有：

mysqladmin -uroot -r -i 1 -pxxx extended-status |grep "Questions\|Queries\|Innodb_rows\|Com_select \|Com_insert \|Com_update \|Com_delete "| Com_delete                               | 1                    || Com_delete_multi                         | 0                    || Com_insert                               | 321                  || Com_select                               | 286                  || Com_update                               | 63                   || Innodb_rows_deleted                      | 1                    || Innodb_rows_inserted                     | 207                  || Innodb_rows_read                         | 5211                 || Innodb_rows_updated                      | 65                   || Queries                                  | 2721                 || Questions                                | 2721                 |

 3. 配合簡單的awk使用

使用awk，同時輸出時間資訊：

mysqladmin -uroot -p -h127.0.0.1 -P3306 -r -i 1 ext |awk -F"|" ‘{  if($2 ~ /Variable_name/){    print " <-------------    "  strftime("%H:%M:%S") "    ------------->";  }  if($2 ~ /Questions|Queries|Innodb_rows|Com_select |Com_insert |Com_update |Com_delete |Innodb_buffer_pool_read_requests/)    print $2 $3;}‘<-------------    12:38:49    -------------> Com_delete                             0 Com_insert                             0 Com_select                             0 Com_update                             0 Innodb_buffer_pool_read_requests       589 Innodb_rows_deleted                    0 Innodb_rows_inserted                   2 Innodb_rows_read                       50 Innodb_rows_updated                    50 Queries                                105 Questions                              1 <-------------    12:38:50    -------------> Com_delete                             0 Com_insert                             0 Com_select                             0 Com_update                             0 Innodb_buffer_pool_read_requests       1814 Innodb_rows_deleted                    0 Innodb_rows_inserted                   0 Innodb_rows_read                       8 Innodb_rows_updated                    8 Queries                                17 Questions                              1

 4. 配合複雜一點的awk

反正也不簡單了，那就更複雜一點，這樣讓輸出結果更友好點，因為awk不支援動態變數，所以代碼看起來比較複雜：

mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 ext |awk -F"|" "BEGIN{ count=0; }"‘{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){    print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";    print "---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical";}else if ($2 ~ /Queries/){queries=$3;}else if ($2 ~ /Com_select /){com_select=$3;}else if ($2 ~ /Com_insert /){com_insert=$3;}else if ($2 ~ /Com_update /){com_update=$3;}else if ($2 ~ /Com_delete /){com_delete=$3;}else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}else if ($2 ~ /Uptime / && count >= 2){  printf(" %s |%9d",strftime("%H:%M:%S"),queries);  printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);  printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);  printf("|%10d %11d\n",innodb_lor,innodb_phr);}}‘----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read -----Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical 10:37:13 |     2231|   274    214     70      0|  4811      160      71       0|      4146           0 10:37:14 |     2972|   403    256     84     23|  2509      173      85      23|      4545           0 10:37:15 |     2334|   282    232     66      1|  1266      154      67       1|      3543           0 10:37:15 |     2241|   271    217     66      0|  1160      129      66       0|      2935           0 10:37:17 |     2497|   299    224     97      0|  1141      149      95       0|      3831           0 10:37:18 |     2871|   352    304     74     23|  8202      226      73      23|      6167           0 10:37:19 |     2441|   284    233     82      0|  1099      121      78       0|      3292           0 10:37:20 |     2342|   279    242     61      0|  1083      224      61       0|      3366           0

 

就這樣了，這幾個命令自己用的比較多，隨手分享出來。

使用mysqladmin ext 瞭解MySQL運行狀態 轉