1

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.

4
  • what if you insert something into table having one field of varchar(50) and the values could be " Start of the activity...." and after the batch insert something like "end of activity..." to a customized table..hope it helps Jul 1, 2015 at 17:24
  • Please run SELECT engine FROM information_schema.tables WHERE table_schema='mysql' and table_name='general_log'; What storage engine is the table ??? Jul 1, 2015 at 17:38
  • @NawazSohail, yes, that is what I am trying to do, write a marker to the table.
    – Fran K.
    Jul 1, 2015 at 18:22
  • @RolandoMySQLDBA, the table is MyISAM and that is what your query returns. I've already converted the table and indexed it as I have seen you recommend a number of times, thank you for the idea and instructions.
    – Fran K.
    Jul 1, 2015 at 18:22

1 Answer 1

1

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 !!!

6
  • I think that might work well. It occurred to me that I can send SELECT NOW() FROM DUAL; which will not only appear in the general_log, but will return the timestamp for that particular entry. I will try having my JMeter pause for a bit then send that between every segment. I'm off to try it out, thanks!
    – Fran K.
    Jul 1, 2015 at 18:43
  • 1
    In MySQL it is simply SELECT NOW();. Jul 1, 2015 at 18:44
  • 4
    Even better, thanks! And It just occurred to me that I can mark my message in the select: SELECT NOW(), "message with X marker";
    – Fran K.
    Jul 1, 2015 at 18:48
  • @FranK. why not write that as an answer ? Jul 2, 2015 at 4:47
  • I'll mark it as the answer when I have it working. After decades of working in software I've noticed that having data to back up my claims is the best situation. So I always wait until I have proof that a concept is working before claiming it works in public.
    – Fran K.
    Jul 2, 2015 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.