Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance?

Question

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?

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

• Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?
• Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
• Jul 21, 2012 : InnoDB - High disk write I/O on ibdata1 file and ib_logfile0
• Sep 20, 2011 : Multi cores and MySQL Performance
• Sep 12, 2011 : Possible to make MySQL use more than one core?

• Ranknoodle: Rolando are you available for consulting?
• tesla747: @RolandiMySQLDBA when you say physical cores for buffer pool instances, what would I do if I'm virtualized?