In literature SQL query efficiency is often measured in "queries pre second" (QPS). How those measures are made, considering that I have a database with production data at my hands? Is there a MySQL tool that can to this?
Asked
Active
Viewed 5.9k times
4 Answers
63
# mysqladmin status
Uptime: 587 Threads: 121 Questions: 3456 Slow queries: 0 Opens: 152 Flush tables: 1 Open tables: 64 Queries per second avg: 5.888
shantanuo
- 31,689
- 78
- 245
- 403
-
Is it "Questions" or "Queries", the denominator? – SOFe Jun 20 '16 at 13:32
21
The easiest way to do this is to create a job that runs:
SHOW STATUS LIKE 'Queries'\G;
at specified intervals. This will return the "queries" value. Take the difference between successive calls and then divide by the number of seconds between measurements to get the queries per second.
Anoop Naik
- 335
- 7
- 15
Kibbee
- 65,369
- 27
- 142
- 182
8
If you are on linux, you can use a tool called MyTop for realtime analytics.
Geoffrey Wagner
- 818
- 1
- 5
- 11
2
Playing with #service mysql status returns those informations (based on mysqladmin binary) :
root@SERVER:~# service mysql status
[info] /usr/bin/mysqladmin Ver 8.42 Distrib 5.5.44, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 5.5.44-0+deb7u1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 1 day 6 hours 36 min 1 sec
Threads: 2 Questions: 332928 Slow queries: 0 Opens: 104 Flush tables: 1 Open tables: 59 Queries per second avg: 3.022.
sdesvergez
- 815
- 8
- 3
-
Not in every distro, and not when using systemd. What you see is actually the output of `mysqladmin status`. – istepaniuk Jan 16 '18 at 10:36
-
It's possibly to call two arguments .... that's "version status", actually. – Kevin_Kinsey Aug 21 '18 at 21:21