[SQL] INSERT waiting under heavy load
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] INSERT waiting under heavy load
[EMAIL PROTECTED] writes: > After digging through all the discussions of "INSERT waiting" problems I am > still not clear about the concensus about solving it. > ... > 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. Well, if there are only a few rows in table1, then this design is inherently going to lose big. Any two transactions trying to update the same table1 row are going to conflict and one will have to wait for the other to complete. Rethink the need for those counters. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] INSERT waiting under heavy load
> > After digging through all the discussions of "INSERT waiting" problems I am > > still not clear about the concensus about solving it. > > ... > > 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. > > Well, if there are only a few rows in table1, then this design is > inherently going to lose big. Any two transactions trying to update the > same table1 row are going to conflict and one will have to wait for the > other to complete. Rethink the need for those counters. I appreciate that it is most likely not the best design though i expect reasonable distribution of UPDATE hits against the first table when the number of rows increases. What I do not understand is this: if the problem is caused by the the acquire lock->modify column->release lock on the table 1, then why does it increase significantly increase as the number of entries in the table 3 grows? The simulation maintains pretty much constant rate of new requests coming to table 3. Alex ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] INSERT waiting under heavy load
[EMAIL PROTECTED] writes: > if the problem is caused by the the acquire lock->modify column->release > lock on the table 1, then why does it increase significantly increase as the > number of entries in the table 3 grows? The simulation maintains pretty much > constant rate of new requests coming to table 3. Are you vacuuming table 1 on a regular basis while you do this? Accumulation of dead tuple versions in table 1 is a pretty severe performance risk in the sort of scenario you are describing. (I'm not sure why it would manifest as transactions showing "INSERT waiting" state though.) regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
