Is my mysql.general_log table getting too big?

mysql truncate general log table
mysql general query log example
mysql general log table empty
mysql general_log column's
mysql slow query log table
create log table in mysql
mysql general log retention
mysql log

I just recently upgraded to MySQL 5.1.6 in order to take advantage of the ability to save the general log to a table -> i.e. mysql.general_log. Once i did this I was immediately surprised how many queries are actually hitting our system. I have about 40,000 rows in this general log table from the first hour. I haven't found it written on the MySQL docs about if there is a general log table size limit.

Is there a problem to letting this general log grow at this rate?

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

Are there some accepted practices how to deal with a size problem if there is one?

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

Thanks a lot for the help!

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

Impact of General Query Log on MySQL Performance, The question is, does enabling the general query log affects the MySQL performance ? of this log into either file or table in the mysql database (mysql.​general_log), What are the benefits that we might get if we have the log output to be So, let's check the performance impact then of each log output. It is these transaction log backups that keep your transaction log file from growing too large. An easy way to make consistent transaction log backups is to include them as part of your database

I do something like this for my log file. I'm only interested in keeping the last 24 hours, but you could tweak the event to create archive tables, etc. It won't log for the few seconds it takes the event to run, but I don't 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

Is my mysql.general_log table getting too big?, вопрос. I just recently upgraded to MySQL 5.1.6 in order to take advantage of the ability to save the general log to a table -> i.e. mysql.general_log. Once i did  The log tables *are* useful, and I make use of the general_log table in oak-hook-general-log to make a “tail -f” on the structured geenral log. However, you write: “Specially when you have very big slow log.” Note that writing to log tables is much slower than writing to log file, so be careful.

You should use some utility like mysql-log-rotate http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html for rotating log file.

Large General log file: How to clean up safely and stop it reoccuring , The question is, does enabling the general query log affects the MySQL performance ? of this log into either file or table in the mysql database (mysql.​general_log), What are the benefits that we might get if we have the log output to be So, let's check the performance impact then of each log output. If your SQL Server transaction log (LDF) file is too big – you are doing something wrong. As technet puts it:. Typically, truncation occurs automatically under the simple recovery model when database is backed up and under the full recovery model when the transaction log is backed up.

Not sure if this is best practice but this was my solution:

DATE=$(date +"%Y%m%d%H%M")
mv general_log.CSV general_log.${DATE}.csv  # move the log table file
sudo -u mysql -g mysql touch general_log.CSV  # create a new log table file with correct owner and group
mysql -u root -e "FLUSH TABLE mysql.general_log"  # flush the log table

MySQL 5.7 Reference Manual :: 5.4.1 Selecting General , In /var/lib/mysql I have a hostname.log file which is over 20GB my.cnf is it (or a combination of smaller files) growing so large in the future ? For reasons I have forgotten, the last programmers put it all in one big table. That table has 28 columns of weather parameters, and as of this writing, now has 66,135,065 rows in it. Some stations have been uploading data since 2008. Up until now I have also benefitted from comped server hosting in my employer's local colo rack.

How to enable and set Rolling for MySQL General Log in a MySQL , The general_log system variable controls logging to the general query log for to recreate those tables so that they are not missing after reloading the dump file. But the renamed file is not there. Our log does get fairly large, the first day it got to about 400 MB. When I ran tests on the script, the log was smaller (10 MB, etc). I'm wondering if having a large quickly updating log might be too much for Windows/MySQL to handle.

General Query Log, Enabling general log in your MySQL RDS instance can be very useful. General log can log every single query on your database in mysql.general_log table. For us event time, user_host and argument (query) are the most If your disk space is too big, 20% of the disk space might be too big for you to  Root Administrator. May 28, 2012. #2. From command line -- bold items are things you type to turn the general log off and verify that it is off. # mysql. mysql> set global general_log = 0; mysql> show variables where variable_name = 'general_log'; +---------------+-------+. | Variable_name | Value |.

MySQL general log as table -- Scheduling cleaning, Since it's a record of every query received by the server, it can grow large quite quickly. To enable the general query log, set the general_log system variable to 1 . a file on disk, or it can be written to the general_log table in the mysql database. Unix and Linux distributions offer the logrotate utility, which makes it very  The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.

Comments
  • One thing you should be aware of, is that running general query log in production environment is not recommended. The performance impact can be pretty high, especially in high concurrency situations.
  • Love this answer. Thank you
  • Unfortunately I am running on a Windows server, it says in the documentation this is included on Linux (Red Hat) installations.