Skip to contentSkip to conversation

poor mysqldump performance

dm-crypt Tracy Reed 4 participants 10 messages Feb 23, 2012 – Feb 25, 2012

lore
Share & tools
lore

Tracy Reed

author
#0 lore raw

I am using dmcrypt/LUKS on:

CentOS 5.5
kernel 2.6.18-194.32.1.el5
MySQL 5.5.12
cryptsetup-luks 1.0.3
ext3

Doing a mysqldump from the LUKS/dmcrypt volume takes twice as long as it does from a non-encrypted volume. Only a few percent of the CPU are taken for the encryption so it isn't kcryptd maxing out the CPU. Most interestingly I notice that iowait goes from 90% for the non-encrypted db to 99% for the encrypted db. It really looks like a lot more IO is somehow generated.

This makes no sense to me as I understand dmcrypt to sit above the disk layer and do a block-for-block encrypt/decrypt of the data as it passes through. How could it possibly cause extra disk IO? At first I thought maybe it was disk alignment (which has bitten me many times before) but we are doing reads here, not writes. iostat confirms that during the mysqldump practically no writes are happening. I've also looked at increasing readahead to no effect.

I've googled and found that as long as dmcrypt isn't maxing out the CPU (for which latest kernels support AES-NI and multi-threaded kcryptd) the performance with and without encryption should be pretty much the same. I don't understand why my reads are taking such a hit.

Any ideas?

Signature · 1 line
Tracy Reed
#1 lore raw

I notice that you are using a really, really old kernel. 2.6.18 is from end of 2006 or so. There were some performance issues with the dm-layer, they might just still be in this old version.

With 3.2.5 I basically see that IO is CPU limited or going at full speed if the CPU is fast enough. I have not tries a MySQL dump though, although this should just be standard disk accesses.

Arno

Quoted 35 lines · Tracy Reed

I am using dmcrypt/LUKS on:

CentOS 5.5
kernel 2.6.18-194.32.1.el5
MySQL 5.5.12
cryptsetup-luks 1.0.3
ext3

Doing a mysqldump from the LUKS/dmcrypt volume takes twice as long as it does from a non-encrypted volume. Only a few percent of the CPU are taken for the encryption so it isn't kcryptd maxing out the CPU. Most interestingly I notice that iowait goes from 90% for the non-encrypted db to 99% for the encrypted db. It really looks like a lot more IO is somehow generated.

This makes no sense to me as I understand dmcrypt to sit above the disk layer and do a block-for-block encrypt/decrypt of the data as it passes through. How could it possibly cause extra disk IO? At first I thought maybe it was disk alignment (which has bitten me many times before) but we are doing reads here, not writes. iostat confirms that during the mysqldump practically no writes are happening. I've also looked at increasing readahead to no effect.

I've googled and found that as long as dmcrypt isn't maxing out the CPU (for which latest kernels support AES-NI and multi-threaded kcryptd) the performance with and without encryption should be pretty much the same. I don't understand why my reads are taking such a hit.

Any ideas?

Signature · 5 lines
Tracy Reed
_______________________________________________
dm-crypt mailing list
[email protected]
http://www.saout.de/mailman/listinfo/dm-crypt
Signature · 6 lines
Arno Wagner, Dr. sc. techn., Dipl. Inform., CISSP -- Email: [email protected] 
GnuPG:  ID: 1E25338F  FP: 0C30 5782 9D93 F785 E79C  0296 797F 6B50 1E25 338F
----
One of the painful things about our time is that those who feel certainty 
are stupid, and those with any imagination and understanding are filled 
with doubt and indecision. -- Bertrand Russell 
author
#2 lore raw

On Fri, Feb 24, 2012 at 12:10:43AM +0100, Arno Wagner spake thusly:

Quoted 4 lines

I notice that you are using a really, really old kernel. 2.6.18 is from end of 2006 or so. There were some performance issues with the dm-layer, they might just still be in this old version.

Yep. Unfortunately, this is in a big corporate production environment which is way behind and difficult to arrange an upgrade. As you no doubt are aware, 2.6.18 is the standard kernel in RHEL/CentOS 5. We really need to move to 6 for a lot of reasons but that is a long story.

