Skip to main content

Enable SQL Query Optimization

To enable the SQL Query Optimization feature, please select your installation type and follow the instructions below. Once enabled, you'll see the first automatic SQL Query Recommendation in one week.

Existing Linux Installation​

Automatic Installation​

Note: If your server is already installed, you can use automatic installation, as it won't add a new server if it has the same hostname.

Run the helper mode from the installer:

For MySQL/MariaDB/Percona Servers:

RELEEM_MYSQL_TYPE=1 bash -c "$(curl -L https://releem.s3.amazonaws.com/v2/install.sh)" enable_query_optimization

For PostgreSQL Servers:

RELEEM_PG_TYPE=1 bash -c "$(curl -L https://releem.s3.amazonaws.com/v2/install.sh)" enable_query_optimization

Use RELEEM_PG_TYPE=1 to explicitly select the PostgreSQL path, or RELEEM_MYSQL_TYPE=1 to explicitly select the MySQL path. Set RELEEM_PG_ROOT_LOGIN and RELEEM_PG_ROOT_PASSWORD when the PostgreSQL superuser is not available through peer/passwordless authentication. Set RELEEM_MYSQL_ROOT_LOGIN and RELEEM_MYSQL_ROOT_PASSWORD when the MySQL root user requires a password. If the password is omitted, the installer first tries passwordless access and then prompts for it in the console.

This command updates /opt/releem/releem.conf, enables query optimization, and prepares the required database permissions.

Manual Installation​

If you prefer to update the agent manually:

MySQL/MariaDB/Percona Servers​

  1. Grant additional permissions to the releem user. The SQL Query Optimization feature requires Additional Permissions for the Releem Agent user.
  2. Add query_optimization=true setting to the /opt/releem/releem.conf.
  3. Restart Releem Agent using the following command: 
    systemctl restart releem-agent
  4. Run the following command: 
    /opt/releem/mysqlconfigurer.sh -p

PostgreSQL Servers​

  1. Grant additional permissions to the releem user as a PostgreSQL superuser: 
    -- PostgreSQL 14+
    GRANT pg_read_all_data TO releem;

    -- PostgreSQL 12/13
    GRANT CONNECT ON DATABASE "<database_name>" TO releem;
    GRANT USAGE ON SCHEMA "<schema_name>" TO releem;
    GRANT SELECT ON ALL TABLES IN SCHEMA "<schema_name>" TO releem;
    GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA "<schema_name>" TO releem;
    Replace <database_name> and <schema_name> with all monitored databases/schemas. For most self-managed deployments with a default database set, start with postgres and public.
  2. Add query_optimization=true setting to the /opt/releem/releem.conf.
  3. Restart Releem Agent using the following command: 
    systemctl restart releem-agent
  4. Run the following command to enable pg_stat_statements settings: 
    /opt/releem/mysqlconfigurer.sh -p

New Linux Installation​

Add this environment variable to the installation command:

RELEEM_QUERY_OPTIMIZATION=true
  1. Click "Add Server" link at Releem Customer Portal.
  2. Select the installation type.
  3. Modify the one-step installation command and the following environment variable: 
    RELEEM_QUERY_OPTIMIZATION=true
  4. Run the modified installation command on your server.

Additional Database Permissions Required​

For MySQL/MariaDB/Percona, the SQL Query Optimization feature requires Additional Permissions for the Releem Agent user.

For PostgreSQL, grant optimization read access to the Releem Agent user:

GRANT pg_read_all_data TO releem;

For PostgreSQL 12/13, use schema-level permissions instead:

GRANT CONNECT ON DATABASE "<database_name>" TO releem;
GRANT USAGE ON SCHEMA "<schema_name>" TO releem;
GRANT SELECT ON ALL TABLES IN SCHEMA "<schema_name>" TO releem;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA "<schema_name>" TO releem;

Data Collection and Analysis​

Once the SQL Query Optimization feature is enabled, Releem will automatically collect and save the EXPLAIN outputs of the top 100 queries and the top 100 slowest queries. This data helps in analyzing the execution plan of queries and optimizing them further.

An example of the EXPLAIN output collected by Releem is provided below:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.60"
    },
    "table": {
      "table_name": "sale_internals_order_discount",
      "access_type": "ref",
      "possible_keys": [
        "IX_SALE_ORDER_DSC_HASH"
      ],
      "key": "IX_SALE_ORDER_DSC_HASH",
      "used_key_parts": [
        "DISCOUNT_HASH"
      ],
      "key_length": "98",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.50",
        "eval_cost": "0.10",
        "prefix_cost": "0.60",
        "data_read_per_join": "1K"
      },
      "used_columns": [
        "ID",
        "MODULE_ID",
        "DISCOUNT_ID",
        "NAME",
        "DISCOUNT_HASH",
        "CONDITIONS",
        "UNPACK",
        "ACTIONS",
        "APPLICATION",
        "USE_COUPONS",
        "SORT",
        "PRIORITY",
        "LAST_DISCOUNT",
        "ACTIONS_DESCR"
      ]
    }
  }
}