- 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
I used /dev/shm because it's already in fstab as a ramdisk. To make it exclusively for postgres 9.3, I did the following:
ReplyDelete1. Created the /dev/shm/pgsql_stats_tmp directory with global read/write/execute privileges in postgres's init file in /etc/init.d
2. Added the path: stats_temp_directory = '/dev/shm/pgsql_stats_tmp' to postgresql.conf
Still testing. I may change permissions to read/execute for all but postgres.
So far, no speed improvements.
Thanks for sharing, nice post! Post really provice useful information!
ReplyDeleteGiaonhan247 chuyên dịch vụ vận chuyển hàng đi mỹ cũng như dịch vụ ship hàng mỹ từ dịch vụ nhận mua hộ hàng mỹ từ website nổi tiếng Mỹ là mua hàng amazon về VN uy tín, giá rẻ.