Strange MySQL speed problems

LLS

New Member
Aug 2, 2016
12
0
1
39
Hi everyboy,

I have weird MySQL performance on my fresh Proxmox 4 installation / new server.
https://www.hetzner.de/us/hosting/produkte_rootserver/ex41

The creation of the database schema (InnoDb with ForeignKeys) is very slow.
The SQL file (40kb) only contains the structure and some foreign keys without any data.

Attached some measurements without any other load, fresh debian, no my.ini modifications:

Proxmox LXC Container:
root@lxc1:/root# time mysql test -u root -p < initialdb.sql
real 0m31.521s
user 0m0.012s
sys 0m0.004s

dd if=/dev/zero of=/tmp/test bs=1M count=1000 oflag=direct
1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 0.386445 s, 2.7 GB/s

Proxmox VM :
root@vm1:~# time mysql -u root -p test < initialdb.sql
real 0m12.456s
user 0m0.004s
sys 0m0.000s

dd if=/dev/zero of=/tmp/test bs=1M count=1000 oflag=direct
1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 0.405222 s, 2.6 GB/s

Desktop (Windows, older Hardware, VirtualBox)
root@jessie:/root# time mysql -u root -p test < initialdb.sql
real 0m2.195s
user 0m0.000s
sys 0m0.004s

dd if=/dev/zero of=/tmp/test bs=1M count=1000 oflag=direct
1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 2.357 s, 445 MB/s


Any ideas about this big difference?

Thanks a lot for any tipps or help!
 
Last edited:
Hi,

Do you use zfs? If so this is the compression.
you write zeros and this is very easy to compress.
 
Hi Wolfgang,

thanks for your reply!

It's a vanilla debian jessie installation on the host and guest(s).
The host system is using ext4 and software raid 1.

I used this tutorial to setup the host: https://pve.proxmox.com/wiki/Install_Proxmox_VE_on_Debian_Jessie

Code:
root@prox4m2 ~ # df
Filesystem      1K-blocks    Used  Available Use% Mounted on
udev                10240       0      10240   0% /dev
tmpfs             6554416   17276    6537140   1% /run
/dev/md2       2078435544 8513196 1964320916   1% /
tmpfs            16386036   43680   16342356   1% /dev/shm
tmpfs                5120       0       5120   0% /run/lock
tmpfs            16386036       0   16386036   0% /sys/fs/cgroup
/dev/md1           498980   72511     400284  16% /boot
/dev/md3       1749725084   69792 1660751316   1% /home
cgmfs                 100       0        100   0% /run/cgmanager/fs
tmpfs                 100       0        100   0% /run/lxcfs/controllers
/dev/fuse           30720      16      30704   1% /etc/pve
tmpfs             3277208       0    3277208   0% /run/user/0



Best regards

LLS


PS: Please apologize the double posting in the German forum - I couldn't delete the post...
 
Last edited:
Can you make a write test on the host with random data?
To see the real write performance of this machine.

use a ramdisk to store the data before you write them.
 
I created a random (200 MB) ram file using:
mkdir /mnt/ram; mount -t ramfs ramfs /mnt/ram
dd if=/dev/urandom of=/mnt/ram/random.txt bs=1048576 count=200
And copied it with via dd
dd if=/mnt/ram/random.txt of=/root/test.txt

Hope thats ok.

Results:

LXC (without ramdisk - not possible in LXC?)
root@lxc1:~# dd if=randomfile.txt of=newfile.txt
2048000+0 records in
2048000+0 records out
1048576000 bytes (1.0 GB) copied, 2.08396 s, 503 MB/s

Proxmox VM
root@vm:/mnt# dd if=/dev/urandom of=/mnt/ram/random.txt bs=1048576 count=200
200+0 records in
200+0 records out
209715200 bytes (210 MB) copied, 8.41317 s, 24.9 MB/s
root@cust1:/mnt# dd if=/mnt/ram/random.txt of=/root/test.txt
409600+0 records in
409600+0 records out
209715200 bytes (210 MB) copied, 0.529465 s, 396 MB/s

VirtualBox (Desktop Test VM)
root@jessie:/# dd if=/mnt/ram/random.txt of=/root/test.txt
409600+0 records in
409600+0 records out
209715200 bytes (210 MB) copied, 1.54733 s, 136 MB/s
 
Last edited:
Here are the missing tests on the host:

Code:
root@prox4m2 ~ # mkdir /mnt/ram; mount -t ramfs ramfs /mnt/ram
root@prox4m2 ~ # free -m
             total       used       free     shared    buffers     cached
Mem:         32003      14503      17500         71        190      11027
-/+ buffers/cache:       3285      28717
Swap:        16367          0      16367
root@prox4m2 ~ # dd if=/dev/urandom of=/mnt/ram/random.txt bs=1048576 count=1000
1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 67.035 s, 15.6 MB/s
root@prox4m2 ~ # free -m
             total       used       free     shared    buffers     cached
Mem:         32003      15511      16492         71        190      12027
-/+ buffers/cache:       3293      28710
Swap:        16367          0      16367
root@prox4m2 ~ # dd if=/mnt/ram/random.txt of=/root/test.txt
2048000+0 records in
2048000+0 records out
1048576000 bytes (1.0 GB) copied, 1.88726 s, 556 MB/s
 
This looks good, so you system work as expected.
Then may be the sync is not ok.
SQL does a lot of syncs in normal setting.

Can you try the test again with this to flags in the dd command
"oflag=direct,dsync"

