Oracle Cloud Infrastructure Documentation

Managing General and Slow Query Logs on the DB System

You can manage and purge general and slow query logs on the MySQL HeatWave DB system.

By default, general log and slow query log are disabled on the DB system.

When enabled, the logs are stored as CSV tables on the DB system:
  • General log is stored in the mysql.general_log table.
  • Slow query log is stored in the mysql.slow_log table.

On MySQL HeatWave DB systems, slow logging is configured per MySQL instance. The primary logs its own slow queries only if slow logging is enabled on the primary. Each read replica logs its own slow queries only if slow logging is enabled on that replica. Logs are not automatically consolidated across primary and replicas, so they must be checked per instance. The slow query log does not capture every executed query. It only records queries that meet the slow query log criteria, such as queries that exceed the duration specified by the long_query_time variable.

To capture every executed query, you must use the general log. The general query log records all SQL statements received by the MySQL server from connected clients. On a read replica, the general log records statements received directly by the replica, it does not record statements that come from the primary.

As there is no default retention period for these logs, they remain on the DB system until you purge them.

Enabling General and Slow Query Logs

Enable the general log and slow query log on a DB system.

  1. Use the DB system configuration to set the following variables:
    • general_log: Set to ON to enable the general log.
    • slow_query_log: Set to ON to enable the slow query log.
    • long_query_time and min_examined_row_limit: Set these variables to define which queries are written to the slow query log.

Estimating General and Slow Query Log Size

Estimate the size of the general log and slow query log tables.

Note

These queries return a rough size estimate only. They do not the return the exact size of the logs. They may return an incorrect size estimate if the log has been truncated previously.
  • To estimate the size of the general log table, run the following query:
    SELECT
  s.rows_now,
  x.avg_write,
  s.rows_now * x.avg_write AS approx_bytes
FROM
  (SELECT COUNT(*) AS rows_now FROM mysql.general_log) AS s
JOIN sys.x$io_global_by_file_by_bytes AS x
  ON x.file LIKE '%general_log.CSV';
  • To estimate the size of the slow query log table, run the following query:
    SELECT
  s.rows_now,
  x.avg_write,
  s.rows_now * x.avg_write AS approx_bytes
FROM
  (SELECT COUNT(*) AS rows_now FROM mysql.slow_log) AS s
JOIN sys.x$io_global_by_file_by_bytes AS x
  ON x.file LIKE '%slow_log.CSV';

Purging General and Slow Query Logs

Purge the general log and slow query log tables.

The following commands truncate the entire log table. If you want to retain some information or history from the log table, you must copy the required information to another table before truncating the log.

Note

For read replicas, the slow logs are managed per MySQL instance. You cannot truncate the query log table directly on a read replica because read replicas run in read-only mode. To truncate the query log table on read replicas, run the truncate command on the source MySQL server or writable endpoint of the DB system. The statement is replicated and applied on the read replicas.
  • To purge the general log, run the following statement:
    CALL sys.truncate_general_log();
  • To purge the slow query log, run the following statement:
    CALL sys.truncate_slow_log();