Re: [PATCH] PostgreSQL DLR speedup

Hi,

Andreas , this is not true what you say. Mysql use LIMIT 1 in delete and  
update. Postgres should use oid because there is not limit support.

It's there because e.g. on EMI we could delete/update to much rows.

Am 10.11.2006, 15:50 Uhr, schrieb Andreas Fink <[email protected]>:

> 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>
>
>
>



-- 
Thanks,
Alex