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 large 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.
