INSERT SQL query very slow in MariaDB Docker container compared to XAMPP

Question

I was looking for the bottleneck of a web application and found out that INSERT queries run significant slower like this:

MariaDB [myforum]> insert into tag set tagtext='abc12345',dateline=unix_timestamp(),canonicaltagid=1234;
Query OK, 1 row affected (0.24 sec)

This was done by installing the mysql-client in the application container for testing purpose. 240ms seems very long for such a simple query. I assumed some dns/network problems. But I see similar results when running the query directly in the MariaDB container, where the connection was made using -h 127.0.0.1:

MariaDB [myforum]> insert into tag set tagtext='abc123',dateline=unix_timestamp(),canonicaltagid=1234;
Query OK, 1 row affected (0.251 sec)

Only INSERT queries are effected. SELECTs were pretty fast as expected. Strangely, this seems to have something to do with the MariaDB Docker installation: I have the same Database on a local XAMPP installation, where same query is fast:

MariaDB [myforum]> insert into tag set tagtext='abc123',dateline=unix_timestamp(),canonicaltagid=123;
Query OK, 1 row affected (0.00 sec) 

I can only do limited changes on the tables itself since these are from vBulletin (old, proprietary forum CMS).

I'm wondering why those simple queries are so extremly slow?

Already tried

• Changed the I/O Scheduler for both discs at runtime and also in Grub with reboot
• Setting barrier=0 in fstab
• Set slice_idle and group_idle to zero (default value was 8)
• Optimized all tables

Structure of the test table (seems also affecting other tables)

CREATE TABLE `tag` (
    `tagid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `tagtext` VARCHAR(100) NOT NULL DEFAULT '',
    `dateline` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `canonicaltagid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`tagid`),
    UNIQUE INDEX `tagtext` (`tagtext`),
    INDEX `canonicaltagid` (`canonicaltagid`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4112
;

Docker Compose file

version: '2'
volumes:
  mysql-data:

services:
  # Here is another service that access the db using dns name 'mariadb'

  mariadb:
    container_name: mariadb
    image: mariadb:10.3
    mem_limit: 3GB
    restart: always
    env_file:
      - mariadb.env
    volumes:
      - ../dump.sql:/docker-entrypoint-initdb.d/dump.sql
      - mysql-data:/var/lib/mysql

System information about the underlying Docker host server

# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 18.04.3 LTS
Release:        18.04
Codename:       bionic

# docker --version
Docker version 19.03.1, build 74b1e89
# docker-compose --version
docker-compose version 1.24.1, build 4667896b

The server has a software raid 1 with two enterprise hdds, enough CPU power and memory (32GB). And it's currently not used for any other application, nor there is any load from users. So I can exclude that its a load issue.

Hod

Is that an insert or an update? Looks like some weird hybrid of the two and I didn't find anything like "insert table set column = value" in the fine manual mariadb.com/kb/en/library/adding-and-changing-data-in-mariadb - what does it do? – Fawnfawna

@CaiusJard It's not weird, just an extension from MySQL, see https://mcmap.net/q/107561/-mysql-insert-into-table-values-vs-insert-into-table-set Using your syntax insert tag(tagtext,dateline,canonicaltagid) values('a123',unix_timestamp(),123); the query took also 295ms, so it's similar slow. – Hod

What is the value of innodb_buffer_pool_size? – Conjugated

@RickJames innodb_buffer_pool_size = 268435456. I kept the entire SQL configuration to the defaults of MariaDB. – Hod

Though it won't solve the problem, you should set the buffer pool to about 70% of RAM. – Conjugated

Answer 1

After some researching, I found information about InnoDB flush parameters. Especially innodb_flush_log_at_trx_commit which is set to 1 per default:

MariaDB [myforum]> show variables like '%innodb_flush%';
+--------------------------------+----------+
| Variable_name                  | Value    |
+--------------------------------+----------+
| innodb_flush_log_at_timeout    | 1        |
| innodb_flush_log_at_trx_commit | 1        |
| innodb_flush_method            | O_DIRECT |
| innodb_flush_neighbors         | 1        |
| innodb_flush_sync              | ON       |
| innodb_flushing_avg_loops      | 30       |
+--------------------------------+----------+

The value of 1 indicates writing and flushing on every commit. So I'd assume this could take overhead. A considerable compromiss was posted here by changing it to 2. This would result in a write to the log file after each commit, but flushes the log only once per second to the disk.

For me this drastically improves write performance: The MySQL cli shows 0.000 sec instead of up to ~300ms like before. Also the HTML rendering time of the affected web application was reduced from 300 - 700ms to ~ 90 - 120ms.

Consequence of this: In the worst case of failture, one second of transactions could be lost. That may not acceptable on very sensitive/important data (like finance transactions) and/or a lot of writes are present. I think in most common web cases like mine this is a suiteable fix and I changed the value globally:

set global innodb_flush_log_at_trx_commit = 2;

A technical documentation from here confirmed what I found out:

innodb_flush_log_at_trx_commit

Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.

However, I'm still open for other/better solutions. This post lists some other tipps, but most of them are not suiteable for me (server has more than enough ressources, proprietary software, ...). But they may helo others with similar problems.

Hod

remember, your 'suitable fix' is only good UNTIL the next STOP/START of the instance, unless you put the new value into your my.cnf [mysqld] section. – Haemophiliac

Additional information request. Post on pastebin.com and share the links. A) complete (not edited) my.cnf or my.ini and your complete php.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis. – Haemophiliac