MariaDB 10.3: InnoDB much slower than MyISAM

Question

I'm using MariaDB 10.3.12 on Arch Linux and haven't modified any of the default settings under /etc/mysql. The following script takes 0.75 s to run on average using the default InnoDB engine:

drop database if exists test_database;
create database test_database;
USE test_database;
CREATE TABLE test_table (namespace char(31) NOT NULL);

This is quite a lot for something that simple. With MyISAM (default_storage_engine=myisam in /etc/mysql/my.cnf.d/server.cnf) it runs in 0.016 s (0.07 s using aria). In an Ubuntu virtual machine, where I have MariaDB v 10.1.38, it runs in 0.04 s on average.

Is there any way to explain why InnoDB is so slow and which variables should I test? I know there are a lot of variables for fine-tuning the performance but it's puzzling to where an order of magnitude difference compared to other engines could possibly come from.

Here are my current InnoDB variables:

+---------------------------------------------+------------------------+
| Variable_name                               | Value                  |
+---------------------------------------------+------------------------+
| ignore_builtin_innodb                       | OFF                    |
| innodb_adaptive_flushing                    | ON                     |
| innodb_adaptive_flushing_lwm                | 10.000000              |
| innodb_adaptive_hash_index                  | ON                     |
| innodb_adaptive_hash_index_parts            | 8                      |
| innodb_adaptive_max_sleep_delay             | 150000                 |
| innodb_autoextend_increment                 | 64                     |
| innodb_autoinc_lock_mode                    | 1                      |
| innodb_background_scrub_data_check_interval | 3600                   |
| innodb_background_scrub_data_compressed     | OFF                    |
| innodb_background_scrub_data_interval       | 604800                 |
| innodb_background_scrub_data_uncompressed   | OFF                    |
| innodb_buf_dump_status_frequency            | 0                      |
| innodb_buffer_pool_chunk_size               | 134217728              |
| innodb_buffer_pool_dump_at_shutdown         | ON                     |
| innodb_buffer_pool_dump_now                 | OFF                    |
| innodb_buffer_pool_dump_pct                 | 25                     |
| innodb_buffer_pool_filename                 | ib_buffer_pool         |
| innodb_buffer_pool_instances                | 1                      |
| innodb_buffer_pool_load_abort               | OFF                    |
| innodb_buffer_pool_load_at_startup          | ON                     |
| innodb_buffer_pool_load_now                 | OFF                    |
| innodb_buffer_pool_size                     | 134217728              |
| innodb_change_buffer_max_size               | 25                     |
| innodb_change_buffering                     | all                    |
| innodb_checksum_algorithm                   | crc32                  |
| innodb_checksums                            | ON                     |
| innodb_cmp_per_index_enabled                | OFF                    |
| innodb_commit_concurrency                   | 0                      |
| innodb_compression_algorithm                | zlib                   |
| innodb_compression_default                  | OFF                    |
| innodb_compression_failure_threshold_pct    | 5                      |
| innodb_compression_level                    | 6                      |
| innodb_compression_pad_pct_max              | 50                     |
| innodb_concurrency_tickets                  | 5000                   |
| innodb_data_file_path                       | ibdata1:12M:autoextend |
| innodb_data_home_dir                        |                        |
| innodb_deadlock_detect                      | ON                     |
| innodb_default_encryption_key_id            | 1                      |
| innodb_default_row_format                   | dynamic                |
| innodb_defragment                           | OFF                    |
| innodb_defragment_fill_factor               | 0.900000               |
| innodb_defragment_fill_factor_n_recs        | 20                     |
| innodb_defragment_frequency                 | 40                     |
| innodb_defragment_n_pages                   | 7                      |
| innodb_defragment_stats_accuracy            | 0                      |
| innodb_disable_sort_file_cache              | OFF                    |
| innodb_disallow_writes                      | OFF                    |
| innodb_doublewrite                          | ON                     |
| innodb_encrypt_log                          | OFF                    |
| innodb_encrypt_tables                       | OFF                    |
| innodb_encryption_rotate_key_age            | 1                      |
| innodb_encryption_rotation_iops             | 100                    |
| innodb_encryption_threads                   | 0                      |
| innodb_fast_shutdown                        | 1                      |
| innodb_fatal_semaphore_wait_threshold       | 600                    |
| innodb_file_per_table                       | ON                     |
| innodb_fill_factor                          | 100                    |
| innodb_flush_log_at_timeout                 | 1                      |
| innodb_flush_log_at_trx_commit              | 1                      |
| innodb_flush_method                         | fsync                  |
| innodb_flush_neighbors                      | 1                      |
| innodb_flush_sync                           | ON                     |
| innodb_flushing_avg_loops                   | 30                     |
| innodb_force_load_corrupted                 | OFF                    |
| innodb_force_primary_key                    | OFF                    |
| innodb_force_recovery                       | 0                      |
| innodb_ft_aux_table                         |                        |
| innodb_ft_cache_size                        | 8000000                |
| innodb_ft_enable_diag_print                 | OFF                    |
| innodb_ft_enable_stopword                   | ON                     |
| innodb_ft_max_token_size                    | 84                     |
| innodb_ft_min_token_size                    | 3                      |
| innodb_ft_num_word_optimize                 | 2000                   |
| innodb_ft_result_cache_limit                | 2000000000             |
| innodb_ft_server_stopword_table             |                        |
| innodb_ft_sort_pll_degree                   | 2                      |
| innodb_ft_total_cache_size                  | 640000000              |
| innodb_ft_user_stopword_table               |                        |
| innodb_idle_flush_pct                       | 100                    |
| innodb_immediate_scrub_data_uncompressed    | OFF                    |
| innodb_io_capacity                          | 200                    |
| innodb_io_capacity_max                      | 2000                   |
| innodb_lock_schedule_algorithm              | fcfs                   |
| innodb_lock_wait_timeout                    | 50                     |
| innodb_locks_unsafe_for_binlog              | OFF                    |
| innodb_log_buffer_size                      | 16777216               |
| innodb_log_checksums                        | ON                     |
| innodb_log_compressed_pages                 | ON                     |
| innodb_log_file_size                        | 50331648               |
| innodb_log_files_in_group                   | 2                      |
| innodb_log_group_home_dir                   | ./                     |
| innodb_log_optimize_ddl                     | ON                     |
| innodb_log_write_ahead_size                 | 8192                   |
| innodb_lru_scan_depth                       | 1024                   |
| innodb_max_dirty_pages_pct                  | 75.000000              |
| innodb_max_dirty_pages_pct_lwm              | 0.000000               |
| innodb_max_purge_lag                        | 0                      |
| innodb_max_purge_lag_delay                  | 0                      |
| innodb_max_undo_log_size                    | 10485760               |
| innodb_monitor_disable                      |                        |
| innodb_monitor_enable                       |                        |
| innodb_monitor_reset                        |                        |
| innodb_monitor_reset_all                    |                        |
| innodb_old_blocks_pct                       | 37                     |
| innodb_old_blocks_time                      | 1000                   |
| innodb_online_alter_log_max_size            | 134217728              |
| innodb_open_files                           | 2000                   |
| innodb_optimize_fulltext_only               | OFF                    |
| innodb_page_cleaners                        | 1                      |
| innodb_page_size                            | 16384                  |
| innodb_prefix_index_cluster_optimization    | OFF                    |
| innodb_print_all_deadlocks                  | OFF                    |
| innodb_purge_batch_size                     | 300                    |
| innodb_purge_rseg_truncate_frequency        | 128                    |
| innodb_purge_threads                        | 4                      |
| innodb_random_read_ahead                    | OFF                    |
| innodb_read_ahead_threshold                 | 56                     |
| innodb_read_io_threads                      | 4                      |
| innodb_read_only                            | OFF                    |
| innodb_replication_delay                    | 0                      |
| innodb_rollback_on_timeout                  | OFF                    |
| innodb_rollback_segments                    | 128                    |
| innodb_scrub_log                            | OFF                    |
| innodb_scrub_log_speed                      | 256                    |
| innodb_sort_buffer_size                     | 1048576                |
| innodb_spin_wait_delay                      | 4                      |
| innodb_stats_auto_recalc                    | ON                     |
| innodb_stats_include_delete_marked          | OFF                    |
| innodb_stats_method                         | nulls_equal            |
| innodb_stats_modified_counter               | 0                      |
| innodb_stats_on_metadata                    | OFF                    |
| innodb_stats_persistent                     | ON                     |
| innodb_stats_persistent_sample_pages        | 20                     |
| innodb_stats_sample_pages                   | 8                      |
| innodb_stats_traditional                    | ON                     |
| innodb_stats_transient_sample_pages         | 8                      |
| innodb_status_output                        | OFF                    |
| innodb_status_output_locks                  | OFF                    |
| innodb_strict_mode                          | ON                     |
| innodb_sync_array_size                      | 1                      |
| innodb_sync_spin_loops                      | 30                     |
| innodb_table_locks                          | ON                     |
| innodb_temp_data_file_path                  | ibtmp1:12M:autoextend  |
| innodb_thread_concurrency                   | 0                      |
| innodb_thread_sleep_delay                   | 10000                  |
| innodb_tmpdir                               |                        |
| innodb_undo_directory                       | ./                     |
| innodb_undo_log_truncate                    | OFF                    |
| innodb_undo_logs                            | 128                    |
| innodb_undo_tablespaces                     | 0                      |
| innodb_use_atomic_writes                    | ON                     |
| innodb_use_native_aio                       | ON                     |
| innodb_version                              | 10.3.12                |
| innodb_write_io_threads                     | 4                      |
+---------------------------------------------+------------------------+

