查看mysql连接数 sql语句执行时间
来源:互联网 发布:js大于等于怎么写 编辑:程序博客网 时间:2024/05/24 04:19
查看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语句要尽量连一起执行,不然误差太大
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)
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语句要尽量连一起执行,不然误差太大
0 0
- 查看mysql连接数 sql语句执行时间
- MySQL 如何查看SQL 语句执行时间
- 查看sql语句执行时间
- 查看SQL语句执行时间
- mysql语句执行时间查看
- mysql SQL语句执行时间
- SQL SERVER 查看语句执行时间
- 查看SQL语句的执行时间
- 查看SQL语句的执行时间
- 查看SQL语句的执行时间
- 查看SQL语句的执行时间
- 查看Sql语句的执行时间
- sqlplus查看sql语句执行时间
- Sql server 查看语句执行时间
- 查看SQL语句的执行时间
- 查看sql语句的执行时间
- mysql 如何查看sql语句执行时间和效率
- Mysql查看sql的执行时间
- html meta中得到viewport指令
- 安卓开发——textAppearance的属性设置
- Jquery封装之拖拽解决遇到的一些小bug
- Setting and Getting Permissions设置和取得许可
- Python图像处理库PIL的基本概念介绍
- 查看mysql连接数 sql语句执行时间
- 安卓开发——TextView属性设置
- 【翻译自HVR官方手册】HVR数据复制软件介绍与术语解释
- HDU 5622 KK's Chemical(dp + 计数、特殊图的染色多项式)
- 字符串模式匹配KMP算法
- c#_使用emgu3.0操作本地摄像头
- 面试笔试杂项积累-leetcode 201-205
- java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
- 深入理解Java虚拟机----(四)性能监控与故障处理工具