mysql 性能调优2

1、Show Global Status ， Show global variables ，Show full processlist
      得到Mysql数据库系统当前各种系统变量和状态 ，并且各种监控工具几乎都是对这三个指令的输出进行数据的计算和统计分析

   mysqladmin -uroot -p extended-status ，mysqladmin -uroot -p extended-variables，mysqladmin -uroot -p extended-processlist

mysql> show global variables;
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name                           | Value                                                                                     |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| auto_increment_increment                | 1                                                                                         |
| auto_increment_offset                   | 1                                                                                         |
| autocommit                              | ON                                                                                        |
| automatic_sp_privileges                 | ON                                                                                        |
| back_log                                | 50                                                                                        |
| basedir                                 | /usr/                                                                                     |
| big_tables                              | OFF                                                                                       |

 

mysql> show global status;
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Aborted_clients                   | 19         |
| Aborted_connects                  | 2          |
| Binlog_cache_disk_use             | 0          |
| Binlog_cache_use                  | 0          |
| Bytes_received                    | 399630956  |
| Bytes_sent                        | 41885453   |

 

mysql> show full processlist;
+------+------+------------------+-------------+---------+------+-------+-----------------------+
| Id   | User | Host             | db          | Command | Time | State | Info                  |
+------+------+------------------+-------------+---------+------+-------+-----------------------+
| 7263 | root | 10.50.11.23:4339 | NULL        | Sleep   | 1762 |       | NULL                  |
| 7264 | root | 10.50.11.23:4341 | wordtraffic | Sleep   | 1758 |       | NULL                  |
| 7356 | root | localhost        | NULL        | Query   |    0 | NULL  | show full processlist |
+------+------+------------------+-------------+---------+------+-------+-----------------------+

 

2、mysqladmin -u-p -i 10 extended status;

每隔10秒记录一下状态