Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up.
Sign up to join this community
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!
Since you are using MySQL, you need to measure off memory of two storage engines
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.
