1

Turning on encryption on an AWS RDS MySQL instance results in significantly slower database dumps (using mysqldump).

mysqldump -u test -p -h xxx.aws.com > dump.sql

Is there a reason for significantly slower DBdumps on AWS RDS after turning on encryption?

1

mysqldump reads all of your data. If the encryption is at the disk, then it takes time to decrypt the data before mysqldump can access it. Hence, the total action is slower.

Do you have any metrics on how much slower (percentage)?

Also, since RDS does key management, there may be a slight, one-time, delay to get the key.

  • I don't have any clear metrics. I was curios if there is any way to speed it up (mysqldump tunning; mysql parameter tunning ?). AWS specified that the performance impact is minimal but I am seeing a 2x-3x increase in DB dump time. – user6386 Jun 12 '18 at 6:20
  • A guess: mysqldump is more I/O intensive than typical mysql usage; their "minimal impact" was based on the rest of mysql. – Rick James Jun 12 '18 at 16:17
0

Encryptions typically compress the data as their first step. It is not needed, but it is the common practice on various, mainly security reasons: in short, the compressed byte stream will be practically redundancy-free, like a random bytestream, which significantly reduces the possibilities of various cryptological attacks.

If you have a slow network connection to your aws VM, then the performance improvement due to the lesser data flow can be higher as the loss due to the CPU need of the compression (+ encryption). It depends also on the applied compression.

In other configurations (slow CPU + fast network) the transfer of the encrypted bytestream might be longer.

If it annoys you, check the CPU usage on the VM. You may play with the MySQL encryption/compression parameters on both of the server and the client side.

  • This seems like little other than speculation. There's no reason to believe that the data on an encrypted block device is compressed, and block device encryption is under discussion, here. There are no "encryption parameters" that are relevant to this issue on RDS. It's enabled, or it isn't. Similarly, compression on the client/server protocol is not tunable. – Michael - sqlbot Jun 12 '18 at 0:04
  • @Michael-sqlbot My impression was that the OP wants to dump a remote mysql content to their local machine. May I ask, how do you think that block device encryption is an important thing here? I think the question is entirely about a protocol-level encryption of the mysql. – peterh says reinstate Monica Jun 12 '18 at 7:02
  • @peterh - Good question. The RDS manual was sufficiently vague to be either block or wire encryption. Wire would be a lot simpler. – Rick James Jun 12 '18 at 23:58
0

The reason is that your mysqldump command is missing some parameters.

There's nothing related about turning Encryption on your RDS instance.

Please add --set-gtid-purged=OFF, --quick and --compress on command parameters that will boost your dump speed:

New mysqldump:

mysqldump -u test -p -h xxx.aws.com --set-gtid-purged=OFF --quick --compress > dump.sql

If you want your dump file to be in gzip format (small file size), so:

mysqldump -u test -p -h xxx.aws.com --set-gtid-purged=OFF --quick --compress | gzip > gziped_dump.sql.gz

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.