Using tmpfs to improve PostgreSQL performance

In PostgreSQL, the statistics collector collects the following information:

1. statistics about table and index accesses
2. usage of user-defined functions
3. current command executed by any server process.

The statistics collector then passes the information to backends using temporary files, location of the temporary files are defined by stats_temp_directory in postgresql.conf, by defaults it points to $PGDATA/pg_stat_tmp
As PostgreSQL is running, there are continuous I/O in stats_temp_directory, the disk IO may affect database performance. PostgreSQL recommends to point stats_temp_directory to RAM-based file system, decreasing physical I/O, thus increasing database performance.

We can use either ramfs or tmpfs, for the differences of the two, see http://www.thegeekstuff.com/2008/11/overview-of-ramfs-and-tmpfs-on-linux/

I will use tmpfs for stats_temp_directory, here are my steps
1. create directory for our new mount point:

# mkdir /mnt/tmp

2. mount tmpfs

# mount -t tmpfs tmpfs /mnt/tmp/ -o size=200m

now we have a file system of 200M.

# df -h /mnt/tmp/
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 200M  8.0K  200M   1% /mnt/tmp

every time the server reboots, /mnt/tmp will be gone, to make the configuration persistent, add this line to /etc/fstab

tmpfs    /mnt/tmp    tmpfs   defaults,size=200m    0 0

3. edit postgres.conf, add this line:

stats_temp_directory = '/mnt/tmp'

4. restart database

$ pg_ctl -D /var/lib/pgsql/data restart

5. confirm we are using the tmpfs

[postgres@linux ~]$ psql
psql (8.4.11)
Type "help" for help.

postgres=# show stats_temp_directory;
 stats_temp_directory
----------------------
 /mnt/tmp
(1 row)

postgres=# \q
[postgres@linux ~]$ ls -lh /mnt/tmp/
total 8.0K
-rw------- 1 postgres postgres 6.0K Sep 24 13:41 pgstat.stat
[postgres@linux ~]$

Similarly, we can use tmpfs to improve the performance of Nagios server.
reference:
http://www.postgresql.org/docs/9.1/static/monitoring-stats.html