Scribd
Upload
132 views2 pages

Optimize PostgreSQL with tmpfs Setup

1. The statistics collector in PostgreSQL collects information about table and index accesses, function usage, and current commands. It stores this temporarily on disk by default, which can impact performance. 2. Moving the temporary files to a RAM-based filesystem like tmpfs can improve performance by reducing physical I/O. 3. The document provides steps to create a tmpfs mount point, configure PostgreSQL to use it for temporary statistics files, and verify the change.

Uploaded by

Moïse Kouadio
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
132 views2 pages

Optimize PostgreSQL with tmpfs Setup

1. The statistics collector in PostgreSQL collects information about table and index accesses, function usage, and current commands. It stores this temporarily on disk by default, which can impact performance. 2. Moving the temporary files to a RAM-based filesystem like tmpfs can improve performance by reducing physical I/O. 3. The document provides steps to create a tmpfs mount point, configure PostgreSQL to use it for temporary statistics files, and verify the change.

Uploaded by

Moïse Kouadio
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

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 [Link], 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 [Link]
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 [Link], 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 [Link]
[postgres@linux ~]$
Putting stats_temp_directory on a ramdisk
This hack is an old chestnut among PostgreSQL performance tuners, but it doesn't seem to be widely known
elsewhere. That's a shame, because it's pure win, and it's ridiculously easy to set up. You don't even need to restart
PostgreSQL.

Here's the situation: PostgreSQL writes certain temporary statistics. These go in the dir given by
the stats_temp_directory setting. By default, that's pg_stat_tmp in the data dir. Temp files get written a lot,
but there's no need for them to persist.

That makes them perfect candidates for a ramdisk (a.k.a. RAM drive). A ramdisk is a chunk of memory treated as a
block device by the OS. Because it's RAM, it's super-fast. As far as the app is concerned, the ramdisk just holds a
filesystem that it can read and write like any other. Moreover, PostgreSQL generally only needs a few hundred kilobytes
for stats_temp_directory; any modern server can fit that in RAM.

In Linux, you set up a ramdisk like this:

As root:

'mkdir /var/lib/pgsql_stats_tmp' [1]

'chmod 777 /var/lib/pgsql_stats_tmp'

'chmod +t /var/lib/pgsql_stats_tmp'

Add this line to /etc/fstab. That 2G is an upper limit; the system will use only as much as it needs.

tmpfs /var/lib/pgsql_stats_tmp tmpfs size=2G,uid=postgres,gid=postgres 0 0

'mount /var/lib/pgsql_stats_tmp'

Then, as postgres:

Change the stats_temp_directory setting in [Link]:

stats_temp_directory = '/var/lib/pgsql_stats_tmp'

Tell PostgreSQL to re-read its configuration:

'pg_ctl -D YOUR_DATA_DIR reload'

And that's it!

Other operating systems have different ways to set up ramdisks. Perhaps I'll cover them in a later post.

[1] The directory /var/lib/pgsql_stats_tmp is an arbitrary choice, but it works well for Debian's filesystem
layout.

Common questions

Powered by AI

To properly mount tmpfs for PostgreSQL, the fstab file must include an entry like `tmpfs /var/lib/pgsql_stats_tmp tmpfs size=2G,uid=postgres,gid=postgres 0 0`, specifying the mount point, file system type, size limits, and ownership to the postgres user and group, ensuring that the tmpfs is mounted correctly on boot .

Beyond reducing physical I/O, using a ramdisk such as tmpfs boosts performance by providing extremely fast read/write speeds as it utilizes RAM, which is significantly faster than hard disk storage. This setup lowers latency, improves throughput, and minimizes wait times for database processes accessing transient statistics, thereby speeding up overall query execution and enhancing application responsiveness .

Tmpfs is preferred over ramfs for PostgreSQL’s temporary file storage because it supports dynamic file system size management, allowing it to swap data to disk if needed, thus preventing out-of-memory errors and potential data loss, unlike ramfs which uses all available memory without limits, potentially leading to system crashes .

PostgreSQL's statistics collector gathers various performance metrics, including table and index accesses, usage of user-defined functions, and current commands executed by server processes. This data is temporarily stored in files located in the stats_temp_directory. By directing these to a RAM-based file system with tmpfs, physical disk I/O is minimized, enhancing database performance .

After setting up tmpfs for the statistics directory, verification can be done by running a query within the PostgreSQL console: `show stats_temp_directory;`. This command should return the path to the tmpfs mount location, confirming that PostgreSQL is using the RAM-based file system for its statistics directory .

Setting specific permissions on the temporary statistics directory, such as `chmod 777` and adding the sticky bit `chmod +t`, ensures that the directory is accessible for reading and writing by PostgreSQL processes while maintaining file integrity and security. The sticky bit restricts users from deleting or renaming files unless they own the files themselves, enhancing security within the shared directory .

To ensure that the tmpfs configuration persists across server reboots, one must add an entry to the /etc/fstab file specifying the tmpfs mount point and options. This includes setting the filesystem type to tmpfs and allocating the required size, as shown by adding `tmpfs /mnt/tmp tmpfs defaults,size=200m 0 0` to the file .

Using a RAM-based file system such as tmpfs for PostgreSQL's stats_temp_directory can significantly enhance performance by reducing physical I/O operations. This decrease in disk IO can lead to faster read and write access, thereby improving overall database performance .

Using a RAM-based system for stats_temp_directory may not be advisable if the system has limited RAM, as it could reduce the available memory for other processes, impacting overall system performance. Additionally, if persistent storage of statistical data is necessary, relying solely on volatile storage in RAM could lead to data loss upon power loss or system reboot .

First, create a new directory for the tmpfs mount point using `mkdir /mnt/tmp`. Then, mount tmpfs using `mount -t tmpfs tmpfs /mnt/tmp/ -o size=200m`. Update postgresql.conf with `stats_temp_directory = '/mnt/tmp'`, and restart PostgreSQL with `pg_ctl -D /var/lib/pgsql/data restart`. Verification involves using `show stats_temp_directory;` in the PostgreSQL console to confirm the directory is set to the new ramdisk location .

You might also like