Been testing various combinations of innodb_adaptive_checkpoint in xtradb 5.1.58 compared to the InnoDB plugin using the sysbench oltp.lua test. This is on a 2xquad core clovertown 1.8Ghz Dell 2950 machine with 6x146GB SAS RAID 5.
What I am seeing is the InnoDB plugin is providing better performance overall going up to 512 threads, where XtraDB has the same results consistently for the threads. WHile consistency is a good thing, I was wondering if anyone else noticed the same type of results?
Here are my results for sysbench oltp.lua
Threads: 1 2 4 8 16 32 48 64 80 96 128 256 512
InnoDB plugin (Tps) 102.66 174.76 321.98 594.22 847.33 998.73 1025.25 1038.25 1037.74 1034.41 1030.28 1003.27 921.33
XtraDB plugin (Tps)
95.16 177.17 316.73 565.08 753.70 766.86 763.04 766.45 764.12 760.98 762.42 756.40 746.12
InnoDB settings:
mysql> show global variables like ‘innodb%’;
±---------------------------------------±----------------- --------+
| Variable_name | Value |
±---------------------------------------±----------------- --------+
| innodb_adaptive_checkpoint | reflex |
| innodb_adaptive_flushing | OFF |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_auto_lru_dump | 0 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_shm_checksum | ON |
| innodb_buffer_pool_shm_key | 0 |
| innodb_buffer_pool_size | 8589934592 |
| innodb_change_buffering | inserts |
| innodb_checkpoint_age_target | 0 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:256M:autoextend |
| innodb_data_home_dir | /home/y/var/mysql/ibdata |
| innodb_dict_size_limit | 0 |
| innodb_doublewrite | ON |
| innodb_doublewrite_file | |
| innodb_enable_unsafe_group_commit | 0 |
| innodb_expand_import | 0 |
| innodb_extra_rsegments | 0 |
| innodb_extra_undoslots | OFF |
| innodb_fast_checksum | OFF |
| innodb_fast_recovery | OFF |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | Barracuda |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_log_at_trx_commit_session | 3 |
| innodb_flush_method | ALL_O_DIRECT |
| innodb_flush_neighbor_pages | 1 |
| innodb_force_recovery | 0 |
| innodb_ibuf_accel_rate | 100 |
| innodb_ibuf_active_contract | 1 |
| innodb_ibuf_max_size | 4294950912 |
| innodb_io_capacity | 500 |
| innodb_lazy_drop_table | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 4294967296 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | /home/y/var/mysql/iblog |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 0 |
| innodb_open_files | 300 |
| innodb_overwrite_relay_log_info | OFF |
| innodb_page_size | 16384 |
| innodb_pass_corrupt_table | 0 |
| innodb_read_ahead | linear |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_recovery_stats | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_show_locks_held | 10 |
| innodb_show_verbose_locks | 0 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_update | 1 |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_stats_sample_pages | 8 |
| innodb_stats_update_need_lock | 1 |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_concurrency_timer_based | OFF |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_purge_thread | 1 |
| innodb_use_sys_malloc | ON |
| innodb_use_sys_stats_table | OFF |
| innodb_version | 1.0.17-12.5 |
| innodb_write_io_threads | 4 |
±---------------------------------------±----------------- --------+
The only thing different between XTradb settings & Innodb settings in innodb_adaptive_checkpoint=on and innodb_log_file_size=1G
If anyone could point out the obvious, it would be appreciated, or if more info is needed I’d be happy to provide it.
Thanks, Partha