Re: Strange performance issue apparently causd by pg_stat timeout

To clarify on the rules...I was using rules for the tables that are
the entry point for the insertion into the database.

On Sat, Jun 5, 2010 at 7:19 AM, Anj Adu <fotographs(at)gmail(dot)com> wrote:
> We run a similar system with very large volumes
>
> I had similar queue build ups (not related to pg_stat_timeouts though)
>
> I was using Rules for my partition management instead of triggers.
>
> Changing from rules to triggers fixed the problem. (rules were extremely slow)
>
> Hope this helps you.
>
> On Wed, Jun 2, 2010 at 8:48 PM, Benjamin Krajmalnik <kraj(at)servoyant(dot)com> wrote:
>> System is running PG 8.4.0 (I have been unable to upgrade because the system
>> needs to be up 24x7), FreeBSD 7.2 amd64, 8 cores, 16GB RAM.
>>
>> Our application is a network monitoring system, so we are constantly
>> inserting vast amounts of data (server presently processes about 50 million
>> transactions per day).
>>
>> As digests of test points come in, they are stored in message queues on a
>> second server (running PG 8.4.4).  A set of daemons process the digests and
>> insert the data into the main database residing on a second server.
>> Presently, the database has about 60GB of data.
>>
>>
>>
>> A few days ago, I noticed that the data in the message queues on the other
>> server was getting backed up, and then after a few minutes it would process
>> and clear.  This was a totally new behavior.   Initially I suspected
>> deadlocks caused by background processes which create materialized views, so
>> I stopped those, yet the behavior continued.  Then I suspected server load,
>> yet CPU utilization and load was fine (l minute and 5 minute load was at <
>> 4), and iostat did not show an overly busy disk subsystem (I had seen it at
>> much higher utilization levels on both the data and log partitions without
>> any performance hits).
>>
>>
>>
>> I then suspected network issues, so I checked the infrastructure (runnig on
>> Juniper Gig switches, of course non-blocking, and all of the port
>> information was clean).
>>
>> Finally, I noticed that the logs had “pg_stat wait timeout” warnings.
>> Initially I though these were caused by our checking the running process via
>> pgadmin from our office to the data center, yet even when I exited pgadmin,
>> the warnings were still there.
>>
>> After further testing, I saw a correlation between the data getting queued
>> up and the “pg_stat wait timeout” warnings.  As data would begin to queue
>> up, I would see the warnings, and about a minute later it would start to
>> dequeue and get stored on the server.
>>
>>
>>
>> I searched the archives and found some messages stating that this has been
>> observed.  The interesting thing is that nothing has changed on the server
>> and it started to manifest itself.  I ran an analyze of the entire database,
>> hoping this may rectify any issue, but unfortunately to no avail.
>>
>>
>>
>> Any suggestions would be deeply appreciated – this behavior is definitely
>> not good.
>>
>>
>>
>> Below is a snapshot from the log files:
>>
>>
>>
>> 2010-06-02 20:59:19 MDT WARNING:  pgstat wait timeout
>>
>> 2010-06-02 21:10:42 MDT WARNING:  pgstat wait timeout
>>
>> 2010-06-02 21:16:21 MDT WARNING:  pgstat wait timeout
>>
>> 2010-06-02 21:21:23 MDT WARNING:  pgstat wait timeout
>>
>> 2010-06-02 21:25:50 MDT WARNING:  pgstat wait timeout
>>
>> 2010-06-02 21:35:20 MDT WARNING:  pgstat wait timeout
>>
>> 2010-06-02 21:39:13 MDT WARNING:  pgstat wait timeout
>>
>> 2010-06-02 21:45:32 MDT WARNING:  pgstat wait timeout
>>
>>
>>
>> Thanks in advance.
>