djangodjango-modelsmariadb-10.5

why are SQL inserts (mariadb) getting slower as a table grows


I'm trying to process some data and write it to a mariadb (10.5.10) table using django :

Here is my model

class AbstractCandle(models.Model):
    exchanged_security = models.ForeignKey('market.exchangedsecurity', on_delete=models.CASCADE)
    date = models.DateTimeField(db_index=True)
    duration = models.DurationField(db_index=True)
    ask_open = models.FloatField(default=-1, db_index=True)
    ask_high = models.FloatField(default=-1, db_index=True)
    ask_low = models.FloatField(default=-1, db_index=True)
    ask_close = models.FloatField(default=-1, db_index=True)

    class Meta:
        unique_together = [('exchanged_security', 'date', 'duration')]
        abstract = True

I've made some tweaking to mariadb to make it faster :

innodb_compression_algorithm=none
innodb_autoinc_lock_mode=2
innodb_buffer_pool_size=1024M
max_allowed_packet=1024M
innodb-log-buffer-size=512M
wait_timeout=28800
unique_checks=0
innodb_flush_log_at_trx_commit=0
foreign_key_checks=0
sync_binlog=0

My problem is that insert throughput is getting slower as the table grows (the table for that object is around 28gb for a 80 million rows ) ?

Monitoring shows this (server is only used for mariadb):

Network

Read

15.2GB

Write

2.78GB

BLOCK I/O

Read

238GB

Write

14.9TB

Questions

Is storage write speed the bottleneck ?

Why does mariadb write so much stuff to hard drive even though i disabled indexes and constraints ?

How can i keep insert speed from going down as the table grows ? (I'm only at 25% of all the rows i need to insert and i fear the last 10% will be way longer expected)

PS : Keys were also disabled on that table before starting


Solution

  • That is a known issue in MySQL, part of it is due to rewriting of indexes as you insert a new row, MySQL put the row in its position per index.

    I hope that this stackoverflow post answers your question