On Mon, 2010-11-08 at 16:23 -0700, Scott Marlowe wrote: > On Mon, Nov 8, 2010 at 3:42 PM, Jason Long <jason@xxxxxxxxxxxxxxxx> wrote: > > On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote: > >> On Mon, Nov 8, 2010 at 11:50 AM, Jason Long <jason@xxxxxxxxxxxxxxxx> wrote: > >> > I currently have Postgres 9.0 install after an upgrade. My database is > >> > relatively small, but complex. The dump is about 90MB. > >> > > >> > Every night when there is no activity I do a full vacuum, a reindex, > >> > >> One question, why? > >> > >> > and then dump a nightly backup. > >> > >> Good idea. > >> > >> > Is this optimal with regards to performance? autovacuum is set to the > >> > default. > >> > >> that depends very much on your answer to the question of why are you > >> doing it and what you're trying to gain / work around with vacuum full > >> / reindex every night. > >> > > > > I have been doing this for several years. Since my database is small > > and it takes little time to do a full vacuum. I am doing the reindex > > because I thought that was recommended after a full vacuum. > > Definitely reindex after a full vacuum on previous versions (i.e. > before 9.0) I think with 9.0 vacuum full is like a cluster without any > reordering, so it likely doesn't need reindexing, but I've not played > with 9.0 much yet. > > > As the data has grown the system is slowing down. Right now I am > > looking at ways to improve performance without getting into the queries > > themselves because I am swamped with new development. > > OK, so it's a standard maintenance procedure you've been doing for a > while. That doesn't really explain why you started doing it, but I > can guess that you had some bloat issues way back when and vacuum full > fixed them, so doing it got kind of enshrined in the nightly > maintenance. > > > Is doing the full vacuum and reindex hurting or helping anything? > > It might help a small amount if you've got regular usage patterns. If > you routinely update whole tables over and over then it might be > helping. > > > Any other quick fixes that I can try? > > Increasing work_mem, shared_buffers, changing random_page_cost and / > or seq_page_cost. > > Log long running queries and run explain analyze on any that show up very often. > > But for real performance, you do often have to "get into the queries" > because an inefficient query may be something you can cut down to > 1/10000th the run time with a simple change, and often that change is > impossible to make by tuning the db, only the query can be tuned. It > might be something simple like you need to cast a type to match some > other type. Hard to say without looking. > > When a 90Meg database is slow, it's almost always poorly written / > non-optimized queries at the heart of it. > I stopped doing the nightly vacuum full and reindex. After 3 months some queries would not complete within 2 minutes. Normally these take less than 5 seconds. I tried vacuum without full and reindex, but the problem was still there. Only vacuum full and reindex returned performance to normal. Now I am back to my previous nightly full vacuum and reindex. Any suggestions? -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
