MariaDB writes at 500kb/s how to get it to write at normal speeds?

by Tschallacka   Last Updated July 11, 2019 20:06 PM

My ubuntu 18.04 has mariadb. I've tried mariadb 10.1, 10.2 and 10.3 all with the same symptons

When I time the harddrive, it has reasonable speeds

$ sudo hdparm -Tt /dev/sda

/dev/sda:
Timing cached reads: 33470 MB in 1.99 seconds = 16840.64 MB/sec
Timing buffered disk reads: 354 MB in 3.03 seconds = 116.92 MB/sec

write:

$ dd if=/dev/zero of=/tmp/output conv=fdatasync bs=384k count=1k; rm -f /tmp/output
1024+0 records in
1024+0 records out
402653184 bytes (403 MB, 384 MiB) copied, 2.4399 s, 165 MB/s

But when I check the actual thouroughput of mariadb it only has 500kb/s which is ridiculously slow. On my windows box it runs at 60mb/s and it both has mariadb 10.2

 10012 be/4 mysql       0.00 B/s  502.34 K/s  0.00 %  2.50 % mysqld  
   563 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.44 % [ext4lazyinit]  
 10009 be/4 mysql       0.00 B/s    0.00 B/s  0.00 %  0.09 % mysqld  
     1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init  
     2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]  
     4 be/0 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/0:0H]  
     6 be/0 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [mm_percpu_wq]  

I've tried various iterations of these settings, but all end up with really slow throughput/no noticable change

innodb_file_per_table=1
innodb_buffer_pool_size=8G
thread_cache_size=10
skip-name-resolve
query_cache_type=1
query_cache_limit=256k
query_cache_min_res_unit=2k
query_cache_size=80M
tmp_table_size=256M
max_heap_table_size=256M

I set the sysctl -w vm.swappiness=0 without any effect.

I'm doing transactions of 5000 rows that get inserted. When I run the exact same code at my workstation it pops each query off per second. ON the remote system it takes roughly 10 minutes for one batch to complete.

I've tried every trick I could find, tried various versions, and nothing really helped.

Does anyone have an explanation why mariadb only runs at 500kb/s?

It's a fresh system, bought today, freshly installed with ubuntu today.

I had a similar issue with an SQLITE database, where the database would wait for the disk to spin around, release, wait, etc... I could disable the waiting on disk there.

Is there a similar setting in MariaDB? to write to disk buffer, where I would accept the risk of powerloss, isntead of waiting on a hardware lock?

Tags : mariadb ubuntu


Related Questions


Updated February 05, 2017 14:02 PM

Updated July 07, 2019 18:06 PM

Updated October 02, 2018 16:06 PM

Updated October 03, 2018 00:06 AM

Updated September 06, 2016 09:02 AM