One of our users is using Cloud SQL(MySQL).
They turn on general logs flag and log_output is file.
They need these general logs because of some special circumstances.
MySQL generates about 8TB of general logs and these logs result a bigger use of the disk.
Here is the tricky part:
They want to remove these general logs file [1] to decrease the size of the disk.
However, this is their production database. They afraid this operation will impact their database’s performance.
Since these log files are located in /var/log/mysql.log, the remove logs operation will execute on the OS level, right? -> This is the part we are not so sure.
If our user executes this truncateAPI, will this operation affect their database’s performance?
Is there any best practice for this kind of situation?
P.S: Our user doesn’t want to turn off general logs flag. They will try to truncate these logs once for a while. But for now, they need to truncate the huge amount of logs that they accumulated in the past few momths.
[1] https://cloud.google.com/sql/docs/mysql/admin-api/v1beta4/instances/truncateLog
Advertisement
Answer
I understand that you have turned on general logs flag and log_output is FILE and you want to remove these general logs files to decrease the size of the disk.
According to to the official documentation link:
To make your general or slow query logs available, enable the corresponding flag and set the log_output flag to FILE. This makes the log output available using the Logs Viewer in the Google Cloud Platform Console. Note that Stackdriver logging charges apply.
If log_output is set to NONE, you will not be able to access the logs.
If you set log_output to TABLE, the log output is placed in a table in your database. If this table becomes large, it can affect instance restart time or cause the instance to lose its SLA coverage; for this reason, the TABLE option is not recommended. If needed, you can truncate your log tables by using the API. For more information, see the instances.truncateLog reference page.
Instances: truncateLog truncate MySQL general and slow query log tables.
If I understood correctly, you can not “truncate the huge amount of logs that they accumulated in the past few months” because you did not set log_output to TABLE, therefore there are no tables to be truncated.
Regarding database performance: TRUNCATE TABLE Statement
On a system with a large InnoDB buffer pool and innodb_adaptive_hash_index enabled, TRUNCATE TABLE operations may cause a temporary drop in system performance due to an LRU scan that occurs when removing an InnoDB table’s adaptive hash index entries. The problem was addressed for DROP TABLE in MySQL 5.5.23 (Bug 13704145, Bug #64284) but remains a known issue for TRUNCATE TABLE (Bug #68184).
Here you can check MySQL Server Log Maintenance.
Removing MySQL general_log FILES should not impact the performance of the database.