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
