1

What are the methods or tools used to scale mysql database. We have mysql database that is increasing in size daily. We just allotted a fix hard drive size on it so my concern is that we will need to relocate or increase the HDD size sometime when the HDD is full in capacity.

Using RAID seemed to be a good idea but it is still fix in size. We need a scalable system for MySQL database.

Right now I can only think of Hadoop as a solution.

The ideal solution for us is when the HDD reaches its limit, we will just add another HDD on the machine then MySQL will recognize it as one, then we can't add anymore HDD on the server, a new server will be setup that will add to the total size of MySQL data storage space.

Jonar
  • 171
  • 4
  • 13

2 Answers2

3

Three most popular methods of scaling are master-slave replication, partitioning and sharding. Master-slave replication is only effective when you want to scale reads. It is not intended to scale a DB size or writes. Partitioning is a method of storing a big table in several physical locations (on several HDDs). Sharding can be considered as partitioning on a global level performed by the application, not by the MySQL server. Basically, sharding is a method of storing similar data across several not directly coupled MySQL databases. First two methods are supported by MySQL internally, sharding should be performed at application level by application developers. It looks like you should use sharding since partitioning can't spread across multiple servers and has certain limitations then.

Alex
  • 7,939
  • 6
  • 38
  • 52
  • 1
    In the posters case sharding does seem like the only option. – Sameer Feb 10 '11 at 08:09
  • @Sameer I tend to agree – Alex Feb 10 '11 at 08:20
  • I am looking to the solution of Partitioning, is it possible to a big table on several physical servers instead of just HDD? – Jonar Mar 14 '11 at 11:00
  • I can't imagine what you are storing in the database to make it grow so big so quickly - it should contain links to files, not the files themselves. Then you can play with nfs mounts or hadoop etc to achieve unlimited distributed storage, while the db can do what it does best with indexes. – Andy Lee Robinson Aug 10 '11 at 11:10
1

Clustering is generally used as a solution to performance, however my interpretation of what you've written is that its the size of the database which is the problem?

Using a NoSQL database is usually done for performance reasons (MySQL replication is simple to set up - so there's not a lot of difference in terms of availabiltiy) But you loose out on a HUGE amount of functionality. Hadoop is not going to solve your data volume problems, and may mean you've got a LOT of code to rewrite.

How you manage data volumes is vastly dependent on the nature of the application and the data - can you delete old data? Can you migrate it offline? Can you consolidate old data?

The ideal solution for us is when the HDD reaches its limit, we will just add another HDD on the machine then MySQL will recognize it as one

While you can simply add disk as a RAID-0 or JBOD array, this is a very messy practice - you're going from a single point of failure to multiple points of failure.

Similarly you can mount the disk and migrate individual table files over (using symlinks from the original locations) but this presupposes that you are not running a single-file innodb backend. This is just as bad as using RAID-0/JBOD.

For a transactional DBMS, mirroring (RAID-1) offers massive performance benefits - but does not increase storage volumes. So I would recommend planning a jump in storage from 1 to 4 drives (configured as a mirrored pair of strip sets).

symcbean
  • 21,009
  • 1
  • 31
  • 52