AWS DMS – Tables Created with No Auto_Increment

The first thing that comes to mind is the Server Model : db.m1.large

What limiting factors are placed on a MySQL RDS?

If you spin up an Amazon RDS instance of MySQL, you would subject yourself to whatever constraints are given. All models of MySQL Amazon RDS have the same major options but differ in only two aspects

• max_connections
• innodb_buffer_pool_size

Here is a Chart I posted Last Month

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

I posted this in my other posts in the DBA StackExchange

• Local database vs Amazon RDS
• Scaling Percona datacenters: setup and replication

You are using m1.large. Since the InnoDB Buffer Pool is 3/4 of the Instance. That's means you have 7.2G but only 1.8 is usable. That model is capable of having up to 623 connections. Each connection can consume memory because of

• join buffers
• sort buffers
• read buffers
• thread info

Amazon RDS is simply micromanaging resources. Since DB Connections can consume RAM, connections are probably being disallowed due to the lack of RAM needed.

SUGGESTIONS

• Try reducing InnoDB log IO innodb_flush_log_at_trx_commit=0 during the mass creation
• Make sure you are not doing large transactions during any automatic backups or snapshots
• Try a bigger server model m1.xlarge or m2.xlarge

• I would suggest that you are 12ms away (latency) from the AWS datacenter. You are stuck with that overhead. Conclusion: Beware of the timings.

• The "<1ms" is realistic only if client and server are in the same datacenter and the disk is SSD. What is "magnetic storage"? Perhaps that is an old, spinning, disk. In that case 10ms is a reasonable guess for one write. And it is hard to determine from the available numbers what the latency really is. (Presumably less than 12ms.) Conclusion: Be sure to use SSDs.

• What does Amazon say about interference from other instances on the same hardware? You have a "small" instance; there are probably many others sharing the same I/O. If one of them has a spike, you might be slowed down. With "large", you might have fewer competing customers. And it might be SSD. Conclusion: Focus on average or 95th percentile, not spikes.

• innodb_flush_log_at_trx_commit=1 requires at least 1 disk write per transaction. =0 delays and combines writes, hence the noticeable speedup. One way to soften it is to batch multiple queries into a single BEGIN...COMMIT. (Only do such where the logic makes sense.) Conclusion: Bigger transactions. (However, you will still be stuck with 12+ms per transaction instead of per insert.)

• Updates to indexes (except for UNIQUE indexes) are delayed and can usually be treated as zero impact.

• char(21) COLLATE utf8_unicode_ci -- Is it always 21 characters? If not, use VARCHAR. Perhaps it is always ascii, not utf8? If so, use CHARACTER SET ascii.

• PRIMARY KEY (seqid), UNIQUE KEYid(id) -- Why have both? As already mentioned, it causes overhead included in the INSERT time, namely to verify uniqueness before committing.

• The activity seems to be very low. Normally people ask questions like yours when they are having trouble with hundreds or thousands of queries per second.

• Here's a way to get a 16-byte, chronologically ordered, UUID: http://mysql.rjweb.org/doc.php/uuid . (But not as easily displayed.)