Thread: Poor performance - fixed by restart

I've recently configured a new high-performance database server:
    2xXeon 3.4G, 2G RAM, 4x15K SCSI disks in RAID 10, h/w RAID

This has been live for a couple of weeks.

The box is running Fedora Core 4.

The only thing running on this box is PostgreSQL 8.1.4 and some stub
applications that handle the interface to Postgres (basically taking XML service
requests, translating into SQL and using libpq). The database is a backend for a
big web application. The web-server and processor intensive front-end run on a
separate server.

Postgres has probably been running for 2 weeks now.

I've just uploaded a CSV file that the web-application turns into the contents
into multiple requests to the database. Each row in the CSV file causes a few
transactions to fire. Bascially adding rows into a couple of table. The tables
at the moment aren't huge (20,000 rows in on, 150,000 in the other).

Performance was appalling - taking 85 seconds to upload the CSV file and create
the records. A separate script to delete the rows took 45 seconds. While these
activities were taking place the Postgres process was using 97% CPU on the
server - nothing else much running.

For comparison, my test machine (750M Athlon, RedHat 8, 256M RAM, single IDE
hard drive) created the records in 22 seconds and deleted them again in 17.

I had autovacuum ON - but to make sure I did first a vacuum analyze (no
difference) then vacuum full (again no difference).

I'd tweaked a couple of parameters in postgres.conf - the significant one I
thought being random_page_cost, so I changed this back to default and did a
'service postgresql reload' - no difference, but I wasn't sure whether this
could be changed via reload so I restarted Postgres.

The restart fixed the problem. The 85 second insert time dropped back down to 5
seconds!!!

To check whether the random_page_cost was making the difference I restored the
old postgres.conf, restarted postgres and redid the upload. Rather suprisingly -
  the upload time was still at 5 seconds.

Any thoughts? I find it hard to believe that Postgres performance could degrade
over a couple of weeks. Read performance seemed to be fine. The postgres memory
size didn't seem to be huge. What else am I overlooking? What could I have
changed by simply restarting Postgres that could make such a drastic change in
performance?

Pete

Peter Wilson <petew@yellowhawk.co.uk> writes:
> I'd tweaked a couple of parameters in postgres.conf - the significant one I
> thought being random_page_cost, so I changed this back to default and did a
> 'service postgresql reload' - no difference, but I wasn't sure whether this
> could be changed via reload so I restarted Postgres.

> The restart fixed the problem. The 85 second insert time dropped back down to 5
> seconds!!!

Um, which parameters did you change *exactly*?

Also, depending on how you were submitting the queries, it's possible
that Postgres was using cached plans already made based on the old
settings.  In that case the restart would've cleared the bad plans
(but starting a fresh connection would've been sufficient for that).

            regards, tom lane