Re: Config parameters

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> So, on a 4 Gig machine you could divide 1G (25%) by the total possible
> connections, then again by the average number of sorts you'd expect per
> query / connection to get an idea.  

Thanks for the advice.  I'll experiment with higher work_mem settings,
as I am regularly doing sorts on large datasets.  I imagine the default
setting isn't very optimal in my case.


> Did you turn off stats collection as well?  That's really the major
> performance issue with autovacuum, not autovacuum itself.  

I did turn off stats collection.  I'm not sure how much of a difference
it makes, but I was trying to squeeze every ounce of performance out of
the database.  


> I.e. the cure may be worse than the disease.  OTOH, if you don't delete
> / update often, then don't worry about it.

I hardly ever delete/update.  I update regularly, but only on small
tables so it doesn't make as big of a difference.  I do huge inserts,
which is why turning off stats/autovacuum gives me some performance
benefit.  I usually only do deletes nightly in large batches, so
autovacuuming/analyzing once an hour works fairly well.


> Haven't researched temp_buffers at all.

Do you usually change temp_buffers?  Mine is currently at the default
setting.  I guess I could arbitrarily bump it up - but I'm not sure what
the consequences would be or how to tell if it is set correctly.


> random_page_cost is the hardest to come up with the proper setting.  

This definitely sounds like the hardest to figure out.  (since it seems
to be almost all trial-and-error)  I'll play with some different values.
 This is only used by the query planner right?  How much of a
performance difference does it usually make to tweak this number?  (i.e.
how much performance difference would someone usually expect when they
find that 2.5 works better than 4?)


> While you can't
> change buffers on the fly, you can change work_mem and random_page_cost
> on the fly, per connection, to see the change.

Thanks for the advice.  I was aware you could change work_mem on the
fly, but didn't think about setting random_page_cost on-the-fly.


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux