We test PostgreSQL on SSD RAID-0 array with a table of 10 billion records. (Part 3, final)

It's time to conduct the final tests and summarize the series of articles. Today we will look at the effect of the size of shared_buffers on database performance. The first parts can be read here and here .

In the course of the development of the cost optimization service for cellular communication Dr. Tariff ( iOS , Android ) for sharing a pilot with one of the partners, we needed a large and productive relational database.
')

Dependence of database performance on the shared_buffers parameter

The PostgreSQL shared_buffers parameter sets the maximum amount of RAM for caching at the database level. The number of entries is 10 billion, the number of active connections is 64, the read load. In this test, after changing the configuration file, the database server was restarted, so before measuring the data, a test run took place for several minutes in order for the cache to fill up.



Reading performance is almost independent of the size of shared_buffers. The size of the database is 40 times higher than the amount of RAM in the computer. With this ratio and the use of the entire amount of data, almost every reading requires accessing the disk.
In real situations, this parameter should not be neglected, since Postgres will cache the most frequently used smaller data and tables.

The dependence of database performance on the fsync parameter

PostgreSQL has an fsync parameter that is responsible for transferring data from RAM to disk upon completion of a transaction. By default, it is enabled, and ensures data integrity in case of failure. A failure can be caused by a power outage, a system hang, a failure in the disk subsystem ... If a failure occurs and fsync = on, then the next time you start the database, the data will be restored. If the failure occurred, but fsync = off, then most likely the data will have to be restored from the last dump. Disabling this parameter can significantly increase the speed of the update and insert operations.



Performance after disabling fsync increased by 2-3 times. This is a very good indicator.

Comparison of speed SSD and HDD drives

For comparison, the test configuration was deployed on the HDD disk. The disk fill rate was several times lower compared to SSD, so it was decided to limit the testing to 1 billion records.



As you can see, the speed of the database on the HDD has slowed down a hundred times in comparison with a single SSD with this type of load. Such a load is demanding on iops disks. Iops for SSD and HDD just differ by two orders of magnitude. For databases, a raid array of fast HDDs is traditionally used. Fast HDDs have iops 1.5–2 times higher than HDDs that participated in testing. A few more times you can increase the speed due to the raid array.

Summary

Despite all the advantages, SSD has disadvantages:

• higher price
• limited write resource

The high price is justified by high performance. Modern SSD drives have sufficient write resources, even for such applications as a database. There is a good article on testing resource SSD drives.

From this test and previous work experience we can draw the following conclusions:

1. PostgreSQL works well with large tables with the number of rows up to 10 billion and with the number of clients up to 1 thousand
2. With database sizes much larger than the amount of RAM and simple queries, performance is limited by the disk subsystem, and shared_buffers has practically no effect on performance. The fsync parameter allows you to significantly increase the speed of writing to the database.
3. SSD drives have much better performance in relation to databases than HDD drives and occupy their niche.
4. SSDs do not scale well in a RAID-0 array for random read. If the task allows, then it is preferable to use several databases on separate disks than one large database on a RAID array.

And the most favorable tariff will be prompted by the application Dr. Tariff on Android and iOS .


Subscribe to our news and share information with friends on Vkontakte and Facebook .