InnoDB Thread Scalability

Here are performance numbers for InnoDB on MyS!L 5.1. The X axis is the number of threads. The Y axis is the time in seconds the the test took.

This is with the sysbench OLTP benchmark.

The data fits 100% in memory.

Oddly enough MySQL became 100% CPU bound which is far from idea. InnoDB is faster but it should be a LOT faster than MyISAM

200802112227

Here’s the InnoDB config:

| have_innodb                     | YES                    | 
| innodb_additional_mem_pool_size | 5242880                | 
| innodb_autoextend_increment     | 8                      | 
| innodb_autoinc_lock_mode        | 1                      | 
| innodb_buffer_pool_size         | 1048576000             | 
| innodb_checksums                | OFF                    | 
| innodb_commit_concurrency       | 8                      | 
| innodb_concurrency_tickets      | 500                    | 
| innodb_data_file_path           | ibdata1:10M:autoextend | 
| innodb_data_home_dir            |                        | 
| innodb_doublewrite              | OFF                    | 
| innodb_fast_shutdown            | 1                      | 
| innodb_file_io_threads          | 4                      | 
| innodb_file_per_table           | ON                     | 
| innodb_flush_log_at_trx_commit  | 0                      | 
| innodb_flush_method             | O_DIRECT               | 
| innodb_force_recovery           | 0                      | 
| innodb_lock_wait_timeout        | 50                     | 
| innodb_locks_unsafe_for_binlog  | OFF                    | 
| innodb_log_buffer_size          | 8388608                | 
| innodb_log_file_size            | 1048576000             | 
| innodb_log_files_in_group       | 2                      | 
| innodb_log_group_home_dir       | ./                     | 
| innodb_max_dirty_pages_pct      | 90                     | 
| innodb_max_purge_lag            | 0                      | 
| innodb_mirrored_log_groups      | 1                      | 
| innodb_open_files               | 300                    | 
| innodb_rollback_on_timeout      | OFF                    | 
| innodb_support_xa               | OFF                    | 
| innodb_sync_spin_loops          | 20                     | 
| innodb_table_locks              | ON                     | 
| innodb_thread_concurrency       | 36                     | 
| innodb_thread_sleep_delay       | 10000                  | 

  1. tgabi

    It’s not surprizing myisam uses more CPU when data fits in memory. Since myisam doesn’t have a row buffer then the OS has to buffer the data. Myisam takes data from the OSs cache one sector at the time (512 bytes or 1K, I’m not sure). This generates ALOT of system calls that uses system CPU in large quantities. You can confirm this on your benchmark – mysql shouldn’t use much system CPU. I had raised this problem with mysql some time ago but all I’ve got was a shrug.

  2. Oh…. it’s not MyISAM that I’m talking about here. It’s InnoDB.

    InnoDB becomes 100% CPU bound.

    I’m not sure why. I’m going to test it with 4.1.24 as well to see if it’s a versioning issue.

    Kevin

  3. analyticarts

    could it be spin waits on the buffer pool? what does “show engine innodb status show in the semaphore section? The docs are not really clear to me on this, so its just a thought.

  4. Analyticarts,

    Agreed, that was my thought as well.

    I’m going to play with it a bit more and also compare it to 4.1.22….

    Kevin

  5. tgabi

    Well, all of them should go 100% CPU if the data fits in memory. If not then is something wrong with the benchmark or the server is network bound.
    What was the ratio of SELECTs versus UPDATE/INSERTs in the test above ? Can you repeat the test with SELECTs only ?

  6. tgabi,

    that’s a good point. However, I wouldn’t have suspected that it would go to 100% at only 3x additional performance.

    I still suspect that there’s a bottleneck here somewhere with wasted CPU.

    Kevin






%d bloggers like this: