Mysql参数汇总

凡是需要耐心。

  •  参数为静态参数则黄色字体标记。
  •  参数为全局变量则粗体标记。
  •  参数为会话变量则下划线标记。
  •  参数为全局、会话变量则不标记。
auto_increment_increment
auto_increment_offset

这两个参数对master-master replication与自增长列有所影响。 为保持数据一致性，复制过程中应设置相同的值。 参数1指定自增长起点，参数2指定偏移量（步长）。

autocommit

事务操作中是否自动提交，如果为1则自动提交，为0则需要commit。

automatic_sp_privileges

该参数控制着server是否自动分配execute和alter权限给创建routine的用户。 默认为1，自动赋权。

back_log

mysql服务在很短的时间内有很多的连接请求时该参数就会发挥作用。 如果短时间内有很大连接数可考虑增加该值。不过限制于系统层面的连接数。 5.6.6之前默认50, 5.6.6之后基于50+（max_connections/5)公式，上限900

basedir

mysql安装目录

big_tables

如果设置为1，则所有的临时表不会在内存中存储，而是在磁盘上。明显这会影响性能。 正常来说，不需要改变默认值，mysql会自动识别是否需要磁盘空间。

bind_address

mysql服务绑定的地址，默认是0.0.0.0，也就是说接受来自不同网卡的请求。 如果机器上有多块网卡，而只需要监听一个网卡，则可通过绑定指定ip实现。

binlog_cache_size

默认值32K，该参数生产过程中一般需要调整。binlog在恢复及复制过程中发挥着不可或缺的作用，因此生产过程中必定是打开（log_bin=1)的，在向磁盘写binlog之前，mysql会将数据的修改带来的binlog记录在一块内存中，然后刷盘。如果一条dml带来的binlog信息很大，导致binlog_cache_size不足，就会导致使用磁盘临时文件来存储。性能影响严重。

根据状态参数binlog_cache_use及binlog_cache_disk_use调谐。前者表示使用了多少binlog_cache,后者表示使用了多少次磁盘空间。可通过百分比适当调整。

binlog_checksum

该参数好像卵用不大，大概就是对每个binlog文件checksum。 不过需要注意的是mysql5.6.6版本之后，复制过程中如果主上设定的参数值（例如CRC32）从上不能识别，则会因出错停止复制。

binlog_direct_non_transactional_updates

如果事务性操作中含有非事务性表，回滚时只能回滚事务性的表。

在mysql 5.6中，binlog的记录格式为row或者是mixed时。该参数不起作用。 5.7中也不可以。

binlog_format

binlog的记录格式。 一般row模式，好处是能保证主从的数据一致性。缺点是binlog记录的是每一行的改变情况，因此磁盘空间占用比较大。 statement格式，好处，由于记录的是逻辑语句。所以磁盘占用率小，但是由于机器上的环境差异及UDF可能会带来数据不一致。

mixed格式。混合记录格式，并不完善

binlog_max_flush_queue_time

这个参数基本上不需要动。默认0，意味着服务器对事务的支持没有延迟。 主要是防止并发事务过高，导致某些事务的RT上升。

binlog_order_commits

当设置为0时，事务可能以和binlog不相同的顺序被提交，从下面的测试也可以看出，这会稍微提升点性能。这不会带来数据不一致，不过会影响热备份。

binlog_row_image

5.6.2新增参数，可选值

full：默认值，记录所有的行信息，和5.6之前记录的没有区别。
minimal:只记录要修改的值
noblob：记录除了blog和text之外的所有字段。
需要注意的是在row模式才起作用。
此参数作用意义：当一个表中含有blob、text类型字段时，会导致binary log日志量暴涨，
特别是有一些游戏数据库。update的时候，即使不更新这些字段，
before image和after image都会被写入到binary
log日志，我们为了节省磁盘，内存，网络流量等，设置此参数来达到这些需求
binlog_rows_query_log_events

通过设置binlog_rows_query_log_events为on，可以在binlog_format=row中记录执行的SQL语句。

binlog_stmt_cache_size

如果事务需要内存超过此字节数，服务器生成如下错误 ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage;max_binlog_cache_size最低值是4096， 最大可能值为16EB（艾字节）。推荐的最大值为4GB ;因为目前的MySQL当二进制日志位置大于4GB时并不生效。

