| Subject | Re: [firebird-support] Delete causes server to hang |
|---|---|
| Author | Alexey Kovyazin |
| Post date | 2016-08-26T11:12:28Z |
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
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.
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