mysql 查看连接时间吗_查看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.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语句要尽量连一起执行，不然误差太大