Guillaume, Thanks for your help. > On 28 Aug 2006 11:43:16 +0200, Guillaume Cottenceau <gc@xxxxxx> wrote: > > max_fsm_pages is 20000 > > max_fsm_relations is 1000 > > Do they look low? > > Yes they are probably too low if you don't run VACUUM on a regular > basis and you have a lot of UPDATE/DELETE activity. FSM doesn't take a > lot of memory so it's usually recommended to have a confortable value > for it. Normally, we run VACUUM ANALYZE overnight. I'd say we have low DELETE activity, kinda high SELECT/INSERT activity, and UPDATE would be in the middle of that. > I usually recommend to read: > http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10116&cNode=5K1C3W > http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10087&cNode=5K1C3W > to understand better what VACUUM and FSM mean. Thanks for the pointer, will read that. > > Can you elaborate? I have created a couple of indexes (according > > to multiple models of use in our application) and they do take up > > quite some disk space (table dump is 600M but after restore it > > takes up 1.5G on disk) but I thought they could only do good or > > never be used, not impair performance.. > > Index slow downs write activity (you have to maintain them). It's not > always a good idea to create them. Of course. How newbie did I look :/. The thing is that I once did a few measurements and noticed no (measurable) impact in INSERT with a supplementary index, so I (wrongly) forgot about this. > > > Maybe a VACUUM FULL fullowed by a REINDEX will have solved your problem. > > > > So these would have reordered the data for faster sequential > > access which is not the case of VACUUM ANALYZE? > > VACUUM ANALYZE won't help you if your database is completely bloated. What do you mean exactly by bloated? If you mean that there is a lot of (unused) data, the thing is that our trim removed most of it. I was kinda hoping that after analyzing the database, the old data would exit the whole picture, which obviously wasn't the case. About REINDEX: is it ok to consider that REINDEX is to indexes what VACUUM FULL is to table data, because it cleans up unused index pages? > And AFAICS you're not running it on a regular basis so your database > was probably completely bloated which means: > - bloated indexes, > - bloated tables (ie a lot of fragmentation in the pages which means > that you need far more pages to store the same data). I suppose that table fragmentation occurs when DELETE are interleaved with INSERT? > The only ways to solve this situation is either to dump/restore or run > a VACUUM FULL ANALYZE (VERBOSE is better to keep a log), and > eventually reindex any bloated index (depends on your situation). Ok. > > > When the free_space_map is to low, VACUUM ANALYZE should have told you > > > via a warning (at least, if your logging is set appropriately). > > > > Unfortunately, we didn't keep the logs of VACUUM ANALYZE, so I > > can't be sure :/ > > You should really run VACUUM ANALYZE VERBOSE on a regular basis and > analyze the logs to be sure your VACUUM strategy and FSM settings are > OK. VACUUM ANALYZE is normally run overnight (each night). Is it not regular enough? There can be hundreds of thousands of statements a day. -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http://mobilefriends.ch/
