I received a recommendation from my hosting provider noting that a general rule in production server management is to ensure that the memory on your database server is larger than the size of your total database.

Our database is 1800mb (and growing) and we're being told to purchase a 2GB server. This didn't sit right with me, but I'm not an expert in production server management. We're running a MySQL db with mostly InnoDB tables and some MyISAMs.


  • This question is unanswerable because we have no idea what your data usage patterns are like. It's always good to load as much of the DB into memory that you can, but if a significant percentage of it is archived data that you don't use regularly, why bother? The only way you'll know is to test. Test with 2GB, test with 4GB, and so on. Plan for growth too. It's 2GB now, but what about next July? – user3914 Jul 9 '12 at 19:58
  • I guess this could probably potentially work on 256MB RAM as well. Or ask them for a highly available cluster of 10 256MB RAM servers. 40 cores, 2.5GB of RAM, 100mbit network should be OK. – Andrew Smith Jul 9 '12 at 20:36
  • Your hosting provider's either trying to scam you into buying more server than you need, or they're incompetent. Either way, find a new one. – womble Jul 10 '12 at 3:44

Since you are using MySQL, you need to measure off memory of two storage engines

  • InnoDB
  • MyISAM

Each Storage Engine has different caching characteristics. I wrote about this in the DBA StackExchange back on April 14, 2011.


InnoDB will cache data and index pages into its Buffer Pool

This query will tell you the ideal size for innodb_buffer_pool_size in GB

SELECT SUM(data_length+index_length)/POWER(1024,3) RecommendedBufferPoolSize
FROM information_schema.tables WHERE engine='InnoDB';

I would also make sure InnoDB uses innodb_file_per_table.


MyISAM will cache index pages into its Key Cache

This query will tell you the ideal size for key_buffer_size

SELECT SUM(index_length)/POWER(1024,3) RecommendedKeyBufferSize
FROM information_schema.tables WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','performance_schema','mysql');

Put these two number together and multiply by 1.1 (to accomoodate DB Connections, buffers for sorting, reading, joining). This should be the amount of RAM needed. In many cases, there may simply be too much data to house in memory.

As a rule of thumb, if you have all InnoDB data, then innodb_buffer_pool_size should be no more than 80% of installed RAM (I prefer 75%).

From here I recommend you just use common sense. Just because you have 10G of InnoDB data and indexes does not mean all of it will sit in RAM, except, of course, you run every SELECT on all tables using every InnoDB table and every InnoDB index.

In your case, if you do not know which storage engine you will be initially using, or if the amount of data far exceeds installed RAM, I would just use 65% of RAM for InnoDB and 10% of RAM for MyISAM.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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