はじめに
この休みの間はひたすらこれです。
というわけで前回はベンチマークのための環境準備とmysqltuner先生による指摘反映をして、少し改善しました。
今の問題はupgrade後のほうが性能が落ちているように見えていることです。これを解決するチューニングは何か？というのを探していきたいともいます。
今回はインターネット上で検索してみましょう。「MariaDB チューニング」で期間を直近1年にしぼって検索してみました。
スロークエリを有効にする
性能を判断できる指標は多いほうがいいです。general_query_logを出すと大量になってしまいますが、スロークエリは常に出すべきでしょう。mysqltuner先生もそうおっしゃっていました。
slow_query_log=ON long_query_time=0.5 log-slow-queries=/var/lib/mysql/slow.log
さて現在のsysbenchでスロークエリが出ているのか、確認してみましたが、スロークエリは出ていませんでした。ただ、今後問題発生したときにまっさきにみたいので、設定しておきましょう。
innodb_buffer_pool_size
現在総メモリの25%にしていますが、ストレージエンジンはinnodbしか使っていないため、80&まで割り当ててみます。1GBなので768MBに設定。
25%と80%で比較してみます。
改善効果は見られないどころか、一部劣化しているので25%に戻しますかね。
variablesの差分をみる
バージョンが異なり、デフォルト値が変更になったパラメータも多数あります。膨大にありますが、関係ありそうなものを見つけるのに1度やってみます。
mysql -u root -e "show variables;"
thread
[root@mariadb10-2-8 ~]# diff variables_5_5_45.log variables_10_2_8.log | grep thread > innodb_encryption_threads 0 > innodb_mtflush_threads 8 < innodb_purge_threads 1 > innodb_purge_threads 4 < innodb_thread_concurrency_timer_based OFF < performance_schema_max_thread_instances 1000 > performance_schema_max_thread_instances 6100 < pseudo_thread_id 2046 > pseudo_thread_id 2054 > slave_domain_parallel_threads 0 > slave_parallel_threads 0 < thread_pool_max_threads 500 > thread_pool_max_threads 65536 > thread_pool_prio_kickup_timer 1000 > thread_pool_priority auto < thread_stack 294912 > thread_stack 299008 > wsrep_slave_threads 1
thread_pool_max_threadsが全然違いますね。
Thread Pool System and Status Variables - MariaDB Knowledge Base
スレッドプール内の最大スレッド数で、これに達すると新しくスレッドが作られなくなります。これは大きい方がいいと思います。
max_allowed_packet
クライアントが送ることができる最大パケットサイズですね。
10.2.8では16MBがデフォルトになったようです。
[root@mariadb10-2-8 ~]# diff variables_5_5_45.log variables_10_2_8.log | grep max_allow < max_allowed_packet 1048576 > max_allowed_packet 16777216
innodb
大量に出ますが、ぱっとみ何か気づくものはありませんでした。（この中に原因はあるかもしれないけど）
[root@mariadb10-2-8 ~]# diff variables_5_5_45.log variables_10_2_8.log | grep innodb < have_innodb YES < innodb_adaptive_flushing_method estimate > innodb_adaptive_flushing_lwm 10.000000 < innodb_adaptive_hash_index_partitions 1 < innodb_additional_mem_pool_size 8388608 < innodb_autoextend_increment 8 > innodb_adaptive_hash_index_partitions 8 > innodb_adaptive_hash_index_parts 8 > innodb_adaptive_max_sleep_delay 150000 > innodb_autoextend_increment 64 < innodb_blocking_buffer_pool_restore OFF > 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_load_abort OFF > innodb_buffer_pool_load_at_startup ON > innodb_buffer_pool_load_now OFF < innodb_buffer_pool_restore_at_startup 0 < innodb_buffer_pool_shm_checksum ON < innodb_buffer_pool_shm_key 0 > innodb_change_buffer_max_size 25 < innodb_checkpoint_age_target 0 > innodb_checksum_algorithm crc32 > innodb_cleaner_lsn_age_factor DEPRECATED > innodb_cmp_per_index_enabled OFF < innodb_concurrency_tickets 500 < innodb_corrupt_table_action assert < innodb_data_file_path ibdata1:10M:autoextend > 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_corrupt_table_action deprecated > innodb_data_file_path ibdata1:12M:autoextend < innodb_dict_size_limit 0 > 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_file > innodb_empty_free_list_algorithm DEPRECATED > 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_checksum OFF < innodb_file_format Antelope > innodb_fatal_semaphore_wait_threshold 600 > innodb_file_format Barracuda < innodb_file_format_max Antelope > innodb_file_format_max Barracuda > innodb_fill_factor 100 > innodb_flush_log_at_timeout 1 < innodb_flush_neighbor_pages area > innodb_flush_neighbors 1 > innodb_flush_sync ON > innodb_flushing_avg_loops 30 > innodb_force_primary_key OFF < innodb_ibuf_accel_rate 100 < innodb_ibuf_active_contract 1 < innodb_ibuf_max_size 402636800 < innodb_import_table_from_xtrabackup 0 > innodb_foreground_preflush DEPRECATED > 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_instrument_semaphores OFF > innodb_io_capacity_max 2000 < innodb_large_prefix OFF < innodb_lazy_drop_table 0 > innodb_large_prefix ON > innodb_lock_schedule_algorithm vats < innodb_locking_fake_changes ON > innodb_locking_fake_changes OFF < innodb_log_block_size 512 < innodb_log_buffer_size 8388608 > innodb_log_arch_dir > innodb_log_arch_expire_sec 0 > innodb_log_archive OFF > innodb_log_block_size 0 > innodb_log_buffer_size 16777216 > innodb_log_checksum_algorithm DEPRECATED > innodb_log_checksums ON > innodb_log_compressed_pages ON < innodb_max_bitmap_file_size 104857600 < innodb_max_changed_pages 1000000 < innodb_max_dirty_pages_pct 75 > innodb_log_write_ahead_size 8192 > innodb_lru_scan_depth 1024 > innodb_max_bitmap_file_size 0 > innodb_max_changed_pages 0 > innodb_max_dirty_pages_pct 75.000000 > innodb_max_dirty_pages_pct_lwm 0.000000 < innodb_merge_sort_block_size 1048576 < innodb_mirrored_log_groups 1 > innodb_max_purge_lag_delay 0 > innodb_max_undo_log_size 10485760 > innodb_mirrored_log_groups 0 > innodb_monitor_disable > innodb_monitor_enable > innodb_monitor_reset > innodb_monitor_reset_all > innodb_mtflush_threads 8 < innodb_old_blocks_time 0 < innodb_open_files 300 > 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_prefix_index_cluster_optimization OFF < innodb_purge_batch_size 20 < innodb_purge_threads 1 > innodb_purge_batch_size 300 > innodb_purge_rseg_truncate_frequency 128 > innodb_purge_threads 4 < innodb_read_ahead linear < innodb_recovery_stats OFF < innodb_recovery_update_relay_log OFF > innodb_read_only OFF < innodb_show_locks_held 10 > innodb_sched_priority_cleaner 0 > innodb_scrub_log OFF > innodb_scrub_log_speed 256 > innodb_show_locks_held 0 < innodb_simulate_comp_failures 0 > innodb_sort_buffer_size 1048576 < innodb_stats_auto_update 1 > innodb_stats_auto_recalc ON > innodb_stats_include_delete_marked OFF < innodb_stats_on_metadata ON > innodb_stats_on_metadata OFF > innodb_stats_persistent ON > innodb_stats_persistent_sample_pages 20 < innodb_stats_update_need_lock 1 < innodb_strict_mode OFF > 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_temp_data_file_path ibtmp1:12M:autoextend < innodb_thread_concurrency_timer_based OFF > innodb_tmpdir < innodb_use_atomic_writes OFF > innodb_track_redo_log_now OFF > innodb_undo_directory ./ > innodb_undo_log_truncate OFF > innodb_undo_logs 128 > innodb_undo_tablespaces 0 > innodb_use_atomic_writes ON < innodb_use_global_flush_log_at_trx_commit ON > innodb_use_global_flush_log_at_trx_commit OFF > innodb_use_mtflush OFF < innodb_use_sys_malloc ON < innodb_use_sys_stats_table OFF < innodb_version 5.5.44-MariaDB-37.3 > innodb_use_trim ON > innodb_version 5.7.19
おわりに
今回はバージョン間での性能歳を見つけることができませんでした。
次回は何が原因でトランザクション性能に差分がでているのか、その指標になる値を見つけて、比較したのち、パラメータ変更につなげていきます。
