檢視mysql連線數 sql語句執行時間

檢視mysql連線數

---

1  ./mysqladmin -uroot -p123456 -h192.168.9.172 processlist
   可以檢視具體詳細資訊
[root@sznagios bin]# pwd
/usr/bin
[root@nagios bin]# ./mysqladmin -uroot -p123456 -h192.168.9.172 processlist
+--------+-----------+---------------------+-----------------+---------+-------+-------+------------------+
| Id     | User      | Host                | db              | Command | Time  | State | Info             |
+--------+-----------+---------------------+-----------------+---------+-------+-------+------------------+
| 27     | centreon  | 192.168.9.172:47700 | centreon_status | Sleep   | 0     |       |                  |
| 78     | cactiuser | localhost           | syslog          | Sleep   | 55    |       |                  |
+--------+-----------+---------------------+-----------------+---------+-------+-------+------------------+

或者 進入mysql也可以檢視：
mysql> show full processlist;
+-------+-----------+---------------------+------------------+---------+-------+-------+-----------------------+
| Id    | User      | Host                | db               | Command | Time  | State | Info                  |
+-------+-----------+---------------------+------------------+---------+-------+-------+-----------------------+
|    15 | cactiuser | localhost           | syslog           | Sleep   |     0 |       | NULL                  |
|   225 | centreon  | localhost           | centreon_status  | Sleep   |     1 |       | NULL                  |
| 76778 | root      | 172.16.200.53:54830 | NULL             | Sleep   | 15344 |       | NULL                  |
| 76779 | root      | 172.16.200.53:54831 | centreon_storage | Sleep   | 15342 |       | NULL                  |
| 76789 | root      | 172.16.200.53:54841 | centreon_storage | Sleep   | 15322 |       | NULL                  |
| 76790 | root      | 172.16.200.53:54843 | centreon_storage | Sleep   | 15317 |       | NULL                  |
| 84523 | root      | localhost           | NULL             | Query   |     0 | NULL  | show full processlist |
+-------+-----------+---------------------+------------------+---------+-------+-------+-----------------------+
7 rows in set (0.00 sec)



2   ./mysqladmin -ucentreon -p123456 -h172.25.128.35 status   
   可以檢視總數 （Threads就是連線數）
[root@sznagios bin]#  ./mysqladmin -ucentreon -p123456 -h192.168.9.172 status
Uptime: 57809  Threads: 76  Questions: 19264613  Slow queries: 33  Opens: 39310  Flush tables: 1  Open tables: 64  Queries per second avg: 333.245




3   sql語句執行時間

3.1  profiles方法
預設profiles不開啟
mysql> show profiles;
Empty set (0.00 sec)

mysql> show variables like "%pro%";
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| profiling                 | OFF   |
| profiling_history_size    | 15    |
| protocol_version          | 10    |
| slave_compressed_protocol | OFF   |
+---------------------------+-------+
4 rows in set (0.00 sec)

開啟profiles
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql;
Database changed
mysql> select * from user;
+-----------------------+-----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+-------

mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration   | Query              |
+----------+------------+--------------------+
|        1 | 0.00020300 | SELECT DATABASE()  |
|        2 | 0.00067100 | select * from user |
+----------+------------+--------------------+
2 rows in set (0.00 sec)
3.2  timestampdiff來檢視測試時間

mysql> set @d=now();
Query OK, 0 rows affected (0.00 sec)


mysql> select * from user;
+-----------------------+-----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------

mysql> select timestampdiff(second,@d,now());
+--------------------------------+
| timestampdiff(second,@d,now()) |
+--------------------------------+
|                             39 |
+--------------------------------+
1 row in set (0.00 sec)

注意：三條sql語句要儘量連一起執行，不然誤差太大