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
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
$ 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?