raghavendra t <raagavendra.rao@xxxxxxxxx> wrote: > System Config > --------------------- > CPU - Intel* Xenon* CPU > CPU Speed - 3.16 GHz > Server Model - Sun Fire X4150 > RAM-Size - 16GB The disk system matters a lot, too. How many drives do you have in what RAID configuration(s)? > My question is something like Steve's, why we should not do > reindexing as our maintenance task. If your VACUUM policy is good, the REINDEX should not be necessary. A good VACUUM policy, in my experience, usually involves setting it to VACUUM any table in which 20% or more of the rows have changed (with autovacuum_vacuum_threshold set pretty low). Cut those about in half for the autovacuum ANALYZE trigger point. You may need to use cost limits to avoid a hit on the production workload when autovacuum kicks in. If you do need that, I've found a 10ms naptime is adequate for us. Then, try running VACUUM ANALYZE VERBOSE *nightly* (again, with cost limits if needed to avoid impact on other processes). Capture the output, as it can be used to find where you have bloat problems. Monitor the last few lines to make sure your fsm (free space manager) settings are high enough -- it'll tell you if they're not. If you do this, you should be able to stop running REINDEX without any performance hit. There will be some dead space in the indexes, but this will likely help with performance of UPDATE and INSERT, as page splits will happen less frequently, and PostgreSQL won't have to constantly be asking the OS for more disk space. > I was doing reindex only to get a best fit and not fall short of > 90% hole, bcoz my table has lot of updates and deletes. We also > has the weekly maintance of VACUUM, but still reindex takes lot of > time. VACUUM won't help a lot with REINDEX time, since REINDEX has to read the entire table once per index and build everything up from scratch every time. If you VACUUM often enough, it is kept in good shape as you go. > Present Paramters settings > ---------------------------------------- > maintainence_work_mem - 1GB > Checkpoint_segment and Wal_buffers are default values. You will probably benefit from increasing those last two. Is everything else at the default? There are a few others which almost always need to be tuned to your run time environment. The defaults are designed to allow the server to start and run even on a very small desktop machine, so that someone's first "test drive" isn't marred by problems. When you gear up for production use, you normally need to tune it. > Kevin, Pierre, Greg, Steve, Hannu, Jorge ----- Thank you for > your wonderfull support and giving me the correct picture on > REINDEX on this thread. I appoligies if i couldnt have shared the > proper information in resolving my issue. Is the above information > provided by me will help out in tuning better. I'm starting to get a better picture of the environment. I really think that if you modify your VACUUM policy you can drop the REINDEX and be in much better shape overall. If you provide information on your disk subsystem, show us what your postgresql.conf file looks like (with all comments stripped out), and give us a general idea of the workload, we might be able to suggest some tuning that will help you overall. And you might think about when and how to upgrade -- autovacuum is something which has been getting better with major releases, and performance in general has been improving markedly. I hope this helps. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
