MySQL real-time scaling

To write this post I pushed the topic glix . So in short:
Amazon now provides a fault tolerant, scalable database service, i.e. our mysql is now in the cloud. You can read more in the article itself , so I will not retell it.

The only disadvantage of this decision is the price. I can’t say that Amazon’s services are cloudy :) are expensive, but when the server is running 24 hours a day, it can cost a lot. Sitting on DevConf I had an idea how to avoid it.

Decision

There are two DB servers, the first one - let's call it “default” - this is our server that frontend calls to, and the second “cloud” is the same cloud MySQL server. Task: when increasing the load on the main database server, turn on the cloud one, synchronize it with the main one and then redirect SQL queries to it.
')
The system will work in 4 modes.

Default

The mode of use of the main database server. When a load is exceeded by a threshold value (max), a certain time interval (max-timeout) set in the parameters, the system switches to the SyncCloud state

Synccloud

The mode of synchronization with the cloud-server.

1. We turn on the cloud database server through a request to the Amazon RDS API.
2. We are waiting for the server to load
3. We connect it as a slave to the main server.
4. We spend reclamation on the slave.
5. We block requests for record to the master.
6. We are replicating again. This must be done in order not to block the wizard immediately, so that there is no large time to lock the base.
7. Disable cloud servar from the wizard. Turn off mysqld on the main server.
8. We enable mysl-proxy on the default-server, which will forward all requests to the cloud-server.

Cloud

Mode of operation with a cloud-server.
In this mode, the default server proxies requests to the cloud server. On the main server, the load is reduced at this time, and after it reaches a value below the threshold (min) for a certain period of time (min-timeout), the system switches to the SyncDefault state

Syncdefault

Synchronization mode with default-server.

1. We start mysqld on a free port (not 3306, because we have mysql-proxy sitting there now).
2. We connect the default-server as a slave to the cloud-server.
3. We carry out replication on the default-server
4. Block write to cloud server
5. We re-replicate
6. Turn off mysql-proxy
7. We move the mysqld server to default to the standard port (3306), which freed mysql-proxy.
8. Send a request to shut down the cloud-server through the Amazon RDS API

Results

As a result, we obtain scalability at the expense of the cloud server and savings, due to its use only at the right moments.
Very interested in the opinion of habrovchan about the viability of such a scheme. In general, there are huge prospects in this area, especially if you manage to start MySQL on a cluster based on Ubuntu Enterprise Cloud (Eucalyptus)

Links

• Amazon Offers MySQL in the Cloud
• Oracle will continue to develop MySQL and create a Cloud Office
• MySQL Enterprise for Amazon EC2
• Joyent Accelerator for MySQL
  They say that they have “Speed ​​- 3X more Transactions per Second than MySQL on Amazon EC2.” I have not checked it myself yet.
• Cloud Computing Framework for XAP