Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.9
-
None
Description
explain select `payment_uid`,`date`,`simulation_state`
|
from `stock`
|
where `date` >= '2017-05-31 22:00:00' and `date` <= '2018-05-31 22:00:00' order by date desc;
|
+------+-------------+-------+------+---------------+------+---------+------+-----------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+-----------+-----------------------------+
|
| 1 | SIMPLE | stock | ALL | date | NULL | NULL | NULL | 233416309 | Using where; Using filesort |
|
+------+-------------+-------+------+---------------+------+---------+------+-----------+-----------------------------+
|
1 row in set (0.01 sec)
|
explain select `payment_uid`,`date`,`simulation_state`
|
from `stock`
|
where `date` >= '2017-05-31 22:00:00' and `date` <= '2018-05-31 22:00:00' order by date ;
|
+------+-------------+-------+------+---------------+------+---------+------+-----------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+-----------+-----------------------------+
|
| 1 | SIMPLE | stock | ALL | date | NULL | NULL | NULL | 233416309 | Using where; Using filesort |
|
+------+-------------+-------+------+---------------+------+---------+------+-----------+-----------------------------+
|
1 row in set (0.00 sec)
|
explain select `payment_uid`,`date`,`simulation_state`
|
from `stock`
|
where `date` >= '2017-05-31 22:00:00' and `date` <= '2018-05-31 22:00:00';
|
+------+-------------+-------+------+---------------+------+---------+------+-----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+-----------+-------------+
|
| 1 | SIMPLE | stock | ALL | date | NULL | NULL | NULL | 233416309 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+-----------+-------------+
|
1 row in set (0.00 sec)
|
DDL is
`date` datetime DEFAULT NULL,
KEY `date` (`date`),
Important step to reproduce ,
We copy a 10.1.24 datadir and used mysql_upgrade -s
The filtered range is 120M rows
(psergey edited a bit to improve readability)
Attachments
Issue Links
- relates to
-
MDEV-17436 Add new meta system configuration variables
-
- Open
-
-
-
- Closed
-
Activity
Actually, the analyze output in my above comment was with tmpdir (/tmp) in tmpfs. And we should also not ignore that using a secondary index to avoid filesort implies reading more the disk. I'm surprised nobody checked the same query with FORCE INDEX(date), to actually show there's improvement to expect.
In my current setup (~227 GiB RAM, 16Gi buffer pool size, SSD, stock table: 40Gi data, 178Gi index), forcing the index (tried twice: 11m34 & 18m53) is slower than sorting in tmpfs (8m41). Now retrying twice with tmpdir in same device as datadir: 24m23 & 29m (again, the second run is slower, no idea why). Now, I'm going to play with sort_buffer_size...
(note that many processes are currently stopped on the machine and during normal operation, there's much less RAM available, that's why innodb_buffer_pool_size is not bigger)
I'm surprised nobody checked the same query with FORCE INDEX(date), to actually show there's improvement to expect.
(even if I missed the first comments, explain does not show much)
Could you re run analyze query with bigger sort buffer like 512M or 1G and tell us what the current value of the sort_buffer_size ?
It was the default value: 2M. I changed to 1G and ran twice: 12min15 & 11m22.
Julien this issue was created with spider testing in mind , while the sorting is happening on the catalog table no records is flowing to a joined spider category table from the original query we wanted to improve with sharding .
We are really missing the result of ANALYZE query with FORCE INDEX to get the time and range
Conclusion :
- Forcing the index avoid file sort cost
- Forcing the index save 25% reading on disk
select 1.8/2.4*100;+-------------+| 1.8/2.4*100 |+-------------+| 75.00000 |+-------------+ - Forcing the index : how much time, reading data is taking. I can't see this from previous comments ? (Cost of rnd read + pk clustered vs direct clustered lookup on SSD)
- Forcing the index enable constant work using spider on joined tables as data flow to next state of the plan without without waiting for sorting
Leaving it to psergey to decide whether there is an issue to solve, and if so, in which version.
Hi Julien, Ivan,
Could you re run analyze query with bigger sort buffer like 512M or 1G and tell us what the current value of the sort_buffer_size ?
It does not change the fact that on SSD or NVMe sorting to read in the correct pk order the majority of record is less an issue to compare to spinning disk
But it can help quantify the cost of a reasonable sorting .