| 2015-03-18 05:30:58 |
Jan Lindström |
bug |
|
|
added bug |
| 2015-03-18 05:34:41 |
Jan Lindström |
description |
Small innodb_buffer_pool_size causes mysqld to spin at 100% CPU.
Here is how I could repeat:
Start server with:
{noformat}
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
{noformat}
Create some dirty pages to trigger flush thread:
{noformat}
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;
{noformat}
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.
It could take a few more INSERTs to get there, so if it's not reproducible with SQL above, just keep inserting.
Tested with Percona Server 5.6.22-72.0. |
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.
It could take a few more INSERTs to get there, so if it's not reproducible with SQL above, just keep inserting.
Tested with Percona Server 5.6.22-72.0. |
|
| 2015-03-18 06:22:31 |
Laurynas Biveinis |
nominated for series |
|
percona-server/5.5 |
|
| 2015-03-18 06:22:31 |
Laurynas Biveinis |
bug task added |
|
percona-server/5.5 |
|
| 2015-03-18 06:22:31 |
Laurynas Biveinis |
nominated for series |
|
percona-server/5.1 |
|
| 2015-03-18 06:22:31 |
Laurynas Biveinis |
bug task added |
|
percona-server/5.1 |
|
| 2015-03-18 06:22:31 |
Laurynas Biveinis |
nominated for series |
|
percona-server/5.6 |
|
| 2015-03-18 06:22:31 |
Laurynas Biveinis |
bug task added |
|
percona-server/5.6 |
|
| 2015-03-18 06:22:36 |
Laurynas Biveinis |
percona-server/5.1: status |
New |
Invalid |
|
| 2015-03-18 06:22:38 |
Laurynas Biveinis |
percona-server/5.5: status |
New |
Invalid |
|
| 2015-03-18 06:22:41 |
Laurynas Biveinis |
percona-server/5.6: status |
New |
Triaged |
|
| 2015-03-18 06:22:45 |
Laurynas Biveinis |
percona-server/5.6: importance |
Undecided |
Low |
|
| 2015-03-18 06:22:47 |
Laurynas Biveinis |
percona-server/5.6: assignee |
|
Laurynas Biveinis (laurynas-biveinis) |
|
| 2015-03-18 06:22:54 |
Laurynas Biveinis |
tags |
|
xtradb |
|
| 2015-03-18 08:17:06 |
Laurynas Biveinis |
percona-server/5.6: status |
Triaged |
In Progress |
|
| 2015-03-18 09:46:14 |
Laurynas Biveinis |
percona-server/5.6: status |
In Progress |
Triaged |
|
| 2015-03-18 09:46:23 |
Laurynas Biveinis |
percona-server/5.6: importance |
Low |
Medium |
|
| 2015-09-02 13:22:24 |
Laurynas Biveinis |
percona-server/5.6: status |
Triaged |
In Progress |
|
| 2015-09-02 14:30:11 |
Laurynas Biveinis |
percona-server/5.6: status |
In Progress |
Triaged |
|
| 2015-11-30 15:12:46 |
Laurynas Biveinis |
percona-server/5.6: assignee |
Laurynas Biveinis (laurynas-biveinis) |
Nickolay Ihalainen (ihanick) |
|
| 2015-12-02 08:50:54 |
Laurynas Biveinis |
percona-server/5.6: milestone |
|
5.6.27-76.1 |
|
| 2015-12-02 08:51:00 |
Laurynas Biveinis |
percona-server/5.6: status |
Triaged |
Fix Released |
|
| 2016-12-27 09:41:04 |
Laurynas Biveinis |
tags |
xtradb |
lru-flusher xtradb |
|
