Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Killing a range query on MyRocks creates zombie queries

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.

Environment

Kubernetes (AKS)
 

  1.  

    1. Dockerfile
       
      FROM gcr.io/gcp-runtimes/ubuntu_18_0_4:latest
       
      RUN apt-get update && \
      apt-get upgrade -y && \
      apt-get install wget lsb-core gnupg2 -y --no-install-recommends && \
      wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb && \
      dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb && \
      percona-release setup ps80 && \
      apt-get install percona-server-server percona-server-rocksdb -y && \
      rm -rf /var/lib/mysql percona-release_latest.$(lsb_release -sc)_all.deb && \
      apt-get purge lsb-core gnupg2 -y && \
      apt-get autoremove -y && \
      apt-get clean && \
      rm -rf /var/lib/apt/lists/*
       

    2. my.cnf
       

  2. CACHES AND LIMITS #
    tmp-table-size = 32M
    max-heap-table-size = 32M
    max-connections = 500
    thread-cache-size = 50
    open-files-limit = 65535
    table-definition-cache = 4096
    table-open-cache = 4096
     

  3. ROCKSDB #
    early-plugin-load = "ha_rocksdb.so"
    rocksdb

rocksdb_max_open_files = -1

rocksdb_max_background_jobs = 2
rocksdb_max_total_wal_size = 4G
rocksdb_block_size = 16384
rocksdb_block_cache_size = 5G
rocksdb_table_cache_numshardbits = 6
rocksdb_large_prefix = ON

  1. crash safety
    rocksdb-flush-log-at-trx-commit = 2
    rocksdb-wal-recovery-mode = 1

  1. rate limiter
    rocksdb_bytes_per_sync = 4194304
    rocksdb_wal_bytes_per_sync = 4194304
    rocksdb_rate_limiter_bytes_per_sec = 104857600 #100MB/s. Increase if you're running on higher spec machines

  1. triggering compaction if there are many sequential deletes
    rocksdb_compaction_sequential_deletes_count_sd = 1
    rocksdb_compaction_sequential_deletes = 199999
    rocksdb_compaction_sequential_deletes_window = 200000

  1. DirectIO bypasses the OS cache
    rocksdb_use_direct_reads = ON
    rocksdb_use_direct_io_for_flush_and_compaction = ON

  1. COLUMN FAMILIES #
    rocksdb_default_cf_options = write_buffer_size=128m;target_file_size_base=32m;max_bytes_for_level_base=512m;level0_file_num_compaction_trigger=4;level0_slowdown_writes_trigger=10;level0_stop_writes_trigger=15;max_write_buffer_number=4;compression_per_level=kLZ4Compression;bottommost_compression=kZSTD;compression_opts=-14:1:0;block_based_table_factory={cache_index_and_filter_blocks=1;filter_policy=bloomfilter:10:false;whole_key_filtering=1};level_compaction_dynamic_level_bytes=true;optimize_filters_for_hits=true;compaction_pri=kMinOverlappingRatio

Attachments

3
  • 14 Feb 2019, 07:35 PM
  • 13 Feb 2019, 07:44 PM
  • 13 Feb 2019, 07:44 PM

Smart Checklist

Activity

Show:

Lalit Choudhary February 25, 2019 at 9:00 AM

 

Thank you for the update.

> but in my code, I have query timeouts set that run KILL if a query is taking too long, and after being killed, this is left as a zombie query.

 

From the above as mysql documentation for KILL command, killing connection takes time and also it depends on the type of transaction we are killing it.  

https://dev.mysql.com/doc/refman/8.0/en/kill.html

When you use KILL, a thread-specific kill flag is set for the thread. In most cases, it might take some time for the thread to die because the kill flag is checked only at specific intervals:

  • During SELECT operations, for ORDER BY and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.

  • {{}}The KILL statement returns without waiting for confirmation, but the kill flag check aborts the operation within a reasonably small amount of time. Aborting the operation to perform any necessary cleanup also takes some time.

And I think due to this query count increased and it's reaching to max_connections.

In these, I would suggest increasing the timeout for long running queries.  Here I don't see any buggy behavior, it's related to the transaction queries.

Derek Perkins February 25, 2019 at 6:09 AM

My max connections are set at 500 as shown in the PMM screenshot. This issue is causing the max connection to be reached in production because these zombie queries are left behind forever. I am having a difficult time replicating the zombie queries outside of our production environment. It doesn't matter what I set the max connection limit to, as that will be quickly reached when this bug is triggered.

Lalit Choudhary February 15, 2019 at 9:51 AM
Edited

Configured mysql with given configuration. Loaded given data.

I can not reproduce the described behavior. I would suggest fix max_connection issue.

 

Table: linkmetrics_pinterest Create Table: CREATE TABLE `linkmetrics_pinterest` ( `pinterest_id` bigint(20) NOT NULL, `domain_id` bigint(20) NOT NULL, `url_id` bigint(20) NOT NULL, `retrieved_at` bigint(20) NOT NULL, `shared` int(11) DEFAULT NULL, PRIMARY KEY (`pinterest_id`), KEY `fk__linkmetrics_pinterest__urls__idx` (`domain_id`,`url_id`,`retrieved_at`), KEY `linkmetrics_pinterest__urls__retrieved_at__idx` (`domain_id`,`url_id`,`retrieved_at`) COMMENT 'rev:default' ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

 

*************************** 1. row *************************** id: 1 select_type: SIMPLE table: linkmetrics_pinterest partitions: NULL type: range possible_keys: fk__linkmetrics_pinterest__urls__idx,linkmetrics_pinterest__urls__retrieved_at__idx key: fk__linkmetrics_pinterest__urls__idx key_len: 24 ref: NULL rows: 8678699 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.01 sec) Query execution time: 76 rows in set (59.69 sec)

 

 

 

*************************** 1. row *************************** id: 1 select_type: SIMPLE table: linkmetrics_pinterest partitions: NULL type: range possible_keys: fk__linkmetrics_pinterest__urls__idx,linkmetrics_pinterest__urls__retrieved_at__idx key: fk__linkmetrics_pinterest__urls__idx key_len: 24 ref: NULL rows: 36 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec) Query execurtion time: 25 rows in set (0.01 sec)

 

Lalit Choudhary February 15, 2019 at 8:36 AM
Edited

please provide an output of the following command. 

 show status like 'max_conn%';
 show status like 'max%conn%';
In this case, the possibility is a max_connection limit reached and that resulting to timeout and killing queries.

my test is still progress...

Also, this could be the trigger for   you reporter for query execution time difference.  I would suggest increasing the max connection limit and see how it works.

 

Derek Perkins February 15, 2019 at 4:16 AM

This query is a truncated version of the query above and only has WHERE 36 clauses, running in 20ms as compared to 2 minutes for the original query I posted.

SELECT pinterest_id,domain_id,url_idFROM domains.linkmetrics_pinterest WHERE ( (domain_id=523149 AND url_id=16064970 AND retrieved_at>=1549324800) OR (domain_id=523562 AND url_id=164159477 AND retrieved_at>=1549324800) OR (domain_id=523154 AND url_id=2891806 AND retrieved_at>=1549324800) OR (domain_id=523168 AND url_id=2891804 AND retrieved_at>=1549324800) OR (domain_id=783517 AND url_id=2763369 AND retrieved_at>=1549324800) OR (domain_id=783517 AND url_id=2891807 AND retrieved_at>=1549324800) OR (domain_id=6636899 AND url_id=305471335 AND retrieved_at>=1549324800) OR (domain_id=523148 AND url_id=2891814 AND retrieved_at>=1549324800) OR (domain_id=613910 AND url_id=2873029 AND retrieved_at>=1549324800) OR (domain_id=523166 AND url_id=161495122 AND retrieved_at>=1549324800) OR (domain_id=810258 AND url_id=2891808 AND retrieved_at>=1549324800) OR (domain_id=535338 AND url_id=2891863 AND retrieved_at>=1549324800) OR (domain_id=548716 AND url_id=2873032 AND retrieved_at>=1549324800) OR (domain_id=544119 AND url_id=2891816 AND retrieved_at>=1549324800) OR (domain_id=523171 AND url_id=2873026 AND retrieved_at>=1549324800) OR (domain_id=523169 AND url_id=2891813 AND retrieved_at>=1549324800) OR (domain_id=544635 AND url_id=4255672 AND retrieved_at>=1549324800) OR (domain_id=523148 AND url_id=1964525 AND retrieved_at>=1549324800) OR (domain_id=552512 AND url_id=165904357 AND retrieved_at>=1549324800) OR (domain_id=525173 AND url_id=2033763 AND retrieved_at>=1549324800) OR (domain_id=523186 AND url_id=2473968 AND retrieved_at>=1549324800) OR (domain_id=523192 AND url_id=180368870 AND retrieved_at>=1549324800) OR (domain_id=562385 AND url_id=2891815 AND retrieved_at>=1549324800) OR (domain_id=544602 AND url_id=2891821 AND retrieved_at>=1549324800) OR (domain_id=533203 AND url_id=164159492 AND retrieved_at>=1549324800) OR (domain_id=540639 AND url_id=161224301 AND retrieved_at>=1549324800) OR (domain_id=574733 AND url_id=160595744 AND retrieved_at>=1549324800) OR (domain_id=569735 AND url_id=2873027 AND retrieved_at>=1549324800) OR (domain_id=526305 AND url_id=84822135 AND retrieved_at>=1549324800) OR (domain_id=525137 AND url_id=2891827 AND retrieved_at>=1549324800) OR (domain_id=523183 AND url_id=293102985 AND retrieved_at>=1549324800) OR (domain_id=6636899 AND url_id=298223052 AND retrieved_at>=1549324800) OR (domain_id=523149 AND url_id=16742767 AND retrieved_at>=1549324800) OR (domain_id=523154 AND url_id=112089669 AND retrieved_at>=1549324800) OR (domain_id=568225 AND url_id=168602528 AND retrieved_at>=1549324800) OR (domain_id=555985 AND url_id=2916665 AND retrieved_at>=1549324800));
Cannot Reproduce

Details

Assignee

Reporter

Labels

Components

Affects versions

Priority

Smart Checklist

Created February 13, 2019 at 7:56 PM
Updated March 6, 2024 at 12:21 PM
Resolved February 25, 2019 at 11:32 AM
Configure