One of our customers gave me a chance to run some benchmarks on 24-core (intel cpu based) server, and I could not miss it and ran few CPU-bound tasks there.
The goal of benchmarks was investigation of InnoDB-plugin and XtraDB scalability in CPU-bound load.
CPU specification:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | processor : 23 vendor_id : GenuineIntel cpu family : 6 model : 29 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz stepping : 1 cpu MHz : 2394.011 cache size : 12288 KB physical id : 3 siblings : 6 core id : 5 cpu cores : 6 fpu : yes fpu_exception : yes cpuid level : 11 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm bogomips : 4788.59 clflush size : 64 cache_alignment : 64 address sizes : 40 bits physical, 48 bits virtual power management: |
I tested MySQL-5.1.30 with InnoDB plugin, Xtradb-1.0.2-1, and XtraDB-1.0.2-2 (rel2). XtraDB-rel2 has not been released yet, we still are doing internal QA for, but it will be ready soon. Main difference XtraDB-rel2 it contains additional scalability fixes for buffer_pool (split_buffer_pool_mutex patch).
my.cnf is
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | [mysqld] user=root binlog_cache_size=1M default_table_type=MYISAM ft_min_word_len=4 innodb_additional_mem_pool_size=16M innodb_buffer_pool_size=15G innodb_data_file_path=ibdata1:10M:autoextend innodb_file_per_table=1 innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=8M innodb_log_files_in_group=2 innodb_log_file_size=512M innodb_status_file=0 innodb_thread_concurrency=0 innodb_io_capacity=1000 innodb_write_io_threads = 16 innodb_read_io_threads = 16 join_buffer_size=1M max_allowed_packet=1M max_connections=3000 max_heap_table_size=64M max_prepared_stmt_count=1000000 query_cache_size=0 skip_grant_tables skip_locking sort_buffer_size=64K table_cache=2048 thread_cache_size=16 thread_concurrency=16 thread_stack=196K tmp_table_size=64M transaction_isolation=REPEATABLE-READ local-infile=1 |
At first I tried sysbench oltp read-only with 10mil rows (the datasize is about 2.5GB), uniform distribution.
The results you can see there: (Results are removed for additional checking)
In the next run I tested sysbench oltp read-write load, and the results are:
Here starting 16 threads the result is dropping down with the same speed as it grew, and with 128 connections we have the same TPS as with 1 connection (and it is on 24-cores box!). XtraDB is slightly better there than InnoDB, but nothing special to be proud of. We definitely we will look how to fix it as next step, read detailed investigation what is the reason of performance drop in next post.
And last one workload I tried is TPCC-like benchmark (you can get it on https://launchpad.net/perconatools), with 100 Warehouses (about 9.5GB datasize).
The result is:
Here the result grows up to 16 connections, but after that InnoDB-plugin is dropping down. XtraDB and XtraDB-rel2 seem quite better, I guess this is mostly because fixes to rw_locks and to buffer_pool mutex (in rel2).
Conclusion: As read-only workload seems fine, read-write cases is something to worry about in 16+ cores boxes.
Intel Based 24 Core Servers are not mainstream these days but as number of cores is increasing now at the same pace as CPU frequency before we believe they are very soon to come. Also in real production there may be not a lot database fits “in-memory” cases – but on other hand 64-128GB RAM per box is not something rare already and recommendation to fit at least active dataset in memory is one we use for our customers.
Hi Vadim,
did you try to replay the same tests with innodb_thread_concurrency=16 ?
Rgds,
-Dim
Dim,
Nope I did not.
I know it may fix scalability a bit, but innodb_thread_concurrency=16 on 24-cores box does not make sense.
Interesting point though. This is how things degrade with unrestricted concurrency… Often you can get better performance restricting Innodb Thread Concurrency… but our goal is rather fixing internal scaling limits so it is not required.
Hi Vadim, did you like to do this test on a 64 Core System with a lot of RAM again 😉 ?
Torsten,
That would be interesting !
@Vadim Tkachenko,
pls mail me directly your needs and i will sent you details about login 😉
Would be interessting to see your results, we only use this box (we have two) for 10% max….never saw a higher load on the box….