1. Use-r/-i parameter
Using the mysqladmin extended-status command, you get all the MySQL performance metrics, the output of show global status, but because most of these metrics are cumulative, you need to perform a margin calculation if you want to know the current state. This is an additional feature of Mysqladmin extended-status, very practical. The default, using Extended-status, see also cumulative value, but, plus the parameter-R (--relative), you can see the difference between the indicators, with the parameter-I (--sleep) can specify the frequency of the refresh, then like the following command:
Copy Code code as follows:
Mysqladmin-uroot-r-I. 1-pxxx extended-status
+------------------------------------------+----------------------+
| variable_name | Value |
+------------------------------------------+----------------------+
| aborted_clients | 0 |
| Com_select | 336 |
| Com_insert | 243 |
......
| threads_created | 0 |
+------------------------------------------+----------------------+
2. Use with grep
Together with grep, we have:
Copy Code code as follows:
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. In conjunction with simple awk
Use awk to output time information simultaneously:
Copy Code code as follows:
Mysqladmin-uroot-p-h127.0.0.1-p3306-r-I 1 ext |\
Awk-f "|" '{\
if ($ ~/variable_name/) {\
Print "<-------------" Strftime ("%h:%m:%s") "------------->"; \
}\
if ($ ~/questions| queries| innodb_rows| Com_select | Com_insert | Com_update | Com_delete | innodb_buffer_pool_read_requests/) \
Print $ $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. With a complex awk
It's not easy anyway, it's a little bit more complicated, so the output is a bit more friendly because awk doesn't support dynamic variables, so the code looks more complicated:
Copy Code code as follows:
Mysqladmin-p3306-uroot-p-h127.0.0.1-r-I 1 ext |\
Awk-f "|" \
"Begin{count=0;}" \
' {if ($ ~/variable_name/&& ++count = 1) {\
Print "----------|---------|---MySQL Command Status--|-----Innodb row operation----| |
Print---time---|---QPS---|select insert update delete| Read inserted updated deleted| Logical physical ";
}\
else if ($ ~/queries/) {queries=$3} \
else if ($ ~/com_select/) {com_select=$3} \
else if ($ ~/com_insert/) {com_insert=$3} \
else if ($ ~/com_update/) {com_update=$3} \
else if ($ ~/com_delete/) {com_delete=$3} \
else if ($ ~/innodb_rows_read/) {innodb_rows_read=$3} \
else if ($ ~/innodb_rows_deleted/) {innodb_rows_deleted=$3} \
else if ($ ~/innodb_rows_inserted/) {innodb_rows_inserted=$3} \
else if ($ ~/innodb_rows_updated/) {innodb_rows_updated=$3} \
else if ($ ~/innodb_buffer_pool_read_requests/) {innodb_lor=$3} \
else if ($ ~/innodb_buffer_pool_reads/) {innodb_phr=$3} \
else if ($ ~/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); \
}}'
Copy Code code as follows:
----------|---------|---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
In this way, these commands themselves to use more, readily shared out.
