Viswamitra's blog

When I see the postgresql logfile I can see below message many times

LOG: using stale statistics instead of current ones because stats collector is not responding

PostgreSQL tracks ‘runtime statistics’ (number of scans of a table, tuples fetched from index/table etc.) in a file, maintained by a separate process (collector). When a backed process requests some of the stats (e.g. when a monitoring tool selects from pg_stat_all_tables) it requests a recent snapshot of the file from the collector.

The log message you see means that the collector did not handle such requests fast enough, and the backend decided to read an older snapshot instead. So you may see some stale data in monitoring for example.

This may easily happen if the I/O system is overloaded, for example. The simplest solution is to move the statistics file to RAM disk (e.g. tmpfs mount on Linux) using stats_temp_directory in postgresql.conf.

The space neede depends on the number of objects (databases, tables, indexes), and usually it’s a megabyte in total or so.

To improve the performance mount the pg_stat_tmp directory on RAM by using below procedure. Do this as root user.

echo “tmpfs $PGDATA/pg_stat_tmp tmpfs size=1G,uid=postgres,gid=postgres 0 0” >> /etc/fstab

mount $PGDATA/pg_stat_tmp

[root@s192-169-159-2 ~]# df -h

Filesystem         Size  Used Avail Use% Mounted on

…..

…..

tmpfs              1.0G     0  1.0G   0% /home/postgres/DATA_9.6/pg_stat_tmp