MySQL Storage – Exploring Extreme Storage Beasts

disk-structuresMySQLstorage

Intro
I have derived this question from my previous question Single Drive vs Multiple Drives

After getting a very detailed answer from a very helpful member RolandoMySQLDBA and learning the fact that multi drives can help a lot in some cases. I started to look into multi drives for my setup which is just a single bit compared to what I found.

More Info
I found quite huge storages like

http://www.newegg.com/Product/Product.aspx?Item=N82E16811152212

While some videos show even GBs of write speed of these kind of equipment

Real Question
Providing that there is enough RAM to hold indexes, Can MySQL or other open source RDBMS (not enterprise graded RDBMS like Oracle because its running on SAN is quite common) can run on these beasts easily?

BECAUSE they contain true multi drives setup and dozens of heads are available.

I am sure it would not be that simple because if it would have that easy then why most enterprise setups use commodity servers. So, what problems are faced using these extreme storage solutions?

Best Answer

All one has to do is run these queries:

MyISAM

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

InnoDB

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

These will recommend the right sizes for the MyISAM Key Cache for existing MyISAM and the InnoDB Buffer Pool for InnoDB data and indexes.

While running these SQL queries recommend the buffer sizes, the human factor must still kick in. We must plan data usage based on hardware and frequently accessed data. We must also ask: Of the amount of buffer recommended, how much of my working set will actually reside in memory?

If you configure MySQL to use 75% of RAM for InnoDB, even on a dedicated MySQL server, the OS will get a busy paging to disk. Just because MySQL can use huge numbers for buffer sizes does not mean it's OK to push the limits. Two have two basic storage engines to consider.

Use of commodity hardware merely demonstrates MySQL configurability with noticeable results. Unless you are using 32-bit RHEL (if you are, please stop using it right now, get plenty of coffee, go upgrade), raising buffer sizes in storage beasts can be well trusted.

For example, my employer has a client with 3 DB servers dedicated to MySQL only. Each has 192GB of RAM of which 162GB is the InnoDB Buffer Pool. The working set is actually 170GB. While there is a little swapping and paging going on, the DB performance is astounding. OS has about 30GB of RAM for itself (16GB of RAM disk for temp tables). I am sure that these servers are just little lizards compared to the komodo dragon-sized DB servers out there. MySQL can be up to the task if configured properly.

Keep in mind that InnoDB puts its own checks and balances in place because the current source code limits InnoDB log files to 4GB. This is the case because InnoDB was originally designed with commodity hardware in mind. In light of this, ACID transaction throughput could possibly bottleneck there regardless of the storage beast's hardware setup and the version of MySQL you choose to run.

CONCLUSION

It's OK to push limits on Big Beefy DB Servers. You should always do so with consideration to the OS in terms of Memory, Swap Space, Working Dataset, and number of transactions expected.

Related Question