It is always wise to give database server all the memory you can give it. If you have 8GB box, give 6GB to MySQ. 2GB should be enough for operating system and various caches. Things get a bit more complicated if you still use MyISAM (which you really really should not).

Peter, referring to your initial comment that ‘innodb_buffer_pool’ should be cca 10% larger than your dataset size – this is a bit misleading – I’ve sees setups where this is blindly followed and then they end up with 60GB in innodb_buffer_pool on 16GB of RAM boxes. Better suggestion would be – if you’re only using InnoDB (which you really should!), give mysql cca 80% of the RAM your box has, making sure you have a gig or two for the OS and other thingies. (I know it is not nearly as simple as that as you should count in the max_connections, various sort buffers, etc, etc…)

PAE will solve the problem with a 32-bit system addressing more than 3.5G of system RAM, creating a lot more space for distinct process working segments. PAE by itself does not solve the problem of 32-bit programs accessing > 2G of useful RAM. That’s a totally separate question that involves some trickery from the people writing the software. Normal 32-bit applications can only “see” 32-bits worth of system RAM, regardless of how much the system actually has.

@Peter,

I’m playing around with buffer pool settings in a staging environment (and..okay, maybe a bit in prod). I set my buffer_pool size to 40000M on a box with 55G. I was expecting to see MySQL immediately allocate the entire 40G chunk so that it was a guaranteed allocation. That doesn’t appear to be the case. Could you be so kind as to elaborate on how innodb preallocates buffer space?

Best,

Wes

If you change innodb_log_size, you’ll have to remove the old log files manually so MySQL can create new ones. See also http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/comment-page-1/#comment-390952

Best regards,
Jochen

thx in the first place for your deep-going background information.

my question: i want to tune a big database. we recently upgrade to mysql 5.5.8 @ freebsd 7.1.
the main db is innodb, myisam is used for the mysql-system-tables(small-sized).

the production-innodb-database allocates now of about 50GB of filesystem-space, the dump is about 19GB big. i know that innodb – ibdata – files will never shrink, so i have to drop and import the data to get rid of this.
the host hast 16GB of RAM, most of this would be available to mysql because there is no other productive service running.

so far i have learned(thanx to your blog!) i definitely want to increase innodb_puffer_pool_size(which is set to just 386MB).
my question is: would i get an additional performance-benefit if i ‘refragment’ the ibdata-file by dropping / importing or would this be a useless exercise?

thx in advance!
harri

My Mysql server is heavily loaded, now 300 qps average.

It uses %50 Cpu in average and just 700MB of ram. My server has 8GB and it has over 3GB free. The slow query log seems fine. There are very few and not frequent ones.

I want to be sure that it is returning the cached results and do not touch the disk unnecessarily.

I think the linux OS caches the innodb file but can I trust on that? And is there any good practice to lower cpu usage through buffering or caching?

innodb_buffer_pool_size is set to default value. (8mb)

I have Innodb, MyIsam and Memory tables mixed.

Here is an output from a tuner script

INNODB STATUS
Current InnoDB index space = 238 M
Current InnoDB data space = 294 M
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 8 M

KEY BUFFER
Current MyISAM index space = 113 M
Current key_buffer_size = 192 M
Key cache miss rate is 1 : 63
Key buffer free ratio = 74 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 256 M
Current query_cache_used = 19 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 7.64 %
Current query_cache_min_res_unit = 4 K
Query Cache is 28 % fragmented

When i run the package to generate a report it takes 15 seconds to finish of the process. but normally in PARADOX(the same kind of data) it takes only 3 seconds. can anybody suggest me to fine tune the ini configuration. whatever changes done in innodb parameters the result stays same. even when i set innodb_buffer_pool_size = 48M instead of 1G the result is same. only difference i could find is when i make query_cache = 0 then the process is increased to 20 seconds instead of 15 seconds. so i set again query_Cache=64M. Can any body suggest me to adjust the configuration to keep my process time same as in paradox?

Thanks in advance

I now know we have a major issue with our innodb_buffer_pool_size configuration…..

Due to the fact that we are using managed virtual services we need to buy memory by chunks. So are there any stats, from ‘show innodb status;’ I assume, I can use to know if we are starting to make headway in the increases we will be making (by hiring more memory and allocating to innodb_buffer_pool_size). Of course we can do bench marking to know when something is improving, but it is always good to fall back onto stats from the system. We need to make the balance between size of the buffer and cost of memory.

Less importantly, when the innodb_buffer_pool_size is grossly increased (ie 100 times) is there anything else that we should consider? The main thing I can find is that innodb_log_file_size should be increased to about 25% of innodb_buffer_pool_size.

Thanks again,

Nick Whalan

Correct, you cannot set it higher than you have unless you have a swap file larger than that, which you could do but would be VERY inefficient…With that setup I would make sure I had good indexes on my tables. Without any MyISAM tables I would change the buffer pool somewhere in between 8-10 G (probably closer to 8, and then monitor system memory usage before raising more, because I’m careful like that).

Paul,

Completely depends on what you do with the database, and if you have MyISAM as well. You must leave at least 10% of your memory for your system to run smoothly, especially with that small amount of RAM.

If I were you I’d buy more RAM. At least double it, unless you’re on a 32 bit system, and then I would switch to a 64 bit system.

If you have to run with what you have, and you have no MyISAM, then you should be safe to set it at 1.5 G, although you will have to do the math to see how many connections you have at once and how much memory each connection takes up…I’m guessing you don’t have a lot of connections.

Peter,

Don’t mean to step on your blog, just seams you get a lot of the same type of questions, so I thought I’d reply…probably won’t happen again.

I have a dedicated server with 2 GB RAM running InnoDB and have a large database that is roughly 10 GB. How high should I set innodb_buffer_pool_size to be?

Thanks,
Paul

Thanks for the info, perhaps you could offer some advise for an innodb performance issue
i am having.

I’ve just inherited a WebRT Ticketing MySQL DB which is 123GB is size, there is a lot
of images in the db. Historically i’ve worked mainly with smaller clustered DB’s with NDB
so an InnoDB db of this size is new territory for me. I’ve run mysqltuner & below is some of the output:

[!!] Highest connection usage: 100% (101/100)
[!!] Query cache prunes per day: 26846
[!!] Temporary tables created on disk: 38% (1M on disk / 4M total)
[!!] InnoDB data size / buffer pool: 123.1G/8.0G

Variables to adjust:
max_connections (> 100)
wait_timeout (< 28800)
interactive_timeout ( 64M)
tmp_table_size (> 128M)
max_heap_table_size (> 128M)
innodb_buffer_pool_size (>= 123G)

The server itself has 16GB RAM, surely i cannot increase the innodb_buffer_pool_size more than the memory on the system.
Any thoughts to increase performance/decrease disk I/O or redesign idea ?

Regards

Ronald

The point is it is no use to set it a lot larger than your database size. For large databases you normally set buffer pool as much as possible/reasonable for amount of memory you have.

I’m pretty sure that I’m missing something here.