Vacuums on large busy databases
От | Francisco Reyes |
---|---|
Тема | Vacuums on large busy databases |
Дата | |
Msg-id | cone.1158247381.60704.30981.1000@zoraida.natserv.net обсуждение исходный текст |
Ответы |
Re: Vacuums on large busy databases
Re: Vacuums on large busy databases Re: Vacuums on large busy databases Re: Vacuums on large busy databases |
Список | pgsql-performance |
My setup: Freebsd 6.1 Postgresql 8.1.4 Memory: 8GB SATA Disks Raid 1 10 spindles (2 as hot spares) 500GB disks (16MB buffer), 7200 rpm Raid 10 Raid 2 4 spindles 150GB 10K rpm disks Raid 10 shared_buffers = 10000 temp_buffers = 1500 work_mem = 32768 # 32MB maintenance_work_mem = 524288 # 512MB checkpoint_segments = 64 Just increased to 64 today.. after reading this may help. Was 5 before. pg_xlog on second raid (which sees very little activity) Database sizes: 1 200GB+ Db and 2 100GB+ I run 3 daily "vacuumdb -azv". The vacuums were taking 2 to 3 hours. Recently we have started to do some data mass loading and now the vacuums are taking close to 5 hours AND it seems they may be slowing down the loads. These are not bulk loads in the sense that we don't have a big file that we can do a copy.. instead it is data which several programs are processing from some temporary tables so we have lots of inserts. There are also updates to keep track of some totals. I am looking to either improve the time of the vacuum or decrease it's impact on the loads. Are the variables: #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-10000 credits #vacuum_cost_page_miss = 10 # 0-10000 credits #vacuum_cost_page_dirty = 20 # 0-10000 credits #vacuum_cost_limit = 200 # 0-10000 credits Is that the way to go to decrease impact? Or should I try increasing maintenance_work_mem to 1GB? A sum of all running processes from "ps auxw" shows about 3.5GB in "VSZ" and 1.5GB in "RSS". I am also going to check if I have enough space to move the stage DB to the second raid which shows very little activity in iostat. Any other suggestions?
В списке pgsql-performance по дате отправления: