Bug #48786 innodb_plugin: performance drop in dbt2 test

• Submitted: 16 Nov 2009 2:04
• Modified: 16 Nov 2009 19:28
• Reporter: Alexey Stroganov
• Status: Not a Bug
• Category: MySQL Server: InnoDB Plugin storage engine
• Severity: S3 (Non-critical)
• Version: 5.1.41/iplugin-1.0.5
• OS: Any
• Assigned to: Inaam Rana
• CPU Architecture: Any

[16 Nov 2009 2:04] Alexey Stroganov

Description:
Results obtained with dbt2 tests for MySQL 5.1.41 show slight drop in performance(~3-4%):

-----------------------------------------------
threads|     5.1.40   | 5.1.41pre |  5.1.41   |
----------------------------------------------|
16     |     49371.53 | 49338.75  |  47476.96 |
40     |     44903.30 | 45251.94  |  43825.85 |
80     |     42311.53 | 42898.58  |  41112.22 |
-----------------------------------------------

After analysis of changes between 5.1.41pre and 5.1.41 we found cause 
of this drop:

revid: 3148.13.17 
...
branches/zip rb://182

Call fsync() on datafiles after a batch of pages is written to disk
even when skip_innodb_doublewrite is set.
...

Below are results that were obtained for reruns of dbt2 test with 16 threads:

5.1.41pre             49338.75
5.1.41pre+patch       47517.95

5.1.41release          47476.96
5.1.41relese-patch     48982.38

How to repeat:
Run dbt2 test against 5.1.40 and 5.1.41.

[16 Nov 2009 14:43] Mikhail Izioumtchenko

what is the innodb_doublewrite value you run with?

[16 Nov 2009 17:33] Calvin Sun

Config from Ranger:

[mysqld]
ignore-builtin-innodb
plugin_load=innodb=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so
user=root
max_connections=200
table_cache=2048
transaction_isolation=REPEATABLE-READ
skip-locking
innodb_status_file=0
innodb_data_file_path=ibdata1:100M:autoextend
innodb_buffer_pool_size=2G
innodb_additional_mem_pool_size=20M
innodb_log_file_size=650M
innodb_log_files_in_group=2
innodb_log_buffer_size=16M
innodb_support_xa=0
innodb_doublewrite=0
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

box:
 - HW: Intel x86_64 (Dual Quad-Core Xeon 5345),RAM 16GB, raid10
 - OS: SuSE 10

dbt2:
 - 10 warehouses, 900 sec

   run_mysql.sh  -c <16,40,80> -n dbt2w10 -w 10 -t 900 -o /tmp/mysql.sock -u root -s 100 -e -v