0

I'm running a python script that reads some historical data and inserts it into MariaDb. There's a lot of data and I consider splitting it into parts and running mulitple instances of a script.

But since a single instance of the script already reads from and writes into Db in a high speed, to my mind, I wonder: how many reads/writes per second can MariaDb tolerate, on average?

And what'll happen if I create even more instances of the script? Will MariaDb break down somehow? Or will it simply slow down a bit but eventually handle all the data correctly?

How to assess whether it's too much of data per second or not?

$ mysql --version
mysql  Ver 15.1 Distrib 10.5.15-MariaDB,

1 Answer 1

3

It depends.

HDD, Writing one row at a time -- 100 rows/second.
SSD plus some tips below -- 10K rows/sec.

For WRITES:

  • SSD is perhaps 10x faster than HDD.
  • Batching 100 rows in a single INSERT statement is about 10x faster than one row at a time. (executemany)
  • Multiple connections (processes) can gain some parallelism, maybe as much as 5x for 10 threads.
  • LOAD DATA can handle a CSV file possibly faster than any of the above points -- certainly better than reading the csv in the app, parsing it, building a query (even batched), sending it to MySQL/MariaDB, having it parse that, etc.
  • Multiple secondary indexes, especially UNIQUE slow down any INSERT to some extent.

For READS:

Reads are faster -- less locking, etc. Some speed tips:

  • Having the buffer_pool big enough to cache all the data -- maybe 10x speedup.
  • Not using UUIDs.
  • Multiple connections, especially if not blocking with writes on the same table(s).
  • Multi-row reads
  • Fewer roundtrips (eg, don't SELECT ids, then turn around and SELECT using those ids.)
  • Aggregate in SQL, not the app. (That is, don't shovel lots of data to the app when the work could be done in the database.)
  • Have the app and the database "closer" geographically. (re: network latency)

READS and WRITES:

Watch out for deadlocks and "lock wait timeout". In particular, if you don't catch these and deal with them, you could "lose" data. (Instead, replay the transaction, etc.)

Generally, more connections leads to more work done. However, after a "few dozen" connections the throughput stalls and the latency goes through the roof.

For 'continuous' high-speed ingestion: http://mysql.rjweb.org/doc.php/staging_table

Very busy

When "too much" is going on, all threads run slower -- waiting for their share of I/O or CPU or cache space or whatever.

There are a variety of timeouts. When such occurs, the particular action (INSERT, Transaction, whatever) will abort. You should be watching for errors so you can take some kind of recovery action, else the activity is simply lost.

MySQL (and the server) are not likely to "crash" when things get too busy.

As for reads versus writes -- They all contend for resources (I/O, CPU, etc); one might be delayed more than others; it is hard to predict which process is more likely to timeout.

The DBA should identify the slowest queries and look for specific ways to speed up (or avoid) those queries, thereby freeing up resources for all processes.

A common case occurs when using mysqldump every night to backup all the data. The typical settings will lock a table for long enough for writes to that table to complain and timeout. There are other ways to do backups; there may be ways to avoid such writes at that time of night; etc.

3
  • Ok. But, generally speaking, what happens when the shrehold of read-s or write-s, or both at the same time gets exceeded? Will a Db crash? Or will some writters or readers simply have to wait for some additional seconds in a queue?
    – Kum
    Aug 8, 2022 at 13:39
  • @Kum - I added some text on that topic.
    – Rick James
    Aug 8, 2022 at 22:35
  • Also noatime option for mounting FS with DB on it can save a lot of iopses - for both reads and writes.
    – Kondybas
    Apr 12 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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