Bug #99717 Peformance regression of parallel count
Submitted: 28 May 2020 7:47 Modified: 6 Apr 2021 12:58
Reporter: Ze Yang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[28 May 2020 7:47] Ze Yang
Description:
In Sunny's commit 453e7255698e1a3cbd89e0226e01e218dab0a119 'WL#12978 - InnoDB:Fix imbalance during parallel scan', Sunny also 'Additional changes are related to pre-fetching pages during the parallel scan. This is to reduce the IO overhead on the parallel scan threads.'.

```
-  block = btr_block_get(page_id_t(page_id.space(), next_page_no),
-                        block->page.size, BTR_SEARCH_LEAF, index, m_mtr);
+  block = buf_page_get_gen(page_id_t(page_id.space(), next_page_no),
+                           block->page.size, RW_S_LATCH, nullptr,
+                           Page_fetch::SCAN, __FILE__, __LINE__, m_mtr);
```
The change also have effect on whether load page to buffer pool.

buf_page_get_gen->Buf_fetch<T>::single_page()

```
4097   /* Don't move the page to the head of the LRU list so that the
4098   page can be discarded quickly if it is not accessed again. */
4099   if (m_mode != Page_fetch::SCAN) {
4100     /* This is a heuristic and we don't care about ordering issues. */
4101     if (access_time == 0) {
4102       buf_page_mutex_enter(block);
4103
4104       buf_page_set_accessed(&block->page);
4105
4106       buf_page_mutex_exit(block);
4107     }
4108
4109     if (m_mode != Page_fetch::PEEK_IF_IN_POOL) {
4110       buf_page_make_young_if_needed(&block->page);
4111     }
4112   }
```

When the buffer pool do not have many free pages, the new parallel count query will almost never load pages to buffer pool. The query will need too much IO, and run too much slow.

This make parallel count sometimes much slower than 8.0.16.

How to repeat:
parallel_count-master.opt
```
--innodb_buffer_pool_size=14M
```

parallel_count.test
```
CREATE TABLE `t` (
  `c1` int DEFAULT NULL,
  `c2` varchar(100) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` varchar(100) DEFAULT NULL
) ENGINE=InnoDB;
insert into t values(1, 'abcdefghigklmnabcd', 'abcdefghigklmnabcd','abcdefghigklmnabcd');
--let $loop_count= 16
--let $loop_num= 0
while ($loop_num <= $loop_count)
{
insert into t select * from t;
--inc $loop_num
}
CREATE TABLE t1 as select * from t;
SELECT COUNT(*) FROM t WHERE c1 > 0;
SELECT COUNT(*) FROM t WHERE c1 > 0;
SELECT COUNT(*) FROM t WHERE c1 > 0;
SELECT COUNT(*) FROM t WHERE c1 > 0;
SELECT COUNT(*) FROM t WHERE c1 > 0;
show status like "Innodb_data_reads";
set profiling=1;
SELECT COUNT(*) FROM t;
SELECT COUNT(*) FROM t;
set profiling=0;
show status like "Innodb_data_reads";
SELECT COUNT(*) FROM t1;
SELECT COUNT(*) FROM t1;
SELECT COUNT(*) FROM t1;
show status like "Innodb_data_reads";
set profiling=1;
SELECT COUNT(*) FROM t1;
SELECT COUNT(*) FROM t1;
set profiling=0;
show status like "Innodb_data_reads";
show profiles;
DROP TABLE t,t1;
```

As the buffer pool almost filled by table 't', when we do parallel count on table t1. This is much slower.

parallel_count.result on my machine

```
CREATE TABLE `t` (
`c1` int DEFAULT NULL,
`c2` varchar(100) DEFAULT NULL,
`c3` varchar(100) DEFAULT NULL,
`c4` varchar(100) DEFAULT NULL
) ENGINE=InnoDB;
insert into t values(1, 'abcdefghigklmnabcd', 'abcdefghigklmnabcd','abcdefghigklmnabcd');
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
CREATE TABLE t1 as select * from t;
SELECT COUNT(*) FROM t WHERE c1 > 0;
COUNT(*)
131072
SELECT COUNT(*) FROM t WHERE c1 > 0;
COUNT(*)
131072
SELECT COUNT(*) FROM t WHERE c1 > 0;
COUNT(*)
131072
SELECT COUNT(*) FROM t WHERE c1 > 0;
COUNT(*)
131072
SELECT COUNT(*) FROM t WHERE c1 > 0;
COUNT(*)
131072
show status like "Innodb_data_reads";
Variable_name	Value
Innodb_data_reads	4014
set profiling=1;
Warnings:
Warning	1287	'@@profiling' is deprecated and will be removed in a future release.
SELECT COUNT(*) FROM t;
COUNT(*)
131072
SELECT COUNT(*) FROM t;
COUNT(*)
131072
set profiling=0;
Warnings:
Warning	1287	'@@profiling' is deprecated and will be removed in a future release.
show status like "Innodb_data_reads";
Variable_name	Value
Innodb_data_reads	4014
SELECT COUNT(*) FROM t1;
COUNT(*)
131072
SELECT COUNT(*) FROM t1;
COUNT(*)
131072
SELECT COUNT(*) FROM t1;
COUNT(*)
131072
show status like "Innodb_data_reads";
Variable_name	Value
Innodb_data_reads	8272
set profiling=1;
Warnings:
Warning	1287	'@@profiling' is deprecated and will be removed in a future release.
SELECT COUNT(*) FROM t1;
COUNT(*)
131072
SELECT COUNT(*) FROM t1;
COUNT(*)
131072
set profiling=0;
Warnings:
Warning	1287	'@@profiling' is deprecated and will be removed in a future release.
show status like "Innodb_data_reads";
Variable_name	Value
Innodb_data_reads	11080
show profiles;
Query_ID	Duration	Query
1	0.00007450	SHOW WARNINGS
2	0.00603425	SELECT COUNT(*) FROM t
3	0.00607675	SELECT COUNT(*) FROM t
4	0.00005625	SHOW WARNINGS
5	0.08273250	SELECT COUNT(*) FROM t1
6	0.08318525	SELECT COUNT(*) FROM t1
Warnings:
Warning	1287	'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead
DROP TABLE t,t1;
```

Suggested fix:
Give the parallel count a chance to load page to buffer pool when no many free pages.
Or add one variable to control the behavior.
[28 May 2020 9:32] Sunny Bains
This code has been disabled and will be removed in a future release. The other problem with this code is that it uses a physical read ahead.
[6 Apr 2021 12:58] MySQL Verification Team
Hi Mr. Yang,

We have decided, upon further inspection, to verify this bug.

This is a bug that points to the performance regression.
[7 Apr 2021 12:54] MySQL Verification Team
This bug has a duplicate in the following bug:

https://bugs.mysql.com/bug.php?id=103213
[3 Jan 13:32] Liu Yuanyuan
I encountered this issue too on 8.0.17, is this bug fixed?
[8 Jan 10:38] MySQL Verification Team
Hi,

Unfortunately, no good news.

This bug is not yet fixed and we do not know when will it be fixed.

Each Development Team has it's own schedule , which changes every week. Hence, nobody knows at the moment when will it be fixed.