1

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?

2
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.

  • I have everything wrapped within a laravel transaction, yet it seems as if it's still writing row by row. Not sure what could be the issue there, as I said, the same code locally goes blazingly fast. Maybe the transaction doesn't work on the remote? Disabling the cache didn't work. I tried with the mysql workbench migration wizard and it went up to 20mb/s so something is happening that makes the write go slow when accessed via php. Thanks for your insight. – Tschallacka Jul 12 at 8:38
  • @Tschallacka - One-row-at-a-time is 10 times as slow as batching lots of rows into a single INSERT. Search Google for laravel batch insert -- There are lots of answers. – Rick James Jul 12 at 14:01
  • I found out I was doing the transaction at the wrong database(modified a script that had pulled it from another source) i was actually reading from, so quite useless, I modified the transaction to be done on the remote server connection. The writes themselves were quite fast in the 5000 batches, but pushing the data to the remote server still quite slow. I'm suspecting some kind of rate limiting being in the way. – Tschallacka Jul 12 at 14:05
0

Yes, you may want to consider setting these two variables with these values in order to sacrifice some durability in exchange for better write performance:

innodb_flush_log_at_trx_commit=2 (1 is the default value)

2 The log buffer is written to the InnoDB redo log after each commit, but flushing takes place once a second. Performance is slightly better, but a OS or power outage can cause the last second's transactions to be lost.

sync_binlog=0 (this is the default value)

MariaDB will synchronize its binary log file to disk after this many events. The default is 0, in which case the operating system handles flushing the file to disk. 1 is the safest, but slowest, choice, since the file is flushed after each write. If autocommit is enabled, there is one write per statement, otherwise there's one write per transaction. If the disk has cache backed by battery, synchronization will be fast and a more conservative number can be chosen.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.