connect_timeout

这个超时使用在MySQL服务器和客户端交换授权包时。默认10秒。

log_bin

打开或关闭binlog的参数。需要指定server-id。

foreign_key_check

外键检查约束。为保证数据一致性，该参数一般打开。在批量导入数据的情况下，可以临时关闭从而提高数据导入的效率。

innodb_adaptive_flushing

innodb自适应刷新，打开该参数，innodb能智能的根据系统的io负载结合缓冲池的情况进行io操作（主要是write操作）。默认打开。

innodb_adaptive_hash_index

innodb的一个特性，即使热数据缓存在缓冲池中，频繁访问一些数据，B+树的树高会起到一定影响，innodb能智能的根据访问的频率从而产生类似键值对的访问，从而避免树高带来的微小消耗。

innodb_additional_mem_pool_size

单位字节数，默认8M，该参数指定的若干大小内存被分配用以存储数据字典信息和一些其他的内部数据结构，应用中表越多，调用了内存越多。如果用完了该块内存，就会调用操作系统的内存，同时也会在error_log中写入警告信息。 5.6.3中被弃用。5.7.4移除了。

innodb_autoinc_lock_mode

mysql自增长锁的模式。

须知
插入操作insert的几种类型
形如insert into table_name values();的插入均为简单插入。sample insert
形如insert ... select，replace ... select 和load data语句为bulk insert
形如INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d')的为mixed insert
以上统称insert-like
0表示表锁模式，即第一条语句执行完成释放lock。保证了增长列的有序性，并发性能不好
1表示simple insert时仍然等待auto-Inc locking的释放，对于bulk insert仍然会采用传统的表锁模式，
回滚操作会带来增长列的不连续行。并发性能好。
2表示完全互斥量模式，这是并发性能最高的方式，不过这种需要配合row模式才能保证复制的一致性。
综上所述。个人认为，如果不需要必须的自增长值的连续性，可以考虑将binlog_format设置为row，
innodb_autoinc_lock_mode=2来支持最大并发同时也能保证数据的一致性。
innodb_buffer_pool_dump_at_shutdown

这个参数的作用在于是否在mysql服务关闭时将buffer_pool里面的数据写入至磁盘文件中。这样做的目的是大大缩小了innodb缓冲池的预热时间。

innodb_buffer_pool_dump_at_now

同上，显而易见，服务运行过程中直接dump进磁盘文件中。

innodb_buffer_pool_instances

这个参数的作用在于将buffer pool化成多个小的pool，好处是一定程度上解决了高并发下线程争用临界资源（有关free lists, flush lists, LRUs，latch锁的mutex及rw lock）问题。缺点是需要一定的额外内存空间。设置多个的前提条件是缓冲池>1G

innodb_buffer_pool_load_at_startup

启动的时候加载关闭前dump出的buffer_pool数据至缓冲池中。

innodb_buffer_pool_load_at_now

服务运行过程中即刻加载。

innodb_buffer_pool_size

如果是其他参数是一间房间的点缀，那么该参数就是一间屋子的大小。屋子的大小很大程度上决定着性能。 个人总结的看法时。随着数据库的运行，缓冲池不可能缓存所有的数据。那么就需要将一些热的数据缓存进来。热数据量过大时，考虑表的拆分，并发的访问过大，考虑用nosql抵挡洪流。 可以根据系统状态参数Innodb_buffer_pool_read_requests/（Innodb_data_reads+Innodb_buffer_pool_read_requests）计算出读命中率，一般来说不低于95才算正常。 注意：在某些情况下，尽管命中率达到很多个9，由于数据的访问异常频繁。导致分母很大。这时候Innodb_data_reads的量即使很大，也不会导致命中率下降。但是由于物理读的频率的限制，也会出现命中率很高的情况下，性能受到很大的影响。 一般来说。该值为系统物理内存的60%-80%。留一定的内存空间至操作系统，避免出现使用swap的情况。

