Capabilities of InnoDB INSERT Performance

Question

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

score 11 · Accepted Answer · edited Mar 21 '22 at 10:00

11

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

Make InnoDB access all your cores
Increase innodb_buffer_pool_size to 12G
Increase innodb_buffer_pool_instances to 2 (First run numactl --hardware to 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)
Increase Log File Size (innodb_log_file_size) to 2047M
support separate tablespace files for individual InnoDB tables (enaled innodb_file_per_table)
support either high performance or high durability (ACID Compliance)
High Performance : innodb_flush_log_at_trx_commit set to 0 or 2
High Durability : innodb_flush_log_at_trx_commit set to 1 (Default)
Increase Size up the innodb_log_buffer_size in conjunction with the number of transactions per second (perhaps 32M)
Your current setting for innodb_flush_log_at_trx_commit is good
Your current setting for innodb_flush_method is good
Increase innodb_read_io_threads to 64
Increase innodb_write_io_threads to 64
Increase innodb_io_capacity to 10000

Here are links to my past posts on tuning the InnoDB storage engine:

Is the CPU performance relevant for a database server? (Apr 26, 2012)
Why does InnoDB store all databases in one file? (Mar 25, 2012)
Multi cores and MySQL Performance (Sep 20, 2011)
Possible to make MySQL use more than one core? (Sep 12, 2011)
About single threaded versus multithreaded databases performance (May 26, 2011)
How to safely change MySQL innodb variable 'innodb_log_file_size'? (Feb 16, 2011)
How do you tune MySQL for a heavy InnoDB workload? (Feb 12, 2011)
Howto: Clean a mysql InnoDB storage engine? (October 29, 2010)

edited Mar 21 '22 at 10:00

Spherical Cowboy

103
4

answered Jul 04 '12 at 19:44

RolandoMySQLDBA

182,700
33
317
520

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 http://www.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 04 '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 04 '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 04 '12 at 22:23
Ok I posted the output of 'numactl' in the question. – Don Wool Jul 05 '12 at 01: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 05 '12 at 02:10
Here is the response that I got back from my admin : – Don Wool Jul 05 '12 at 03:41
It's actually 2 CPU modules, with eight total cores each. The scheduler treats them as 16 individual CPUs. In this generation of AMD processor, each module has two dies with four cores on each die. They go to some trouble to make each core as close to a full-fledged CPU with its own bus and cache, there is essentially no difference between 4x4, 2x8, or 16x1, except for cooling. On this sort of system, I have always tuned to the number of cores, not modules or dies. – Don Wool Jul 05 '12 at 03:42
NUMA output and physical CPU modules/dies have been detached from each other for a couple of generations of processor. His interpretation would be right in the first gen of multicore or anything with a hyperthreaded bus, but not so much with this stuff.
Yes, it has 128GB.
– Don Wool Jul 05 '12 at 03:42
Set innodb_buffer_pool_instances to 4 and set innodb_buffer_pool_size to 64G – RolandoMySQLDBA Jul 05 '12 at 11:25
So you would set the pool_instances to each NUMA node, in this case, not each 'physical CPU'. ok. also read the Jeremy Cole blog piece referenced - which was fascinating but didn't see direct tie in to innodb_buffer_pool_instances, rather it seems piece addressed swappiness issue (brilliantly solved). Was it something someone said in the threads? Thx for all the help! --Don – Don Wool Jul 05 '12 at 13:42

Capabilities of InnoDB INSERT Performance

1 Answers1

Linked