Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

Thanks!

share|improve this question
    
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

1 Answer 1

up vote 3 down vote accepted

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

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

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.

share|improve this answer
    
Excellent, thanks for the very thorough response! –  David Jul 9 '12 at 21:30

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.