对于swap的看法。 如果物理内存已满时，系统会将一些未结束程序swap out到swap空间里。需要的时候在swap in. 这样就是与磁盘打交道了。性能显而易见。。存在少量的page out不可怕，最糟糕的是有大量page in。 意味着置换的非常频繁。

innodb_change_buffer_max_size

占buffer_pool最大的比例。 innodb的核心特性之insert buffer（高版本为change buffer）不再赘述。 如果mysql存在大量的insert，update（非唯一索引列），delete时。可能需要增加该值。用以提升io性能。

innodb_change_buffering

change_buffering支持的类型，可选值有none,inserts,deletes,changes,purges,all 默认为all.即所有能用到change buffer的时候都使用该功能。

innodb_doublewrite

innodb核心特性之一，能确保数据在写入过程中，不会因为写中断而导致redo和原数据都懵逼的状态。 因为redo log的写入是原子性的，所以不需要都double write特性。

innodb_fast_shutdown

innodb关闭的几种模式。动态参数，默认值为1，可选值为0，1,2

  • 为0时，全部脏叶刷盘并且change buffer合并。关闭缓慢。
  • 为1时，跳过以上操作。关闭迅速。
  • 为2时，innodb只会刷新他的log，当mysql崩溃的时候，提交的事务不会丢失。但是恢复的时间会很长。

一般来说，使用innodb_fast_shutdown在紧急情况下或者troubleshooting中。比如数据有在内存中有雪崩的危险。

innodb_file_per_table

innodb数据的存储分为独享表空间与共享表空间。 独享表空间中每个表的数据和索引被单独存放在各自的.ibd文件中。共享表空间在系统表空间中。 相比之下，独享比共享的性能和效率要更高一些。

innodb_flush_log_at_timeout

默认情况下，innodb每隔一秒钟刷新log buffer里面的日志。这里可以调整刷新频率。 我的看法是，一般不需调整。

innodb_flush_log_at_trx_commit

动态参数，可选值为0,1,2

  • 默认值为1，事务的每次提交都会写日志并且刷盘。可完全支持事务ACID的特性。
  • 0表示不立即刷新，根据innodb_flush_log_at_timeout设置的秒数刷新。这样当mysql crash之后就会丢失一秒钟的事务量。
  • 2表示刷到操作系统的缓存中，系统crash会带来丢失数据的风险。

一般情况下，如果要保证数据的一致性,该参数设置为1并sync_binlog=1。 在一些主从的架构中，由于异步复制导致的主从延迟较大，可考虑在从上适当调整这两个参数值。

innodb_flush_method

定义flush 数据（data和log)的方式，影响着io的生产力。 默认调用系统的fsync()。 可选值有fsync、O_DSYNC、O_DIRECT_NO_FSYNC

innodb_flush_neighbors

刷新临近页，innodb在从buffer pool中刷脏叶的同时也会刷新同在一个区里面的脏叶。为的是减少io压力。

  • 默认为1，意为刷新一个区的中的临近页。

  • 为0时，则不刷新临近页。

  • 为2时，刷新整个区的页。

  • 对于HDD，刷新打开临近页功能相对于不同时间刷新独立页因减少了大量的寻道时间能缓解io压力。

  • 对于SSD，可不打开。因为过多的刷新也许是不必要的刷新，从而到导致不必要的读入。

innodb_ft_*

有关于innodb全文索引特性的一些参数。感兴趣可根据官方文档了解。

innodb_io_capacity

对于innodb的刷脏叶，指定了io能力，这个参数可以根据磁盘的io能力进行调整。如果存在大量的写入操作。则可以考虑增大这个参数。前提当然也是硬件支持。

innodb_io_capacity_max

如果io能力在innodb_io_capacity的设置仍然存在io瓶颈。则系统会参考这个限制。

innodb_rollback_on_timeout

当查询因锁等待错误而中断时，只有最后一条语句回滚了，整个事务还没有中止。如果将该选项设置为1会在锁等待超时后立即回滚。

innodb_large_prefix

支持更长的索引前缀，776字节到3072字节的提升。

innodb_lock_wait_timeout

innodb等待行锁超时的时间。如果在高并发的OLTP系统。可考虑增加该值。默认50秒。 这个参数不会影响死锁的释放时间，mysql对于死锁有位图的方式。至于回滚哪一个事务，不仅仅是看事务的权重，也有不存在脏读的一致性要求。

