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.

Currently we have a database that is 35gb in size and is running on a Virtual Machine with 2GB of RAM. The database is expected to increase at a rate of 3 to 4Gb per day continuously.

What are the suggested hardware specs for this type of setup? CPU and RAM allocations, etc.

I've also created a similar question about mysql scaling. Scaling MySQL Database But haven't tried yet the suggestions since I'm focusing on mysql optimization.

share|improve this question

3 Answers 3

I would suggest right away using MySQL 5.5 with the following:

innodb_file_per_table <- always enable this
innodb_io_capacity=65536
innodb_read_io_threads=64
innodb_write_io_threads-64
query_cache_size=0 (Default : Disbale your query cache)

Until MySQL 5.5 gets mature, do not use the multiple buffer pools.

How much RAM you need depends on two(2) things

For MyISAM, this query shows the key buffer size your system should be set to
SELECT CONCAT(CEILING(ndxsum/POWER(1024,2)),'M') RecommendedKeyBufferSize FROM (SELECT SUM(index_length) ndxsum FROM information_schema.tables WHERE engine='MyISAM' and table_schema NOT IN ('information_schema','mysql')) A;

For InnoDB, this query shows the innodb buffer pool size your system should be set to
SELECT CONCAT(CEILING(innodbsum/POWER(1024,2)),'M') RecommendedInnoDBBufferPoolSize FROM (SELECT SUM(data_length+index_length) innodbsum FROM information_schema.tables WHERE engine='InnoDB') A;

To get a complete picture of what you have as far MySQL Data, run this query:

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,index_length XSize,data_length+index_length TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

Set (SELECT 0 pw) BB For Bytes
Set (SELECT 1 pw) BB For KiloBytes
Set (SELECT 2 pw) BB For MegaBytes
Set (SELECT 3 pw) BB For GigaBytes
Set (SELECT 4 pw) BB For TeraBytes
Set (SELECT 5 pw) BB For PetaBytes (email me if you start using this)

The maximum key_buffer_size is 4GB, so do not surpass this.

The maximum innodb_buffer_pool_size should be 75-80% or installed RAM

Always set innodb_log_file_size to 25% of innodb_buffer_pool_size

The maximum innodb_log_file_size is 2047M (InnoDB will not work with 2G or 2048M, you can check the MySQL source code)

With these things in mind, you should be able to forecast the settings you need in /etc/my.cnf and for your hardware.

Give it a Try !!!

share|improve this answer
    
I forgot to mention, we are already using MySQL 5.5. thanks. –  Jonar Mar 14 '11 at 2:00
    
I ran the query to check for the recommended innodbufferpool size, and it gave me this value:30465M. Should I follow that or just stick to this rule:"The maximum innodb_buffer_pool_size should be 75-80% or installed RAM"? –  Jonar Mar 14 '11 at 2:16
    
This depends entirely on 4 things: 1) the amount of RAM on your DB server, 2) the sum of MyISAM indexes to size up you r key cache, 3) how much memory you want the OS to have at its disposal, and 4) is the DB server a dedicated DB server or does it also run apache, munin, or other tools ? –  RolandoMySQLDBA Mar 14 '11 at 15:36
    
About 1.5 years ago, I read a nice article on why not to MySQL to a Cloud Environment ( mysqlperformanceblog.com/2009/07/13/… ). If you have 30GB of InnoDB, you should consider getting MySQL out of the Cloud and back to bare metal. However, if most of the InnoDB data is strictly archival, convert what is not your working dataset to MyISAM or, better yet, to the ARCHIVE storage engine. Then rerun the diskspace report query. If you cannot scale back the working set under 1.5GB, get MySQL out of the Cloud. –  RolandoMySQLDBA Mar 14 '11 at 15:47

hmm, so in a years time you'll have a 1.3 Tb database.

You're right - one small VM is not going to handle it.

Assuming this is going to be used for more than just simple logging, you need a lot processor/memory - although you've given no information about how the system is likely to be used.

Forget about high-end hardware, you need to jump straight to a commodity cluster system. I'd recommend starting with 4 boxes so you can get to grips with both sharding and replication from the start. 4Gb memory (although this is where you should be flexible - adding memory is a lot easier than CPU / NICs / disk bandwidth) and dual physical cores, and mirrored local storage would probably be a sensible starting point. They should be isolated on their on 1Gb LAN segment, and jumbo frames is probably a good idea.

Although you might consider moving to a NoSQL database - but again with similar hardware spec.

share|improve this answer

Obviously you've given us very little detail to work on but if you're looking at buying your own hardware then I'd suggest that a really standard dual-CPU system, initially with a single mid-range E56xx series Xeon, either 3 x 2GB or 3 x 4GB of 1333/1066 DDR3 memory, 2 or 4 1Gbps NICs, a hardware RAID controller with a mirrored pair of either small cheapo (147-300GB) 7.2krpm SATA or 10krpm SAS boot disks then a RAID 10 array of either 4 decent quality 7.2krpm 2TB disks or 4 x 600GB 10/15krpm SAS disks. That should hit most if not all of your performance and resilience requirements plus give you a very quick and easy way to double your CPU capacity and also add a lot more memory as required, plus a reasonable set of disks too.

People like HP, Dell, IBM and supermicro sell this kind of machine the most so there should be lots of good deals around.

Come back to us if you have more details ok.

share|improve this answer

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.