PostgreSQL on different FS (ext3, ext4, xfs)

The article is an article that grew out of the question raised in the Q&A. In short it was so... was offered the option of testing PostgreSQL on a specific file system, and the question was, is that a normal approach and whether it is possible somehow to trust the results of this test. In the discussion of alternative options was not found and I decided to test as he planned to originally.



In fact, that was how everything went:
There is a mediocre server with the following configuration:
the

the
• HP Proliant DL160 G5
the
• 1x Intel Xeon CPU E5405 @ 2.00 GHz (128KiB L1, L2 12MiB)
the
• 4x FB-DIMM DDR2 Synchronous 667 MHz (1.5 ns) to a total of 8GB RAM
the
• RAID LSI SAS1064E 4-Port PCI-E 3Gb/s
the
• 4x SAS DF146BABUE HP 146GB 3.5" LFF 3G 15K RPM

Gentoo Linux kernel-3.7.1, glibc-2.15-r3, gcc-4.5.4 the

• PostgreSQL Server 9.2.1

Four disks were collected two RAID1, one of them hosted operating system, the second volume is the range for tests. This disc is used as a LVM PV and for the test a dedicated LVM volume with a size of 55GB. The test is as follows:
the

the
• formatted volume and mount the directory.
the
• run the initialize postgresql cluster.
the
• copy into the cluster pre-prepared configuration file postgresql.conf;
the
• run and test postgresql create database pgbench;
the
• run initialization tables using pgbench;
the
• cycled in the "SELECT's only" run pgbench with a miscellaneous quantity of client connections, results are stored (each test lasts 2 hours)
the
• cycled in the "TPC-B" run pgbench with a miscellaneous quantity of client connections, results are stored (each test lasts 2 hours).

The number of clients in each test case, what varies from 8 to 96 (8,16,32,64,96)
Total for one system file we get 10 results (5 SELECT s only 5 TPC-B)

A few details of importance:
the

the
• RAID works without WriteCache;
the
• all processes/services have been stopped/disabled except PostgreSQL, process table is almost empty (except for nuclear processes);
the
• WAL logs live on the same partition as the data;
the
• test database is initialized in such a way as to occupy 80% space of the whole disk;
the
• before each run pgbench would be reset caches.

Configuring postgresql.conf
max_connections = 100
shared_buffers = 1500MB
work_mem = 16MB
maintenance_work_mem = 128MB
effective_io_concurrency = 1
checkpoint_segments = 32
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9

Results for "SELECT's only" and TPC-B





Conclusions: in General as expected breakthrough and revolutionary ruptures in the results no, all file systems in General have approximately the same level of performance. If you look closely, we can note a few points:
ext4 shows slightly better performance than ext3 (on average 1-3%);
ext4 shows the best performance in TPC-B test (from 1% to 8%);
— xfs shows the best performance in SELECT's only (from 1% to 3%);

In General, we can say "but you can put any FS, the difference is so small that it can be neglected". But remember that (here you can insert any pathetic phrase about the importance of small things). In General, do not neglect trifles))

That's about it. to throw poop, to criticize, to Express thoughts, make your ideas...

PS Andrei Fefelov, Hello!!! )))

Article based on information from habrahabr.ru