Uploaded image for project: 'Percona Monitoring and Management'
  1. Percona Monitoring and Management
  2. PMM-4001

speed up Prometheus once-a-minute schema query

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.0.0-beta6
    • Component/s: MySQLd_Exporter
    • Labels:
      None

      Description

      We had a problem in our production environment where the Prometheus `auto_increment` schema queries that run once a minute in PMM bogged down during heavy load so that they were taking longer than a minute to run. They eventually swamped the database with more and more sessions running the query.

      This problem of this query running very slowly has happened to others, as described here:

      https://www.percona.com/forums/questions-discussions/percona-monitoring-and-management/46275-pmm-table-status-this-query-it-s-very-slow-server-has-hang

      We found that this query can run about 100X faster in our environment if we put the `columns` table first, `STRAIGHT JOIN`ed to the `tables` table. [Our environment has 7 schemas, 840 tables, 41,000 columns.]

      SELECT t.table_schema, t.table_name, column_name, `auto_increment`
      FROM information_schema.columns AS c
        STRAIGHT_JOIN information_schema.tables AS t
          ON BINARY t.table_schema = c.table_schema AND BINARY t.table_name = c.table_name
      WHERE c.extra = 'auto_increment' AND t.auto_increment IS NOT NULL

      I'd like to submit a Pull Request to make this standard in PMM, but I couldn't find where in the project this Prometheus query lives? If this looks reasonable, perhaps can you point me to that and I'll make a PR? (I can find the query in the native `prometheus` repo here, but it's slightly different, with `USING` on the join.

      https://github.com/prometheus/mysqld_exporter/blob/master/collector/info_schema_auto_increment.go#L25-L36

      So I'm wondering if there is a copy with this slight variation included in PMM?)

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                Unassigned
                Reporter:
                colindkelley@gmail.com Colin D Kelley
                Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved: