What is the best filesystem for insert performance on PostgreSQL?

Question

I'm curious if anyone out there has done any experimentation or comparisons between file systems and database performance. On Linux, I'm wondering what is the optimal file system for a postgres database. Also, what settings (inode, etc) are ideal for it? Is this something that may drastically differ based on the data in the database?

If you are looking for a question relating to general filesystem / database performance, this post has some good information.

However, I would like to get as much advice on insert performance opposed to read performance as possible. Thanks for all of the great answers!

Solution 1

Buy a copy of "postgresql high performance" by Greg Smith. It is a great Book and two or more chapters are about Disk Hardware and filesystems. You will learn a lot.

In short: there is no short answer.

But i will try to summerize:

• don't use ext2 until you know what you are doing.
• with ext3 beware of checkpoint spikes because of fsync calls, see page 113 and 82 and 79
• use ext4 or xfs
• there are other options

But as you are really asking yourself what FS to use, you should read the book!

Solution 2

First of all, you want a reliable filesystem first, and a fast one second. Which rules out some options...

Performance testing shows that often XFS gives the best performance. There are some stability issues with it once you reach disk-very-close-to-full scenarios, but as long as you monitor for that not happening, it'll give you slightly better performance.

In theory you don't need a journaling filesystem for the pg_xlog directory, but the difference in speed is usually so small it's just not worth it. For the data directory, you really should always have a metadata journaling filesystem.

Solution 3

I did such a detailed report but it is only in French. If you read french or are happy with automatic translation tools... You can reuse the methodology and run it for yourself.

Executive summary: I used pgbench. The Linux I/O scheduler has very little importance for performances and the filesystem only a little. So, if you are in a hurry, just choose the default. I choosed JFS.

Solution 4

Database management systems implement their own journalling through the database logs, so installing such a DBMS on a journalled file system degrades performance through two mechanisms:

1. Redundant journalling increases the amount of disk activity

2. Physical disk layout can be fragmented (although some journalling file systems do have mechanisms to clean this up).

3. Lots of disk activity can fill up the journal, causing spurious 'disk full' conditions.

I have seen an instance some years ago where this was done on LFS file system on a Baan installation on a HP/UX box. The system had persistent performance and data corruption issues that went undiagnosed until someone worked out that the file systems were formatted with LFS.

Volumes holding database files will normally have a small number of large files. DBMS servers will normally have a setting that configures how many blocks are read in a single I/O. Smaller numbers would be appropriate for high volume transaction processing systems as they would minimise caching of redundant data. Larger numbers would be appropriate for systems such as data warehouses that did a lot of sequetial reads. If possible, tune your file system allocation block size to be the same size as the multi-block read that the DBMS is set to.

Some database management systems can work off raw disk partitions. This gives varying degrees of performance gain, typically less so on a modern system with lots of memory. On older systems with less space to cache file system metadata the savings on disk I/O were quite significant. Raw partitions make the system harder to manage, but provide the best performance available.

RAID-5 volumes incur more write overhead than RAID-10 volumes, so a busy database with lots of write traffic will perform better (often much better) on a RAID-10. Logs should be put physically separate disk volumes to the data. If your database is large and mostly read only (e.g. a data warehouse) there may be a case to put it on RAID-5 volumes if this does not unduly slow down the load process.

Write-back caching on a controller can give you a performance win at the expense of creating some (reasonably unlikely but possible) failure modes where data could be corrupted. The biggest performance win for this is on highly random access loads. If you want to do this, consider putting the logs on a separate controller and disabling write-back caching on the log volumes. The logs will then have better data integrity and a single failure cannot take out both the log and data volumes. This allows you to restore from a backup and roll forward from the logs.

Solution 5

Filesystem is only part of the problem. You can get significant performance boost by changing your IO scheduler. Fortunately this is fairly easy to test as you can change the IO scheduler on the fly. I'd suggest trying each one for a couple of days under typical load and see which gives the best performance.

View more solutions

Comments

[Oskar Duveborn]

The best filesystem would be more memory? ;)

[KevinRae]

+1 for Oskar. We just went from a server config where RAM was ~33% of the total size of the DB to a new machine where total RAM was greater than the size of the DB. Now we can cache the entire DB in memory. Our slowest SQL query is now 2 orders of magnitude faster.

[niXar]

Journalling data degrades performance; journalling metadata should have at worst minimal impact, and most likely, almost none. Not journalling metadata is unadvisable.

[bortzmeyer]

My benchmarks showed very little change when changing the I/O scheduler, probably because every DBMS already has its own scheduler.

[David Pashley]

MySQL copes a lot better under high load from using the deadline scheduler.

[Avery Payne]

You might want to /not/ use XFS to store a database, namely because it will (when needed) zero out blocks that it can't recover.

[Avery Payne]

pgbench does something similar and is included with most installs.

[sciurus]

Agreed, this is the sort of topic Greg covers very well. There is a sample chapter at packtpub.com/sites/default/files/… if you would like to evaulate before borrowing or buying the book.

[sciurus]

This contains some good general advice, but I downvoted because it also contains information that is irrelevant or incorrect for postgresql and modern journaled filesystems.

[Elijah]

Funny, when I was having this problem, the book didn't exist. Now, I'm really grateful for effort Greg put into that book.

[Janning]

I bought another copy just to honour this great work :-)