How to achieve a responsive frontend while working closely with the base?

Question

MySQL

There is an application on Ruby On Rails, running on a mysql database in Amazon RDS. On instances, dozens of workers are spinning non-stop, collecting information from external sources and writing it to the database. At the same time, there is a frontend in which users can view the collected information and perform various manipulations on it, 99% read-only.
It happens that these same workers load the base quite heavily, which results in a rather slow operation of the frontend.
The absolute relevance of information for the user is not important, there may be delays up to several hours (depending on the data).
The idea arose to create some kind of separation, or layer, between these parts of the application, perhaps something like a buffer, which, at the cost of the relevance of information for the user, will provide him with a good speed of query execution.
Tell me, how reasonable is such a decision, is it feasible? Some specific advice, links, patterns would be useful. Unfortunately, I have absolutely no experience in this matter, so it is difficult to start somewhere, to find the right path.

Answer 1

If the base is heavily loaded and reflected on the front, I would work:
1. Indexes, for example, removed them, for MySQL Innodb greatly increases the insertion and then the work. Since the delays are not important, I would also collect this data into the database for the front (maybe not even mysql, for example, redis), but there are already indexes in the new database.
2. I would look in the direction of replication and the division of the base into a Master where you can write and a Slave from which you would lazily read.

Answer 2

You need to understand what it means to “load the database heavily” - these can be locks, data processing (read + write) or a large amount of write. RDS has Read Replica (http://aws.amazon.com/rds/mysql/#Read_Replica) - perhaps
RDS will help now at the beginning of development - it does not provide many opportunities (not access to binary logs).

Answer 3

The simplest thing you can try is to make 2 databases and set up master-> slave replication, read from the slave, write to the master.
Even within the same server, this can give a good increase (however, as a result, the load on the server itself will increase slightly), then you can rotate the indexes, you can also make different indexes on different master / slave servers. The main thing is to try to physically separate the storage for these bases.
You can periodically pause replication by increasing the interval between the actual data in the master and the analyzed data in the slave.
You can raise the slave on unreliable storage (tmpfs or enable forced write caching, for example, in the ext3 / ext4 mount options - data=writeback), this will increase the speed of copying data to the slave.