更正：之前认为将这个变量设得很大来防止查询失败。后来了解到这只会导致更严重的问题，因为许多堵塞的事务会互相锁住。所以对于需要立即返回结果的影响程序应该减小该值。像电商网站。

innodb_log_buffer_size

innodb 日志缓冲池的大小，默认8M。独立于buffer pool之外的内存。每秒中会对这块内存进行刷盘。达到1/2大小、checkpoint点时都会进行刷盘。我的看法是，结合数据写的情况，适当调整，一般设置成50M足以。可以根据Innodb_log_waits状态参数，看是否因为该参数过小带来不必要的性能影响。

innodb_log_file_size

设置每个log file的大小，默认48M。过小日志轮转（rotate）受影响，影响checkpoint，过大恢复缓慢。设置多大是一门技术，优化是一个调优的过程。

innodb_log_file_in_group

设置log file的组数。冗余的作用。印象中该参数可能不起作用。

innodb_log_group_home_dir

innodb redo log的位置。

innodb_lru_scan_depth

如果free链不够用了。innodb会在lru的冷端扫描一定深度的页进行刷盘。 如果脏叶写的速度过慢，过少，可根据硬件情况酌情调整。io调整参数。

innodb_max_dirty_page_pct

buffer pool中脏叶所占的比例。不会影响刷新的频率。影响池中可存在脏叶的比例。默认值75.

innodb_old_blocks_pct

lru链上mid point的位置。为防止大量的表扫将缓冲池中的热数据冲刷出来可适当调大这个比例。

innodb_old_blocks_time

设置多长时间内再访问就会被挪至lru的热端。

innodb_online_alter_log_max_size

innodb支持在线ddl产生的临时log文件的限制。

innodb_open_files

innodb同一时刻可以使用的表空间。默认自动调整的。

innodb_page_size

innodb 数据页的大小，这是读入内存的最小单元。 一般来说不需要更改。默认16KB

innodb_purge_threads

innodb后台清理线程数，当系统中有大量的dml操作时增加线程数可以带来性能的提升。 也要依据CPU的核数。5.7。7之前默认一个，之后4个。

innodb_random_read_ahead

打开innodb的随机预读机制可以优化io，除表扫之外，也会带来不必要的读。默认关闭。 状态参数Handler_read_rnd_next的值如果非常高，则代表开启了预读模式，并且存在大量的表扫。 Handler_read_key也是相关状态变量，表示读取索引的请求数目，正常情况下，该值不应该过多的小于Handler_read_rnd_next的值。因为这意味着大部分行的读取都没有使用到索引。 此外Handler_read_first表示读索引中第一项的次数。如果为1，可以看做是一次全索引扫描的标志。

innodb_read_io_threads

innodb后台读线程数。

innodb_write_io_threads

innodb后台写线程数。

innodb_read_only

innodb存储引擎只读参数。

innodb_sort_buffer_size

用于innodb排序缓冲的大小。可根据Sort_merge_passes状态参数调整。 Sort_merge_passes 表示当需要排序时，在排序缓冲中无法将结果完全存放，则将会基于磁盘创建临时文件进行排序。如果该值较高，则应提高sort_buffer_size大小。最好的办法是找到是由哪些排序SQL造成的。

sort_buffer_size

每个线程需要排序的时候会分配此缓存。查明是否需要增加此缓存的大小、检查sort_merge_passed状态变量。也可以检查sort_range、sort_rows和sort_scan来查明你执行了多少个排序操作。 需要注意的是，因为该参数针对每个线程，所以过大的参数会导致资源浪费。 应set 会话级别的。

innodb_stats_*

有关innodb收集统计信息的参数。尤其重要的是cardinality值及估计的行数。 列举其中重要的几个。

innodb_stats_method

统计信息时，关于null值的看待。 可选值nulls_equal、nulls_unequal、nulls_ignored，默认nulls_equal

innodb_stats_persistent

表示是否将统计信息保存在磁盘上，以便下一次读取不需要额外收集。

innodb_stats_persistent_sample_pages