Quoted 4 lines

With 3.2.5 I basically see that IO is CPU limited or going at full speed if the CPU is fast enough. I have not tries a MySQL dump though, although this should just be standard disk accesses.

Are you aware of anything in 2.6.18 which might actually cause this? It is hard to push for a serious upgrade to our standard database build on the basis that it "might" fix the problem.

Signature · 1 line
Tracy Reed
#3 lore raw
Quoted 5 lines · Tracy Reed

On Fri, Feb 24, 2012 at 12:10:43AM +0100, Arno Wagner spake thusly:

Quoted 4 lines

I notice that you are using a really, really old kernel. 2.6.18 is from end of 2006 or so. There were some performance issues with the dm-layer, they might just still be in this old version.

RHEL5 uses old dmcrypt code which is stable but has known limitations.
The last change in RHEL was backporting suport for XTS mode.

Backporting of some performance changes is almost impossible (if it easy I perhaps tried that long time ago).

Also please note that this is exactly where RHEL customer requests helps - and there were no such requests. So other things get priority.

So if you are using CentOS my advice is simple - try to upgrade to CentOS6 and test it. It should be in some aspect better but still database performance over dmcrypt can have problems.

Setting nr_request will not help btw, this is different problem.

Milan

author
#4 lore raw

On Fri, Feb 24, 2012 at 09:18:58AM +0100, Milan Broz spake thusly:

Quoted 2 lines

RHEL5 uses old dmcrypt code which is stable but has known limitations.

Aside from being single-threaded (not the problem in our case) what are the limitations? My extensive googling hasn't turned up anything relevant short of reading large amounts of the dm-crypt list archive.

Quoted 1 line

The last change in RHEL was backporting suport for XTS mode.

I can't find any good info on exactly what this is but I wonder if it is related to block size since.

One thing I have been wondering about is block size and CBC. mysqldump is probably doing a lot of tiny reads. Just how much data does dm-crypt have to read to pull a single piece of data from the disk? Could the use of cipher block chaining be causing it to read a lot more than it otherwise would so it can decrypt the piece of data that it needs? I have a basic crypto education (university class, read Applied Cryptography, used it plenty as a sysadmin/security guy) but don't know the details of how the IV is generated from the previous block in dm-crypt.

It looks like XTC mode uses the sector number as IV which might result in reading less data. Perhaps I should try ECB mode instead of my current:

Cipher mode: cbc-essiv:sha256

Quoted 3 lines

Also please note that this is exactly where RHEL customer requests helps - and there were no such requests. So other things get priority.

We have RHEL also and can deploy this solution on RHEL and run the question by RedHat if it comes to that, no problem. But it will likely still be on RHEL5. However, this allows me to make a good argument for getting things upgraded to RHEL6.

Quoted 3 lines

So if you are using CentOS my advice is simple - try to upgrade to CentOS6 and test it. It should be in some aspect better but still database performance over dmcrypt can have problems.

I'll try an ECB mode (I am aware of the cryptographic downside as far as identical plaintext blocks go) just to see if that is the issue. Then I'll try RHEL/CentOS 6 and XTC.

Thanks!

Signature · 1 line
Tracy Reed
#5 lore raw
Quoted 4 lines · Tracy Reed

On Fri, Feb 24, 2012 at 09:18:58AM +0100, Milan Broz spake thusly:

Quoted 2 lines

RHEL5 uses old dmcrypt code which is stable but has known limitations.

Quoted 8 lines

Aside from being single-threaded (not the problem in our case) what are the limitations? My extensive googling hasn't turned up anything relevant short of reading large amounts of the dm-crypt list archive.

Quoted 1 line

The last change in RHEL was backporting suport for XTS mode.

I can't find any good info on exactly what this is but I wonder if it is related to block size since.

It is a block cipher mode. Shpuld not have any relation to your problem.

Quoted 5 lines

One thing I have been wondering about is block size and CBC. mysqldump is probably doing a lot of tiny reads. Just how much data does dm-crypt have to read to pull a single piece of data from the disk? Could the use of cipher block chaining be causing it to read a lot more than it otherwise would so it can decrypt the piece of data that it needs?

