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

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s