Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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

share|improve this answer
    
Rolando are you available for consulting? –  Ranknoodle Apr 23 '13 at 15:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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