Hi Markus, Thanks for your message. > Guillaume Cottenceau wrote: > > > We noticed a slowdown on our application while traffic was kinda > > heavy. The logics after reading the docs commanded us to trim the > > enlarged tables, run VACUUM ANALYZE and then expect fast > > performance again; but it wasn't the case[1]. > > What exactly do you mean with "trim the enlarged tables"? We have a couple of logs files which get larger over time (millions of rows). As they are log files, they can be trimmed from older values. > > Out of the blue, we dumped the database, removed it, recreated > > from the restore, and now the performance is lightning fast > > again. > > > > Does it look familiar to anyone? I thought running VACUUM ANALYZE > > after a trim should be enough so that pg has assembled the data > > and has good statistical knowledge of the tables contents.. > > This looks like either your free_space_map setting is way to low, or you I don't know much about free_space_map. Trying to search in documentation, I found run time configuration of the two following parameters for which the current values follow: max_fsm_pages is 20000 max_fsm_relations is 1000 Do they look low? Notice: table data is only 600M after trim (without indexes), while it was probably 3x to 10x this size before the trim. Machine is a 2G Dell 1850 with lsi logic megaraid. > have index bloat. 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.. > 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? > It also might make sense to issue a CLUSTER instead (which combines the > effects of VACUUM FULL, REINDEX and physically reordering the data). I was reluctant in using CLUSTER because you have to choose an index and there are multiple indexes on the large tables.. > 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 :/ -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http://mobilefriends.ch/
