Innodb
MariaDB 10.3：InnoDB 比 MyISAM 慢得多
我在 Arch Linux 上使用 MariaDB 10.3.12 並且沒有修改
/etc/mysql. 以下腳本使用預設的 InnoDB 引擎平均需要 0.75 秒才能執行：drop database if exists test_database; create database test_database; USE test_database; CREATE TABLE test_table (namespace char(31) NOT NULL);對於這麼簡單的事情來說，這是相當多的。使用 MyISAM (
default_storage_engine=myisamin/etc/mysql/my.cnf.d/server.cnf) 執行時間為 0.016 秒（使用 aria 為 0.07 秒）。在我擁有 MariaDB v 10.1.38 的 Ubuntu 虛擬機中，它的平均執行時間為 0.04 秒。有什麼方法可以解釋為什麼 InnoDB 這麼慢以及我應該測試哪些變數？我知道微調性能有很多變數，但令人費解的是，與其他引擎相比*，數量級*差異可能來自哪裡。
這是我目前的 InnoDB 變數：
+---------------------------------------------+------------------------+ | 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 | +---------------------------------------------+------------------------+**編輯。**這是一個將 204 個值添加到數據庫中的測試。
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');使用 InnoDB 平均需要 0.22 秒，使用 MyISAM 平均需要 0.01 秒。
**編輯。**這是使用 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 | +------------------------+----------+----------+------------+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 | +------------------------+----------+----------+------------+這是使用 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 | +------------------------+----------+----------+------------+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 | +------------------------+----------+----------+------------+
**TLDR；**問題原來是文件系統。不要將 BTRFS 用於數據庫儲存。
我使用一些我必須使用的數據對不同文件系統的 MariaDB 時序進行了分析。儘管這是針對我的情況的，但我認為無論如何我都會發布結果以防萬一。
我嘗試了以下配置：
- “BTRFS（子卷）” - 這是我在發布這個問題之前所擁有的。一個 BTRFS 分區，用於所有內容，並帶有子卷，例如
/和/home. 該目錄/var/lib/mysql位於/子卷上。- “BTRFS” - 為
/var/lib/mysql. 這裡沒有子卷。- “ext4” - 為
/var/lib/mysql.- “XFS” - 為
/var/lib/mysql. 在做了更多的研究之後，這可能是我應該在生產環境中使用的。XFS 以高性能著稱。- “F2FS” - 為
/var/lib/mysql. 這速度很快，但對於生產環境來說不夠穩定。只有在可以接受潛在數據失去的情況下才使用此選項。- “F2FS (no_barrier)” - 與 “F2FS” 相同，但安裝了
fsync_mode=nobarrier選項，這減少了記憶體刷新的次數。這比正常 F2FS 更快，但風險更大。在所有情況下，文件系統都駐留在使用 dm-crypt 準備的 LUKS 分區上。
測試案例 1。
首先，我測量了總共 27 個表的創建表和外鍵約束的時間。我不能在這裡發布實際內容，但是每個表大約有 15 個欄位，總共有 81 個 FK 約束，只是為了給你一個粗略的想法。我執行的腳本還包括其他一些內容，例如在創建所有表後截斷它們並在此處和那裡更改某些列。這是時間，以 s 為單位。每個數字是兩次執行的平均值。
| | 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 | |-------------------+--------+------|如您所見，使用 InnoDB 並將數據庫保持在與其他配置相同的子卷上
/比其他配置慢一個數量級。這就是為什麼我直到現在才真正使用 Aria 來工作。使用單獨的 BTRFS 分區要快得多，切換到其他文件系統的速度更快。我對 F2FS 擊敗所有其他文件系統的優勢感到驚訝。測試案例 2。
在這裡，我將數據插入到案例 1 中創建的表中。同樣，我無法發布實際內容，但執行的 SQL 語句的總大小約為 28 MiB，因此對於產生有意義的計時來說是一個很好的數量。這些是時間：
| | 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 | |-------------------+--------+-------|結論與案例 1 相同。BTRFS 明顯慢於其他文件系統。
測試案例 3。
這就像案例 2，但所有內容都包含在
START TRANSACTION;andCOMMIT;語句之間。| | 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 | |-------------------+--------+------|Aria 沒有結果，因為它不支持事務。否則，將所有內容包裝到單個事務中會顯著減少所有情況的時間，除了 F2FS（無屏障），這仍然是最快的解決方案，但與情況 2 相比只有很小的改進。
測試案例 4。
這是一個簡短的測試，我使用與
test_table原始問題相同的方法。測量的時間用於創建表格並將 203 值輸入其中。每個報告的時間是五次執行的平均值。| | 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 | |-------------------+--------+-------|我們在這裡看到的時間尺度要小得多這一事實並沒有改變結論。如果數據庫保存在同一個子卷上，使用 BTRFS
/仍然慢得令人無法接受。結論
我決定使用安裝了該
fsync_mode=nobarrier選項的 F2FS，這顯然是最快的解決方案。由於我只使用我的個人電腦進行開發，因此我對可能失去 SQL 數據的情況很好（應該不太可能，但仍然如此）。否則我會選擇 XFS。使用的硬體：i7-6700HQ@2.60GHz，256 GB PCIe M.2 Samsung SM951