EDIT. Here is a test for adding 204 values into the database.

USE test_database;
insert into test_table values
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a');

It takes 0.22 s on average using InnoDB and 0.01 s using MyISAM.

EDIT. This is the profile for creating the table with InnoDB:

+------------------------+----------+----------+------------+
| Status                 | Duration | CPU_user | CPU_system |
+------------------------+----------+----------+------------+
| Starting               | 0.000035 | 0.000000 |   0.000079 |
| Checking permissions   | 0.000005 | 0.000000 |   0.000009 |
| Opening tables         | 0.000011 | 0.000000 |   0.000030 |
| After opening tables   | 0.000003 | 0.000000 |   0.000009 |
| System lock            | 0.000002 | 0.000000 |   0.000004 |
| Table lock             | 0.000030 | 0.000000 |   0.000061 |
| Creating table         | 0.455613 | 0.036331 |   0.107722 |
| After create           | 0.000022 | 0.000004 |   0.000015 |
| Query end              | 0.000011 | 0.000069 |   0.000240 |
| Commit                 | 0.000011 | 0.000005 |   0.000017 |
| Closing tables         | 0.000010 | 0.000005 |   0.000015 |
| Unlocking tables       | 0.000009 | 0.000056 |   0.000193 |
| Closing tables         | 0.000009 | 0.000031 |   0.000109 |
| Commit_implicit        | 0.000017 | 0.000051 |   0.000175 |
| Starting cleanup       | 0.000008 | 0.000007 |   0.000026 |
| Freeing items          | 0.000010 | 0.000003 |   0.000010 |
| Updating status        | 0.000035 | 0.000008 |   0.000028 |
| Reset for next command | 0.000054 | 0.000342 |   0.000000 |
+------------------------+----------+----------+------------+

