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?
