[debian-mysql] Bug#925385: /usr/bin/mysql: Poor MariaDB performance

[Jeffrey Walton]

Package: mariadb-client-core-10.1
Version: 10.1.37-0+deb9u1
Severity: important
File: /usr/bin/mysql

Dear Maintainer,

I am working on a Tinkerboard (https://www.asus.com/us/Single-Board-Computer/Tinker-Board/), which is an ARM dev-board. The board has a Cortex-A17 1.4 GHz cpu, 2 GB RAM, and 16 GB SDcard. The board runs Debian 9.8.

I have a program that tries to use MySQL, but MySQL/MariaDB is mostly unusable on this board due to poor performance.

I installed the following packages (and recommends) and ran mysql_secure_installation. Everything else is a Debian default.

* mysql-server
* mysql-client
* mysql-utilities
* libmariadbd-dev

The test program/SQL below attempts to create two small tables and two users. It takes 13 seconds to complete. I've read about the changes Debian made to MySQL at https://mariadb.com/kb/en/library/moving-from-mysql-to-mariadb-in-debian-9/, but I don't know what is causing this problem. This is not my area of expertise.

The smae program and SQL runs fine on Fedora 29 and dev-boards. Performance is about where one expects, give or take.

The command to setup the test database is:

    # Password set using mysql_secure_installation
    $ time mysql -uroot -pZZZZZZZZZZZZZZZZZZZZZZZZZZZZ < testdb.sql

    real    0m12.799s
    user    0m0.040s
    sys     0m0.000s

The contents of testdb.sql are:

DROP DATABASE IF EXISTS testdb;
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

#####################################################

CREATE TABLE IF NOT EXISTS name_number
(
	`nn_id` INT PRIMARY KEY AUTO_INCREMENT,
	`nn_name` VARCHAR(64) CHARACTER SET utf8,
	`nn_number` VARCHAR(18) CHARACTER SET utf8
);

CREATE INDEX IF NOT EXISTS number_index ON name_number(nn_number);

#####################################################

CREATE TABLE IF NOT EXISTS whitelist
(
	`list_id` INT PRIMARY KEY AUTO_INCREMENT,
	`nn_id` INT NOT NULL,
	`list_date` DATETIME,
	FOREIGN KEY fk1_id (nn_id)
	REFERENCES name_number(nn_id)
);

#####################################################

CREATE TABLE IF NOT EXISTS blacklist
(
	`list_id` INT PRIMARY KEY AUTO_INCREMENT,
	`nn_id` INT NOT NULL,
	`list_date` DATETIME,
	FOREIGN KEY fk2_id (nn_id)
	REFERENCES name_number(nn_id)
);

#####################################################

CREATE USER IF NOT EXISTS 'testdb_admin'@'localhost' IDENTIFIED BY 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
CREATE USER IF NOT EXISTS 'testdb_admin'@'%' IDENTIFIED BY 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';

CREATE USER IF NOT EXISTS 'testdb_user'@'localhost' IDENTIFIED BY 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY';
CREATE USER IF NOT EXISTS 'testdb_user'@'%' IDENTIFIED BY 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY';

GRANT ALL PRIVILEGES ON testdb.* TO 'testdb_admin'@'localhost';
GRANT ALL PRIVILEGES ON testdb.* TO 'testdb_admin'@'%';

GRANT SELECT,INSERT,UPDATE,DELETE ON testdb.* TO 'testdb_user'@'localhost';
GRANT SELECT,INSERT,UPDATE,DELETE ON testdb.* TO 'testdb_user'@'%';

-- System Information:
Debian Release: 9.8
  APT prefers stable-updates
  APT policy: (500, 'stable-updates'), (500, 'stable')
Architecture: armhf (armv7l)

Kernel: Linux 4.4.132+ (SMP w/4 CPU cores)
Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8), LANGUAGE=en_US.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
Init: systemd (via /run/systemd/system)

Versions of packages mariadb-client-core-10.1 depends on:
ii  libaio1         0.3.110-3
ii  libc6           2.24-11+deb9u4
ii  libncurses5     6.0+20161126-1+deb9u2
ii  libreadline5    5.2+dfsg-3+b1
ii  libstdc++6      6.3.0-18+deb9u1
ii  libtinfo5       6.0+20161126-1+deb9u2
ii  mariadb-common  10.1.37-0+deb9u1
ii  zlib1g          1:1.2.8.dfsg-5

mariadb-client-core-10.1 recommends no packages.

mariadb-client-core-10.1 suggests no packages.

-- no debconf information