Not the answer you need?
Register and ask your own question!

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

acerpaceracerpacer EntrantCurrent User Role Beginner
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*

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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
  • acerpaceracerpacer Entrant Current User Role Beginner
    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.nemesscott.nemes MySQL Sage Current User Role Patron
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.