And the same thing with MyISAM:

+------------------------+----------+----------+------------+
| Status                 | Duration | CPU_user | CPU_system |
+------------------------+----------+----------+------------+
| Starting               | 0.000062 | 0.000018 |   0.000043 |
| Checking permissions   | 0.000014 | 0.000004 |   0.000009 |
| Opening tables         | 0.000024 | 0.000007 |   0.000017 |
| After opening tables   | 0.000009 | 0.000003 |   0.000007 |
| System lock            | 0.000007 | 0.000002 |   0.000004 |
| Table lock             | 0.000052 | 0.000015 |   0.000037 |
| Creating table         | 0.006708 | 0.000235 |   0.000554 |
| After create           | 0.000018 | 0.000005 |   0.000012 |
| Query end              | 0.000009 | 0.000002 |   0.000006 |
| Commit                 | 0.000008 | 0.000002 |   0.000005 |
| Closing tables         | 0.000008 | 0.000003 |   0.000005 |
| Unlocking tables       | 0.000006 | 0.000001 |   0.000004 |
| Closing tables         | 0.000006 | 0.000002 |   0.000004 |
| Commit_implicit        | 0.000014 | 0.000004 |   0.000010 |
| Starting cleanup       | 0.000007 | 0.000002 |   0.000005 |
| Freeing items          | 0.000008 | 0.000003 |   0.000006 |
| Updating status        | 0.000028 | 0.000008 |   0.000019 |
| Reset for next command | 0.000008 | 0.000002 |   0.000006 |
+------------------------+----------+----------+------------+

