Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-5417

Killing a range query on MyRocks creates zombie queries

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Cannot Reproduce
    • Affects Version/s: 8.0.13-4
    • Fix Version/s: None
    • Component/s: MyRocks
    • Environment:

      Description

      My database is going down frequently due to exceeding max connections. There are queries timing out that have KILL ID run. These are always range queries, usually with 10+ WHERE clauses. The client thinks that the connection has been severed, so it reconnects. This process continues until connections have been maxed out and the database becomes unavailable. I have attached a screenshot from PMM showing how quickly the connection and thread count get out of control.

      There doesn't seem to be a direct correlation between either the number of rows returned by the query that causes it nor the number of rows in the table itself. QPS also doesn't seem to be a factor. I can trigger this error with a single query.

      Here is the specific table and query referenced in the screenshot, but I have seen this happen on multiple tables with different queries, though the query pattern was similar.

      CREATE TABLE `linkmetrics_pinterest_pipeline` ( 
       `pipeline_id` bigint(20) NOT NULL, 
       `domain_id` bigint(20) NOT NULL, 
       `url_id` bigint(20) NOT NULL, 
       `retrieved_at` bigint(20) NOT NULL, 
       `deadline_at` bigint(20) NOT NULL, 
       `expected_by` bigint(20) NOT NULL, 
       `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
       `modified_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
       PRIMARY KEY (`pipeline_id`), 
       KEY `linkmetrics_pinterest_pipeline__retrieved_at__expected_by__idx` (`domain_id`,`url_id`,`retrieved_at`,`expected_by`) 
      ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8

       

      explain SELECT url_id,MIN(expected_by) FROM linkmetrics_pinterest_pipeline WHERE ((domain_id=531319 AND url_id=1936797 AND retrieved_at=0 AND expected_by<=1549607608)...) GROUP BY domain_id,url_id;
      *************************** 1. row ***************************
       id: 1
       select_type: SIMPLE
       table: linkmetrics_pinterest_pipeline
       partitions: NULL
       type: range
      possible_keys: linkmetrics_pinterest_pipeline__retrieved_at__expected_by__idx
       key: linkmetrics_pinterest_pipeline__retrieved_at__expected_by__idx
       key_len: 24
       ref: NULL
       rows: 4
       filtered: 100.00
       Extra: Using where; Using index for group-by
      

       

      This may be related to https://jira.percona.com/browse/PS-5416. Killing processes doesn't always leave zombie queries. The only confirmed zombie queries I have are ones that triggered the phantom table/index scans in that issue.

        Smart Checklist

          Attachments

          1. image (1).png
            image (1).png
            277 kB
          2. image (2).png
            image (2).png
            45 kB
          3. my.cnf
            7 kB

            Activity

              People

              • Assignee:
                lalit.choudhary Lalit Choudhary
                Reporter:
                derekperkins Derek Perkins
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: