Slow insert and update commands during mysql for red sort

Question

I am trying to make a replication server from MySQL to redshift, for this I am parsing a MySQL bing file. For initial replication, I take a dump of the mysql table, converting it to a CSV file and uploading it to S3, and then use the redshift copy command. For this, performance is effective.

After the initial replication for continuous synchronization, when I read binlog, inserts and updates should start sequentially, which is very slow.

Is there anything that can be done to improve performance?

One possible solution that I can think of is to wrap statements in a transaction, and then send the transaction right away to avoid multiple network calls. But this will not solve the problem that one update and insertion of statements in redshift is very slow. One update statement takes 6 seconds. Knowing the redshift constraints (that it is a columnar database and inserting one row will be slow), what can be done to circumvent these constraints?

---

Edit 1: Regarding DMS: I want to use redshift as a warehousing solution that constantly replicates our MYSQL, I do not want to denormalize the data, since I have 170+ tables in mysql. During ongoing replication, the DMS shows many errors several times a day and completely fails in a day or two, and it is very difficult to decrypt the DMS error logs. In addition, when I delete and reload tables, it deletes existing tables in redshift and creates a new table, and then begins to insert data that leads to downtime in my case. I wanted to create a new table, and then switch the old to the new and delete the old table

Answer 1

Here is what you need to do to get DMS to work.

1) create and run the dms task using "transfer and current replication" and "Falling tables to the target"

2) it probably will not succeed, do not worry. stop the dms task.

3) at redshift, make the following changes to the table

• Change all dates and timestamps to varchar (since the used dms parameters for the red copy cannot handle the dates '00: 00: 00 00:00 'that you get in mysql)
• change all bool to varchar - due to an error in dms.

4) on dms - change the task to "Truncate" in "target table preparation mode"

5) restart the dms task - full reboot

now - the initial copy and current binlog replication should work.

Make sure you are using the latest version of the replication instance

Make sure you follow the instructions here.

http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.MySQL.html

If your source is aurora, also make sure binlog_checksum is set to "none" (poor documentation)