This is the profile for inserting values into the table with InnoDB:

+------------------------+----------+----------+------------+
| Status                 | Duration | CPU_user | CPU_system |
+------------------------+----------+----------+------------+
| Starting               | 0.000439 | 0.000093 |   0.000345 |
| Checking permissions   | 0.000013 | 0.000003 |   0.000010 |
| Opening tables         | 0.000029 | 0.000006 |   0.000022 |
| After opening tables   | 0.000010 | 0.000002 |   0.000008 |
| System lock            | 0.000009 | 0.000002 |   0.000007 |
| Table lock             | 0.000009 | 0.000002 |   0.000007 |
| Init for update        | 0.000041 | 0.000009 |   0.000033 |
| Update                 | 0.001986 | 0.000000 |   0.002014 |
| End of update loop     | 0.000020 | 0.000000 |   0.000018 |
| Query end              | 0.000008 | 0.000000 |   0.000008 |
| Commit                 | 0.089969 | 0.011578 |   0.006221 |
| Closing tables         | 0.000030 | 0.000006 |   0.000019 |
| Unlocking tables       | 0.000011 | 0.000002 |   0.000008 |
| Closing tables         | 0.000019 | 0.000004 |   0.000016 |
| Starting cleanup       | 0.000009 | 0.000002 |   0.000006 |
| Freeing items          | 0.000023 | 0.000005 |   0.000018 |
| Updating status        | 0.000042 | 0.000009 |   0.000033 |
| Reset for next command | 0.000012 | 0.000002 |   0.000009 |
+------------------------+----------+----------+------------+

And the same thing with MyISAM:

+------------------------+----------+----------+------------+
| Status                 | Duration | CPU_user | CPU_system |
+------------------------+----------+----------+------------+
| Starting               | 0.000569 | 0.000561 |   0.000000 |
| Checking permissions   | 0.000015 | 0.000013 |   0.000000 |
| Opening tables         | 0.000031 | 0.000032 |   0.000000 |
| After opening tables   | 0.000010 | 0.000009 |   0.000000 |
| System lock            | 0.000009 | 0.000009 |   0.000000 |
| Table lock             | 0.000010 | 0.000010 |   0.000000 |
| Init for update        | 0.000062 | 0.000062 |   0.000000 |
| Update                 | 0.000246 | 0.000247 |   0.000000 |
| End of update loop     | 0.000018 | 0.000017 |   0.000000 |
| Query end              | 0.000008 | 0.000008 |   0.000000 |
| Commit                 | 0.000007 | 0.000007 |   0.000000 |
| Closing tables         | 0.000008 | 0.000007 |   0.000000 |
| Unlocking tables       | 0.000036 | 0.000037 |   0.000000 |
| Closing tables         | 0.000016 | 0.000016 |   0.000000 |
| Starting cleanup       | 0.000007 | 0.000007 |   0.000000 |
| Freeing items          | 0.000014 | 0.000013 |   0.000000 |
| Updating status        | 0.000030 | 0.000030 |   0.000000 |
| Reset for next command | 0.000009 | 0.000010 |   0.000000 |
+------------------------+----------+----------+------------+

Answer 1

TLDR; The problem turned out to be the file system. Don't use BTRFS for database storage.

---

I did an analysis of MariaDB timings for different file systems using some of the data that I have to work with. Even though this is specific to my situation I thought I'd post the results anyway in case there is interest.

I tried the following configurations:

• "BTRFS (subvolume)" - This is what I had prior to posting this question. One BTRFS partition for everything with subvolumes for things like / and /home. The directory /var/lib/mysql was located on the / subvolume.
• "BTRFS" - Separate BTRFS partition for /var/lib/mysql. No subvolumes here.
• "ext4" - Separate ext4 partition for /var/lib/mysql.
• "XFS" - Separate XFS partition for /var/lib/mysql. After having done some more research this is probably what I should use in a production environment. XFS is known for high performance.
• "F2FS" - Separate F2FS (Flash-Friendly File System) partition for /var/lib/mysql. This is fast but not considered stable enough for production environments. Only use this if potential data loss is acceptable.
• "F2FS (no_barrier)" - Same as "F2FS" but mounted with the fsync_mode=nobarrier option, which reduces the number of cache flushes. This is faster but even riskier than regular F2FS.

