[MDEV-5808] InnoDB FTS tests are much slower with XtraDB comparing to InnoDB plugin - JIRA

Details

Type: Bug
Status: Open
Priority: Minor
Resolution: Unresolved
Affects Version/s: 10.0.9
Fix Version/s: 10.0
Component/s: None
Labels:
- performance

Description

Some InnoDB tests, at least those from innodb_fts suite, are 20-100+% slower when they are executed with XtraDB than with InnoDB plugin.
Absolute numbers can vary a lot between the machines, but the difference still holds; same for executing them normally vs --mem; same for debug vs non-debug builds.

Few examples:

On a RelWithDebInfo build, fulltext test, with --mem:

innodb_fts.fulltext 'xtradb'             [ pass ]   2154
innodb_fts.fulltext 'xtradb'             [ pass ]   2417
innodb_fts.fulltext 'xtradb'             [ pass ]   2264
innodb_fts.fulltext 'xtradb'             [ pass ]   3487
innodb_fts.fulltext 'xtradb'             [ pass ]   2864
innodb_fts.fulltext 'xtradb'             [ pass ]   1880
innodb_fts.fulltext 'xtradb'             [ pass ]   2806
innodb_fts.fulltext 'xtradb'             [ pass ]   3358
innodb_fts.fulltext 'xtradb'             [ pass ]   2536
innodb_fts.fulltext 'xtradb'             [ pass ]   2458
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 26.224 of 32 seconds executing testcases

innodb_fts.fulltext 'innodb_plugin'      [ pass ]    453
innodb_fts.fulltext 'innodb_plugin'      [ pass ]    459
innodb_fts.fulltext 'innodb_plugin'      [ pass ]    470
innodb_fts.fulltext 'innodb_plugin'      [ pass ]    453
innodb_fts.fulltext 'innodb_plugin'      [ pass ]    497
innodb_fts.fulltext 'innodb_plugin'      [ pass ]    444
innodb_fts.fulltext 'innodb_plugin'      [ pass ]    384
innodb_fts.fulltext 'innodb_plugin'      [ pass ]    259
innodb_fts.fulltext 'innodb_plugin'      [ pass ]    263
innodb_fts.fulltext 'innodb_plugin'      [ pass ]    440
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 4.122 of 10 seconds executing testcases

On a Debug build, innodb_fts_stopword_charset test, with --mem:

innodb_fts.innodb_fts_stopword_charset 'xtradb' [ pass ]  10652
innodb_fts.innodb_fts_stopword_charset 'xtradb' [ pass ]  11193
innodb_fts.innodb_fts_stopword_charset 'xtradb' [ pass ]  11409
innodb_fts.innodb_fts_stopword_charset 'xtradb' [ pass ]  11774
innodb_fts.innodb_fts_stopword_charset 'xtradb' [ pass ]  10810
innodb_fts.innodb_fts_stopword_charset 'xtradb' [ pass ]  10562
innodb_fts.innodb_fts_stopword_charset 'xtradb' [ pass ]  10616
innodb_fts.innodb_fts_stopword_charset 'xtradb' [ pass ]  10682
innodb_fts.innodb_fts_stopword_charset 'xtradb' [ pass ]  10424
innodb_fts.innodb_fts_stopword_charset 'xtradb' [ pass ]  10399
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 108.521 of 117 seconds executing testcases

innodb_fts.innodb_fts_stopword_charset 'innodb_plugin' [ pass ]   7189
innodb_fts.innodb_fts_stopword_charset 'innodb_plugin' [ pass ]   7306
innodb_fts.innodb_fts_stopword_charset 'innodb_plugin' [ pass ]   7225
innodb_fts.innodb_fts_stopword_charset 'innodb_plugin' [ pass ]   7403
innodb_fts.innodb_fts_stopword_charset 'innodb_plugin' [ pass ]   7195
innodb_fts.innodb_fts_stopword_charset 'innodb_plugin' [ pass ]   7221
innodb_fts.innodb_fts_stopword_charset 'innodb_plugin' [ pass ]   7318
innodb_fts.innodb_fts_stopword_charset 'innodb_plugin' [ pass ]   7684
innodb_fts.innodb_fts_stopword_charset 'innodb_plugin' [ pass ]   7311
innodb_fts.innodb_fts_stopword_charset 'innodb_plugin' [ pass ]   7055
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 72.907 of 81 seconds executing testcases

