mysql.slow_log table related query

  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql.slow_log table related query

    We have had slow query threshold set to 5 seconds and over years this table has grown up to be very large and I see slow_log.CSV size to be 120GB in mysql data directory.

    I planning to run a truncate slow_log to clear this table but concern is this table is huge would there be any issue doing so ?

    is it safe to do so?

    has any one tried this before ?

    any other alternate option ?

    I am using MySQL 5.1.52


  • #2

    I'm bit confused. What do you mean by this sentence. "this table has grown up to be very large and I see slow_log.CSV size to be 120GB in mysql data directory"
    Are you taking about table or file? If table, are you keeping slow log information in table? (Table with CSV storage engine)? Please give some specific information.


    • #3
      Hi Ishaque,

      Yes, you can truncate mysql.slow_log table, however I never tried this on that big one (may be slow).
      You may also rotate the table and analyse/drop/archive later like this:

      CREATE TABLE mysql.slow_log2 LIKE mysql.slow_log;RENAME TABLE mysql.slow_log TO mysql.slow_log_backup, mysql.slow_log2 TO mysql.slow_log;

      Also if it's so big with 5s threshold it means you have a lot of bad queries


      • #4

        Yes this table is using CSV engine and 120 GB is size of slow_log.CSV file


        Thanks for your suggestion, yes concern was the size I have increased threshold to higher number and this table in not growing in last few weeks. Yes tool has been thru many hands and we are working on improving the queries

        I presume rotation trick you mentioned can be done without a downtime like schedule a job daily when system is not that busy ?

        Thanks again for all your help