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.
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 !!!
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.
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.
