MySQL Queries Per Second

[bbrendon]

I was fed up with the basic average QPS that was in the default UserParameter so I came up with something more accurate. You've probably noticed that mysql's show status QPS becomes less accurate the longer the mysql daemon runs.

I have attached my script. It was a quick hack, but works quite well.

Setup your agent config file such as:
UserParameter=mysql[qps],/usr/local/mrtg/mysql-qps.pl

Add an item through the GUI and you're good to go. The script auto-adjusts for the polling frequency, so it is not necessary to make any changes to the script. I use 5 minute polling interval, but the script will work with any because it knows the last time it ran and can calculate accordingly.

Enjoy.

[Alexei]

Alternatively:

1. Take total number of MySQL queries
2. Calculate Delta (speed per second)

[bbrendon]

I tried that. I got some very small numbers, like 0.04 ish... I've never seen it go below about 35.

I set the store value as delta (speed per second)

[pdwalker]

Alternatively:

1. Take total number of MySQL queries
2. Calculate Delta (speed per second)

Remember that the delta in queries, divided by the time interval the queries took place gives you your queries per second.

so, if you query the number of questions every 100 seconds, then set your use multiplier by .01 to "divide" the delta of questions by 100 (1/100 = .01)

[Alexei]

To get real number of something per second, do not use multiplier. ZABBIX automatically performs all necessary calculations.

[pdwalker]

Whenever I try to use the delta (speed per second) with the net.if.in[eth1,bytes] key and no multiplier, I consistently get no data.

Using the delta (simple change) and the multiplier applied, it works.

Would you like me to try to track down the specifics of the behaviour?

- Paul

[pdwalker]

delta appears to be working for me in 1.1b4.

Will check some others with delta (speed) rather than delta (simple check) to see if they are working now as well.

- Paul

[chakatz]

Another MySQL QPS Script (in PHP)

Here's a script I use which works quite well/perfectly(?) for QPS.
I use no multiplier and ZABBIX v1.4.2

Setup your agent config file similar to:
UserParameter=mysql.qps,php -f /usr/local/sbin/qps.php

Don't forget to change the username and password.

qps.php

<?php
$queriesfile = '/tmp/.mysql-qps.dat';
$connection = mysql_connect("localhost","username","password");
$last = split("-", file_get_contents($queriesfile));
$timestamp_old = $last[0];
$queries_old = $last[1];
$row = mysql_fetch_assoc(mysql_query("show status like 'Questions'", $connection));
$queries_new = $row['Value'];
$timestamp_new = time();
if (($queries_new-$queries_old)<=0) $qps=0;
else $qps = round(($queries_new - $queries_old) / ($timestamp_new - $timestamp_old));
$fhandle = fopen($queriesfile, 'w');
fwrite($fhandle, $timestamp_new . "-" . $queries_new);
fclose($fhandle);
echo "$qps\n";
?>

[pdwalker]

That's overkill. You're invoking the overhead of a php intrepreter to get the number?

Just use the example user parameter for mysql.questions then store it as a delta (speed per second).

Under zabbix v1.1.7, I have it defined as such:

Description: mysql: qps
Type: Zabbix Agent
Key: mysql.questions
Type of Information: numeric (float)
Units: <blank>
Use Multipler: Do not use
Update Interval: 30
Store Value: Delta (speed per second)

That's it

ps: in my case, I defined my mysql.questions in my userparams as:
UserParameter=mysql.questions,/<path>/mysqladmin -u<user> -p<password> -h<server> status|cut -f8 -d" "|tr - [:cntrl:]

[chakatz]

ps: in my case, I defined my mysql.questions in my userparams as:
UserParameter=mysql.questions,/<path>/mysqladmin -u<user> -p<password> -h<server> status|cut -f8 -d" "|tr - [:cntrl:]

Of course that makes perfect sense. I now understand that is what Alexei suggested above.
However "|tr - [:cntrl:]" returned errors for me.
I tried "|tr -d [:blank:]" But it didn't seem to be needed.
In the end I used:

UserParameter=mysql.questions,mysqladmin status|cut -f8 -d" "

See any problems with that? Why did you add the "|tr - [:cntrl:]" pipe?

[richlv]

Alternatively:

1. Take total number of MySQL queries
2. Calculate Delta (speed per second)

i finally got to this issue - this particular method is sooo much better, what about making it the default in templates for new releases ?