In all cases the file systems reside on a LUKS partition prepared with dm-crypt.

Test case 1.

First, I measured the time for creating tables and foreign key constraints for a total of 27 tables. I can't post the actual content here but each table has about 15 fields and there are a total of 81 FK constraints, just to give you a rough idea. The script that I ran also includes some other stuff like truncating all tables after they are created and altering some columns here and there. Here are the timings, in s. Each number is an average over two runs.

|                   | InnoDB | Aria |
|-------------------+--------+------|
| BTRFS (subvolume) |  473.9 | 86.7 |
| BTRFS             |   53.0 | 40.9 |
| ext4              |   35.1 | 29.0 |
| XFS               |   29.2 | 27.2 |
| F2FS              |   18.0 | 26.3 |
| F2FS (nobarrier)  |    5.6 | 16.9 |
|-------------------+--------+------|

As you can see, using InnoDB and keeping the databases on the same subvolume as / is an order of magnitude slower than other configurations. This is why I actually used Aria for my work until now. Using a separate BTRFS partition is much faster and switching to one of the other file systems even faster. I was surprised by the margin by which F2FS beats all other file systems.

Test case 2.

Here I'm inserting data into the tables created in case 1. Again, I can't post the actual contents but the total size of SQL statements executed is about 28 MiB, so a good amount for producing meaningful timings. These are the timings:

|                   | InnoDB |  Aria |
|-------------------+--------+-------|
| BTRFS (subvolume) | 1483.6 | 144.3 |
| BTRFS             |  134.3 | 137.7 |
| ext4              |   90.2 | 121.5 |
| XFS               |   82.6 | 116.3 |
| F2FS              |   51.1 | 105.6 |
| F2FS (nobarrier)  |   20.4 |  26.9 |
|-------------------+--------+-------|

The conclusions are the same as for case 1. BTRFS is significantly slower than the other file systems.

Test case 3.

This is just like case 2 but everything if wrapped between the START TRANSACTION; and COMMIT; statements.

|                   | InnoDB | Aria |
|-------------------+--------+------|
| BTRFS (subvolume) |  351.7 | N/A  |
| BTRFS             |   38.0 | N/A  |
| ext4              |   31.4 | N/A  |
| XFS               |   30.8 | N/A  |
| F2FS              |   26.1 | N/A  |
| F2FS (nobarrier)  |   18.8 | N/A  |
|-------------------+--------+------|

No results for Aria because it doesn't support transactions. Otherwise, wrapping everything into a single transaction reduces the time significantly for all cases except for F2FS (nobarrier), which is still the fastest solution, but only a small improvement compared to case 2.

Test case 4.

This is a short test where I'm using the same test_table as in the original question. The time measured is for creating the table and entering the 203 values into it. Each reported time is an average over five runs.

|                   | InnoDB |  Aria |
|-------------------+--------+-------|
| BTRFS (subvolume) |  0.914 | 0.105 |
| BTRFS             |  0.164 | 0.015 |
| ext4              |  0.122 | 0.080 |
| XFS               |  0.099 | 0.070 |
| F2FS              |  0.068 | 0.070 |
| F2FS (nobarrier)  |  0.037 | 0.053 |
|-------------------+--------+-------|

The fact that we are looking at a much smaller timescale here doesn't change the conclusions. Using BTRFS were the databases are kept on the same subvolume as / is still unacceptably slow.

Conclusion

I've decided to go with F2FS mounted with the fsync_mode=nobarrier option, which is clearly the fastest solution by a large margin. Since I'm using my personal computer for development only I'm fine with the potential loss of SQL data (should be quite unlikely, but still). Otherwise I'd go with XFS.

---

^{Hardware used: [email protected], 256 GB PCIe M.2 Samsung SM951}

Answer 2

Have innodb_file_per_table=OFF when doing this task. This will cut back on the I/O needed.

Also consider upgrading to MySQL 8.0, which has rewritten the Data Dictionary stuff.

Inserting rows is much faster if done in batches.