Mysql count how many queries per second are executed, is there any way?

Question

i have a busy web server with LAMP installed, and i was wondering, is there any way to count how many queries per second (mysql) are executed in the server ?

Thank you.

score 11 · Answer 1 · answered Jun 26 '13 at 18:02

11

SELECT s1.variable_value / s2.variable_value
FROM information_schema.global_status s1, information_schema.global_status s2
WHERE s1.variable_name='queries'
AND s2.variable_name ='uptime';

answered Jun 26 '13 at 18:02

n00b

131
1
3

For anyone using mysql 5.7+, the global status table has moved to the performance_schema – Matthew Conradie Jan 28 '21 at 09:27

score 8 · Answer 2 · answered May 02 '10 at 18:29

8

Try Jeremy Zawodny's excellent utility mytop.

If you have the Perl module Time::HiRes installed, mytop will automatically use it to generate high-resoution query per second information.

answered May 02 '10 at 18:29

Bill Karwin

538,548
86
673
828

1

i don't have that installed. i found some other way mysqladmin status – Nikos May 02 '10 at 18:45

score 5 · Answer 3 · answered May 02 '10 at 19:10

5

There's useful information to be mined from the SHOW GLOBAL STATUS; command, including the number of queries executed (if your MySQL is 5.0.76 or later).

See http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

answered May 02 '10 at 19:10

Dave W. Smith

24,318
4
40
46

what all the questions included as part of this queries...? – Uday Aug 16 '12 at 00:20
I am serching for how mysql calculates this questions values or queries values. I am doing the sum of COM_SELECT, COM_UPDATE, COM_SET_OPTION, COM_UPDATE, COM_DELETE and few more but not getting the exact match between these two. – Uday Aug 16 '12 at 00:22
2

The diff between `Questions` and `Queries` may involve Stored Routines. – Rick James Mar 19 '18 at 14:00

score 5 · Answer 4 · edited Jul 11 '17 at 11:00

5

You can use:

 mysqladmin -u root -p  status

which will return output like:

Uptime: 17134  Threads: 2  Questions: 1245  Slow queries: 0  Opens: 49  Flush tables: 1  Open tables: 42  Queries per second avg: 0.072

Here queries per second is: 0.072, which is questions/uptime.

edited Jul 11 '17 at 11:00

marc_s

732,580
175
1,330
1,459

answered May 16 '17 at 07:57

Prashant Prabhakar Singh

1,120
4
15
33

score 2 · Answer 5 · answered Jul 31 '12 at 09:53

2

When you use the "STATUS" command (not SHOW STATUS), MySQL will calculate the queries per second since server start for you.

Tested with MySQL 5.1.63.

answered Jul 31 '12 at 09:53

Gene Vincent

5,237
9
50
86

score 0 · Answer 6 · answered May 22 '12 at 14:33

0

We can have a small script for this. It will be some thing like the below.

    declare -i a
    declare -i b
    declare -i c
    a=`mysql -uroot -pxxxxx -e "show status like 'Queries'" | 
    tail -1 | awk '{print    $2}'`
    echo "$a"
    sleep 1
    b=`mysql -uroot -pxxxxx -e "show status like 'Queries'" | 
    tail -1 | awk '{print                 $2}'`
    echo "$b"
    c=$b-$a
    echo "Number of Queries per second is: $c"

answered May 22 '12 at 14:33

Uday

1,480
4
27
44

This should be all possible to do within MySQL wouldn't it? I don't know how to do it sadly. – Mike Gifford Apr 20 '13 at 13:59

Mysql count how many queries per second are executed, is there any way?

6 Answers6