This is to follow up on my previous post and show the results for MySQL 5.5.8 and Percona Server on the fastest hardware I have in our lab:Â a Cisco UCS C250 server with 384GB of RAM, powered by a Virident tachIOn 400GB SLC card.
To see different I/O patterns, I used different innodb_buffer_pool_size settings: 13G, 52G, an 144G on a tpcc-mysql workload with 1000W (around 100GB of data). This combination of buffer pool sizes gives us different data/memory ratios (for 13G – an I/O intensive workload, for 52G – half of the data fits into the buffer pool, for 144G – the data all fitsÂ into memory). For the cases when the data fits into memory, it is especially important to have big transactional log files, as in these cases the main I/O pressure comes from checkpoint activity, and the smaller the log size, the more I/O per second InnoDB needs to perform.
So let me point out the optimizations I used for Percona Server:
For MySQL 5.5.8, I used:
For both servers I used:
The raw results, config, and script are in our Benchmarks Wiki.
HereÂ are the graphs:
In this case, both servers showÂ a straightÂ line, and it seems having 8 innodb_buffer_pool_instances was helpful.
The finalÂ graph shows theÂ difference between different settings ofÂ innodb_io_capacity for MySQL 5.5.8.
Small innodb_io_capacity valuesÂ are really bad, while 20000 allows us to get a more stable line.
In summary, if weÂ take theÂ average NOTPM for the finalÂ 30 minutes of the runs (to avoid the warmup stage), we get the followingÂ results:
This is actually the first case where I’ve seen NOTPM greater than 100,000 for a tpcc-mysql workload with 1000W.
The main factors that allow us to get a 1.4x improvement in Percona Server are:
We recognize that hardware like the Cisco UCS C250 and the Virident tachIOn card may not be for the mass market yet, but
it is a good choice for if you are looking for highÂ MySQL performance, and we tune Percona Server to get the most from such hardware. Actually, from my benchmarks, I see that the Virident card is not fully loaded, and we may benefit from running two separate instances of MySQL on a single card. This is a topic for another round.
(Edited by: Fred Linhoss)
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.