Percona server 5.5 completely stalls during import (myloader)

[Mischa ter Smitten]

http://dba.stackexchange.com/questions/120923/mysql-percona-server-5-5-completely-stalls-during-import-myloader

The problem seem to be caused by: innodb_adaptive_flushing_method = keep_average, innodb_adaptive_flushing_method = estimate runs stable.

[Mischa ter Smitten (mischa-ter-smitten)]

Output of:

gdb -ex "set pagination 0" -ex "thread apply all bt" --batch -p $(pidof mysqld)

[Laurynas Biveinis (laurynas-biveinis)]

Unfortunately some of the symbol information is missing in gdb dump, does the server have debug info packages installed?

[Mischa ter Smitten (mischa-ter-smitten)]

No, how does that work? Installing the percona-server-5.5-dbg package and then what?

[Laurynas Biveinis (laurynas-biveinis)]

Please just retry the gdb command after having the package installed.

[Mischa ter Smitten (mischa-ter-smitten)]

I will do that.

[Mischa ter Smitten (mischa-ter-smitten)]

Output of:

gdb -ex "set pagination 0" -ex "thread apply all bt" --batch -p $(pidof mysqld)

With percona-server-5.5-dbg installed

[Laurynas Biveinis (laurynas-biveinis)]

Thanks. I am analysing the dump.

Did this started happening after an upgrade? If yes, can you tell the latest PS 5.5 version which worked for you?

[Mischa ter Smitten (mischa-ter-smitten)]

Hard to say, it's on new hardware, new installation.

On our previous setup we never encountered the problem (in our normal workload). But we never do mass imports there and we also have different hardware there so it's hard to compare...

[Laurynas Biveinis (laurynas-biveinis)]

Mischa and everybody experiencing random hangs on 5.5.46 / 5.6.27, please test server build with a preliminary fix, available at:

5.5: http://jenkins.percona.com/view/Percona-RELEASES/job/percona-server-5.5-RELEASE/148/
5.6: http://jenkins.percona.com/view/Percona-RELEASES/job/percona-server-5.6-RELEASE/50/

(please click through the blue dots to navigate to the right packages for your system).

Please confirm whether with these packages the problem does not occur anymore. In case it still does, please repeat the previous procedure: 1) make sure the debug info package is installed; 2) get the server to hanged state; 3) execute "gdb -ex "set pagination 0" -ex "thread apply all bt" --batch -p $(pidof mysqld)" and post here its output.

[Mischa ter Smitten (mischa-ter-smitten)]

I'll try to test them today.

[Mischa ter Smitten (mischa-ter-smitten)]

Is there any reason why I would need to avoid using innodb_adaptive_flushing_method = keep_average on non SSD cards.

On our machines we have:
* single SSD
* multiple SSD, software RAID
* multiple SSD, hardware RAID

But non of them have SSD cards.

[Laurynas Biveinis (laurynas-biveinis)]

I assume this is asked in the context of the current bug, i.e. why keep_average causes this hang. But it's not keep_average itself which is the cause of the bug (if the fix is confirmed), but rather increased mutex pressure. The bug has probability of appearing with any adaptive flushing setting.

[Mischa ter Smitten (mischa-ter-smitten)]

Yes, in the context of the current bug.

We don't have the problem when we use innodb_adaptive_flushing_method = estimate and when using innodb_adaptive_flushing_method = keep_average on MariaDB.

[Laurynas Biveinis (laurynas-biveinis)]

If our analysis is correct then 1) the bug has a probability of appearing with any innodb_adaptive_flushing_method on Percona Server, just some more likely than others 2) MariaDB is not affected with any innodb_adaptive_flushing_method.

We will post the final bug analysis after we have confirmations that the issue disappeared.

[Mischa ter Smitten (mischa-ter-smitten)]

I've had 52 successful imports (before patch not more than +/- 5) last night so it looks like it fixed our problem.

[Laurynas Biveinis (laurynas-biveinis)]

https://github.com/percona/percona-server/pull/251, https://github.com/percona/percona-server/pull/252

[Yura Sorokin (yura-sorokin)]

Fixed by Oracle in 5.5.49, 5.6.30 and 5.7.12.

5.5 commit: 93e6f38
https://github.com/mysql/mysql-server/commit/93e6f388860490a6066cc07253a6aab94029e502

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-945