Re: indexes greatly slowing data entry - Mailing list pgsql-general

We are having the exact same problem on PostgreSQL 7.1.3.  In our situation,
vacuum analyze, fixes the problem!
There is hardly any hard disk activity (P3 933 MHz server with 512 MB RAM,
SCSI HDU 10,000 RPM).

I have increased the SHARED_MEM buffers in postgres.conf and change the
commit to be async (if I recall correctly).

We are importing anywhere from 10,000 - 100,000 records each day and after
this import, performance takes a major nose dive on us.

And, because this data gets imported during normal business hours we can not
tie up the server with a vacuum.  I understand 7.2 has a better vacuum but
this doesn't seem to solve our problem because we need to be able to use the
database during the 30 minutes it takes to import the data.  After about
5,000 records is when we notice the performance drop.

There are approximately 25 users on the database at any given time.  Problem
seems slightly amplified when users logged goes from 1 to 25.

Any ideas?!?!?!

Thanks, Eric.

""DE Sesa, Michael C."" <michael.desesa@exeloncorp.com> wrote in message
news:AFAB5F8466DAD411B586000255B03E1416CCC1@mobntxch12.peco.com...
> I'm having a problem with performance and indexing seems to be the cause.
I
> have a database with two tables with exactly the same structure.  There
are
> two fields in each which I need to have indexed.  One is a varchar(12),
the
> other a timestamp.
>
> Data comes from another computer as an ASCII delimited text file at about
> 20,000 records per hour. Everything was fine until the database grew to
> about 10 Gigabytes, then the computer could no longer keep up with putting
> the data into the postgres database (using a perl script with DBI.)  I
found
> that when I dropped one of the indexes on varchar, the problems went away.
> It did not matter from which table I dropped the index.
>
> With both varchar indexes in place, data for the table with the varchar
> index created second would take 10 times as long to store data into
compared
> to the other table.  From vmstat: blocks in (bi) is 10 times higher with
> both varchar indexes when compared to with only one, and it looks like the
> raid array is maxed out.
>
> The indexes on timestamp did not seem to impinge on performance at all.
> There is no swapping to/from disk going on.  Can anyone help me figure out
> what is the problem/solution?
>
> I do an automated vacuum analyze nightly.  Performance is not increased
> afterward.
>
> pg 7.0.3 on Red Hat Linux 7.1.
>
>
> ************************************************************************
> This e-mail and any of its attachments may contain Exelon Corporation
> proprietary information, which is privileged, confidential, or subject
> to copyright belonging to the Exelon Corporation family of Companies.
> This e-mail is intended solely for the use of the individual or entity
> to which it is addressed.  If you are not the intended recipient of this
> e-mail, you are hereby notified that any dissemination, distribution,
> copying, or action taken in relation to the contents of and attachments
> to this e-mail is strictly prohibited and may be unlawful.  If you have
> received this e-mail in error, please notify the sender immediately and
> permanently delete the original and any copy of this e-mail and any
> printout. Thank You.
> ************************************************************************
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>