Adaptive checkpoint methodology not performing?

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

innodb_adaptive_checkpoin tries to avoid stalls, as you can read on http://www.percona.com/doc/percona-server/5.1/scalability/in nodb_io.html?id=percona-server:features:innodb_io_51&red irect=1

The measure Tps does not take stalls into account; 100 Tps in the first minute and 0 in the second minute gives an average of 50 Tps, which is the same as a db-server doing 50 Tps continuously. The latter is much more desirable.

Your buffer pool is only 8 GB, which gives shorter stalls than a 80 GB buffer pool (because it has fewer dirty pages to flush).

gmouse is correct, the adaptive checkpointing feature is designed to produce stable throughput. InnoDB’s flushing algorithm is too lazy, which makes the server start with a blaze of speed, then drop to nothing for several minutes at a time. It’s much better to have consistent throughput over time. See the second chart on [URL]http://www.percona.com/software/percona-server/benchmarks/[/URL] for example.