MariaDB Performance – How to Increase Write Speed from 500kb/s

mariadbUbuntu

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?

Best Answer

query_cache_type=1
query_cache_size=80M

For every write, it must scan the 80M for any references to the table being written to, and purge those entries. This is an example of where the Query cache hurts performance, and should be completely disabled for production servers.

innodb_flush_log_at_trx_commit=1

cause a disk flush at every "transaction". If you are using autocommit=ON and writing one row per SQL statement, then this is a huge overhead. Some workarounds:

BEGIN;
write lots of rows
COMMIT;

and/or

INSERT ... VALUES (...), (...), ... -- Writing lots of rows in a single SQL.

and/or

Switch to SSDs

500KB/s is not a useful metric -- Are they tiny rows or huge rows? 100 rows per second is a typical speed for HDD drives with all the worst settings (innodb_flatc=1, single-row transactions, etc). Adding any one of the above tips, you might get to 1000 rows/sec. Going beyond that is possible, but more challenging.

As for local versus remote... There could be differences in the settings. There could be significant latency. A distance of 120ms (a few thousand miles or kilometers) would explain the 10 minutes.

My high speed ingestion blog may be useful.

Related Question