Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

You need to tune your InnoDB settings in the following areas:

Here are my past posts on tuning the InnoDB storage engine

share|improve this answer
    
Thx for this incredibly useful reply!! I salute you. Regarding log size, do I have to worry about making it too big? my concern is something that Tkachenko wrote about mysqlperformanceblog.com/2011/09/18/disaster-mysql-5-5-flushing. I realize I am on Percona, so maybe this isn't a concern.. but I want to be sure I dont run into a stall scenario. I am digging into the rest of your reply... –  Don Wool Jul 4 '12 at 21:30
    
regards innodb_buffer_pool_instances I have a 16-cpu box (I had thought it was 10). regards numactl my admin says "You have 16 total CPUs, and four blocks of RAM, 32G each. Each block of RAM is treated as local memory by four CPUs." –  Don Wool Jul 4 '12 at 21:42
    
Please run numactl --hardware and post the output in the question. I am trying to figure out physical CPUs and I want to make sure the admin isn't saying CPUs when he means cores. –  RolandoMySQLDBA Jul 4 '12 at 22:23
    
Ok I posted the output of 'numactl' in the question. –  Don Wool Jul 5 '12 at 1:12
    
To me, output looks like quad-quad core (16 cores) using 4 CPUs. Therefore, set innodb_buffer_pool_instances=4. One more request: Please double check, does the DB server have 64GB or 128GB ??? –  RolandoMySQLDBA Jul 5 '12 at 2:10
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.