Open source databases on large machines: disk speed and innodb_io_capacity. Part 2
Today we bring to your attention the second part of the article by Sveta Smirnova and Anastasia Raspopina on improving InnoDB performance.
Pyotr Zaitsev , the founder of Percona at his master class on July 5, will also discuss this issue in great detail. Peter will talk about how to properly use MySQL 5.7 to ensure maximum performance, and also give specific recommendations regarding server configuration, database schema, application architecture, and hardware selection. Do not miss the opportunity to visit this unique master class , especially for PG Day Peter will prepare it for the first time in Russia in Russian!
')
In this article, I’ll tell you how I looked for a bottleneck that prevented performance improvement in my previous post .
The powerful machine I used in the tests in my previous article has a relatively slow disk, and therefore I expected my tests to reach a level where I couldn’t increase performance anymore due to the speed of the disk.
Hardware Configuration :
Processors : physical = 4, cores = 72, virtual = 144, hyperthreading = yes
Memory : 3.0 TB
Disk speed : about 3K IOPS
OS : CentOS 7.1.1503
File System : XFS
Tested versions and configuration : the same as in the first article in this series (read it to find out details).
Although I expected my tests to stop growing in performance due to disk speed, there were no high IO values in the iostat results. I conducted testing with a complete set of data that fit in memory. In this case, write performance only affected dropping data to the disk and writing to the log. But we still need to see a noticeable decrease in speed. So I decided to try the RW tests completely in memory. I created a ramdisk and installed MySQL datadir on it. Surprisingly, the results on the SSD and ramdisk did not differ.
I asked my colleagues from Postgres Professional to test PostgreSQL using ramdisk. They got similar results:
Interestingly, the value of innodb_io_capacity does not affect this situation. The data for the graph below was taken when I ran the ramdisk tests. I wanted to see if, using this variable, I could control the IO activity on the disk, which by default is very fast.
This completely contradicts all my past experiences with less productive machines. Percona reassigned the machine with a faster disk (which I used earlier in this article ), so I used a similar one with a lower disk speed.
Hardware Configuration :
Processors : physical = 2, cores = 12, virtual = 24, hyperthreading = yes
Memory : 47.2 GB
Disk speed : about 3K IOPS
OS : Ubuntu 14.04.5 LTS (trusty)
File system : ext4
Again , in this case, innodb_io_capacity tests with fewer processor cores showed more predictable results.
Conclusion :
Both MySQL and PostgreSQL on a machine with a large number of processor cores reach the limits of CPU resources before the speed of the disk can begin to affect performance. However, we tested only one scenario. In other cases, results may vary.
You can leave your questions to Svetlana in the comments, and also ask in person at her master class at PG Day'17 about debugging MySQL performance.
Pyotr Zaitsev , the founder of Percona at his master class on July 5, will also discuss this issue in great detail. Peter will talk about how to properly use MySQL 5.7 to ensure maximum performance, and also give specific recommendations regarding server configuration, database schema, application architecture, and hardware selection. Do not miss the opportunity to visit this unique master class , especially for PG Day Peter will prepare it for the first time in Russia in Russian!
')
In this article, I’ll tell you how I looked for a bottleneck that prevented performance improvement in my previous post .
The powerful machine I used in the tests in my previous article has a relatively slow disk, and therefore I expected my tests to reach a level where I couldn’t increase performance anymore due to the speed of the disk.
Hardware Configuration :
Processors : physical = 4, cores = 72, virtual = 144, hyperthreading = yes
Memory : 3.0 TB
Disk speed : about 3K IOPS
OS : CentOS 7.1.1503
File System : XFS
Tested versions and configuration : the same as in the first article in this series (read it to find out details).
Although I expected my tests to stop growing in performance due to disk speed, there were no high IO values in the iostat results. I conducted testing with a complete set of data that fit in memory. In this case, write performance only affected dropping data to the disk and writing to the log. But we still need to see a noticeable decrease in speed. So I decided to try the RW tests completely in memory. I created a ramdisk and installed MySQL datadir on it. Surprisingly, the results on the SSD and ramdisk did not differ.
I asked my colleagues from Postgres Professional to test PostgreSQL using ramdisk. They got similar results:
Interestingly, the value of innodb_io_capacity does not affect this situation. The data for the graph below was taken when I ran the ramdisk tests. I wanted to see if, using this variable, I could control the IO activity on the disk, which by default is very fast.
This completely contradicts all my past experiences with less productive machines. Percona reassigned the machine with a faster disk (which I used earlier in this article ), so I used a similar one with a lower disk speed.
Hardware Configuration :
Processors : physical = 2, cores = 12, virtual = 24, hyperthreading = yes
Memory : 47.2 GB
Disk speed : about 3K IOPS
OS : Ubuntu 14.04.5 LTS (trusty)
File system : ext4
Again , in this case, innodb_io_capacity tests with fewer processor cores showed more predictable results.
Conclusion :
Both MySQL and PostgreSQL on a machine with a large number of processor cores reach the limits of CPU resources before the speed of the disk can begin to affect performance. However, we tested only one scenario. In other cases, results may vary.
You can leave your questions to Svetlana in the comments, and also ask in person at her master class at PG Day'17 about debugging MySQL performance.
Source: https://habr.com/ru/post/327226/
All Articles