Mysql – How to do safety mirroring innodb table on thesql

innodbMySQL

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

Best Answer

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.

Related Solutions

How to Improve InnoDB DELETE Performance

You could delete data in batches.

In SQL Server, the syntax is delete top X rows from a table. You then do it in a loop, with a transaction for each batch (if you have more than one statement, of course), so to keep transactions short and maintain locks only for short periods.

In MySQL syntax: DELETE FROM userTable LIMIT 1000

There are restrictions on that (can't use LIMIT in deletes with joins, for instance) but in this case you might be able to do it that way.

There is an additional danger to using LIMIT with DELETE when it comes to replication; the rows deleted are sometimes not deleted in the same order on the slave as it was deleted on the master.

Mysql – How to migrate large blob table from thesql to postgresql

I think that simplest way is to use that --hex-blob switch on mysqldump and restore by psql, with decode(string text, type text). However it's not that simple, because you need to change a little produced dump (sed, awk), adding that decode function. For example:

mysqldump -u root -p --skip-quote-names --hex-blob --skip-triggers \
    --compact --no-create-info mysql samples > prepg.dump

sed "s/0x\([0-9,A-F]*\))/decode('\1','hex'))/g" prepg.dump > pg.dump

psql session:

CREATE TABLE samples
(
    file_id integer PRIMARY KEY,
    file bytea
);

\i 'path/to/pg.dump'

Best Answer

Related Solutions

How to Improve InnoDB DELETE Performance

Mysql – How to migrate large blob table from thesql to postgresql

Related Question