Query Insights

Query Insights in QueryWise allows you to track slow queries in your database. This feature is essential for optimizing your database performance. It supports both MySQL and PostgreSQL databases.

Enable Slow Query Log for MySQL

The slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at least min_examined_row_limit rows to be examined. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization.

To enable slow query log, you need to change the following MySQL configuration in MySQL configuration file (e.g. /etc/mysql/my.cnf):

    slow_query_log = ON long_query_time = 1 log_output = TABLE

After you change the MySQL configuration, you need to restart MySQL to make the change effective.

Enable pg_stat_statements for PostgreSQL

The pg_stat_statements module provides a means for tracking planning and execution statistics of all SQL statements executed by a server.

To enable pg_stat_statements, you need to modify the following PostgreSQL configuration in PostgreSQL configuration file (e.g. /etc/postgresql/14/main/postgresql.conf):

    shared_preload_libraries = 'pg_stat_statements'
    pg_stat_statements.track = all

After you change the PostgreSQL configuration, you need to restart PostgreSQL to make the change effective.

You also need to create the pg_stat_statements extension for each database. You can use the following SQL command:

    CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

With Query Insights, you can monitor and optimize your database operations effectively.