保存在磁盘上的收集信息，每次采样的页数。默认采样20个页。

innodb_stats_transient_sample_pages

临时统计（保存在内存中）采样的页数。默认采样8页

innodb_strict_mode

如果该参数为on，则对于warning也作为error返回。也就是严格检查插入innodb表中的数据。

innodb_support_xa

支持分布性事务（两阶段提交）。redo log与binlog的写入用了该特性。默认开启。 在主从复制架构中为了保证数据一致性不要关闭该参数。

innodb_sync_spin_loops

该参数设定了一个线程等待mutex锁前自旋的次数（为减少context switch带来的CPU损失时间），默认30次。若最终仍为获取，则进入suspended状态。 很多时候，为了避免堵塞。也有可能将该值设定为0.（baofoo是这么干的）

innodb_table_locks

此变量定义了innodb是如何处理lock tables语句发出的表锁请求。默认立刻返回并且内部将表锁住。当关闭时（设置为0），它会接受lock table语句线程直到所有锁释放后才从lock tables ... write返回。

innodb_sync_array_size
定义互斥锁/锁等待数组的大小。 增加该值会拆分用于协调线程的内部数据结构，以提高具有大量等待线程的工作负载的并发性。 此设置必须在 MySQL 实例启动时配置，之后无法更改。 对于经常产生大量等待线程（通常大于 768）的工作负载，建议增加该值。我们通过代码看到看这个会根据n_threads来初始化，默认为1. n_threads是mysql最多存在的线程数。

 

 

innodb_thread_concurrency

innodb可限制并发线程数。如果参数设置大于0，则表示检查机制开启，允许进入的线程数就是参数的值。如果设置过大会因为增加消耗系统层面的连接和资源导致性能退化。 一般情况下，值得设定应小于CPU的核数。 根据系统的负载，硬件环境调整参数。

innodb_undo_directory

undo空间独立出来的目录。关于将undo独立出来，是很有必要的，如果将undo文件部署在ssd类的高速盘上，可以大大加快io，从而提升数据库性能。

innodb_undo_logs

设置undo使用回滚段的个数。替代了老版本的innodb_rollback_segments 默认128。1个回滚段支持1024事务（早期中，能满则大部分应用），128*1024就是现在能支持的事务数。

innodb_undo_tablespaces

设定undo的表空间数。 如果等于4，则就有undo001~undo004的undo tablespace文件。

innodb_use_native_aio

指定mysql是否使用Linux 异步io子系统。该参数值针对Linux系统。并且不能在服务运行的时候更改。一般来说，不需要动这个参数，默认开启的。

insert_id

没什么作用，alter 或者insert对于自增长列的使用。

join_buffer_size

连接查询（mysql将所有查询均认为是连接查询，即使是单表查询）时所需要的。当没有使用到索引时（连接结果集相对较大），该参数尤其重要。。

为了查出是否需要增加join_buffer_size,可以检查select_scan状态选项，它包括第一张表执行完整扫描的连接数量，同样select_full_range_join，它包含使用范围搜索的链接数量。这些状态变量的值不会随着join_buffer_size的值变化而变化，这样就可以利用他们来查处是否需要大的join_buffer_size，而不是衡量该值改变后的有效性。

keep_files_on_create

创建myisam表时的选项，关于是否将.MYD文件存放在数据字典中。

key_buffer_size

索引块缓冲对所有的线程共享。myisam表的 1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)

key_cache_age_threshold

这个作用于myisam参数有点像innodb的lru链的mid_point点。 当MyISAM的key_buffer_size被分为热链和温链时，key_cache_age_threshold就被用来控制什么样的情况下处于热链的数据会被转移到温链中。key_cache_age_threshold是指的block个数，如果在最近的 （key_cache_age_threshold * key_cache_division_limit/100）次访问中，处于热链顶部的block没有被访问到，那么这些块将被移到温链的顶部（处于温链顶部的block会很快被移除出key cache）。

large_page_size

如果该参数被应用，将会显示内存页的大小。

last_insert_id

最后一个自增列插入的auto_increment值。

local_infile

如果禁用此项，将会导致LOAD DATA INFILE语句不可用。

lock_wait_timeout

锁等待超时时间

