mysql基础调优_MYSQL基础：参数调优:1:InnoDB调优参数基本信息一览

InnoDB、MyISAM 都是MySQL存储引擎，在InnoDB存储引擎中，page是最小单位，然后是extent(区)，段(segment)和tablespace，和Oracle在名称上较为相似。

默认情况下，表空间的page的大小为16K，但可以调整默认大小(innodb_page_size)，Page的数量最少为64个，常见的组合如下所示：

Page Size

Page Count

Extent Size

4KB

256

1MB

8KB

128

1MB

16KB

64

1MB

32KB

64

2MB

64KB

64

4MB

InnoDB文件格式

InnoDB方式作为存储引擎，文件会分为.frm和.ibd两类，简单说明如下：

.frm：包含表定义等meta数据的文件

.ibd：主要存储数据信息，包括表的数据和索引数据等。

确认方式

方式1: show engines

可以通过show engines命令来确认当前的存储引擎, 可以看到InnoDB是DEFAULT的设定。

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

mysql>

InnoDB选项和变量一览

Name

Cmd-Line

Option File

System Var

Status Var

Var Scope

Dynamic

daemon_memcached_enable_binlog

Yes

Yes

Yes

Global

No

daemon_memcached_engine_lib_name

Yes

Yes

Yes

Global

No

daemon_memcached_engine_lib_path

Yes

Yes

Yes

Global

No

daemon_memcached_option

Yes

Yes

Yes

Global

No

daemon_memcached_r_batch_size

Yes

Yes

Yes

Global

No

daemon_memcached_w_batch_size

Yes

Yes

Yes

Global

No

foreign_key_checks

Yes

Both

Yes

ignore-builtin-innodb

Yes

Yes

Global

No

- Variable: ignore_builtin_innodb

Yes

Global

No

innodb

Yes

Yes

innodb_adaptive_flushing

Yes

Yes

Yes

Global

Yes

innodb_adaptive_flushing_lwm

Yes

Yes

Yes

Global

Yes

innodb_adaptive_hash_index

Yes

Yes

Yes

Global

Yes

innodb_adaptive_hash_index_parts

Yes

Yes

Yes

Global

No

innodb_adaptive_max_sleep_delay

Yes

Yes

Yes

Global

Yes

innodb_additional_mem_pool_size

Yes

Yes

Yes

Global

No

innodb_api_bk_commit_interval

Yes

Yes

Yes

Global

Yes

innodb_api_disable_rowlock

Yes

Yes

Yes

Global

No

innodb_api_enable_binlog

Yes

Yes

Yes

Global

No

innodb_api_enable_mdl

Yes

Yes

Yes

Global

No

innodb_api_trx_level

Yes

Yes

Yes

Global

Yes

innodb_autoextend_increment

Yes

Yes

Yes

Global

Yes

innodb_autoinc_lock_mode

Yes

Yes

Yes

Global

No

Innodb_available_undo_logs

Yes

Global

No

innodb_background_drop_list_empty

Yes

Yes

Yes

Global

Yes

Innodb_buffer_pool_bytes_data

Yes

Global

No

Innodb_buffer_pool_bytes_dirty

Yes

Global

No

innodb_buffer_pool_chunk_size

Yes

Yes

Yes

Global

No

innodb_buffer_pool_dump_at_shutdown

Yes

Yes

Yes

Global

Yes

innodb_buffer_pool_dump_now

Yes

Yes

Yes

Global

Yes

innodb_buffer_pool_dump_pct

Yes

Yes

Yes

Global

Yes

Innodb_buffer_pool_dump_status

Yes

Global

No

innodb_buffer_pool_filename

Yes

Yes

Yes

Global

Yes

innodb_buffer_pool_instances

Yes

Yes

Yes

Global

No

innodb_buffer_pool_load_abort

Yes

Yes

Yes

Global

Yes

innodb_buffer_pool_load_at_startup

Yes

Yes

Yes

Global

No

innodb_buffer_pool_load_now

Yes

Yes

Yes

Global

Yes

Innodb_buffer_pool_load_status

Yes

Global

No

Innodb_buffer_pool_pages_data

Yes

Global

No

Innodb_buffer_pool_pages_dirty

Yes

Global

