On 8/27/07, Kevin Kempter <kevin@xxxxxxxxxxxxxxxxxxx> wrote: > Hi List; > > I've just inherited multiple postgres database servers in multiple data > centers across the US and Europe via a new contract I've just started. What pg version are you working with, and on what OS / OS version? > Each night during the nightly batch processing several of the servers (2 in > particular) slow to a crawl - they are dedicated postgres database servers. > There is a lot of database activity going on sometimes upwards of 200 > concurrent queries however I just dont think that the machines should be this > pegged. I am in the process of cleaning up dead space - their #1 fix for > performance issues in the past is to kill the current vacuum process. > Likewise I've just bumped shared_buffers to 150000 and work_mem to 250000. way too big for work_mem as mentioned before. Set it to something reasonable, like 8M or so. Then, if you've got one query that really needs lots of memory to run well, you can set it higher for that connection / query only. You can even set work_mem to a particular number for a particular user with alter user command. Oh, and 200 concurrent queries is a LOT. > Even at that I still see slow processing/high system loads at nite.I have > noticed that killing the current vacuum process (autovacuum is turned on) > speeds up the entire machine significantly. > The servers are 4-CPU intel boxes (not dual-core) with 4Gig of memory and > attached to raid-10 array's It sounds to me like your systems are I/O bound, at least when vacuum is running. If you want to get good performance and have vacuum run in a reasonable amount of time, you might need to upgrade your RAID subsystems. Do you have battery backed caching controllers? Which exact model controller are you using? How many drives in your RAID10 array? What types of queries are typical (OLAP versus OLTP really)? > Any thoughts on where to start? The vacuum cost settings to reduce the impact vacuum has. Increasing fsm settings as needed. Vacuum verbose to see if you've blown out your fsm settings and to see what fsm settings you might need. reindexing particularly bloated tables / indexes. hardware upgrades if needed. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
