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@xxxxxxxxx> 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@xxxxxxxxxxxxx> 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. > -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
