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
