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):
15.2GB
2.78GB
238GB
14.9TB
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
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