Details
-
Type:
Bug
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 10.0.17
-
Fix Version/s: 10.0
-
Component/s: Storage Engine - XtraDB
-
Labels:
Description
Small innodb_buffer_pool_size causes mysqld to spin at 100% CPU.
Here is how I could repeat:
Start server with:
sql/mysqld --no-defaults --language=$(pwd)/sql/share/english --basedir=$(pwd) --datadir=$X --socket=$(pwd)/$X/mysql.sock --port=3310 --innodb-buffer-pool-size=5M
Create some dirty pages to trigger flush thread:
create table t1 (a int primary key auto_increment, b varchar (1000));
insert into t1 (b) values (repeat("a", 999));
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
This causes the mysqld process to appear at 100% CPU in `top`.
The problem seems to be related to heuristics with hard-coded constants that
assume a certain minimum pool size. In page_cleaner_adapt_lru_sleep_time(),
if the number of free pages is less than @@innodb_lru_scan_depth / 20 (which
works out as 51 by default), it will never sleep between trying to flush
pages. Probably the page flusher has another heuristics that doesn't try to
flush out more than some percentage of total pages or something.
Se with small buffer pool, we end up with two heuristics fighting one another,
and 100% cpu spinning results.
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
For me it took a few more INSERTs to get there, so if it's not reproducible with SQL above, just keep inserting.
Is this true for XtraDB only or is InnoDB also affected ?
I didn't get it with InnoDB. "ERROR 1206 (HY000): The total number of locks exceeds the lock table size" happened sooner than it got into the spin.
Have you tested does Percona 5.6 has this problem ? If it has, I rather wait them to fix it.
Yes, it also happens on Percona Server 5.6.22-72.0, but I don't know if it has been reported to them.
Work-around: set --innodb-lru-scan-depth=100