Poor performance - fixed by restart

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux