Last Comment Bug 524011 - [AMO] Increase innodb_buffer_pool
: [AMO] Increase innodb_buffer_pool
Status: RESOLVED FIXED
10/29/2009 @ 9pm
:
Product: Infrastructure & Operations
Classification: Other
Component: WebOps: Other (show other bugs)
: other
: All Other
: -- normal (vote)
: ---
Assigned To: Dave Miller [:justdave] (justdave@bugzilla.org)
: matthew zeier [:mrz]
Mentors:
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2009-10-22 18:33 PDT by Jeff Balogh (:jbalogh)
Modified: 2013-10-09 10:29 PDT (History)
4 users (show)
justdave: needs‑downtime+
See Also:
Due Date:
QA Whiteboard:
Iteration: ---
Points: ---
Cab Review: ServiceNow Change Request (use flag)


Attachments

Description Jeff Balogh (:jbalogh) 2009-10-22 18:33:53 PDT
From bug 522112 comment 0:
> Per recommendation from Percona, our addons DB would perform best with at least
> 10 GB of innodb_buffer_pool and at least a few gigs of index cache.  To achieve
> this, we need to get all of the servers up to 16 GB of RAM.

We have the RAM now, let's use it. :)
Comment 1 Dave Miller [:justdave] (justdave@bugzilla.org) 2009-10-25 20:50:06 PDT
Master database will need downtime for a mysqld restart (should be fairly instantaneous, but might throw a few spurious errors to web clients when it happens)

Slaves can be done while pulled from the load balancer pool, so can be done whenever.  They all need to be done.

I know innodb_buffer_pool needs to go to 10G.  There's a couple other memory-related settings that needed to be tweaked, too, and I still need to research on those.
Comment 2 Dave Miller [:justdave] (justdave@bugzilla.org) 2009-10-29 21:51:23 PDT
OK, this is done.

Made the following changes:

innodb_buffer_pool_size=10G (up from 2G)
innodb_flush_method=O_DIRECT  (wasn't present before - based on Percona recommendation)
key_buffer=1G   (up from 300M)
query_cache_size=1G (up from 128M)
Comment 3 Jeff Balogh (:jbalogh) 2009-10-30 10:03:32 PDT
(In reply to comment #2)
> query_cache_size=1G (up from 128M)

"Be cautious about sizing the query cache excessively large, which increases the overhead required to maintain the cache, possibly beyond the benefit of enabling it. Sizes in tens of megabytes are usually beneficial. Sizes in the hundreds of megabytes might not be."

http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

I'm not sure how you profile the effectiveness of the query cache, but we should be mindful of this.
Comment 4 Dave Dash [:davedash, :dd] (assign all bugs to mbrandt) 2009-10-30 10:04:57 PDT
Is there really a point to using the query cache?  Our data gets cached in memcache, the query cache isn't buying us much.
Comment 5 Dave Miller [:justdave] (justdave@bugzilla.org) 2009-11-03 14:45:59 PST
ok, we should probably bump that one back down again then.

Note You need to log in before you can comment on or make changes to this bug.