MySQL常見的配置引數概覽
一、MySQL常見的配置引數有400+項，其中DBA常用的配置引數大概可以分為如下幾種：
①、各種buffer：緩衝各種配置引數
②、各種cache：快取各種配置引數
③、各種log：日誌相關配置引數
④、各種file：檔案相關配置引數
⑤、各種max：各種最大值配置引數
二、在MySQL中輸入命令 show global variablesG;可以檢視配置引數的明細。如下為結尾部分：
mysql> show global variablesG
省略部分內容
*************************** 435. row ***************************
Variable_name: version
Value: 5.6.36
*************************** 436. row ***************************
Variable_name: version_comment
Value: MySQL Community Server (GPL)
*************************** 437. row ***************************
Variable_name: version_compile_machine
Value: x86_64
*************************** 438. row ***************************
Variable_name: version_compile_os
Value: linux-glibc2.5
*************************** 439. row ***************************
Variable_name: wait_timeout
Value: 28800
439 rows in set (0.11 sec)
三、各種buffer相關配置引數
mysql> show global variables like "%buffer%";
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 | 相當於128M
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
22 rows in set (0.00 sec)
引數說明：
①、innodb_buffer_pool_size：緩衝池位元組大小（建議為實體記憶體的75%）
預設為128M，MySQL中以KB為單位顯示。
②、innodb_log_buffer_size：為尚未執行的日誌寫入事務分配的快取（起到I/O加速的作用）
四、各種cache相關配置引數
mysql> show global variables like "%cache%";
+--------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| have_query_cache | YES |
| host_cache_size | 279 |
| innodb_disable_sort_file_cache | OFF |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_total_cache_size | 640000000 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| metadata_locks_cache_size | 1024 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| stored_program_cache | 256 |
| table_definition_cache | 1400 |
| table_open_cache | 2000 |
| table_open_cache_instances | 1 |
| thread_cache_size | 9 |
+--------------------------------+----------------------+
24 rows in set (0.00 sec)
五、各種log相關配置引數
mysql> show global variables like "%log%";
+-----------------------------------------+-----------------------------+
| Variable_name | Value |
+-----------------------------------------+-----------------------------+
| back_log | 80 |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | IGNORE_ERROR |
| binlog_format | STATEMENT |
| binlog_gtid_simple_recovery | OFF |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlogging_impossible_mode | IGNORE_ERROR |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /data/mysql/JSH-01.log |
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_undo_logs | 128 |
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /data/mysql/JSH-01.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| log_warnings | 1 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_basename | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| simplified_binlog_gtid_recovery | OFF |
| slow_query_log | OFF |
| slow_query_log_file | /data/mysql/JSH-01-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog | 0 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+-----------------------------------------+-----------------------------+
61 rows in set (0.00 sec)六、各種file相關配置引數
mysql> show global variables like "%file%";
+---------------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------------+-----------------------------+
| character_set_filesystem | binary |
| core_file | OFF |
| ft_stopword_file | (built-in) |
| general_log_file | /data/mysql/JSH-01.log |
| init_file | |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_disable_sort_file_cache | OFF |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_open_files | 2000 |
| keep_files_on_create | OFF |
| large_files_support | ON |
| local_infile | ON |
| lower_case_file_system | OFF |
| myisam_max_sort_file_size | 9223372036853727232 |
| open_files_limit | 65535 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | 7693 |
| pid_file | /data/mysql/JSH-01.pid |
| relay_log_info_file | relay-log.info |
| secure_file_priv | NULL |
| slow_query_log_file | /data/mysql/JSH-01-slow.log |
+---------------------------------------+-----------------------------+
28 rows in set (0.00 sec)七、各種max相關配置引數
mysql> show global variables like "%max%";
+------------------------------------------+----------------------+
| Variable_name | Value |
+------------------------------------------+----------------------+
| binlog_max_flush_queue_time | 0 |
| ft_max_word_len | 84 |
| group_concat_max_len | 1024 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_change_buffer_max_size | 25 |
| innodb_compression_pad_pct_max | 50 |
| innodb_file_format_max | Antelope |
| innodb_ft_max_token_size | 84 |
| innodb_io_capacity_max | 2000 |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_dirty_pages_pct_lwm | 0 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_online_alter_log_max_size | 134217728 |
| max_allowed_packet | 4194304 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_delayed_threads | 20 |
| max_digest_length | 1024 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| myisam_max_sort_file_size | 9223372036853727232 |
| optimizer_trace_max_mem_size | 16384 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | 3504 |
| performance_schema_max_digest_length | 1024 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | 7693 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_mutex_instances | 15906 |
| performance_schema_max_rwlock_classes | 40 |
| performance_schema_max_rwlock_instances | 9102 |
| performance_schema_max_socket_classes | 10 |
| performance_schema_max_socket_instances | 322 |
| performance_schema_max_stage_classes | 150 |
| performance_schema_max_statement_classes | 168 |
| performance_schema_max_table_handles | 4000 |
| performance_schema_max_table_instances | 12500 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 402 |
| slave_max_allowed_packet | 1073741824 |
| slave_pending_jobs_size_max | 16777216 |
+------------------------------------------+----------------------+
57 rows in set (0.00 sec)
相關文章
- nginx 常見引數以及重定向引數配置Nginx
- Nginx 內容快取及常見引數配置Nginx快取
- JVM常見引數設定JVM
- MySQL的配置檔案的引數設定MySql
- "三高"Mysql - Mysql備份概覽MySql
- MySQL體系結構概覽MySql
- CUDA常見編譯器配置問題一覽編譯
- Java教程：影響MySQL效能的配置引數JavaMySql
- MySQL日誌引數配置簡介MySql
- 常用的jvm配置引數 ：永久區引數配置JVM
- docker常見配置Docker
- Nginx常見配置Nginx
- mysqldump常見使用場景及引數參考MySql
- mysql常見操作MySql
- Python指令碼的常見引數獲取和處理方式Python指令碼
- 概覽
- Django中的session常見配置DjangoSession
- MySQL8.0新增配置引數詳解MySql
- MySQL常見索引概念MySql索引
- MySQL 常見錯誤MySql
- Mysql:常見問題MySql
- 引數配置
- 配置Java環境變數時的一個常見錯誤Java變數
- 線性同餘-常見語言編譯器引數編譯
- SoDA：2020年數字概覽報告
- keepalived 1.3.5常見配置以及常見問題解決
- DOM概覽
- 公司概覽
- MySQL效能最佳化之Open_Table配置引數的合理配置建議MySql
- 怎樣看電腦顯示器的各種引數？常見的電腦顯示器引數詳解
- Mysql：1236常見錯誤MySql
- Mysql 常見面試題MySql面試題
- ES 筆記十二： 顯示 Mapping 設定與常見引數筆記APP
- JavaWeb引數配置JavaWeb
- MySQL 配置InnoDB配置非持久優化器統計資訊引數MySql優化
- MySQL主從複製配置引數 -- logs-slave-updatesMySql
- MySQL服務端innodb_buffer_pool_size配置引數MySql服務端
- MySQL中幾種常見的日誌MySql