查看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.172status
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.1profiles方法默认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.2timestampdiff来查看测试时间
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语句要尽量连一起执行,不然误差太大