slow xtradb cluster. slow-query log size is 536M. how to clean log files?

[acerpacer]

Hi

we are running 3 servers and using percona xtradb cluster.

Server is getting slower as each week pass.
does size of mysql-bin.* and slow-query-log affect the operation.

How can I flush the logs file without disturbing the cluster?

is

>flush logs;
>master reset

appropriate?

file listing in /data directory

total 3.7G
drwxrwxr-x 1 mysql mysql 554 Oct 13 14:41 ./
drwxrwxr-x 1 root root 330 Apr 29 10:50 ../
-rw-rw---- 1 mysql mysql 82 May 6 16:53 GRA_6_7.log
-rwxrwxr-x 1 mysql mysql 56 Apr 29 10:26 auto.cnf*
-rwxrwxr-x 1 mysql mysql 129M Oct 13 14:55 galera.cache*
-rwxrwxr-x 1 mysql mysql 109 Oct 13 14:41 grastate.dat*
-rw-rw---- 1 mysql mysql 265 Oct 13 14:41 gvwstate.dat
-rw-rw---- 1 mysql mysql 512M Oct 13 14:55 ib_logfile0
-rw-rw---- 1 mysql mysql 512M Oct 1 12:50 ib_logfile1
-rw-rw---- 1 mysql mysql 512M Oct 8 23:06 ib_logfile2
-rwxrwxr-x 1 mysql mysql 76M Oct 13 14:55 ibdata1*
drwxrwxr-x 1 mysql mysql 2.5K Apr 29 10:22 mysql/
-rw-rw---- 1 mysql mysql 93M Aug 14 13:39 mysql-bin.000001
-rw-rw---- 1 mysql mysql 143 Aug 14 13:39 mysql-bin.000002
-rw-rw---- 1 mysql mysql 1.1G Oct 1 12:51 mysql-bin.000003
-rw-rw---- 1 mysql mysql 363M Oct 13 14:37 mysql-bin.000004
-rw-rw---- 1 mysql mysql 943K Oct 13 14:53 mysql-bin.000005
-rw-rw---- 1 mysql mysql 95 Oct 13 14:37 mysql-bin.index
drwxrwxr-x 1 root root 3.3K Apr 29 10:49 performance_schema/
-rw-rw---- 1 mysql mysql 45 Oct 13 14:39 rsync_sst_complete
drwx------ 1 mysql mysql 1.2K Jul 23 08:33 db/
-rw-rw---- 1 mysql mysql 536M Oct 13 14:42 dbserver01-slow.log
-rw-r----- 1 mysql root 1.1M Oct 13 14:41 dbserver01.err
-rw-rw---- 1 mysql mysql 6 Oct 13 14:37 dbserver01.pid
-rwxrwxr-x 1 mysql mysql 932K May 28 17:18 db01.err*

[scott.nemes]

Hi acerpacer;

The amount of disk space you use will not affect performance (generally; if you run out of disk space, or get very low, then that is another issue all together). However if your slow query log is growing quickly, then that means you are likely seeing more slow queries, which will slow down your system. So you need to process the slow query log using a tool like pt-query-digest, and see what you can do to tune your worst queries through indexing and/or re-writing as necessary.

You should be able to move/rename the existing slow query log, and then run “flush logs”. No “master reset” required. Flush logs causes MySQL to close/re-open the logs (slow query log, error log, general log). So as long as you move/rename the log first, and then run “flush logs”, MySQL will create a new empty log in it’s place.

Alternatively you could just disable slow query logging, move/rename the slow query log, and then re-enable slow query logging, which will also create a new slow query log file.

-Scott

[acerpacer]

Thanks Scott.

It actually helped. Database is better after flushing logs. It is not significantly faster but a little bit better.

Now I think is the right time to setup the log rotate.

Any precautions I should take before setting up log rotate?

Thanks

[scott.nemes]

Hi acerpacer;

Not particularly. I would just flush the logs after the rotation so MySQL re-opens the new base log, but otherwise should be fairly straight forward once you get a rotation config to your liking.

The below blog post has some good info, plus a logrotate.d example config. I think their “post rotate” section is a bit over complicated though, so I would probably shrink that down to just flushing the logs, but up to you for what is best in your environment.

https://www.percona.com/blog/2013/04/18/rotating-mysql-slow-logs-safely/

-Scott