How to Write Marker Messages into MySQL General Log

logsMySQLmysql-5.6performance

I'm build a performance testing harness for our distributed web app. I'm using JMeter to drive it by simulating poorly performing parts of the app from the web side. I've set up MySQL to log to the general_log table. Now I would like to have JMeter send marker messages into the general_log so that, after identifying a poorly performing segment, I can fetch all of the queries run between markers surrounding that segment for further analysis.

I've tried sending an INSERT query on the table, but it always returns Error : You can't use locks with log tables. and my row is not created (but the INSERT query itself shows up). I was hoping to send a customized message, but this doesn't work.

So, how do I insert a test run specific message into the general_log? Alternately, is there another solution? I was thinking of adding a table similar to the general_log table for my use, but we have a variety of deployments and being able to run this test on any of them without modification would be ideal.

Best Answer

Since you have mysql.general_log already converted to MyISAM and indexed, I would recommend making snapshots of that table based on a timestamp range. What I mean by snapshot is a temp table that contains just the events you wish to mark.

Suppose you want to log entries from the last 10 minutes. This Dynamic SQL should do it for you :

SET sql_log_bin = 0; ## Do this so it does not replicate to slaves
CREATE DATABASE IF NOT EXISTS myloggingdatabase;
USE myloggingdatabase
SET @now = NOW();
SET @ten_minutes_ago = @now - INTERVAL 10 MINUTE;
SET @snapshot = CONCAT('general_log_',DATE_FORMAT(@now(),'%Y%m%d_%H%M%00'));
SET @sql = CONCAT('DROP TABLE IF EXISTS ',@snapshot);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = CONCAT('CREATE TABLE ',@snapshot,' LIKE mysql.general_log');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = CONCAT('INSERT INTO ',@snapshot,' SELECT * FROM mysql.general_log');
SET @sql = CONCAT(@sql,' WHERE event_time>=',QUOTE(@ten_minutes_ago));
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

If you want all the events from yesterday, do this

SET sql_log_bin = 0; ## Do this so it does not replicate to slaves
CREATE DATABASE IF NOT EXISTS myloggingdatabase;
USE myloggingdatabase
SET @now = NOW();
SET @midnight_today = DATE(@now) + INTERVAL 0 SECOND;
SET @midnight_yesterday = @midnight_today - INTERVAL 1 DAY;
SET @snapshot = CONCAT('general_log_',DATE_FORMAT(@midnight_today(),'%Y%m%d_%H%M%00'));
SET @sql = CONCAT('DROP TABLE IF EXISTS ',@snapshot);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = CONCAT('CREATE TABLE ',@snapshot,' LIKE mysql.general_log');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = CONCAT('INSERT INTO ',@snapshot,' SELECT * FROM mysql.general_log');
SET @sql = CONCAT(@sql,' WHERE event_time>=',QUOTE(@midnight_yesterday));
SET @sql = CONCAT(@sql,' AND event_time<',QUOTE(@midnight_today));
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

You will to have discard those temp tables now and then by simply running

DROP DATABASE IF EXISTS myloggingdatabase;

GIVE IT A TRY !!!

Related Question