Access slow query and general logs for Cloud Databases


Database logs can be useful tools when analyzing database performance or troubleshooting issues. You can enable logging slow queries or general database activity for a MySQL Cloud Databases instance by attaching a configuration group to the instance with the appropriate parameters set.

Prerequisites

The steps in this article require that you have an existing MySQL configuration group or create a new one. A MySQL configuration group holds the values for MySQL options used at startup. To learn more about MySQL configuration groups, see Managing configuration groups for cloud databases with the Trove command line tool.

The examples use trove to make changes to configuration groups. Instructions for installing and configuring trove can be found in Managing configuration groups for cloud databases with the Trove command line tool.

To apply configuration groups you will need to enable root access to the database instance. This can be done through the Cloud Databases API or with a trove command:

trove root-enable instanceID

Enable the slow query log

You can use the slow query log to find queries that take a long time to execute and are therefore candidates for optimization. You can access the MySQL slow query log by writing it to a table and setting persistent values to appropriate parameters in your configuration group.

For more information about the MySQL slow query log, see the MySQL documentation on the slow query log.

To enable the logging of slow queries:

  1. Grant root access to the mysql.slow_log table.

  2. Set the configuration parameters that enable logging slow queries to the mysql.slow_log table:

    Parameter name Suggested value Description
    log_output 'TABLE' Tells MySQL to write logs to a table
    slow_query_log 1 Enables the slow query log

    For example, to create a new configuration group with trove that enables slow query logging, run:

    trove configuration-create EnableSlowQueryLog '{"log_output":"'TABLE'","slow_query_log":"1"}' --datastore MySQL
    
  3. Optionally, set the configuration parameters that define the conditions under which queries are written to the slow query log:

    Parameter name Suggested value Description
    long_query_time 0 or more The duration of a query to be logged as slow, in seconds. The default is 10 seconds.
    log_queries_not_using_indexes 0 or 1 Whether or not to log slow queries that use indexes. Default is 0 (off)
    expire_logs_days 0 to 99 The number of days to keep logs before deletion. The default is 0 (no automatic removal).

    For example, to add parameters to the configuration group created in the previous step that set the slow query duration to 15 seconds and cause logs to be deleted after one day, run:

    trove configuration-patch EnableSlowQueryLog '{"long_query_time":"15","expire_logs_days":"1"}'
    
  4. If necessary, attach the configuration group with these parameters to the instance for which you want to enable slow query logging. To attach the configuration using trove, run:

    trove configuration-attach EnableSlowQueryLog instanceID
    
  5. After the configuration is applied to your server, you can retrieve the slow query log from the database with a query. For example:

    mysql -e "select * from mysql.slow_log order by start_time desc limit"
    

Enable the general query log

You can use the general query log to track all activity, including any connections to the database and all queries sent to the database. It can be useful when you want to check the queries being sent by a client for troubleshooting purposes.

For more information about the MySQL general query log, see the MySQL documentation on the general query log.

To enable the general query log

  1. Grant root access to the mysql.general_log table.

  2. Set the configuration parameters that enable logging server activity to the mysql.general_log table:

    Parameter name Suggested value Description
    log_output 'TABLE' Tells MySQL to write logs to a table

    For example, to create a new configuration group with trove that enables general query logging, run:

    trove configuration-create EnableGeneralLog '{"log_output":"'TABLE'","slow_query_log":"1"}' --datastore MySQL
    
  3. Optionally, set the configuration parameter that tells MySQL how long to keep logs.

    Parameter name Suggested value Description
    expire_logs_days 0 to 99 The number of days to keep logs before deletion. The default is 0 (no automatic removal).

    For example, to add a parameter to the configuration group created in the previous step that will cause logs to be deleted after one day, run:

    trove configuration-patch EnableGeneralLog '{"expire_logs_days":"1"}'
    
  4. If necessary, attach the configuration group with these parameters to the instance for which you want to enable general query logging. To attach the configuration using trove, run:

    trove-attach EnableGeneralLog <em>instanceID</em></pre></li>
    
  5. After the configuration is applied to your server, you can retrieve the general query log from the database with a query. For example:

    mysql -e "select * from mysql.general_log order by event_time desc limit 1\G"
    


Was this content helpful?




© 2011-2013 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License


See license specifics and DISCLAIMER