locked_in_memory
log_bin

开始binlog，指定server id。

log_bin_basename

binlog的命名格式。

 log_bin_index

binlog index的名字。

log_bin_trust_function_creates

关于binlog记录的权限相关。

log_bin_use_v1_row_events

log_error

错误日志指定的位置及名字 。

log_output

日志的输出格式，可选的有file，table，none.

log_queries_not_using_indexes

记录不走索引的查询，即使该查询不是慢查询。我之前实验过一次，好像有问题。

log_slave_updates

从上的binlog是否开启。一般用于从又生从（或者双主情况下).

log_slow_admin_statements

不仅仅记录dml的慢查询，同时记录ddl等其他sql语句的慢日志。

log_slow_slave_statements

当慢查询打开了，该参数支持将在从上执行超过指定时间的查询记录下来（记录在主上）。

log_throttle_queries_not_using_indexes
log_warnings

控制mysql 将警告信息也记录到错误日志文件里

long_query_time

超过该秒数的查询将会被记录到满日志中。

low_priority_updates

如果设置为1，将会优先select或者lock table read语句，INSERT, UPDATE, DELETE, and LOCK TABLE WRITE 语句将会被等待。只存在表级锁上，也就是在innodb上不起作用。

lower_case_file_system

关于文件系统大小写是否敏感的设置。

lower_case_table_names

关于表名是否敏感的设置。

master_info_repository

在从上对主信息的保存方式，文件形式或者表形式。

master_verify_checksum

开启这个功能，将会导致主将会在读binlog的时候进行检测校验。

max_allowed_packet

mysql允许的传输包的最大限制。在bulk insert以及大过程函数的时候可能需要注意该点。 防止出现因max_allowed_packet的限制导致失败。5.6.6之前1M，之后默认4M。

max_binlog_cache_size

事务语句binlog使用内存的最大量。

max_binlog_size

如果写一个超过该值的binlog，mysql会轮转binlog file。

max_connect_errors

一个连接失败次数超过该值，就会出现断开。

max_connections

允许最大并发的连接数。如果因为max_connections而导致的连接错误，会导致状态参数Connection_errors_max_connections的增加。

max_error_count

show warnings语句最多能显示的error，warnings。

max_heap_table_size

对用户创建memory表的最大限制。

max_insert_delayed_threads

5.6.7之后就被弃用了。

max_join_size

指定查询结果最大返回集，超过该值就会报错。当调试找出没有索引的查询时，该选项会有很大帮助。

max_length_for_sort_data

结果排序的最大长度，优化group by，order by的参数。

max_sort_length

排序的最大结果集，剩下的将会被忽略。如果要增大该值，一般也要增大sort_buffer_size

max_prepared_stmt_count

prepare语句的数量上限。

max_rely_log_size

与主上的max_binlog_size效果类似。在从上如果relay log造成file大于这个值，就是轮转relay log file

group_concat_max_len

限制了group_concat函数可以返回的字节数。

用于myisam表
myisam_recover_options
myisam_repair_threads
myisam_sort_buffer_size

myisam_stats_method

myisam_use_mmap

net_*

new

old

old_alter_table

old_passwords

open_file_limit

optimizer_prune_level

如果该变量设置为on，优化器删除即时搜索发现不太有效的计划；如果设置为off，优化器使用详尽的搜索。默认值为1（on），如果怀疑优化器选择的不是最优路径，可以考虑改变它的值。

optimizer_search_depth

优化器搜索的最大深度。。该值越大，优化器越有可能为复杂的查询找到最优计划。提高该值的代价就是优化器在搜索计划时的时间开销增大。如果设置为0，服务器会自动选择一个合理的值。默认为62（最大值）。

optimizer_switch

该变量控制各种优化器特性。合理使用该参数，需要知道优化器的工作原理并且具有丰富的经验。

  • index_merge：启用或禁用合并优化，该优化几个从合并扫描中获取的行记录。并把结果并为一条记录。在EXPLAIN的输出中，Merge列显示的就是这个选项。
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index
_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mr
r=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization
=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,
use_index_extensions=on
optimizer_trace
enabled=off,one_line=off
optimizer_trace_features
greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subse
lect=on