I find it an ominous sign, worth looking into.

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Jan Lindström added a comment - 2014-03-27 13:27

https://bugs.launchpad.net/percona-server/+bug/1298318

Oracle MySQL 5.6.16:

jan@jan-GE70-0NC-0ND ~/mysql-5.6.16/mysql-test $ ./mysql-test-run --repeat=10 --mem innodb_fts.fulltext
Logging: ./mysql-test-run  --repeat=10 --mem innodb_fts.fulltext
2014-03-27 12:40:10 6708 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2014-03-27 12:40:10 6708 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000)

2014-03-27 12:40:10 6708 [Note] Plugin 'FEDERATED' is disabled.
2014-03-27 12:40:10 6708 [Note] Binlog end
2014-03-27 12:40:10 6708 [Note] Shutting down plugin 'MyISAM'
2014-03-27 12:40:10 6708 [Note] Shutting down plugin 'CSV'
MySQL Version 5.6.16
Checking supported features...
 - SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory '/home/jan/mysql-5.6.16/mysql-test/var'...
 - symlinking 'var' to '/dev/shm/var_auto_rKVt'
Installing system database...
Using server port 51642

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
innodb_fts.fulltext                      [ pass ]    379
innodb_fts.fulltext                      [ pass ]    293
innodb_fts.fulltext                      [ pass ]    325
innodb_fts.fulltext                      [ pass ]    323
innodb_fts.fulltext                      [ pass ]    331
innodb_fts.fulltext                      [ pass ]    298
innodb_fts.fulltext                      [ pass ]    311
innodb_fts.fulltext                      [ pass ]    299
innodb_fts.fulltext                      [ pass ]    292
innodb_fts.fulltext                      [ pass ]    303
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 3.154 of 11 seconds executing testcases

Completed: All 10 tests were successful.

Percona Server 5.6:

jan@jan-GE70-0NC-0ND ~/Percona/5.6/mysql-test $ ./mysql-test-run --repeat=10 --mem innodb_fts.fulltext
Logging: ./mysql-test-run  --repeat=10 --mem innodb_fts.fulltext
2014-03-27 13:23:08 1241 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2014-03-27 13:23:08 1241 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000)

2014-03-27 13:23:08 1241 [Note] Plugin 'FEDERATED' is disabled.
2014-03-27 13:23:08 1241 [Note] Binlog end
2014-03-27 13:23:08 1241 [Note] Shutting down plugin 'MyISAM'
2014-03-27 13:23:08 1241 [Note] Shutting down plugin 'CSV'
MySQL Version 5.6.16
Checking supported features...
 - SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory '/home/jan/Percona/5.6/mysql-test/var'...
 - symlinking 'var' to '/dev/shm/var_auto_YvfC'
Installing system database...
Using server port 39227

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
innodb_fts.fulltext                      [ pass ]   1776
innodb_fts.fulltext                      [ pass ]   1165
innodb_fts.fulltext                      [ pass ]   1078
innodb_fts.fulltext                      [ pass ]   2337
innodb_fts.fulltext                      [ pass ]   1295
innodb_fts.fulltext                      [ pass ]   1149
innodb_fts.fulltext                      [ pass ]   1165
innodb_fts.fulltext                      [ pass ]   1207
innodb_fts.fulltext                      [ pass ]   1088
innodb_fts.fulltext                      [ pass ]   1226
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 13.486 of 20 seconds executing testcases

Completed: All 10 tests were successful.

Show

