align-toparrow-leftarrow-rightbackbellblockcalendarcamerachatcheckchevron-downchevron-leftchevron-rightchevron-small-downchevron-small-leftchevron-small-rightchevron-small-upchevron-upcircle-with-crosscrosseditfacebookglobegoogleimagesinstagramlocation-pinmagnifying-glassmailmoremuplabelShape 3 + Rectangle 1outlookpersonplusImported LayersImported LayersImported Layersshieldstartwitteryahoo

Re: [brisbane-webtech] MySQL Innodb Pool Size

From: Arjen L.
Sent on: Tuesday, October 30, 2012 10:31 AM
Hi all

Wizards don't know the specific profile of your application, and neither does any article or book.
Such tools are definitely good to learn more about sane baseline configurations for a production system, but from there you always need to tune appropriately for your specific situation - and that will be a moving target, tuning is not a one-off thing. Your dataset, site usage and user behaviour will evolve over time.

The only issue with an InnoDB buffer pool that's "too large" is that it uses RAM that could otherwise be used for other buffers. So if you have an abundance of RAM, it's not an issue. But few of us do. So settings always reflect a trade-off.

It's not necessary to reduce disk I/O to nada, so fitting 100% of your (active) dataset+indexes in memory is not really the point - a bit of disk I/O is ok.
But it's also important to consider other settings that are affected by the size of your buffer pool. If you don't configure your InnoDB log files appropriately, you'll still incur a lot of disk I/O - and if you're not using the appropriate version of MySQL/MariaDB (or with incorrect settings) you'll see a significant performance dip whenever there is a switch of log files and the server needs to flush dirty buffer pages so it can clear the other log.

Also, depending on your settings, you'll find that startup takes quite long as the server will need to process the redo log, load the pages that were changed and apply those changes again - if you keep lots of pages in memory, it's good to tune things such that there's a bit of I/O both for reads and writes and that the settings are such that both that and the startup time don't go up sharply.

It's the kind of stuff Open Query does for clients, and of course training.

Cheers,
Arjen.


> Nice wizard
> 
> 
> On Mon, Oct 29, 2012 at 5:00 PM, Peter van Dijk < [address removed] >
> wrote:
> 
> Percona have a pretty good tool for configurating stuff at
> https://tools.perc...­
> 
> I'd suggest avoid taking the advice of random people on the internet,
> and do some tests with anywhere from 8GB to 11GB yourself prior to
> putting it in production.
> 
> 
> On Mon, Oct 29, 2012 at 4:24 PM, Mischa < [address removed] >
> wrote:
> 
> Hi WebTechs,
> 
> How large is too large to increase the Innodb pool size?
> We were running it at 2.5Gig and now we have increased to 8Gig the
> system has 12gig of ram.
> 
> Could we increase again?
> 
> Regards
> Mischa


-- 
Exec.Director @ Open Query (http://openquery....­) MySQL services
Sane business strategy explorations at http://upstarta.c...­
Personal blog at http://lentz.com....­

Our Sponsors

People in this
Meetup are also in:

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy