pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Poor performance - fixed by restart 2+ messages / 2 participants [nested] [flat]
* Poor performance - fixed by restart @ 2006-06-21 23:08 Peter Wilson <petew@yellowhawk.co.uk> 0 siblings, 1 reply; 2+ messages in thread From: Peter Wilson @ 2006-06-21 23:08 UTC (permalink / raw) To: pgsql-performance 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Poor performance - fixed by restart @ 2006-06-22 16:24 Tom Lane <tgl@sss.pgh.pa.us> parent: Peter Wilson <petew@yellowhawk.co.uk> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2006-06-22 16:24 UTC (permalink / raw) To: Peter Wilson <petew@yellowhawk.co.uk>; +Cc: pgsql-performance 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-06-22 16:24 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2006-06-21 23:08 Poor performance - fixed by restart Peter Wilson <petew@yellowhawk.co.uk> 2006-06-22 16:24 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox