Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.6
Description
Consider an attached testcase mdev34190-test.diff
It creates a table with 160K rows, each row being about 1K.
This should take about 10K InnoDB disk pages (assuming page fill ratio=100%).
Then, I restart the server, and do an ANALYZE for a full table scan.
I take care to prevent any other activity:
- Disable buffer pool save/load
- Disable background statistics collection
- Following Marko's advise, use innodb_fast_shutdown=0
I get:
show status like 'innodb_pages_read';
|
Variable_name Value
|
Innodb_pages_read 143
|
analyze format=json
|
select * from t1 force index (PRIMARY)
|
order by a asc, b asc, c asc, d asc;
|
ANALYZE
|
{
|
"query_block": { |
"select_id": 1, |
"r_loops": 1, |
"r_total_time_ms": 910.051, |
"table": { |
"table_name": "t1", |
"access_type": "index", |
"key": "PRIMARY", |
"key_length": "1028", |
"used_key_parts": ["a", "b", "c", "d"], |
"r_loops": 1, |
"rows": 1, |
"r_rows": 163840, |
"r_table_time_ms": 794.313, |
"r_other_time_ms": 115.711, |
"r_engine_stats": { |
"pages_accessed": 11708, |
"pages_read_count": 271, |
"pages_read_time_ms": 13.03 |
},
|
"filtered": 100, |
"r_filtered": 100, |
"using_index": true |
}
|
}
|
}
|
only 271 pages were read? which took just 13 ms of 794 ms spent in the engine?
show status like 'innodb_pages_read';
|
Variable_name Value
|
Innodb_pages_read 12112
|
Note that Innodb_pages_read shows a more reasonable number of 12K.
If I get the same query to do a backward scan by using ORDER BY DESC, I get a more reasonable numbers:
analyze format=json
|
select * from t1 force index (PRIMARY)
|
order by a desc, b desc, c desc, d desc;
|
ANALYZE
|
{
|
"query_block": { |
"select_id": 1, |
"r_loops": 1, |
"r_total_time_ms": 1129.6, |
"table": { |
"table_name": "t1", |
"access_type": "index", |
"key": "PRIMARY", |
"key_length": "1028", |
"used_key_parts": ["a", "b", "c", "d"], |
"r_loops": 1, |
"rows": 1, |
"r_rows": 163841, |
"r_table_time_ms": 1036.171, |
"r_other_time_ms": 93.424, |
"r_engine_stats": { |
"pages_accessed": 11708, |
"pages_read_count": 11707, |
"pages_read_time_ms": 315.503 |
},
|
"filtered": 100, |
"r_filtered": 100, |
"using_index": true |
}
|
}
|
}
|
Attachments
Issue Links
- relates to
-
MDEV-31558 Add InnoDB engine information to the slow query log
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-