No

Innodb_buffer_pool_pages_flushed

Yes

Global

No

Innodb_buffer_pool_pages_free

Yes

Global

No

Innodb_buffer_pool_pages_latched

Yes

Global

No

Innodb_buffer_pool_pages_misc

Yes

Global

No

Innodb_buffer_pool_pages_total

Yes

Global

No

Innodb_buffer_pool_read_ahead

Yes

Global

No

Innodb_buffer_pool_read_ahead_evicted

Yes

Global

No

Innodb_buffer_pool_read_ahead_rnd

Yes

Global

No

Innodb_buffer_pool_read_requests

Yes

Global

No

Innodb_buffer_pool_reads

Yes

Global

No

Innodb_buffer_pool_resize_status

Yes

Global

No

innodb_buffer_pool_size

Yes

Yes

Yes

Global

Varies

Innodb_buffer_pool_wait_free

Yes

Global

No

Innodb_buffer_pool_write_requests

Yes

Global

No

innodb_change_buffer_max_size

Yes

Yes

Yes

Global

Yes

innodb_change_buffering

Yes

Yes

Yes

Global

Yes

innodb_change_buffering_debug

Yes

Yes

Yes

Global

Yes

innodb_checksum_algorithm

Yes

Yes

Yes

Global

Yes

innodb_checksums

Yes

Yes

Yes

Global

No

innodb_cmp_per_index_enabled

Yes

Yes

Yes

Global

Yes

innodb_commit_concurrency

Yes

Yes

Yes

Global

Yes

innodb_compress_debug

Yes

Yes

Yes

Global

Yes

innodb_compression_failure_threshold_pct

Yes

Yes

Yes

Global

Yes

innodb_compression_level

Yes

Yes

Yes

Global

Yes

innodb_compression_pad_pct_max

Yes

Yes

Yes

Global

Yes

innodb_concurrency_tickets

Yes

Yes

Yes

Global

Yes

innodb_data_file_path

Yes

Yes

Yes

Global

No

Innodb_data_fsyncs

Yes

Global

No

innodb_data_home_dir

Yes

Yes

Yes

Global

No

Innodb_data_pending_fsyncs

Yes

Global

No

Innodb_data_pending_reads

Yes

Global

No

Innodb_data_pending_writes

Yes

Global

No

Innodb_data_read

Yes

Global

No

Innodb_data_reads

Yes

Global

No

Innodb_data_writes

Yes

Global

No

Innodb_data_written

Yes

Global

No

Innodb_dblwr_pages_written

Yes

Global

No

Innodb_dblwr_writes

Yes

Global

No

innodb_deadlock_detect

Yes

Yes

Yes

Global

Yes

innodb_default_row_format

Yes

Yes

Yes

Global

Yes

innodb_disable_resize_buffer_pool_debug

Yes

Yes

Yes

Global

Yes

innodb_disable_sort_file_cache

Yes

Yes

Yes

Global

Yes

innodb_doublewrite

Yes

Yes

Yes

Global

No

innodb_fast_shutdown

Yes

Yes

Yes

Global

Yes

innodb_fil_make_page_dirty_debug

Yes

Yes

Yes

Global

Yes

innodb_file_format

Yes

Yes

Yes

Global

Yes

innodb_file_format_check

Yes

Yes

Yes

Global

No

innodb_file_format_max

Yes

Yes

Yes

Global

Yes

innodb_file_per_table

Yes

Yes

Yes

Global

Yes

innodb_fill_factor

Yes

Yes

Yes

Global

Yes

innodb_flush_log_at_timeout

Yes

Yes

Global

Yes

innodb_flush_log_at_trx_commit

Yes

Yes

Yes

Global

Yes

innodb_flush_method

Yes

Yes

Yes

Global

No

innodb_flush_neighbors

Yes

Yes

Yes

Global

Yes

innodb_flush_sync

Yes

Yes

Yes

Global

Yes

innodb_flushing_avg_loops

Yes

Yes

Yes

Global

Yes

innodb_force_load_corrupted

Yes

Yes

Yes

Global

No

innodb_force_recovery

Yes

Yes

Yes

Global

No

innodb_ft_aux_table

Yes

Global

Yes

innodb_ft_cache_size

