Sample Header Ad - 728x90

MariaDB: Writing even 25MB of data into a LONGBLOB is very slow

0 votes
1 answer
209 views
We recently added MariaDB as a storage option for our tool. Before that we only allowed sqlite. Among other tables I have a very simple temporary file storage table:
create table tmp_file_storage
(
    uuid          varchar(255) not null,
    creation_date datetime     not null,
    filename      varchar(255) null,
    data_type     varchar(255) null,
    file          longblob     null
)
In production, writing a single entry into this table with a 25MB file (even if the table is empty), takes about 8 minutes. On my development PC it takes about 2 minutes, which is already way too slow for such a small file. Writing the data currently happens in 2 steps:
INSERT INTO tmp_file_storage(uuid, creation_date, filename, data_type, file)
VALUES ('someuuid', 'YYYY-MM-DD hh:mm:ss', NULL, NULL, NULL);
UPDATE TABLE tmp_file_storage
SET
  filename='filename',
  data_type='xml',
  file='.... 25MB of data ...';
WHERE
  tmp_file_storage.uuid = 'someuuid'
First we thought it could be something about our code, but we were able to reproduce this problem with just sending those queries to MariaDB. I tried using different Storage Engines (InnoDB, MyISAM, Aria) and it always took pretty much the same time. Here is the my.ini in case that matters. We haven't changed a lot of stuff.
[mysqld]
datadir=D:/.../data
innodb_lock_wait_timeout = 120
innodb_log_file_size     = 512M
innodb_log_buffer_size   = 128M
innodb_buffer_pool_size  = 1G
max_allowed_packet       = 500M
The database runs on the same server, and doesn't have a lot of load. Maybe around 10-100 requests a minute. With 25MB already taking so long, something seems to be actively slowing MariaDB down. In sqlite this operation usually takes less than a second. Any ideas?
Asked by Tekay37 (101 rep)
Jun 27, 2024, 02:28 PM
Last activity: Jun 17, 2025, 09:04 AM