Hi Thomas,
90000 records versions is not that big amount to slow down Firebird
completely for 30 seconds.
In this old article below we made tests with updating/deleting 1mln of
records, but it was much faster than you describe.
http://ib-aid.com/en/articles/negative-impact-of-indices-to-insert-update-and-delete-performance-in-firebird-sql/
However, I suspect that deleting of records could be done regularly, and
there could be long running writeable transaction which retains old
record versions, so Firebird need to clean not 90k, but much more of them.
Anyway, right now there is not enough information - to investigate it, run
gstat -r -t < tablename > stat.txt
before and after delete,
and review number of records versions and max versions, to figure out
what is the actual amount of records versions to be cleaned inside the
database.
Regards,
Alexey Kovyazin
IBSurgeon http://ib-aid.com
Hey Helen
Thanks for hour reply, let me see if i can clarify the problem, the
actual delete of the 90.000 rows is not the problem, this completes in
10-30 seconds. That is just fine, performance does not matter here.
I have 2 web servers, on each of them, there is a background thread
that inserts into the request table, these two threads are the only
ones that insert into that table.
Every hour the previous mentioned delete procedure is in executed from
an application server.
However when the procedure is executed every executing user-request on
the webserver is waiting for the database server to respond, and the
number of attachments spike on the database server, resulting i
connection pool full exceptions.
this image shows how the attachment count spikes, 95% of the
attachments are from the two webservers.
https://dl.dropboxusercontent.com/u/937321/attachement_spike.PNG
None of the user-requests on the webserver
selects/inserts/updates/deletes from the request table, information
about the request is handled over to the background thread that
inserts a row into the table.
From my perspective it looks like the delete of the 90.000 rows
results in a complete hang/standstill on the database server. In those
few seconds i looks like FB does not finish any statements.
Thomas
