Announcement

Collapse
No announcement yet.

MySQL Slow Query Logs:-

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL Slow Query Logs:-

    “Slow Query”:- Slow Query in simple terms can be defined as the query which took long time to get executed..

    By default MySQL server is configured to mark the query which takes more than 10 seconds to get executed as Slow Query. It is best practice to log all such queries in the log file so that they can be examined periodically.

    The major question which now comes in the mind is why any query would be slow...???

    Generally when any query gets executed on any database, it tries to find the index which enables the query to find desired values from the column.

    An index is a sorted set of data that references the primary key of a table. If the proper index is missing even a simple query may take long time to execute.

    Whereas if you use index in every column, any queries that add, change or delete data would then have to update all of the indexes so this will take long time and consume high resources.

    Now let’s see how one can enable and configure them on the server.

    Open mysql configuration file which is generally “/etc/my.cnf”
    Now add the following lines in it.
    log-slow-queries=1
    log-slow-queries=/var/lib/mysql/slow.log

    The “/var/lib/mysql/slow.log” is the path of the file where you wish to store it (you may change it according to your requirements)

    Once done, create a file with the correct ownership and file permissions using the following commands
    touch /var/lib/mysql/slow.log
    chmod 660 /var/lib/mysql/slow.log
    chown mysql:mysql /var/lib/mysql/slow.log


    Now restart MySQL.

    /etc/init.d/mysql restart Or /etc/init.d/mysqld restart

    Now if you wish to change the default time after which any query is marked as slow query, you may add the following in line my.cnf file above log-slow-queries
    long_query_time=5

    Save the file and again restart the MySQL server for the changes to come into effect..

    It is important to take into consideration the server resources (RAM and CPU) before setting up the long_query_time.


    Regards,
    Cyril Clive
Working...
X