Decryption is in 512 byte blocks. There is really no way around that if you want to be secure. But 512 byte is already the smallest unit you can read from disk.

Quoted 4 lines

I have a basic
crypto education (university class, read Applied Cryptography, used it plenty as a sysadmin/security guy) but don't know the details of how the IV is generated from the previous block in dm-crypt.

It is not, at least not from the previous disk block. In disk encryption you have to generate the IV somehow from the block number (e.g. via ESSIV) or do without (e.g. XTS mode). You still have to decrypt each block fully. Blocks used in dm-crypt are allways 512 Bytes in size, hence no extra reads or writes compared to non-encrypted disks should ever be required.

Disk encryption is a bit different than normal data-stream encryption and not covered well or at all in the usual crypto literature.

Quoted 4 lines

It looks like XTC mode uses the sector number as IV which might result in reading less data. Perhaps I should try ECB mode instead of my current:

Cipher mode: cbc-essiv:sha256

Please to not. ECB is massively insecure. And it will not result in reading less data. Well, you can do it as an experiement, but please do not use it when it has to be secure.

Quoted 9 lines
Quoted 3 lines

Also please note that this is exactly where RHEL customer requests helps - and there were no such requests. So other things get priority.

We have RHEL also and can deploy this solution on RHEL and run the question by RedHat if it comes to that, no problem. But it will likely still be on RHEL5. However, this allows me to make a good argument for getting things upgraded to RHEL6.

Quoted 6 lines
Quoted 3 lines

So if you are using CentOS my advice is simple - try to upgrade to CentOS6 and test it. It should be in some aspect better but still database performance over dmcrypt can have problems.

I'll try an ECB mode (I am aware of the cryptographic downside as far as identical plaintext blocks go) just to see if that is the issue.

It should not. But if you notice a difference, it may be interesting to see what it is.

There is also another possible factor that may make a larger or smaller difference: If I remember correctly, you said the dump was going to the same disk, but from different partitions for encrypted and non-encrypted. This may influence speeds. First, head seeks are dependend on seek distance. The farther the heads have to move, the slower. And second, different disk regions have different data density. My observation is that disk linear throughput drops to less than half towards the end of many disks. And third, you may have a different fragmentation or data placement situation or filesystem parameter on both source disks.

Generally moving a lot of data around on one disk is subject to all sorts of wired effects. Often it is faster to move the data over a second disk as intermediate step.

So maybe you should try this with exactly the same partitions first, with exactly the same filesystems and data, just once with dm-crypt and once without. Maybe even put the same binary disk image in both, once on the raw partition and once on the encrypted one. Or try with dumping to /dev/null. As far as I remember, mysqldump just produces an output data stream that should be redirectable to STDOUT. Has been a few years that I used MySQL last, though.

Quoted 1 line

Then I'll try RHEL/CentOS 6 and XTC.

Sounds like a good plan.

Arno

Quoted 9 lines

Thanks!

Signature · 5 lines
Tracy Reed
_______________________________________________
dm-crypt mailing list
[email protected]
http://www.saout.de/mailman/listinfo/dm-crypt
Signature · 6 lines
Arno Wagner, Dr. sc. techn., Dipl. Inform., CISSP -- Email: [email protected] 
GnuPG:  ID: 1E25338F  FP: 0C30 5782 9D93 F785 E79C  0296 797F 6B50 1E25 338F
----
One of the painful things about our time is that those who feel certainty 
are stupid, and those with any imagination and understanding are filled 
with doubt and indecision. -- Bertrand Russell 
author
#6 lore raw

On Fri, Feb 24, 2012 at 09:05:29PM +0100, Arno Wagner spake thusly:

Quoted 2 lines

Blocks used in dm-crypt are allways 512 Bytes in size, hence no extra reads or writes compared to non-encrypted disks should ever be required.

Ok, so there goes that theory.

Quoted 3 lines

Please to not. ECB is massively insecure. And it will not result in reading less data. Well, you can do it as an experiement, but please do not use it when it has to be secure.

