Geek Answers Handbook

Is my mysql.general_log table getting too big?

I recently upgraded to MySQL 5.1.6 to take advantage of the ability to save a common log in a table -> that is, mysql.general_log. As soon as I did this, I was immediately surprised how many queries actually hit our system. I have about 40,000 rows in this general log table from the first hour. I did not find it in MySQL docs about whether the overall size of the log table exists.

Is there a problem allowing this general magazine to grow at such a rate?

If there is a problem with size, how to deal with it?

Is there any accepted practice on how to deal with the size problem, if any?

Should I make an event to clear the table and save the data to a file so often?

Thank you very much for your help!

+5
source share
3 answers

The general_log table uses the CSV engine by default, which is literally just a full-blown CSV file on your disk, but it can be accessed through SQL. This means that its size limit is the size limit of files in your file system.

+5
source

I am doing something similar for my log file. I'm only interested in saving the last 24 hours, but you can set up an event to create archive tables, etc. It will not register for the few seconds it takes to trigger this event, but I do not mind.

CREATE EVENT `prune_general_log` ON SCHEDULE
EVERY 1 DAY STARTS '2013-10-18'
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT 'This will trim the general_log table to contain only the past 24 hours of logs.'
DO BEGIN
  SET GLOBAL general_log = 'OFF';
  RENAME TABLE mysql.general_log TO mysql.general_log2;
  DELETE FROM mysql.general_log2 WHERE event_time <= NOW()-INTERVAL 24 HOUR;
  OPTIMIZE TABLE general_log2;
  RENAME TABLE mysql.general_log2 TO mysql.general_log;
  SET GLOBAL general_log = 'ON';
END
+6
source

You need to use some utility to rotate the log file, for example mysql-log-rotate http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html .

+1
source

More articles:


All Articles