From: alex-lists-pgsql@yuriev.com
To: pgsql-sql@postgresql.org
Subject: INSERT waiting under heavy load
Date: Fri, 6 Jan 2006 13:42:27 -0500
Message-ID: <20060106184227.GA1890@s2.yuriev.com> (raw)
After digging through all the discussions of "INSERT waiting" problems I am
still not clear about the concensus about solving it.
I am running ration 6:1 SELECT:INSERT (insert fires up an UPDATE trigger
that hits a column in a table holding keys used by SELECT). I am looking at
doing about 2,000 INSERT/UPDATE per second, with possible peaks at 10,000
INSERT/UPDATE per second (i.e. 60,000 SELECTs).
(table 1)
The table holding primary keys is expected to grow to around 10,000 rows.
This is the table that gets 50% of SELECTs and 100% of UPDATES. This is the
owner status table. It is optimized so with a single SELECT against this
table all information needed for real-time clients would be accessible.
(table 2)
The 2nd number of rows in the second table is expected to be around 100
times the number of rows in the 1st table. Each entry in this table has uses
first table's column as a foreign key to avoid unlinked entries. It also has
foreign key dependecies to some other tables that for the purpose of the
application are never updated. This table gets the other 50% of SELECTs.
(table 3)
Finally, the 3rd table (audit log) is expected to have arbitraty number of
entries (measured in millions). It gets virtually no SELECT activity in the
mornal operations. If the data from this table is needed, a snapshot of this
table gets pulled into a different table (most likely on a different
database) and gets processed there. The table gets cleaned up at specific
intervals using DROP TABLE/CREATE TABLE sequence. It is guaraneed that when
the management application (non-real time) performs DROP TABLE/CREATE table
combination. The only thing that I do not particulary like is that every INSERT
into this table has to adjust a counter column in a corresponding row of the
(table1) via (table3->table2->table1) path.
The server is configured to accept about 200 connections from clients. The
problem is that after first couple of hours of working normally, as the
table (3) grows, the backend indicates that more and more INSERTs into table
3 are held up in the "INSERT waiting" state.
It happens even when table 1 contains only one row, table 2 contains 4 rows.
Is there anything that can be done to diagnose why "INSERT waiting" state
becomes so prevalent?
Would pulling the counter from table 1 into a table
(4) that contains only reference to appropriate table (1) row and counter
value make it better?
Thanks,
Alex
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: alex-lists-pgsql@yuriev.com
Subject: Re: INSERT waiting under heavy load
In-Reply-To: <20060106184227.GA1890@s2.yuriev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox