Geek Answers Handbook

Randomly slowing queries in a MySQL innoDB table with 300 mm rows and 50-100 queries per second

I have a MySQL database with one InnoDB table with approximately 300 million rows. Up to 10 connected clients send 50-60 requests per second. Everything was smooth for several months until recently, when MySQL started to stop when using a lot of CPUs (the 100% +. uptime shows values ​​such as 15, 12, 15.). Requests that take 500 ms take a few seconds, from tens to hundreds. Executing the SHOW PROCESSLIST shows requests hanging in the Sending data state.

I can’t understand why any help is appreciated.

Server

Intel (R) Xeon (R) CPU E5 @ 2.40 GHz | 12 Cpus | 32 GB RAM

my.cnf

 innodb_file_per_table = 1 tmp-table-size = 32M max-heap-table-size = 32M innodb-log-files-in-group = 2 innodb-flush-method = O_DIRECT innodb-log-file-size = 512M innodb-buffer-pool-size = 26G innodb-flush-log-at-trx-commit = 2 innodb-file-per-table = 1 innodb_file_format = barracuda

Table (name: records)

 +----------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | identifier | int(11) | YES | MUL | 0 | | | timestamp | int(11) | YES | MUL | NULL | | | rtype | int(5) | YES | MUL | NULL | | | x1 | int(11) | YES | | NULL | | | x2 | int(11) | YES | | NULL | | | net | bigint(20) | YES | | NULL | | | created_at | datetime | NO | | NULL | | +----------------+------------+------+-----+---------+----------------+

Indexed and used in the WHERE query:

  • timestamp (UNIX timestamp as INT)
  • identifier
  • rtype (five possible values, 1-5)

Data size

 Data_length = ~18 GB Index_length = ~16 GB

Query

 SELECT identifier, timestamp, x1 AS a, x2 AS b, net FROM records WHERE identifier=1010 AND timestamp >=1463111100 AND timestamp <= 1463738400 AND rtype=5 ORDER BY timestamp;

(Returns about 900 lines, sometimes ends in less than a second, sometimes 10-100 seconds)

Query Analysis

 select_type = SIMPLE type = index_merge possible_keys = indeXidentifier, indeXtimestamp, indeXrtype key = indeXidentifier, indeXrtype key_len = 4,5 rows = 10641 Extra = Using intersect(indeXidentifier,indeXrtype); Using where
+5
source share
2 answers

I have two recommendations:

1. Reorder the columns in your multi-column index. Recommended order: identifier, rtype, timestamp.

Unique index scanning is faster than index range scanning, so it’s better to display first.

2. Modify your request as follows:

 select * from( SELECT identifier, timestamp, x1 AS a, x2 AS b, net FROM records WHERE identifier=1010 AND timestamp >=1463111100 AND timestamp <= 1463738400 AND rtype=5 ) t1 ORDER BY timestamp;

To avoid using an index for sorting.

+3
source

Any of these is optimal for SELECT in Question:

 INDEX(rtype, identifier, timestamp) INDEX(identifier, rtype, timestamp)

The principle is to first put all the "= constants" of the WHERE part, and then add one more thing (the "range" over the timestamp ). More cooking tips .

There is no need to put this in a subquery - this will slow down by creating an unnecessary tmp table.

Why did it suddenly slow down? The likely reason is caching. Immediately after adding a new index, less things were cached in RAM, and all SELECTs hit the disk a lot.

Let me double check the query plan. Specify EXPLAIN SELECT ... It should be one line long, indicating that it uses an index with three columns and does not say "intersect", "temporary" or "filesort".

If something else is wrong, please indicate what to explain, plus SHOW CREATE TABLE (this is more descriptive than DESCRIBE .)

Another thing to do: disable the query cache. Add / change these parameters in my.cnf and restart the server:

 query_cache_type = OFF query_cache_size = 0

How does INSERTs ? One row at a time? If they can be "dosed", even several dozen at a time, this will greatly help.

Since you are commenting on the CPU, it looks like you have a request related to the CPU, not an I / O binding. Do SHOW FULL PROCESSLIST; - do you see some kind of request with a big "time"? Is that something you haven't talked about yet?

Please run

 SHOW VARIABLES LIKE 'max_connections'; SHOW GLOBAL STATUS LIKE 'Max_used_connections';

(Values ​​may lead to a discussion of thunder herds.)

+1
source

More articles:


All Articles