Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Open-Source Databases on Big Machines: Disk Speed and innodb_io_capacity

DZone's Guide to

Open-Source Databases on Big Machines: Disk Speed and innodb_io_capacity

When looking at performance results for open-source DBs on powerful machines, is there anything that can prevent those databases from reaching peak performance?

· Performance Zone
Free Resource

Transform incident management with machine learning and analytics to help you maintain optimal performance and availability while keeping pace with the growing demands of digital business with this eBook, brought to you in partnership with BMC.

In this post, I’ll look for the bottleneck that prevented the performance in my previous post from achieving better results.

The powerful machine I used in the tests in my previous post has a comparatively slow disk, and therefore, I expected my tests would hit a point when I couldn’t increase performance further due to the disk speed.

Here's the hardware configuration:

  • Processors: physical = 4, cores = 72, virtual = 144, hyperthreading = yes.

  • Memory: 3.0T.

  • Disk speed: about 3K IOPS.

  • OS: CentOS 7.1.150.

  • File system: XFS.

  • Versions tested and configuration: Same as in the first post of this series (check the post for specifics).

Even though I expected my tests would stop increasing in performance due to the disk speed, I did not observe high IO rates in the iostat output. I already tested with a full dataset that fits in memory. In this case, write performance only affected data flushes and log writes — but we should still see a visible decrease in speed. So, I decided to try RW tests totally in memory. I created a ramdisk and put the MySQL datadir on it. Surprisingly, results on the SSD and ramdisk did not differ.

I asked my colleagues from Postgres Professional to test PostgreSQL with the ramdisk. They got similar results:

It’s interesting that the value of innodb_io_capacity does not have any effect on this situation. Data for the graph below was taken when I ran tests on ramdisk. I wanted to see if I could control the IO activity of a disk, which is extremely fast by default, using this variable.

This totally contradicts all my past experiences with smaller machines. Percona re-purposed the machine with a faster disk (which I used before, described in this post), so I used a similar one with slower disk speed.

Here's the hardware configuration:

  • Processors: physical = 2, cores = 12, virtual = 24, hyperthreading = yes.

  • Memory: 47.2G.

  • Disk speed: about 3K IOPS.

  • OS: Ubuntu 14.04.5 LTS (trusty).

  • File system: ext4.

Again, in this case, innodb_io_capacity benchmarks with a smaller number of CPU cores showed more predictable results.

Conclusion

Both MySQL and PostgreSQL on a machine with a large number of CPU cores hit CPU resources limits before disk speed can start affecting performance. We only tested one scenario, however. With other scenarios, the results might be different.

Evolve your approach to Application Performance Monitoring by adopting five best practices that are outlined and explored in this e-book, brought to you in partnership with BMC.

Topics:
disk speed ,performance ,open source ,databases

Published at DZone with permission of Sveta Smirnova, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}