Hi I am running the most recent version of Percona Server.
Server version: 5.5.24-55 Percona Server (GPL), Release 26.0
I have a 10 cpu box of these characteristics.
processor : 0
vendor_id : AuthenticAMD
cpu family : 16
model : 9
model name : AMD Opteron(tm) Processor 6128
stepping : 1
microcode : 0x10000d9
cpu MHz : 800.000
cache size : 512 KB
It has SSD and 64GB of RAM. Innodb is approx 10GB, so innodb_buffer_pool_size set to 10GB.
I have a table that is as follows :
create table TODAY
( symbol_id integer not null
, openp decimal(10,4)
, high decimal(10,4)
, low decimal(10,4)
, last decimal(10,4) not null
, volume int
, last_updated datetime -- the time of the last quote update
, prev decimal(10,4) null
, PRIMARY KEY ( symbol_id )
)
If I start with an empty table and do an insert of 23,000 rows it takes around 10 seconds. If I subsequently do an update where every column of every row is updated (except symbol_id of course) it takes a bit more like 11-12 seconds.
Is this generically the write performance I should expect from Innodb? Is there any suggestion for improving this performance? updating 23,000 rows is an extreme case, as typically during a trading day I need to update approx 1000 rows every 5 seconds (so, that is the more realistic constraint I am dealing with).
Other relevant mysql.cnf settings I have changed :
innodb_buffer_pool_size = 10G
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
BTW if instead of Innodb I create the table with ENGINE=MEMORY it takes around 4 seconds to do the insert, 6 seconds to do the update.
Many TIA if someone can help me figure out what the benchmark for this type of query is, or help me improve the time.
Don
PS full Innodb settings.
mysql> show global variables like 'innodb%'; +-------------------------------------------+------------------------+ | Variable_name | Value | +-------------------------------------------+------------------------+ | innodb_adaptive_flushing | ON | | innodb_adaptive_flushing_method | estimate | | innodb_adaptive_hash_index | ON | | innodb_adaptive_hash_index_partitions | 1 | | innodb_additional_mem_pool_size | 8388608 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_blocking_buffer_pool_restore | OFF | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_restore_at_startup | 0 | | innodb_buffer_pool_shm_checksum | ON | | innodb_buffer_pool_shm_key | 0 | | innodb_buffer_pool_size | 10737418240 | | innodb_change_buffering | all | | innodb_checkpoint_age_target | 0 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_corrupt_table_action | assert | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_dict_size_limit | 0 | | innodb_doublewrite | ON | | innodb_doublewrite_file | | | innodb_fake_changes | OFF | | innodb_fast_checksum | OFF | | innodb_fast_shutdown | 1 | | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 2 | | innodb_flush_method | O_DIRECT | | innodb_flush_neighbor_pages | area | | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | | innodb_ibuf_accel_rate | 100 | | innodb_ibuf_active_contract | 1 | | innodb_ibuf_max_size | 5368692736 | | innodb_import_table_from_xtrabackup | 0 | | innodb_io_capacity | 200 | | innodb_kill_idle_transaction | 0 | | innodb_large_prefix | OFF | | 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 | 67108864 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 75 | | 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_page_size | 16384 | | innodb_purge_batch_size | 20 | | innodb_purge_threads | 1 | | innodb_random_read_ahead | OFF | | innodb_read_ahead | linear | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_recovery_stats | OFF | | innodb_recovery_update_relay_log | OFF | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | 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 | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_concurrency_timer_based | OFF | | innodb_thread_sleep_delay | 10000 | | innodb_use_global_flush_log_at_trx_commit | ON | | innodb_use_native_aio | ON | | innodb_use_sys_malloc | ON | | innodb_use_sys_stats_table | OFF | | innodb_version | 1.1.8-rel26.0 | | innodb_write_io_threads | 4 | +-------------------------------------------+------------------------+ 90 rows in set (0.00 sec)
I ran numactl –hardware and here is the output that I got. The comments from my admin are noted below (as toward interpretation).
root@prog:/data/mysql# numactl --hardware available: 4 nodes (0-3) node 0 cpus: 0 1 2 3 node 0 size: 32766 MB node 0 free: 21480 MB node 1 cpus: 4 5 6 7 node 1 size: 32768 MB node 1 free: 25285 MB node 2 cpus: 12 13 14 15 node 2 size: 32768 MB node 2 free: 20376 MB node 3 cpus: 8 9 10 11 node 3 size: 32768 MB node 3 free: 24898 MB node distances: node 0 1 2 3 0: 10 16 16 16 1: 16 10 16 16 2: 16 16 10 16 3: 16 16 16 10
Best Answer
You need to tune your InnoDB settings in the following areas:
numactl --hardwareto determine the number of Physical CPUs. What every number of CPUs it reports, use that number. I learned this recently in Jeremy Cole's Blog)Here are my past posts on tuning the InnoDB storage engine