optimizer_trace_max_mem_size optimizer_trace_offset performance_schema performance_schema_accounts_size performance_schema_digests_size performance_schema_events_stages_history_long_size performance_schema_events_stages_history_size performance_schema_events_statements_history_long_size performance_schema_events_statements_history_size performance_schema_events_waits_history_long_size performance_schema_events_waits_history_size performance_schema_hosts_size performance_schema_max_cond_classes performance_schema_max_cond_instances performance_schema_max_file_classes performance_schema_max_file_handles performance_schema_max_file_instances performance_schema_max_mutex_classes performance_schema_max_mutex_instances performance_schema_max_rwlock_classes performance_schema_max_rwlock_instances performance_schema_max_socket_classes performance_schema_max_socket_instances performance_schema_max_stage_classes performance_schema_max_statement_classes performance_schema_max_table_handles performance_schema_max_table_instances performance_schema_max_thread_classes performance_schema_max_thread_instances performance_schema_session_connect_attrs_size performance_schema_setup_actors_size performance_schema_setup_objects_size performance_schema_users_size pid_file plugin_dir port preload_buffer_size profiling profiling_history_size protocol_version proxy_user pseudo_slave_mode pseudo_thread_id query_alloc_block_size

query_cache_limit

不缓存超过这个字节的查询结果集。

query_cache_min_res_unit

查询缓存分配的块的最小大小（以字节为单位）。 默认值为4096（4KB）

query_cache_size

mysql服务器存储查询及其结果集的缓存大小。增大该值，可以提高性能，因为查询插入到缓存里，接下来，执行相同的查询不需要查询解析、优化和执行，就可以从缓存中取到结果集。但是，该变量不要设置的过大，因为当需要从缓存中删除查询时，即修改表数据，互斥体争用将阻塞并行查询，尤其是多核计算机和高并发环境下。该变量的合理值是小于100M，尽管可以接受突然宕机而设置的大一点。

最佳实践是query_cache_size设置的偏小一些，使用flush query cache定期整理碎片，而不是增大query_cache_size的值。为了确定查询缓存是否有效，可以查看Qcache_free_blocks、Qcache_free_memory、Qcache_hits、Qcache_inserts、

Qcache_lowmem_prunes

查看有多少次查询缓存失效是由于内存不足导致的。

Qcache_not_cached、Qcach_queries_in_cache何Qcache_total_blocks状态变量。

cache_type
query_cache_wlock_invalidate

如果某个数据表被其他的链接锁住，是否仍然从查询缓存中返回数据。通常不需要改变。 query_prealloc_size rand_seed1 rand_seed2 range_alloc_block_size read_buffer_size read_only read_rnd_buffer_size relay_log relay_log_basename relay_log_index relay_log_info_file relay_log_info_repository relay_log_purge relay_log_recovery relay_log_space_limit report_host report_password report_port report_user rpl_stop_slave_timeout secure_auth secure_file_priv server_id server_id_bits server_uuid skip_external_locking skip_name_resolve skip_networking skip_show_database slave_allow_batching slave_checkpoint_group slave_checkpoint_period slave_compressed_protocol slave_exec_mode slave_load_tmpdir slave_max_allowed_packet slave_net_timeout slave_parallel_workers slave_pending_jobs_size_max slave_rows_search_algorithms slave_skip_errors slave_sql_verify_checksum slave_transaction_retries slave_type_conversions slow_launch_time slow_query_log slow_query_log_file socket sort_buffer_size sql_auto_is_null sql_big_selects

sql_buffer_result

当此变量设置时，服务器会把篾条select语句的结果缓存到临时表中。当客户端查询需要长时间获取结果时这有助于提前释放表锁。在把结果存储到临时表中之后，服务器会释放原表上的锁，在第一个客户端仍获取结果时让其他线程可访问。

为了找出查询是否在结果集上消耗过多时间。执行show processlist来检查查询在“Sending data”状态下的时间。

