7

My postgresql 9.5 logs shows every minute the message:

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

Except a post on serverfault which is not helpfull and seems related to standby databases setup (which is not my case), I don't find anything to solve this.

What's the meaning of this? How can I solve that?


Additional information based on Greda's reply:

  • I have 4 CPU, and in routine less than 5% of 1 CPU is used
  • I have 32Gb RAM, and in routine ~ 500Mb are used
  • This is not a VM
  • This runs Ubuntu 14.04.3 LTS/Linux 3.13.0 SMB x86/64

I have also tried to tweak the postgresql.conf parameters as follows

  • shared_buffers: 1024MB
  • work_mem: 10MB
  • maintenance_work_mem: 1024MB
  • effective_cache_size: 4GB

The error message is still there.

P.S: I fully understand that's not a severe error, but it floods my logs, and I have the fear to not see important messages...


Some additional observation:

  • VACUUM FULL VERBOSE ANALYZE doesn't helps
  • I have a never ending proccess connected to the database. When this is running, the VACUUM FULL VERBOSE ANALYZE seems blocked.

Can that never ending process be the root causes of my problem? In that case, since I need to have that proccess running, do I have to tweak something on the server?


Edit:

I had a never ending process connected to the db. I've tried to stop it, and it doesn't helps.

My postgresql.conf file is available there

3
  • Process: permanent connection to the database, idle most of the time and a few inserts every 30 minutes Jan 21, 2016 at 19:35
  • Selinux: nothing special for the postgres user and the production user Jan 21, 2016 at 19:37
  • 1
    Sometimes even the default settings of selinux or apparmor can cause problems, especially if you installed postgresql from source code, or through a different repository/package manager than the main one for the OS. If you have either of those turned on, could you turn them off for a few minutes (because you see the log message every minute) and see if that stops it? Also, try stopping the never-ending process for just a few minutes, and see if that solves it (unlikely, but it is worth testing it).
    – jjanes
    Jan 21, 2016 at 20:41

4 Answers 4

4

This basically means your (virtual?) machine is too slow or overloaded, because the statistics collector background task is being starved. It is running with a lower priority by default, because you do not want it to disrupt normal database operations (too much).

If you can afford it, add some more CPUs to it and make sure you have plenty of RAM.

Technically though, this is not a severe error - your queries will run, but may be using less than optimal execution plans because they did not see an update to table statistics in a while.

If you want to, you can always force an update of statistics for the most commonly changed tables by running the ANALYZE or VACUUM ANALYZE statements on those.

5
  • Sorry, another addition: I have a never ending running process which is connected to the db. The vacuum full verbose analyze completes as soon as I stop that process. Maybe is it the root cause of my problem? I'll edit the description accordingly. Jan 21, 2016 at 12:42
  • If it is caused by overload, it is almost surely an overload of the IO system, not of the CPU or RAM
    – jjanes
    Jan 21, 2016 at 18:23
  • I would be surprised of IO perfs problems because I know that the IO are fast on that server, and my db are very small Jan 21, 2016 at 19:38
  • I asked this in a reply to your comment that had been deleted since: can you post your postgresql.conf (without comments)? Other than that - if the process is truly idle, VACUUM will never block it, even if FULL. If it is idle in transaction and has touched some tuples, then that is another story. Make sure you check pg_stat_activity to see if there's a backend_xid associated with it. Jan 24, 2016 at 1:47
  • Thanks Grega. I've update the description of the problem: added postgresql.conf + tested without any connection to the db (still problematic) Mar 1, 2016 at 14:57
3

To temporarily alleviate the problem until you find a solution, have you tried sending a SIGHUP to the server when it occurs?

I have a similar problem in a fairly different situation: the database runs on a windows desktop PC together with Apache and proprietary machine control software. My case fits in the "slow or overloaded" category. However, I see this happening even at night while the machine is idle. I've been monitoring many system parameters with PRTG to try to find a link with a specific activity, but I can only see the consequences, not the cause.

I don't see this as a simple warning because, once it starts, queries that normally take 100ms will start taking 10 seconds (but still leave other queries unaffected). Also, it stays that way until I send a SIGHUP (pg_ctl.exe reload ...), which I think is weird since the process should resume once it is no longer starved.

P.S. please don't hail me about the windows desktop part: I know, and ... circumstances.

2
  • Thanks Syranolic. It happens every minute, so I don't think that SIGHUP would help. Mar 1, 2016 at 14:54
  • 1
    I've optimized / fixed many parts of my system now and it helps. I only get the warning once a day while the system is the busiest. Unfortunately, the warnings still won't stop unless I send a SIGHUP (I've scheduled it every hour as a temporary solution), so there's still something weird...
    – Syranolic
    Mar 6, 2016 at 15:09
2

Disappeard when upgrading to 9.5.2.1 (http://apt.postgresql.org/pub/repos/apt/), but came back after server restart.

It seems that it was due to the service restart during installation

The workaround is to restart the service.

Closed question

1

This happened to me when I disabled IPv6 on the server without restarting Postgres. I came across a detailed explanation here (search for "The statistics collector" in the page), but in short:

PostgreSQL [...] will loop through all the addresses returned [for localhost], create a UDP socket and test it until it has a socket that works.

If the socket it had seleccted was IPv6 and it is later disabled, it stops working and you get that message in the logs.

You can check to which IP and UDP port the "postmaster" (or "postgres") service is bound with

netstat -n -u -p

The output is something like this:

Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
udp        0      0 127.0.0.1:47780         127.0.0.1:47780         ESTABLISHED 2824/postmaster     

or on another host where it is bound to IPv6 ("udp6"):

# netstat -n -u -p
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
udp6       0      0 ::1:51761               ::1:51761               ESTABLISHED 1006/postgres       

Restarting postgres as suggested in the accepted answer does indeed fix it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.