We have a mysql database that has roughly 80 gigabytes (GB) of data using inno_db engine. We are looking to increase performance on the database (we looked at the slow log and gone through and optimized the db and indexes) and one avenue is to get better hardware.

Currently our mysql DB is on a 6 year old server with 16GB of mem with 2 x Xeon 2.00GHz (2 cores) with the ability to have 32 GB mem max.

We are looking to buy a new server for the mysql database with the following specs:

Dell PowerEdge R420 - Highlight specs

  • 2 Intel Xeon E5-2450 2.10GHz, 20M Cache
  • 128 GB RAM
  • 400GB Solid State Drive

So if the database is 80 GB and we have 128 GB of Memory on this new server my question (s) are:

Can we put the full database into memory and see performance gains?

Would setting the innodb_buffer_pool_size to 88GB effectively be the same as putting the "the DB in memory"?

Can mysql take advantage of this memory? Are there any limitations on this on the mysql side?

Would there be any pitfalls of loading the full mysql in database or having such a large innodb_buffer_pool_size?

1 Answer 1


I have a client at my employer's company with 192GB RAM using 162GB Buffer Pool.

Here is what we did for the client

  • InnoDB Tuning : I set the following options
    • innodb_read_io_threads (maxed it to 64)
    • innodb_write_io_threads (maxed it to 64)
    • innodb_thread_concurrency = 0 (It's default now in MySQL 5.5/5.6)
    • innodb_io_capacity (set it to IOPs of Disk, for this client, 20000 [2-0-0-0-0 not typo])
    • innodb_buffer_pool_size=162G
    • innodb_buffer_pool_instances=2 (Number of Physical Cores)
  • OS Tuning (numactl --interleave=all)

If you forget numactl --interleave=all, the buffer pool experiences swapping.

Here are my past posts on tuning InnoDB

  • Rolando are you available for consulting?
    – Ranknoodle
    Commented Apr 23, 2013 at 15:58
  • @RolandiMySQLDBA when you say physical cores for buffer pool instances, what would I do if I'm virtualized?
    – tesla747
    Commented Nov 19, 2016 at 3:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.