looking at your patch I'm puzzled why this select oid stuff is in
there at all.
The original MySQL version is same as your patch. Straightforward
delete with a where statement.
No two step select/delete. Doesn't make any sense to me to search,
return the oid and then delete based on that oid which basically
makes another search.
So I totally agree on your patch.
On 09.11.2006, at 17:20, Ben Suffolk wrote:
Hi,
I have been doing some benchmarking on the pgsql DLRs. I actually
wrote a quick application as opposed to using kannel. The
application simulated message sending and DLRs by looping a
thousand times over the following :-
1. Adding a random number of messages into the DLR table (and
storing them in an in memory hash)
2. Deleting a random number of messages from the DLR table (based
on a random selection from the hash)
3. Updating a smaller random number of messages in the DLR table
(again select from the hash).
These test were carried out on a pretty old box running FreeBSd 6.1
and PostgreSQL 8.1.
I ran the benchmark a number of times, and with the existing SQL I
was anywhere between 100 and 140 messages a second.
I updated the SQL (the delete and update) to remove the OID
selection bit. I did not add any LIMIT so that it will still work
with the earlier versions of pgsql. Again after running this a
number of times I would average between 199 and 201 messages a
second (It was a lot more consistent this time).
Attached is the patch.
Regards
Ben
<dlr_pgsql.patch>
