Re: Update Performance from 6.5.0 to 6.5.3 to 7.0

* Bryan White <bryan(at)arcamax(dot)com> [000526 13:00] wrote:
> I have a database that is used to manage email mailing lists (opt in
> ezines). When an email message bounces the bouncecount in the customer
> record is incremented.
>
> My process used to do this on the fly as a bounce was recieved. This was on
> 6.5.0. We recently updgraded to 6.5.3 and faster hardware. For the most
> part the update went smooth except for this process. It seemed to overload
> the server. I did not have time to deal with it then, so I changed the
> process to just log the bounce for later processing.
>
> Now I need to fix the problem. I have written a script to process the log
> file. It filters out duplicates which will reduce the total number of
> updates that are done. It works inside a transaction. Too keep the size of
> the transacations down (I have had problems in the past with large
> transations) it closes one transaction and opens a new one after every 100th
> update.
>
> The problem is that performance is still less than I need. I am getting
> about 5 updates processed per second on my test server. I never analysed it
> on 6.5.0 but I was not having a problem and that was running on slower
> hardware with more activity on the box.
>
> My test server is running Postgres 7.0. It is a PIII 733 with 384M RAM and
> 2 IDE HDs running RedHat 6.2.
>
> The backend is using about 80% of the CPU. The load average is around 1.00.
>
> The is what the update statement looks like:
> update customer
> set bouncecount = bouncecount + 1,
> bouncedate = CURRENT_DATE
> where email = 'bryan(at)arcamax(dot)com'
> and bouncedate != CURRENT_DATE;
>
> Explain says:
> Index Scan using icusem2 on customer (cost=0.00..4.98 rows=1 width=238)

Please describe your customer table better.

One thing I found was that postgresql (and just about any other
database) is excrutiatingly slow on update/insert if you made too
many indecies on the table being updated.

how many indecies do you have on this table?

you may want to try a combined index on both bouncedate and email.

--
-Alfred Perlstein - [bright(at)wintelcom(dot)net|alfred(at)freebsd(dot)org]
"I have the heart of a child; I keep it in a jar on my desk."