I already did a test with it and as you correctly expected, it made no difference. We certainly won't be deploying ECB. I am aware of the same plaintext security issues with it.

Quoted 2 lines

It should not. But if you notice a difference, it may be interesting to see what it is.

I found no difference.

Quoted 4 lines

There is also another possible factor that may make a larger or smaller
difference: If I remember correctly, you said the dump was going to the same disk, but from different partitions for encrypted and non-encrypted. This may influence speeds. First, head seeks are dependend on seek distance. The

You remember incorrectly: The dump is being done using mysqldump on a remote machine. So it is going over the network and writing to totally different disks.

Quoted 4 lines

regions have different data density. My observation is that disk linear throughput drops to less than half towards the end of many disks. And third, you may have a different fragmentation or data placement situation or filesystem parameter on both source disks.

This is a very good observation and could be part of the problem here. I have been using different logical volumes on the same disk. I will try using the same LV mapped with dm-crypt vs no crypto and see if that helps.

Thanks!

Signature · 1 line
Tracy Reed
#7 lore raw
Quoted 10 lines · Tracy Reed

On Fri, Feb 24, 2012 at 09:05:29PM +0100, Arno Wagner spake thusly:

Quoted 2 lines

Blocks used in dm-crypt are allways 512 Bytes in size, hence no extra reads or writes compared to non-encrypted disks should ever be required.

Ok, so there goes that theory.

Quoted 3 lines

Please to not. ECB is massively insecure. And it will not result in reading less data. Well, you can do it as an experiement, but please do not use it when it has to be secure.

Quoted 8 lines

I already did a test with it and as you correctly expected, it made no difference. We certainly won't be deploying ECB. I am aware of the same plaintext security issues with it.

Quoted 2 lines

It should not. But if you notice a difference, it may be interesting to see what it is.

I found no difference.

As expected. Still good to know, sometomes these assumptions turn out to be wrong.

Quoted 8 lines
Quoted 4 lines

There is also another possible factor that may make a larger or smaller
difference: If I remember correctly, you said the dump was going to the same disk, but from different partitions for encrypted and non-encrypted. This may influence speeds. First, head seeks are dependend on seek distance. The

You remember incorrectly: The dump is being done using mysqldump on a remote machine. So it is going over the network and writing to totally different disks.

Ah, sorry.

Quoted 8 lines
Quoted 4 lines

regions have different data density. My observation is that disk linear throughput drops to less than half towards the end of many disks. And third, you may have a different fragmentation or data placement situation or filesystem parameter on both source disks.

This is a very good observation and could be part of the problem here. I have been using different logical volumes on the same disk. I will try using the same LV mapped with dm-crypt vs no crypto and see if that helps.

It would be an easy explanation. Let us know about the results.

Quoted 1 line

Thanks!

No problem.

Arno

Signature · 6 lines
Arno Wagner, Dr. sc. techn., Dipl. Inform., CISSP -- Email: [email protected] 
GnuPG:  ID: 1E25338F  FP: 0C30 5782 9D93 F785 E79C  0296 797F 6B50 1E25 338F
----
One of the painful things about our time is that those who feel certainty 
are stupid, and those with any imagination and understanding are filled 
with doubt and indecision. -- Bertrand Russell 
author
#9 lore raw

On Fri, Feb 24, 2012 at 01:29:54AM +0000, Alasdair G Kergon spake thusly:

Quoted 4 lines

Have you done much tuning, in particular /sys/block/<devices>/queue/nr_requests ?

http://www.redbooks.ibm.com/redpapers/pdfs/redp4285.pdf remains a good background read.

Given that a non-dmcrypt mysqldump on the same disks (different logical volume) finishes and 15 minutes and a dmcrypt mysql backup happens in 30 minutes any tuning being done would have to be dmcrypt-specific and affect something in the dmcrypt layer or above, correct?

nr_requests only exists for the physical disk device. Just for kicks I just bumped it up to 8192 from the default 1024 and ran my test again. Same
performance: Twice as slow as without dmcrypt.

Signature · 1 line
Tracy Reed