Have you a ssd in your old HW?
Can you send the VM config?
 
Thank you so much for your help!

Attached the results with direct,dsync options:

Proxmox VM

root@vm:~# dd if=/mnt/ram/random.txt of=/root/test.txt bs=1M oflag=direct,dsync
200+0 records in
200+0 records out
209715200 bytes (210 MB) copied, 12.2254 s, 17.2 MB/s

Proxmox LXC
root@f10:~# dd if=file.txt of=filenew.txt bs=1M oflag=direct,dsync
200+0 records in
200+0 records out
209715200 bytes (210 MB) copied, 16.615 s, 12.6 MB/s

Host
root@prox4m2 ~ # dd if=/mnt/ram/random.txt of=/root/test2.txt bs=1M oflag=direct,dsync
200+0 records in
200+0 records out
209715200 bytes (210 MB) copied, 8.59019 s, 24.4 MB/s

Virtualbox / Test Desktop (SSD)

root@jessie:/projects/humhub-hosting/etc# dd if=/mnt/ram/random.txt of=/root/test.txt bs=1M oflag=direct,dsync
200+0 records in
200+0 records out
209715200 bytes (210 MB) copied, 2.16455 s, 96.9 MB/s

---

Indeed on my "old" desktop I'm using a SSD, but I also tested the DB creation script on e.g. Jiffybox ( non SSD, VHost, also MySQL Standard Configuration) - it tooks 3-4sec to create it.

What is the best way to export and provide you the VM configuration?
I've created the VMs via web interface without any "special" options (e.g. quota, acl, ...).








 
Here a test of another vhost provider (jiffybox).
The DB creation tooks about 3 secs.

There *should be* no SSD.

root@f2:~# dd if=/dev/urandom of=/root/random.txt bs=1048576 count=200
200+0 records in
200+0 records out
209715200 bytes (210 MB) copied, 19.4171 s, 10.8 MB/s

root@f2:~# dd if=random.txt of=new.txt bs=1M oflag=direct,dsync
200+0 records in
200+0 records out
209715200 bytes (210 MB) copied, 0.597391 s, 351 MB/s
 
on the shell

qm config <VMID>
pct config <VMID>
 
root@prox4m2 / # qm config 100
Configuration file 'nodes/prox4m2/qemu-server/100.conf' does not exist
root@prox4m2 / # pct config 100
arch: amd64
cpulimit: 6
cpuunits: 1024
hostname: testing1
memory: 8192
net0: name=eth0,bridge=vmbr1,gw=138.201.60.xx,hwaddr=92:9A:75:8D:C8:3D,ip=138.201.210.xx/32,type=veth
ostype: debian
rootfs: local:100/vm-100-disk-1.raw,size=500G
swap: 512


root@prox4m2 / # qm config 101
bootdisk: ide0
cores: 2
ide0: local:101/vm-101-disk-1.qcow2,size=200G
ide2: local:iso/debian-8.5.0-amd64-netinst.iso,media=cdrom
keyboard: de
memory: 2048
name: testing2
net0: e1000=5A:7C:5C:7D:69:AD,bridge=vmbr1
numa: 0
ostype: l26
smbios1: u
root@prox4m2 / # pct config 101
Configuration file 'nodes/prox4m2/lxc/101.conf' does not exist
 
This result look like the cache is turned on.

You can also enable the cache.
But how important are the data on the DB?
I case of power lost you can lose some data what are written at the moment.

AFIK Vbox use also cache (writeback) as default.
 
Ahh, I found the cache options for the LXC containers hard drives.

On my use case it's important to quickly create the database (this is done multiple times) - a "small" data lose is acceptable.
What caching option do you suggest for LXC and the MySQL use case?

Thank you so much!
 
Sorry, the cache options are only available for VMs.

Is there also some option for LXC?
 
No your right, only VM are able to change the cache.
CT are using the kernel settings and so it is the DB job to handle(set) this.

I would try, but I think the best setting in your case is writeback (not writeback unsafe).
 
I did some further tests with cache modes and the VM

none: 15sec
writeback: 12sec
writeback (unsafe): 2sec
write through: 17sec
directsync: 15sec

Hmm, writeback (unsafe) has sweet results :)

I'll check some InnoDb/MySQL options for LXC....
 
Hmm, i tried different innodb options 'innodb_flush_log_at_trx_commit' and 'innodb_flush_method' but LXC still takes more than 30secs to create the database.

So the solution for me is to switch from LXC to VM, this will speed up from 30sec to 15sec (12sec with writeback).
I think 12-15sec is acceptable.

Or do you see any chance to get similar results also with LXC?

Last questions, how unsafe is writeback (unsafe)? :)

Thanks again for your help!
 
from the manual.

"In case you don’t care about data integrity over host failures, use cache=unsafe. This option tells QEMU that it never needs to write any data to the disk but can instead keep things in cache. If anything goes wrong, like your host losing power, the disk storage getting disconnected accidentally, etc. your image will most probably be rendered unusable. When using the -snapshot option, unsafe caching is always used."
 

About

The Proxmox community has been around for many years and offers help and support for Proxmox VE, Proxmox Backup Server, and Proxmox Mail Gateway.
We think our community is one of the best thanks to people like you!

Get your subscription!

The Proxmox team works very hard to make sure you are running the best software and getting stable updates and security enhancements, as well as quick enterprise support. Tens of thousands of happy customers have a Proxmox subscription. Get yours easily in our online shop.

Buy now!