PostgreSQL: Linux kernel I/O tuning
When you are installing a database server, PostgreSQL is not the only thing, which can be tuned. The operating system will also provide a handful of switches capable of improving performance nicely. If you are using Linux, the I/O scheduler of the operating system can be something worth investigating.
Basically the job of the I/O scheduler of the Linux kernel is to pass data to the underlying storage device in a reasonable and optimized way. Depending on the strategy of the I/O scheduler the performance of your PostgreSQL database instance may vary. Of course, if the load on your system is low, the I/O scheduler won’t make much of a difference – however, if you are facing I/O limitations and if your system has to handle high load choosing the right I/O scheduler can be vital. I remember a special case when the load reported by the kernel almost collapsed after changing the I/O scheduler (174 to 4.5 !). While such a significant drop might be rare – it can still be quite beneficial.
In general the following I/O schedulers are supported by Linux:
NOOP is the simplest of all I/O schedulers. It just sends data to a FIFO queue, merges requests to save on disk seeks and passes data on. It can be useful to use NOOP and PostgreSQL in highly virtualized setups where the underlying host system does all the magic.
CFQ is short for “Completely Fair Scheduler”. The idea of CFQ is give all processes belonging to the same priority class same sized time slices. A process performing sequential I/O might receive a lot more bandwidth than a process doing random I/O (in a database random I/O can frequently happen during OLTP-workloads).
CFQ is the default value and usually a good idea for desktop application, which should stay responsive. It is not necessarily a good idea for database servers such as PostgreSQL.
The deadline scheduler tries to make sure that no process can suffer from starvation. The kernel imposes a deadline for each I/O. Practical tests have shown that using deadline is practically always faster than relying on the default (= CFQ). Actually I have not seen a case personally, in which CFQ was better than deadline. In many cases it makes no difference but I have never encountered a scenario in which deadline was the worse choice.
Changing the I/O scheduler:
Changing the I/O scheduler is not hard at all. If you run PostgreSQL all you have to do is to change the scheduler settings of the underlying block device. To find out which scheduler there is, just do:
$ cat /sys/block/sda/queue/scheduler noop deadline [cfq]
To change it, just use:
$ echo deadline > /sys/block/sda/queue/scheduler
This is all it takes to make this work until you reboot.