Re: [Pypgsql-users] Help needed on performance

[Bob K. <bo...@ip...>]

Hi!

The only thing that comes immediately to mind is to be sure that you're 
vacuuming regularly. It seems to me that you're inserting and deleting rows 
quite a bit, and without regular vacuuming, the indexes will become pretty 
useless. I had similar problems with a table that had only a few dozen rows, 
but a couple hundred thousand deleted rows. Performance was horrible until I 
remembered to vacuum, which I now do daily with a cron job. I don't think 
Pypgsql has any bearing on the problem, however.

HTH.

.... Bob

On Friday 13 June 2003 02:04, Dave Strickler wrote:
> If there's a good FAQ on this, please just point me there....
>
> Background
> ------------
> I have a database that has 3 tables in it. One of the tables has about
> 15-20 fields in it, a number of them are strings, and so I have defined
> them as TEXT. The others are mostly INT4.
> I have indexes on 4 of the TEXT fields, and on one other INT4 fields.
>
> The table has 500k to 1m (size fluxuates) rows in it and is run on a
> Dual Processor 2.4 Xenon, with 2GB of RAM and a single IDE drive. Its
> running RH8, and has Apache running as well as Postgresql.
>
>
>
> Problem
> ----------
> The crux of the problem is performance. At somewhere around 500k
> records in the table, the speed of SELECTs and DELETEs gets really poor.
> 'Poor' as in 2-3 minutes to do a DELETE of 1k rows. Under this number,
> the speed is excellent.
>
> Any ideas on this?
>
> TIA,
>
>
>
> Dave Strickler
> MailWise, LLC
> (617) 848-5942
> ------------------------------------
> Want to get rid of SPAM, Viruses, and unwanted content?
> MailWise Filter - nothing to install, maintain or upgrade.
> "Intelligent Email Protection(tm)"
> Info at  http://www.mailwise.com