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.
