[MDEV-5808] InnoDB FTS tests are much slower with XtraDB comparing to InnoDB plugin Created: 2014-03-09  Updated: 2022-10-08  Resolved: 2022-10-08

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.0.9
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Jan Lindström (Inactive)
Resolution: Won't Fix Votes: 0
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.

 Comments   
Comment by Jan Lindström (Inactive) [ 2014-03-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.

Comment by Jan Lindström (Inactive) [ 2014-04-24 ]

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.

Generated at Thu Feb 08 07:07:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.