- statistics about table and index accesses
- usage of user-defined functions
- 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=200mnow we have a file system of 200M.
# df -h /mnt/tmp/every time the server reboots, /mnt/tmp will be gone, to make the configuration persistent, add this line to /etc/fstab
Filesystem Size Used Avail Use% Mounted on
tmpfs 200M 8.0K 200M 1% /mnt/tmp
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 ~]$ psqlSimilarly, we can use tmpfs to improve the performance of Nagios server.
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 ~]$
reference:
http://www.postgresql.org/docs/9.1/static/monitoring-stats.html
