1

I have been looking into this for a while and cannot find a suitable solution so would like to know if someone here can please help.

I am trying to set up database back-end that will assist with doing analytics. I need to stick with MySQL or MariaDB as this is what we currently use on all projects and queries.

My problem is that I have access to a few virtual machines. Most of them pretty decent, but they all have a range and capacity of 1-2TB. They cannot be increased in disk size as they have been provisioned and in use for other purposes as well.

As they are only used infrequently and sitting idle for large amounts of time I am trying to set it up as a cluster for a database.

Now, what I would like to know is, how do I enable these machines to be scaled such that I can use the hard drive space as a total.

I.e. I have Server 1 (1.5TB), Server 2 (1.0TB), Server 3 (1.8TB), Server 4 (1.5TB), Server 5 (1.2TB). I would like to shard/cluster them all together to add all the space together so that I can utilise the 7TB in total.

The reason for this is that the data being analysed is generally larger than what 1 of the servers can hold. I am not to worried about replication/consistency and systems being down (As it;s not business critical and any lost data can be recovered and re-imported easily). Although I am happy to use a Master/Slave arrangement if needed (In case something goes wrong).

I have look into the following and haven't found any one thing that answers my question...

  • MaxScale
  • Percona XtraDB Cluster
  • Galera Cluster

I have tried with the above packages, but most of the resources I can find tend to only talk about multi master or master slave systems. Or horizontal scaling for maximum read/write access.

I need a solution for vertically scaling by adding multiple machines together. Mostly for total disk space, less important for CPU, RAM etc.

3

What you're looking for is typically called shared-data architecture, where multiple database nodes access the same set of database files (a database) distributed across the nodes, and the database capacity approximates the sum of disk and CPU capacities of all nodes (more or less).

The MySQL cluster solutions you're referring to implement the shared-nothing architecture, where the database copies are replicated between nodes, and each database node works with its own "copy", so you're still limited by the disk and processor capacity of an individual node.

I don't think there exists a shared-data cluster solution for MySQL, as implementing it is a non-trivial endeavour -- just ask Oracle RAC folks.

If your workload allows, you might consider a cluster file system, such as CephFS or GlusterFS, which will present the sum of all servers' disk volumes as a single file system to a single database node. This will at least address your disk capacity requirement.

0
  • For analytics in Data Warehouse situations, I find that building and maintaining "Summary tables" is very effective -- one-tenth the size of data, 10x speedup for queries.

  • Would you care to share some info on the data and the analytics; I may have further ideas.

  • For making the disk space available to MySQL -- this is an OS technique. However, it might require that no table is bigger than will fit on one of the machines, and it would make MySQL somewhat fragile because all 5 servers would have to be up for MySQL to be up.

  • Clustrix may have a solution. Spider is another possibility; it is included with MariaDB.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.