524011 - [AMO] Increase innodb_buffer_pool

Status: RESOLVED FIXED

(Infrastructure & Operations Graveyard :: WebOps: Other, task)

Description

[Jeff Balogh (:jbalogh)]

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]]

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]]

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)]

(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)]

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]]

ok, we should probably bump that one back down again then.