Yes

Yes

Yes

Global

No

innodb_ft_enable_diag_print

Yes

Yes

Yes

Global

Yes

innodb_ft_enable_stopword

Yes

Yes

Yes

Both

Yes

innodb_ft_max_token_size

Yes

Yes

Yes

Global

No

innodb_ft_min_token_size

Yes

Yes

Yes

Global

No

innodb_ft_num_word_optimize

Yes

Yes

Yes

Global

Yes

innodb_ft_result_cache_limit

Yes

Yes

Yes

Global

Yes

innodb_ft_server_stopword_table

Yes

Yes

Yes

Global

Yes

innodb_ft_sort_pll_degree

Yes

Yes

Yes

Global

No

innodb_ft_total_cache_size

Yes

Yes

Yes

Global

No

innodb_ft_user_stopword_table

Yes

Yes

Yes

Both

Yes

Innodb_have_atomic_builtins

Yes

Global

No

innodb_io_capacity

Yes

Yes

Yes

Global

Yes

innodb_io_capacity_max

Yes

Yes

Yes

Global

Yes

innodb_large_prefix

Yes

Yes

Yes

Global

Yes

innodb_limit_optimistic_insert_debug

Yes

Yes

Yes

Global

Yes

innodb_lock_wait_timeout

Yes

Yes

Yes

Both

Yes

innodb_locks_unsafe_for_binlog

Yes

Yes

Yes

Global

No

innodb_log_buffer_size

Yes

Yes

Yes

Global

No

innodb_log_checkpoint_now

Yes

Yes

Yes

Global

Yes

innodb_log_checksum_algorithm

Yes

Yes

Yes

Global

Yes

innodb_log_checksums

Yes

Yes

Yes

Global

Yes

innodb_log_compressed_pages

Yes

Yes

Yes

Global

Yes

innodb_log_file_size

Yes

Yes

Yes

Global

No

innodb_log_files_in_group

Yes

Yes

Yes

Global

No

innodb_log_group_home_dir

Yes

Yes

Yes

Global

No

Innodb_log_waits

Yes

Global

No

innodb_log_write_ahead_size

Yes

Yes

Yes

Global

Yes

Innodb_log_write_requests

Yes

Global

No

Innodb_log_writes

Yes

Global

No

innodb_lru_scan_depth

Yes

Yes

Yes

Global

Yes

innodb_max_dirty_pages_pct

Yes

Yes

Yes

Global

Yes

innodb_max_dirty_pages_pct_lwm

Yes

Yes

Yes

Global

Yes

innodb_max_purge_lag

Yes

Yes

Yes

Global

Yes

innodb_max_purge_lag_delay

Yes

Yes

Yes

Global

Yes

innodb_max_undo_log_size

Yes

Yes

Yes

Global

Yes

innodb_merge_threshold_set_all_debug

Yes

Yes

Yes

Global

Yes

innodb_monitor_disable

Yes

Yes

Yes

Global

Yes

innodb_monitor_enable

Yes

Yes

Yes

Global

Yes

innodb_monitor_reset

Yes

Yes

Yes

Global

Yes

innodb_monitor_reset_all

Yes

Yes

Yes

Global

Yes

Innodb_num_open_files

Yes

Global

No

innodb_numa_interleave

Yes

Yes

Yes

Global

No

innodb_old_blocks_pct

Yes

Yes

Yes

Global

Yes

innodb_old_blocks_time

Yes

Yes

Yes

Global

Yes

innodb_online_alter_log_max_size

Yes

Yes

Yes

Global

Yes

innodb_open_files

Yes

Yes

Yes

Global

No

innodb_optimize_fulltext_only

Yes

Yes

Yes

Global

Yes

innodb_optimize_point_storage

Yes

Yes

Yes

Session

Yes

Innodb_os_log_fsyncs

Yes

Global

No

Innodb_os_log_pending_fsyncs

Yes

Global

No

Innodb_os_log_pending_writes

Yes

Global

No

Innodb_os_log_written

Yes

Global

No

innodb_page_cleaners

Yes

Yes

Yes

Global

No

Innodb_page_size

Yes

Global

No

innodb_page_size

Yes

Yes

Yes

Global

No

Innodb_pages_created

