How to fix slow dbbackup on Virtualised Linux box ASA10?

[former_SQLA_member1694906]

I'm in the process of moving an ASA10 DB on Linux from a bare metal machine into a VM on a cloud-based host (a pretty powerful machine). This is using VirtualBox 5.2.6 64 bit.

They're both 64 bit and have 16G RAM (BM) 32G RAM (VM)

It does work, but I'm finding that dbbackup is VERY slow on the VM compared to the bare metal. (It would take about an hour on VM compared with 3 minutes on BM)

The disk access (as measured by copying a large file) is twice as fast on the VM.

I've not measured performance of intense updates yet, but get the impression that general performance (eg. counting the number of rows of a large table) is OK.

Any ideas on what I might try to diagnose this?

[Breck_Carter]

Do the dbsrv*.exe and dbbackup.exe processes run on the same physical computer, or are they separated by a path through the cloud?

Does the destination backup file reside on a disk that is locally attached to the physical computer that runs dbbackup.exe, or is the file sent off through the cloud?

One hour versus 3 minutes is such a large difference, it's hard to imagine anything other than ... slow ... interweb ... throughput.

[former_SQLA_member1694906]

It's a Linux box, but yes, the same machine and backing up to the same volume as the main db - it's sent off somewhere else afterwards.

So no Internet (or even LAN) involved.

[Breck_Carter]

Please show us the exact command that executes dbbackup, with all the parameters.

[former_SQLA_member1694906]

This is the simplest version: dbbackup -c "uid=dba;pwd=xxxxxx;eng=m1008;dbn=tdb-oct05_m1008" backups

I've also tried with other parameters, but it didn't make any difference...

[Vlad]

I want to add that testing I/O by copying files might give you wrong (unexpected) results. Usually linux uses cache for file operations, and your performance might be "cache" performance. You can try to use hdaparm to measure the difference in I/O operations, but I think this will be the waste of time.

Can you please compare the process (DB backup and/or DB srv) behavior when you backup the DB file? Is CPU/I_O consumption different?

[former_SQLA_member1694906]

I did do some actual db performance test (eg. inserting into a new table a select from an existing table) and the VM was only about 50% slower than the BM in that case.

What I'm also investigating now is not using a VM at all, as in this particular case I don't have to use one - it's just that we like compartmentalising things!

However, when that's all done, I shall report back, as this is still something of interest to me.

[former_SQLA_member1694906]

OK, some results from hdparm on the old BareMetal machine, the new VM and then the host of the VM

BM: /dev/sda1: Timing cached reads: 7666 MB in 2.00 seconds = 3836.39 MB/sec Timing buffered disk reads: 796 MB in 3.01 seconds = 264.72 MB/sec

VM: /dev/sda1: Timing cached reads: 18710 MB in 2.00 seconds = 9362.42 MB/sec Timing buffered disk reads: 4496 MB in 3.00 seconds = 1496.57 MB/sec

(Here the score increased every time I ran the test!)

VMHost BM: /dev/sda1: Timing cached reads: 20582 MB in 2.00 seconds = 10302.29 MB/sec Timing buffered disk reads: 1470 MB in 3.00 seconds = 489.85 MB/sec

[former_SQLA_member1694906]

OK, so I have the answer!

It's nothing to do with Virtualisation.

It's nothing to do with RAM

It's nothing to do with CPU

It's nothing to do with Disk type

It's all about the Linux (kernel?) version.

It flies on Ubuntu 12.04 (whether VM or BM), but runs like a dog on Ubuntu 16.04 or Debian 9. I've not tried it on Ubuntu 14.04. Presumably this (old) code relied on certain OS features that have been deprecated, or somehow emulated, in more recent versions.

I know this is unsafe to run on Public IP, but the machine is behind a firewall with only our own webservers able to access it.

[Breck_Carter]

Google this:

why is ubuntu 16.04 so slow

[Vlad]

Oh! I was waiting for this answer, but haven't expected it. This is all about melt-whatever fixes. Now every single DB will tell you that the performance is worse than before, because of this bug fix 🙂

[former_SQLA_member1694906]

I don't even know if you're joking!

[former_SQLA_member1694906]

Breck, it might be interesting to find out from your developers if ASA16 (or whatever the latest version is) has a similar issue?

[former_SQLA_member1694906]

You know, I'm beginning to think that these SSD drives might actually be slower for writing. Maybe the copy test was only faster because the reading part was so quick???

Just ran dd and it looks like write speed is not an issue:

dd if=/dev/zero of=/tmp/test2.img bs=1G count=1 oflag=dsync 1+0 records in 1+0 records out 1073741824 bytes (1.1 GB, 1.0 GiB) copied, 3.52853 s, 304 MB/s

[former_SQLA_member1694906]

OK, I installed ASA10 on the host machine (and shut down the VM).

Interestingly, that is very slow on the backup too (est 70 mins) I see 95-99% CPU load for dbsrv10 and 52% for dbbackup here.

When doing the backup on the old BM, I see CPU of 64% for dbsrv10 and 42% for dbbackup.

Not sure what this is telling me, though!

The old machine was running Ubuntu 12.04 and the new one is Debian 9 (both 64 bit). Might this be the difference?!

[Vlad]

This is more interesting 🙂 It seems that dbbackup is doing something expensive for CPU resources, or... it could be also the case: CPU goes up to 99%, in case if you do not have enough CPU resources 🙂

I know a theory a little bit and thus I'd ask you to try profiling one of the processes. Hopefully you can identify the most expensive commands.

According to your test result from above, this is not a pure I/O issue. I assume that 99% is the User time, so this means a real "calculation" task.

Maybe developers can assist you with this question. I know that dbbackup is a black-box, and I will not be surprised that it is a single-threaded application.

I am sorry, I cannot help you better.

[former_SQLA_member1694906]

See below for output of report

https://scontent-lht6-1.xx.fbcdn.net/v/t1.0-9/19554924_10156039281926354_6406045402665242680_n.jpg?o...

That high ranking of "swapper" to someone with a little knowledge hints at page thrashing...

[Vlad]

my google skills whisper me, this is the "idle" task :) your image tells me, dbsrv10 consumes 20% and dbbackup only 10%. Are you sure that your CPU goes up to 99%?

[former_SQLA_member1694906]

Well, I just took a quick look at the backup (which is still running!) and it's currently saying 76% CPU for dbsrv10 and 36.5% for dbbackup

[former_SQLA_member1694906]

I'm wondering what perf is looking at - bear in mind* that this is a guest endowed with 4 CPUs. So it could well be that 3 of them are idle (making 75%)

• I say that now, but realise I've not mentioned it hitherto! Apologies 😉

[Vlad]

Alex, if I am not mistaken, perf is doing so-called "sampling". So it measures and aggregates API calls every portion of time. E.g. here you can find different parameters to show what your process is doing.

When I was working with dbbackup16 (or maybe even 17), I found out that this utility is pretty simple and either it works, or not.

As I said, with perf you can try to understand what it does under the hood. But I do not know much how :( E.g. flaming graphs can be helpful to find CPU intensive calls for both processes.

Who knows, maybe since dbsrv has to go through all records in the DB file (full table scan) to send them to dbbackup, this is where you lose the performance, and hopefully the large cache size can help you with it.

[former_SQLA_member1694906]

I was hoping it might help, but throwing resources at a problem doesn't always help - it doesn't in this case! But thanks for your help and moral support 😉