mariadb サーバのパラメータ

秘伝のタレ

mysqlを使ってる頃からあるパラメータ。
今はdockerコンテナでmariadbに移したものを利用。
普段はクラウドの中で動いていて、データとログとパラメータを永続化領域に置いてバックアップしたものを、ローカルdockerの中にそのまま持ってきて環境をクローンしてる。

無効な内容もあるかもしれないけど、時々見直しはしても基本このまま。

[mysqld]
max_allowed_packet=2000M
log_warnings=1
query_cache_size=640M
query_cache_type=1
query_cache_limit=1280M
innodb_buffer_pool_size=5120M
innodb_log_file_size=128M
read_buffer_size=320M
log-error=/var/log/mariadb.log
slow_query_log
slow_query_log_file=/var/log/mariadb_slow.log
long_query_time=30

innodb_data_file_path=ibdata1:1G
innodb_file_per_table=ON

[mysqldump]

max_allowed_packet=200M

mysql使い始めの頃はマシンのメモリも8GB程度だったけど、今は20GB程度をdockerに使わせるからチューニングとかしなくなった。

innodb関連のパラメータを確認

show variables like '%innodb%' ;
Variable_nameValue
ignore_builtin_innodbOFF
innodb_adaptive_flushingON
innodb_adaptive_flushing_lwm10.000000
innodb_adaptive_hash_indexOFF
innodb_adaptive_hash_index_parts8
innodb_adaptive_max_sleep_delay0
innodb_autoextend_increment64
innodb_autoinc_lock_mode1
innodb_background_scrub_data_check_interval0
innodb_background_scrub_data_compressedOFF
innodb_background_scrub_data_interval0
innodb_background_scrub_data_uncompressedOFF
innodb_buf_dump_status_frequency0
innodb_buffer_pool_chunk_size134217728
innodb_buffer_pool_dump_at_shutdownON
innodb_buffer_pool_dump_nowOFF
innodb_buffer_pool_dump_pct25
innodb_buffer_pool_filenameib_buffer_pool
innodb_buffer_pool_instances1
innodb_buffer_pool_load_abortOFF
innodb_buffer_pool_load_at_startupON
innodb_buffer_pool_load_nowOFF
innodb_buffer_pool_size134217728
innodb_change_buffer_max_size25
innodb_change_bufferingall
innodb_checksum_algorithmfull_crc32
innodb_cmp_per_index_enabledOFF
innodb_commit_concurrency0
innodb_compression_algorithmzlib
innodb_compression_defaultOFF
innodb_compression_failure_threshold_pct5
innodb_compression_level6
innodb_compression_pad_pct_max50
innodb_concurrency_tickets0
innodb_data_file_pathibdata1:12M:autoextend
innodb_data_home_dir
innodb_deadlock_detectON
innodb_default_encryption_key_id1
innodb_default_row_formatdynamic
innodb_defragmentOFF
innodb_defragment_fill_factor0.900000
innodb_defragment_fill_factor_n_recs20
innodb_defragment_frequency40
innodb_defragment_n_pages7
innodb_defragment_stats_accuracy0
innodb_disable_sort_file_cacheOFF
innodb_disallow_writesOFF
innodb_doublewriteON
innodb_encrypt_logOFF
innodb_encrypt_tablesOFF
innodb_encrypt_temporary_tablesOFF
innodb_encryption_rotate_key_age1
innodb_encryption_rotation_iops100
innodb_encryption_threads0
innodb_fast_shutdown1
innodb_fatal_semaphore_wait_threshold600
innodb_file_format
innodb_file_per_tableON
innodb_fill_factor100
innodb_flush_log_at_timeout1
innodb_flush_log_at_trx_commit1
innodb_flush_methodfsync
innodb_flush_neighbors1
innodb_flush_syncON
innodb_flushing_avg_loops30
innodb_force_load_corruptedOFF
innodb_force_primary_keyOFF
innodb_force_recovery0
innodb_ft_aux_table
innodb_ft_cache_size8000000
innodb_ft_enable_diag_printOFF
innodb_ft_enable_stopwordON
innodb_ft_max_token_size84
innodb_ft_min_token_size3
innodb_ft_num_word_optimize2000
innodb_ft_result_cache_limit2000000000
innodb_ft_server_stopword_table
innodb_ft_sort_pll_degree2
innodb_ft_total_cache_size640000000
innodb_ft_user_stopword_table
innodb_idle_flush_pct100
innodb_immediate_scrub_data_uncompressedOFF
innodb_instant_alter_column_allowedadd_drop_reorder
innodb_io_capacity200
innodb_io_capacity_max2000
innodb_large_prefix
innodb_lock_schedule_algorithmfcfs
innodb_lock_wait_timeout50
innodb_log_buffer_size16777216
innodb_log_checksumsON
innodb_log_compressed_pagesON
innodb_log_file_size100663296
innodb_log_files_in_group1
innodb_log_group_home_dir./
innodb_log_optimize_ddlOFF
innodb_log_write_ahead_size8192
innodb_lru_flush_size32
innodb_lru_scan_depth1536
innodb_max_dirty_pages_pct90.000000
innodb_max_dirty_pages_pct_lwm0.000000
innodb_max_purge_lag0
innodb_max_purge_lag_delay0
innodb_max_purge_lag_wait4294967295
innodb_max_undo_log_size10485760
innodb_monitor_disable
innodb_monitor_enable
innodb_monitor_reset
innodb_monitor_reset_all
innodb_old_blocks_pct37
innodb_old_blocks_time1000
innodb_online_alter_log_max_size134217728
innodb_open_files2000
innodb_optimize_fulltext_onlyOFF
innodb_page_cleaners1
innodb_page_size16384
innodb_prefix_index_cluster_optimizationOFF
innodb_print_all_deadlocksOFF
innodb_purge_batch_size300
innodb_purge_rseg_truncate_frequency128
innodb_purge_threads4
innodb_random_read_aheadOFF
innodb_read_ahead_threshold56
innodb_read_io_threads4
innodb_read_onlyOFF
innodb_replication_delay0
innodb_rollback_on_timeoutOFF
innodb_scrub_logOFF
innodb_scrub_log_speed256
innodb_sort_buffer_size1048576
innodb_spin_wait_delay4
innodb_stats_auto_recalcON
innodb_stats_include_delete_markedOFF
innodb_stats_methodnulls_equal
innodb_stats_modified_counter0
innodb_stats_on_metadataOFF
innodb_stats_persistentON
innodb_stats_persistent_sample_pages20
innodb_stats_traditionalON
innodb_stats_transient_sample_pages8
innodb_status_outputOFF
innodb_status_output_locksOFF
innodb_strict_modeON
innodb_sync_array_size1
innodb_sync_spin_loops30
innodb_table_locksON
innodb_temp_data_file_pathibtmp1:12M:autoextend
innodb_thread_concurrency0
innodb_thread_sleep_delay0
innodb_tmpdir
innodb_undo_directory./
innodb_undo_log_truncateOFF
innodb_undo_logs128
innodb_undo_tablespaces0
innodb_use_atomic_writesON
innodb_use_native_aioON
innodb_version10.5.7
innodb_write_io_threads4

コメント