As I remember, effective_cache_size is a query planner hint. It helps the QP decide whether or not to use an index in place of a sequential scan for queries of middling selectivity (say, 1-3%.) It doesn't actually affect the cache size, and it's nontrivial to imagine a query which would actually be degenerate in this case.
That said, I am always interested to hear about real queries where the query planner is making poor decisions. This is, sadly, not very low hanging fruit. Regards, Peter On Jan 13, 2011, at 9:02 PM, Carson Gross <[email protected]> wrote: > John > > It's hard to simulate production loads of the particular queries I'm > concerned with, so I was hoping that we could tweak stuff and see. > > We've got out app pretty tuned up with indexes and all the rest, at > this point I'm just looking for any last bits of low hanging fruit, > er, laying around. (We are running a ronin database, so kicking that > up is another obvious lever to pull.) > > The two params I mentioned seem like the potentially big wins, > effective_cache_size because it *should* if I understand boost the > memory that postgres will dedicate to queries across the board (don't > know how it interacts w/ work_mem) and checkpoint segments because > we've got one table that is appended to quite frequently and in lar > volumes. Unfortunately, without trying it out in production, it will > be hard to know what the benefits are. > > I'll let you know if we get anywhere. > > Cheers, > Carson > > On Jan 13, 2:05 pm, John McCaffrey <[email protected]> wrote: >> I've been working on postgres tuning for an app recently, and I was >> wondering if you were able to change these configs locally and see a >> meaningful improvement. >> >> I've seen improvements of large queries with sorts, by increasing work_mem >> (which can be done within your session), but other settings always seem to >> take a good deal of fiddling with, in order to get them to make a >> difference, so I'm wondering if you've already proven that this change will >> help or not. >> >> (it might be bold of me to assume that you'd pull down your prod data from >> heroku, and fire up a local postgres to tune your queries against) >> >> Here's a good list of postgres tuning options that you can do >> nowhttp://robots.thoughtbot.com/post/2638538135/postgresql-performance-c... >> >> I'd be interested in any of the things you've already done to improve your >> performance on heroku, The more we share the better things are for all of >> us. >> >> -John >> >> On Tue, Jan 11, 2011 at 11:48 AM, Carson Gross <[email protected]>wrote: >> >> >> >> >> >>> Hey Guys, >> >>> Any further thoughts on this? >> >>> Thanks, >>> Carson >> >>> On Jan 7, 1:35 pm, Carson Gross <[email protected]> wrote: >>>> Sure. We've got a big, ugly database that we are constantly slamming >>>> data into (nearly constant appends on one table, with occasionally >>>> purges.) We'd like to increase the checkpoint segments to see if that >>>> boosts write performance. >> >>>> On the cache side, according to this website: >> >>>> http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server >> >>>> "Setting effective_cache_size to 1/2 of total memory would be a normal >>>> conservative setting, and 3/4 of memory is a more aggressive but still >>>> reasonable amount." >> >>>> Again, as near as I can tell, the default in Heroku is to set it to >>>> 200MB. We are running a ronin database, which should have 1.7 Gigs of >>>> RAM, giving 850MB to 1.3 GB of cache as the ideal, at least according >>>> to that wiki entry. >> >>>> Our goal is to extract maximum performance with minimum effort and bug >>>> creation and, therefore, tweaking some postgres parameters would be an >>>> ideal way to do so. Let me know if you need any more details on our >>>> use case. >> >>>> Thanks! >>>> Carson >> >>>> On Jan 7, 12:40 pm, Matthew Soldo <[email protected]> wrote: >> >>>>> Hi Carson, >> >>>>> We don't support tuning the dedicated database. >> >>>>> I'd love to hear more about your requirements and needs around this. >>> It's >>>>> possible that this could be supported in the future. >> >>>>> Matt >> >>>>> On Fri, Jan 7, 2011 at 10:50 AM, Carson Gross <[email protected]> >>> wrote: >>>>>> Is it possible to tune a dedicated postgres database? In particular, >>>>>> we'd like to change the effective cache size and checkpoint segments >>>>>> setting from the defaults (appear to be ~200MB and 40, respectively.) >> >>>>>> Cheers, >>>>>> Carson >> >>>>>> -- >>>>>> You received this message because you are subscribed to the Google >>> Groups >>>>>> "Heroku" group. >>>>>> To post to this group, send email to [email protected]. >>>>>> To unsubscribe from this group, send email to >>>>>> [email protected]<heroku%[email protected]> >>> <heroku%[email protected]<heroku%252Bunsubscribe@googlegroups. >>> com> >> >>>>>> . >>>>>> For more options, visit this group at >>>>>> http://groups.google.com/group/heroku?hl=en. >> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "Heroku" group. >>> To post to this group, send email to [email protected]. >>> To unsubscribe from this group, send email to >>> [email protected]<heroku%[email protected]> >>> . >>> For more options, visit this group at >>> http://groups.google.com/group/heroku?hl=en. >> >> -- >> -John > > -- > You received this message because you are subscribed to the Google Groups > "Heroku" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/heroku?hl=en. > -- You received this message because you are subscribed to the Google Groups "Heroku" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/heroku?hl=en.
