I am using an AWS RDS server to host my MySQL DB.
I work for a footfall company, we have our system installed all over the UK and every time a visitor is seen we write to the DB. We also provide a portal to our customers allowing them to view all the data as readable reports. Customers only use the portal once a week on average so there aren't anywhere near as many reads on the DB as writes.
The graph above shows the read and write IOPS as you can see the write IOPS is loads higher then read IOPS.
On the portal we don't show any of todays stats and at midnight every night we run a load of cron jobs which summarises all the data.
I am thinking of separating my RDS Server into 2. One would contain all the summarised data which the portal would read from to show the stats. The other would contain all of today's data and it would constantly be written to. At midnight the cronjobs would them summarise the data from the DB containing today's data and store the summarised data in the portal DB.
The idea behind this is to make the portal as fast as possible by removing all the writes.
I am no MySQL guru so this may have no effect, the RDS server can handle all the writes and reads to the DB with the current specs. If the current RDS server can handle the requests I am presuming this won't have an effect?
We are currently just trying to optimise our performance so any advice is really helpful.