Hi all. We have a table called DeckEJB with the follow structure and
associates indices as indicated below. Our application makes use of this
table by doing INSERTs into it, and then after every 741 inserts, one
DELETE. We are not SELECTing from the table in our test, though in
production use SELECTs will be made, infrequently, into the table.
As we load test our application, the database (PostgreSQL 7.0.3 on Linux)
seems to get slower and slower over several hours, until it is perhaps 1/4
of its fastest speed. When I perform a VACUUM ANALYZE on the table,
everything speeds up again to its fastest speed again, but then begins to
degrade again. Everytime I do a VACUUM ANALYZE, it happens over again. The
number of rows in the table, however, is always between 0 and 741.
The EXPLAIN of the INSERTs and DELETEs only shows that it gets a result,
which is understandable. So what is the problem here? It could be because of
the index updating, but why is it slowing down so much?
DeckEJB Table Structure:
seed, integer, not null, primary key
identifier, varchar(255)
card, integer
Indices:
deck_id_x is an index on DeckEJB( identifier )
deckejb_pkey is an index on DeckEJB( seed )
Insert Statement: INSERT INTO DeckEJB Values ( x, "Kalador", y ); -- x always increasing, y = rnd( 0, 741 )
In addition, after every 741 INSERTs, the following DELETE is performed: DELETE FROM DeckEJB WHERE
identifier="Kalador";