Yes

Global

No

Innodb_pages_read

Yes

Global

No

Innodb_pages_written

Yes

Global

No

innodb_print_all_deadlocks

Yes

Yes

Yes

Global

Yes

innodb_purge_batch_size

Yes

Yes

Yes

Global

Yes

innodb_purge_rseg_truncate_frequency

Yes

Yes

Yes

Global

Yes

innodb_purge_threads

Yes

Yes

Yes

Global

No

innodb_random_read_ahead

Yes

Yes

Yes

Global

Yes

innodb_read_ahead_threshold

Yes

Yes

Yes

Global

Yes

innodb_read_io_threads

Yes

Yes

Yes

Global

No

innodb_read_only

Yes

Yes

Yes

Global

No

innodb_replication_delay

Yes

Yes

Yes

Global

Yes

innodb_rollback_on_timeout

Yes

Yes

Yes

Global

No

innodb_rollback_segments

Yes

Yes

Yes

Global

Yes

Innodb_row_lock_current_waits

Yes

Global

No

Innodb_row_lock_time

Yes

Global

No

Innodb_row_lock_time_avg

Yes

Global

No

Innodb_row_lock_time_max

Yes

Global

No

Innodb_row_lock_waits

Yes

Global

No

Innodb_rows_deleted

Yes

Global

No

Innodb_rows_inserted

Yes

Global

No

Innodb_rows_read

Yes

Global

No

Innodb_rows_updated

Yes

Global

No

innodb_saved_page_number_debug

Yes

Yes

Yes

Global

Yes

innodb_sort_buffer_size

Yes

Yes

Yes

Global

No

innodb_spin_wait_delay

Yes

Yes

Yes

Global

Yes

innodb_stats_auto_recalc

Yes

Yes

Yes

Global

Yes

innodb_stats_include_delete_marked

Yes

Yes

Yes

Global

Yes

innodb_stats_method

Yes

Yes

Yes

Global

Yes

innodb_stats_on_metadata

Yes

Yes

Yes

Global

Yes

innodb_stats_persistent

Yes

Yes

Yes

Global

Yes

innodb_stats_persistent_sample_pages

Yes

Yes

Yes

Global

Yes

innodb_stats_sample_pages

Yes

Yes

Yes

Global

Yes

innodb_stats_transient_sample_pages

Yes

Yes

Yes

Global

Yes

innodb-status-file

Yes

Yes

innodb_status_output

Yes

Yes

Yes

Global

Yes

innodb_status_output_locks

Yes

Yes

Yes

Global

Yes

innodb_strict_mode

Yes

Yes

Yes

Both

Yes

innodb_support_xa

Yes

Yes

Yes

Both

Yes

innodb_sync_array_size

Yes

Yes

Yes

Global

No

innodb_sync_debug

Yes

Yes

Yes

Global

No

innodb_sync_spin_loops

Yes

Yes

Yes

Global

Yes

innodb_table_locks

Yes

Yes

Yes

Both

Yes

innodb_temp_data_file_path

Yes

Yes

Yes

Global

No

innodb_thread_concurrency

Yes

Yes

Yes

Global

Yes

innodb_thread_sleep_delay

Yes

Yes

Yes

Global

Yes

innodb_tmpdir

Yes

Yes

Yes

Both

Yes

Innodb_truncated_status_writes

Yes

Global

No

innodb_trx_purge_view_update_only_debug

Yes

Yes

Yes

Global

Yes

innodb_trx_rseg_n_slots_debug

Yes

Yes

Yes

Global

Yes

innodb_undo_directory

Yes

Yes

Yes

Global

No

innodb_undo_log_truncate

Yes

Yes

Yes

Global

Yes

innodb_undo_logs

Yes

Yes

Yes

Global

Yes

innodb_undo_tablespaces

Yes

Yes

Yes

Global

No

innodb_use_native_aio

Yes

Yes

Yes

Global

No

innodb_use_sys_malloc

Yes

Yes

Yes

Global

No

innodb_version

Yes

Global

No

innodb_write_io_threads

Yes

Yes

Yes

Global

No

timed_mutexes

Yes

Yes

Yes

Global

Yes

unique_checks

Yes

Both

Yes

参考文章

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html