sql_log_bin
sql_log_off
sql_mode
  • STRICT_TRANS_TABLES：如果一个值不能插入一个事务性表，那么中止该语句。对于非事务性表，如果该问题发生在一个影响到表中单行数据的语句中或是发生在一个影响到表中多行数据的语句中的数据的第一行，那么中止该语句。
  • NO_ENGINE_SUBSTITUTION：如果没有指定的存储引擎则报错
  • ansi_quotes:该模式告诉mysql服务器使用ANSI SQL标准中定义的引号，而不使用mysql本身默认的引号。

sql_notes sql_quote_show_create sql_safe_updates sql_select_limit sql_slave_skip_counter sql_warnings ssl_ca ssl_capath ssl_cert ssl_cipher ssl_crl ssl_crlpath ssl_key storage_engine stored_program_cache sync_binlog sync_frm sync_master_info sync_relay_log sync_relay_log_info system_time_zone

table_definition_cache

存储在缓冲中的表定义的数量。当表数量很大时，可以增大该值。如果需要，可以调整该值，以便最近的表刷新（FLUSH TABLES）后，保持Opended_table_definitions小于或等于Open_table_definitions。

table_open_cache

缓存表的数量。状态参数Open_tables反应了问题，保持Opened_tables小于等于Opentables的数量。

innodb_print_all_deadlocks

启用此选项时，有关innodb用户事务中所有死锁的信息都记录在mysqld错误日志中，否则将使用show engine innodb status查看有关仅最后一个死锁的信息。

table_open_cache_instances

thread_cache_size

为将来使用缓存起来的线程数量。当一个客户端断开连接时，通常其线程也被销毁。如果该选项设置为正值N，那么连接断开后的N个线程将被缓存起来。对于一个应用使用成百上千链接的情况，还是很有用的。 thread_concurrency thread_handling

thread_stack

每个线程的栈大小。如果该值设置的过小，将会限制sql语句的复杂性，存储过程的递归深度等等。。 如果出现类似'Thread stack overrun'的错误信息，请增大该参数。 time_format time_zone timed_mutexes timestamp

tmp_table_size

内存中，内部临时表的最大值。服务器默认设置为max_heap_table_size和tmp_table_size二者中的最小值。如果有足够的内存，并且Created_tmp_disk_tables状态变量在增大，增大该值很有必要。

event_scheduler

是否打开时间调度器。默认关闭。打开后， 可通过show processlist查看到user为event_scheduler的工作线程。

log_bin_trust_function_creators

当启用二进制日志记录时，此变量适用。 它控制存储的函数创建器是否可信，不创建将导致不安全事件写入二进制日志的存储函数。 如果设置为0（默认值），则不允许用户创建或更改存储的函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER特权。 设置为0还强制限制必须使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性来声明函数。 如果变量设置为1，MySQL不会对存储函数创建执行这些限制。 此变量也适用于触发器创建。

tmpdir transaction_alloc_block_size transaction_allow_batching transaction_prealloc_size tx_isolation tx_read_only unique_checks updatable_views_with_limit version version_comment version_compile_machine version_compile_os

wait_timeout
服务器关闭非交互连接之前等待活动的秒数

MySQL服务器所支持的最大连接数是有上限的，因为每个连接的建立都会消耗内存，因此我们希望客户端在连接到MySQL Server处理完相应的操作后，应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接，他们不仅会白白消耗内存，而且如果连接一直在累加而不断开，最终肯定会达到MySQL Server的连接上限数，这会报'too many connections'的错误。对于wait_timeout的值设定，应该根据系统的运行情况来判断。在系统运行一段时间后，可以通过show processlist命令查看当前系统的连接状态，如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大，可以进行适当的调整小些。

warning_count

系统状态变量

关于query_cache
Com_select

处理的select的次数。

Qcache_insert

将结果集insert进缓存的次数。 在缓存完成预热后，我们总希望Qcache_inserts远远小于Com_select。不过由于缓存与服务器内部的复杂与多样性。仍然很难说，这个比率多少才是一个合理的值。

Qchche_hits
Qcache_inserts

当这两个状态变量的比值大于3:1时 通常查询缓存是有效的。不过这个比率最好能够达到10:1

 

 sql_safe_updates

  打开这个系统变量，防止数据被意外更新，或者删除。

 

posted @ 2017-03-22 12:40  xiangerfer  阅读(6362)  评论(1)  编辑  收藏  举报