Jan Lindström added a comment - 2014-03-27 13:27 https://bugs.launchpad.net/percona-server/+bug/1298318 Oracle MySQL 5.6.16: jan@jan-GE70-0NC-0ND ~/mysql-5.6.16/mysql-test $ ./mysql-test-run --repeat=10 --mem innodb_fts.fulltext Logging: ./mysql-test-run --repeat=10 --mem innodb_fts.fulltext 2014-03-27 12:40:10 6708 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000) 2014-03-27 12:40:10 6708 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000) 2014-03-27 12:40:10 6708 [Note] Plugin 'FEDERATED' is disabled. 2014-03-27 12:40:10 6708 [Note] Binlog end 2014-03-27 12:40:10 6708 [Note] Shutting down plugin 'MyISAM' 2014-03-27 12:40:10 6708 [Note] Shutting down plugin 'CSV' MySQL Version 5.6.16 Checking supported features... - SSL connections supported Collecting tests... Removing old var directory... Creating var directory '/home/jan/mysql-5.6.16/mysql-test/var'... - symlinking 'var' to '/dev/shm/var_auto_rKVt' Installing system database... Using server port 51642 ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 innodb_fts.fulltext [ pass ] 379 innodb_fts.fulltext [ pass ] 293 innodb_fts.fulltext [ pass ] 325 innodb_fts.fulltext [ pass ] 323 innodb_fts.fulltext [ pass ] 331 innodb_fts.fulltext [ pass ] 298 innodb_fts.fulltext [ pass ] 311 innodb_fts.fulltext [ pass ] 299 innodb_fts.fulltext [ pass ] 292 innodb_fts.fulltext [ pass ] 303 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 3.154 of 11 seconds executing testcases Completed: All 10 tests were successful. Percona Server 5.6: jan@jan-GE70-0NC-0ND ~/Percona/5.6/mysql-test $ ./mysql-test-run --repeat=10 --mem innodb_fts.fulltext Logging: ./mysql-test-run --repeat=10 --mem innodb_fts.fulltext 2014-03-27 13:23:08 1241 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000) 2014-03-27 13:23:08 1241 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000) 2014-03-27 13:23:08 1241 [Note] Plugin 'FEDERATED' is disabled. 2014-03-27 13:23:08 1241 [Note] Binlog end 2014-03-27 13:23:08 1241 [Note] Shutting down plugin 'MyISAM' 2014-03-27 13:23:08 1241 [Note] Shutting down plugin 'CSV' MySQL Version 5.6.16 Checking supported features... - SSL connections supported Collecting tests... Removing old var directory... Creating var directory '/home/jan/Percona/5.6/mysql-test/var'... - symlinking 'var' to '/dev/shm/var_auto_YvfC' Installing system database... Using server port 39227 ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 innodb_fts.fulltext [ pass ] 1776 innodb_fts.fulltext [ pass ] 1165 innodb_fts.fulltext [ pass ] 1078 innodb_fts.fulltext [ pass ] 2337 innodb_fts.fulltext [ pass ] 1295 innodb_fts.fulltext [ pass ] 1149 innodb_fts.fulltext [ pass ] 1165 innodb_fts.fulltext [ pass ] 1207 innodb_fts.fulltext [ pass ] 1088 innodb_fts.fulltext [ pass ] 1226 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 13.486 of 20 seconds executing testcases Completed: All 10 tests were successful.

Hide

Permalink

Jan Lindström added a comment - 2014-04-24 11:33

FTS test are slower on XtraDB because it happened to contain newer InnoDB storage engine version merged from Oracle. Same problem will show up with MySQL 5.6 as noted on bug report. Decreasing the importance, because the affect is limited to FTS.

Show

Jan Lindström added a comment - 2014-04-24 11:33 FTS test are slower on XtraDB because it happened to contain newer InnoDB storage engine version merged from Oracle. Same problem will show up with MySQL 5.6 as noted on bug report. Decreasing the importance, because the affect is limited to FTS.

InnoDB FTS tests are much slower with XtraDB comparing to InnoDB plugin

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile