Mysql Gathering Queries per Second

One of the key questions asked of any DBA managing a database is how many queries per sec are there against a server. Mysql exposes the queries and questions status counters which you can use to get this data. However before I get into the script I must explain the difference. Questions are the amounts of calls that have been executed against the server while queries are the amount of statements. In most implementations where routines such as procedures aren't used these will be the same, since every call to the database will only be a single statement. However since routines can and often contain more then one statement your queries counter will differentiate from your questions counter. In all the scripts below I key off questions, since my concerns is calls coming into the server.

This first procedure is very simple and provides a snapshot in time of how many calls per second your server is taking. By passing a wait time variable in, you tell mysql to delta the start and end, and divide by the wait time

Drop Procedure if exists ;

DELIMITER //
CREATE PROCEDURE mysql.QuestionsPerSec(WaitTime int)

BEGIN

Declare StartCount int;

Declare EndCount int;

select VARIABLE_VALUE into StartCount from information_schema.global_status where variable_name = 'Questions';

select sleep(WaitTime);

select VARIABLE_VALUE into EndCount from information_schema.global_status where variable_name = 'Questions';

Select cast((EndCount - StartCount) as decimal(10,3)) /WaitTime as 'Queries Per Second';

END //

DELIMITER ;

Example Call

Call mysql.QuestionsPerSec(10);

Grabbing how many calls per sec has its place during unexpected traffic peaks or data issues, but if you don't know what your baseline is, the number is meaningless. In order record this information, use the following sproc. Notice it creates a performance database, as well as a table if they don't exist. Once you have the table in place, how you want to parse and analyze is up to you.

Drop Procedure if exists mysql.RecordQuestionsPerSec;

DELIMITER //
CREATE PROCEDURE mysql.RecordQuestionsPerSec(WaitTime int)

BEGIN

Declare StartCount int;

Declare EndCount int;

CREATE DATABASE IF NOT EXISTS PerformanceHistory;

CREATE Table IF NOT EXISTS PerformanceHistory.QuestionPerSec (QuestID int primary key Auto_Increment, QuestionsPerSecond decimal(10,3), DateTaken datetime) engine = myisam;

select VARIABLE_VALUE into StartCount from information_schema.global_status where variable_name = 'Questions';

select sleep(WaitTime);

select VARIABLE_VALUE into EndCount from information_schema.global_status where variable_name = 'Questions';

insert into PerformanceHistory.QuestionPerSec(QuestionsPerSecond, DateTaken)

Select cast((EndCount - StartCount) as decimal(10,3)) /WaitTime as 'Queries Per Second', now();

END //

DELIMITER ;