I have one Mysql InnoDb table with rush trafik on row update. The table is also frequently used for heavy select query (especially group by for summary report). As I know this heavy select query will interfere or reduce the update peformance. So i have an idea to mirror this table, so the heavy select query will do in this mirror table (delay 15 menit still accepted).

Fyi, this table have size approximately 10 GB, I have used mysql event (scheduled every 15 minute) to copy this table to another with "insert into select" query. Actually it need more than 60 second for copy process, but the update query still impacted when this event running.

So Is there any best/common practise for mirroring table mysql with minimum impact to currently query (especially update) process on the master table?

Note : I want to do on the same server

  • 1
    Use the built-in replication mechanism
    – Barmar
    Sep 20 '20 at 5:12
  • Are you talking about mirroring on the same server? Replication is for multiple servers.
    – Barmar
    Sep 20 '20 at 5:12
  • Yes, I want to do on the same server.
    – Ayip.Eiger
    Sep 20 '20 at 5:30
  • 1
    I'm not sure there's a simple method for mirroring tables. Database Administrators is the place to ask about server configuration.
    – Barmar
    Sep 20 '20 at 5:40
  • 1
    Why do you assume your select statement will interfere with updates? SELECTS are non-blocking and aquire no locks apart from a metadata lock preventing table structure modification. Have you measured a performance drop in inserts/updates while running a select?
    – danblack
    Sep 20 '20 at 7:05

Plan A: Speed up the Summary by building and maintaining a Summary Table: http://mysql.rjweb.org/doc.php/summarytables . In particular, this is likely to segregate the summary select, which will now touch only the summary table, from the updates. And it will

Plan B: Use replication on the same server. Be sure to have a different "Port" (such as 3307) for that Replica. Direct summary requests to that port. Continue to direct writes to the Primary (your current instance).

Plan C: If the "update" is just a "Likes" or "Visits" counter, then do the following to avoid some of the interference. Move that column to a separate table. The two tables would have the same PRIMARY KEY for ease of JOINing. The update will avoid interfering with any selects that don't need also need to counter.

Plan D: Plans A,B,C are independent; do a combination of them.

The repeated copying of the table will (I predict) be poor for both maintenance and performance.

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.