mysql配置参数

zdc253212956

　　最近一直在进行mysql的学习，主要是学习mysql的一些配置参数，了解参数的意义，考虑其对mysql性能的影响，向同事要了一份正式服务器上的mysql配置，目前的配置能够满足正式生产环境中的性能要求，在学习mysql的过程中，对该配置中的一些参数进行了解和学习，这里总结和整理一下，方便以后回顾。
　　其中涉及的配置参数主要有以下几个，接下来会依次进行学习和了解：
　　skip-external-locking
　　在某些系统中，lockd锁管理器可能不能正常工作，可以使用skip-external-locking告诉mysqld不使用外部锁定。（这意味着你不能在同一个数据目录运行两个mysqld服务器，而且如果你使用相同内存配置，也需要特别注意）
　　在Mysql4.0版本之后，外部锁定默认是禁用的，而启用外部锁定（系统锁定），如果系统上的lockd不能完全工作（比如Linux系统），很容易造成mysqld死锁。
　　明确的禁用外部锁定，可以通过配置skip-external-locking。 外部锁定只影响存储引擎为MyISAM表的访问。
　　key_buffer_size = 16M
　　这个参数的含义是：MyISAM表用于索引块的缓冲区大小，可以被所有线程所共享，默认值是8388608（即8M），它是对MyISAM表性能影响最大的一个参数，如果数据库存储引擎为InnoDB类型，这个参数是无效的。可以通过增大这个值，以便索引更好的处理所有读和多写操作。
　　这里有两篇相关的文章值得参阅： mysql优化：Key_buffer_size   MySQL的key_buffer_size参数  
　　max_allowed_packet = 1M
　　 表示一个数据包的最大大小，或任何生成的/中间字符串，或任何mysql_stmt_send_long_data() API 函数传递的参数。数据包消息的缓冲区被初始化为net_buffer_length的字节（net_buffer_length默认值为16384：16KB，最大可以设置为1048576：1M），但是在需要的时候可以增长到max_allowed_packet字节，这个参数的默认值是4M，在接收一些大的数据包时可能会出现错误，最大可以设置为1GB。当你通过修改这个变量的值更改消息缓冲区的大小时，如果客户端程序允许的话，建议在客户端也修改缓冲区的大小。在客户端库，默认的max_allowed_packet是1GB，但是个人的客户端可能会重写这个值，例如，mysql和mysqldump分别是16 MB和24 MB。可以通过在命令行设置或在配置文件中修改max_allowed_packet参数来更改客户端的值，需要注意的是：在session会话级别中，这个变量是只读的。
　　table_open_cache = 64     
　　表示所有线程打开表的数量，增加该值会增加mysqld要求的文件描述符的数量？你可以通过检查Opened_tables状态变量来确定是否需要增加表缓存数量（Opened_tables表示已打开的表的数量）。如果这个值很大，你又不经常使用FLUSH TABLES（这个命令会强制关闭并重新打开所有表），可以增加table_open_cache的值。
　　 table_open_cache和max_connections系统变量影响文件服务器保持打开的最大数量。如果你增加了其中一个或两个值，可能会超过操作系统上每个进程打开的文件描述符的数量限制。许多的操作系统允许你增加这个限制，但是也需要确定操作系统是否有可能增加打开文件的限制，以及如何去做。
　　table_open_cache的值与max_connections的值存在关系。例如，对于200个并发运行的连接，指定table_open_cache的值 至少为200 *  N，其中 N是 任何参与 执行 查询的连接中关联 表的最大 数量。你还必须为临时表和文件保留一些额外的文件描述符。
　　请确保你的操作系统能够处理由table_open_cache设置的隐含打开的文件描述符的数量。如果table_open_cache值设置的太高，MYSQL可能使用完文件描述符而拒绝连接，不能执行查询，并会变的不可靠。还必须需要考虑的是，MyISAM存储引擎中，每个唯一打开的表都需要两个文件描述符。你可以通过在mysqld的启动选项中配置open-files-limit参数，来增加MySQL可用的文件描述符的数量。
　　sort_buffer_size = 512K
　　表示为每一个需要排序的会话分配一个指定的缓存区大小。sort_buffer_size的值不特定于任何存储引擎，它适用于对优化的一般方式。通过SHOW GLOBAL STATUS命令，如果你发现每秒钟有许多的Sort_merge_passes输出（Sort_merge_passes表示不得不做合并排序算法的数量），我们就需要考虑增加sort_buffer_size的值，以加快order by 或 group by 等操作的查询性能（order by和group by的查询效率很难通过优化查询和改善索引提供效率）。
　　优化器会尝试找出有多少空间是必要的，但可以分配更多，直到达到极限。值得注意的是：如果全局的设置，大于系统所需要的值，将减缓大部分涉及排序的查询效率。最好在session会话级别增加它的值，而且值针对那些需要增加sort_buffer_size的session会话。在Linux系统中，有256KB和2MB的阈值，其中较大的值可能显著减慢内存分配，所以你应该考虑那些较小的值。
　　net_buffer_length = 8K
　　每个客户端线程都与一个connection连接缓冲区和结果缓冲区相关联，默认值是16K。两者最初的大小都是net_buffer_length，但是根据需要会动态的扩大到max_allowed_packet设置的大小，结果缓冲区在每一个sql语句执行后都会缩小到设置的net_buffer_length。
　　这个变量通常不应该改变，但是如果你的系统内存很小，你可以将其设置为客户端语句的期望长度。如果语句的长度超过这个值，connection连接缓冲区会自动的扩大。net_buffer_length参数的最大值可以设置到1M。需要注意的是：在session会话级别中，这个变量是只读的。
　　 read_buffer_size = 256K
　　 进行顺序扫描的MyISAM表的 每个线程，都为它扫描的每个表分配一个指定大小的缓冲区。如果需要做很多的顺序扫描，你可能会增大这个值，默认值为131072（128K）。这个变量的值应该是4KB的倍数。如果它被设置为不是4KB的倍数，它的值将被舍入为4KB的最近倍数。
　　该参数在以下情况时适用于所有的搜索引擎：

• 　　缓存索引在一个临时文件（而不是临时表），使用ORDER BY进行行排序时。
  
• 　　进行分区批量插入操作时。
  
• 　　对于嵌套查询缓存结果。
  

　　如果使用 另外一个存储引擎，需要为MEMORY表确定内存块大小。 read_buffer_size 最大允许 设置为 2GB 。
　　read_rnd_buffer_size = 512K
　　此变量用于多范围读取优化，包括MyISAM表以及任何存储引擎。当从一个有排序操作的MyISAM表的一个关键分拣操作中读取行，该行通过该缓冲区读取，以避免磁盘寻道。设置这个值为较大的值可以显著的提高ORDER BY操作的性能， 然而，这是分配给每一个客户端的缓冲区，所以不应该在全局级别将其设置为一个较大的值。相反，只有在需要进行大量查询操作的客户端才建议在session会话级别增大这个变量值。read_rnd_buffer_size最大允许设置为2GB。
　　myisam_sort_buffer_size = 8M
　　表示在REPAIR TABLE上进行MyISAM索引排序时，或通过CREATE INDEX、ALTER TABLE创建索引时，分配的缓冲区大小。相应的，对于InnoDB引擎，有InnoDB_sort_buffer_size的设置。
　　query_cache_size= 8M     
　　表示分配给高速缓存查询结果的内存量。默认情况下，查询缓存是禁用的。这是通过使用默认的query_cache_size为 1M，query_cache_type为0（为0表示不启用查询缓存）使用的，这样做会显著降低开销，因为如果你设置了query_cache_size为0，你也需要在启动时设置query_cache_type为0。
　　
　　允许设置的值为1024的倍数，其他设置的值会四舍五入到最近的那个值。 需要 注意的是，即使query_cache_type设置为0，query_cache_size字节的内存也会被默认的分配。
　　查询缓存需要一个最小大小约40 kb的分配结构（具体的值取决于系统结构）。如果设置的query_cache_size太小，还可能会产生一些问题。
　　query_cache_type的值有0、1、2三种，0表示不进行任何查询缓存；1表示 缓存所有可缓存的查询结果除了那些以SELECT SQL_NO_CACHE开头的查询；2表示只缓存以SELECT SQL_CACHE开头的查询结果。mysql官方doc建议设置为2。
　　thread_cache_size = 20
　　表示服务器将会缓存重用的线程数量，当一个客户端断开连接，如果缓存中线程的数量小于设置的thread_cache_size，那么这个客户端的线程会变放入到缓存中。请求的线程如果可能的话，会从高速缓存中去的线程，当缓存为空时，才会创建新的线程。如果系统中存在许多新的连接的话，增加这个变量值可以提高性能。通常情况下，如果你的代码中很好的做了线程实现，这种性能改进并不显著。然而，如果你的服务器每秒有数百个连接，你通常应该设置thread_cache_size足够高，大多数新连接都会使用缓存的线程。通过比较这个变量与Connections（表示尝试连接到Mysql服务器的数量（无论是否连接成功））和Threads_created（表示处理connection连接所创建的线程的数量）状态变量之间的区别，你可以看到线程缓存的高效。
　　
　　这个变量的默认值是根据以下公式计算的，封顶为100：8 + (max_connections / 100)，但是在嵌入式服务器（libmysqld）这个变量是没有效果的，在MySQL 5.7.2版本之后，这个参数也不再可见。
　　log-bin=mysql-bin
　　表示启用二进制日志记录，服务器记录了所有改变数据语句的二进制日志，用于备份和复制。
　　
　　binlog_format=mixed 之前 MySQL中事务隔离级别与binlog_format的一点理解中学习，这里不再赘述。
　　innodb_flush_log_at_trx_commit = 2
　　这个变量的官方定义是：Controls the balance between strict ACID compliance for commit operations，and higher performance  that is possible when commit-related I/O operations are rearranged and done in batches。我自己的理解是用于控制两种关系之间的平衡，这两种关系：提交操作严格的ACID特性，提交相关的IO操作被分批的重新排列和完成时可能带来的高性能。你可以通过修改这个变量的默认值达到更好的性能，但是你可能会在意外崩溃时丢失一秒的事务。

• 　　默认值为 1 完全符合数据库ACID特性，这个值表示，在每次事务提交的时候，InnoDB日志缓冲区的内容都会被写入到日志文件，并且日志文件会被刷新到磁盘。
  
• 　　如果变量值为0，则表示InnoDB日志缓冲区的内容大约每秒被写入日志文件一次，并且日志文件会被刷新到磁盘。那些日志缓冲区中没有写入的内容会在事务提交的时候被写入日志文件。由于进程调度的问题，每秒的刷新并不能100%保证每一秒都发生。由于刷新磁盘的操作只有大约每秒才发生一次，所以在任何mysqld进程崩溃的时候，你都会丧失一秒的事务。
  
• 　　如果变量值为2，则表示InnoDB日志缓冲区的内容会在事务提交的时候写入到日志文件，并且日志文件会大约每秒刷新一次磁盘。同样的，由于进程调度的问题，每秒的刷新并不能100%保证每一秒都发生。由于刷新磁盘的操作只有大约每秒才发生一次，所以在操作系统崩溃或突然断电的时候，你都会丧失一秒的事务数据。
  
• 　　在MySQL 5.6.6版本中，InnoDB日志刷新频率由变量innodb_flush_log_at_timeout,控制，它允许你将日志刷新频率设置为N秒（默认值是1，可以设置1到2700之间的整数值），但是任何mysqld进程的崩溃都会清除高达N秒的事务数据。
  
• 　　DDL变化和其他内部InnoDB的活动，则是独立的innodb_flush_log_at_trx_commit设置进行InnoDB日志刷新。
  
• 　　InnoDB的崩溃恢复机制是不管变量innodb_flush_log_at_trx_commit的设置的，事务要么全部应用，要么全部删除。
  

　　根据数据库应用设置的持久性和一致性，建议参考如下方式进行InnoDB事务设置：

• 　　如果启用了二进制日志，设置sync_binlog=1。
  
• 　　总是设置innodb_flush_log_at_trx_commit=1。
  

　　这么建议的原因是：许多操作系统和一些磁盘硬件愚弄刷新到磁盘的操作，他们可能会告诉mysqld：刷新操作已经发生，但是事实上并没有发生。然后事务的持久性即使设置为1，也不能得到保证。在最糟糕的情况突然断电甚至会造成InnoDB数据的损坏。在SCSI磁盘控制器或本身加速文件刷新的磁盘上使用电池支持的磁盘缓存，会使操作更安全。你也可以尝试使用Unix命令hdparm禁用磁盘写入缓存的硬件高速缓存，或使用特定的硬件供应商提供的其他一些命令。
　　
　　sync_binlog
　　如果这个变量的值大于0，MySQL服务器会在sync_binlog提交组被写入到二进制日志之后，使用fdatasync()命令同步二进制日志到磁盘。默认的sync_binlog变量值为0，表示不同步到磁盘。mysql服务器依赖于操作系统不时的刷新二进制文件的内容，用于任何其他文件。值为1是最安全的选择，因为在崩溃的情况下你最多从二进制日志丢失一个提交组。然而，它也是最慢的选择（除非你你的磁盘具有电池备份缓存，这会使得同步非常快）。
　　innodb_lock_wait_timeout = 20
　　表示InnoDB事务从等待获取行锁到放弃的时间长度，默认的值为50秒。一个事务试图获取被另一个InnoDB事务锁定的行所等待的最大时间，超时时会发出以下错误信息：
　　ERROR 1205 (HY000)： Lock wait timeout exceeded； try restarting transaction（锁等待超时，试图重启事务）
　　当锁等待超时后，当前的语句会回滚（并不是整个事务回滚）。如果需要整个事务都回滚，需要在服务器启动时通过innodb_rollback_on_timeout参数设置。
　　在高度交互的应用程序或OTLP系统中，为了更好的用户反馈或将更新放入一个队列等待后续处理，你可能会减小该变量值。对于长期运行的后端操作，比如在一个数据仓库中存在大批量的插入或更新操作等待完成时，你可能会增加该值。
　　innodb_lock_wait_timeout仅适用于InnoDB的行级锁。一个MySQL表锁不会发生在InnoDB，这个参数并不适用于等待表锁。
　　锁等待超时值不适用于死锁，因为在事务死锁时，InnoDB会立即检测到它们并事务回滚。
　　innodb_lock_wait_timeout可以在运行时，通过SET GLOBAL或SET SESSION声明进行设置。修改全局的设置需要SUPER权限，并会影响接下来所有连接客户端的操作。任何客户端都可以在SESSION会话级别设置innodb_lock_wait_timeout，它只会影响到该客户端。
　　至此，线上mysql服务器上的配置文件参数已经全部做了整理，对于这些参数也有了一定的认识和了解，接下来，对于经常使用到的connections变量做一下整理，为接下来与同事讨论mysql的优化及设置做些准备，主要是以下几个connections变量：
　　max_connections
　　系统变量，它表示最大允许的并发客户端连接数，会影响在服务器上运行的线程数量，默认值是151，增加该值会增加mysqld请求的文件描述符的数量。如果所请求的描述符的数量不可用，服务器会减少max_connections的值。连接拒绝是因为，max_connections的最大值，达到了Connection_errors_max_connections状态变量的增量。
　　thread_cache_size 变量的默认值就与max_connections有关。
　　max_user_connections
　　表示允许任何给定的MySQL用户帐户同时连接的最大数目。默认值为0表示不限制。此变量可以在服务器启动时或运行时设置一个全局值。它也有一个只读会话值，表示与当前会话相关联的帐户的有效同时连接的限制值。会话级别的max_user_connections初始化如下：

• 　　如果用户帐户具有非零的MAX_USER_CONNECTIONS资源限制（帐户的资源限制通过GRANT语句指定），会话级别的MAX_USER_CONNECTIONS值就设为该限制。
  
• 　　否则的话，会话级别的MAX_USER_CONNECTIONS的值会被设置为全局值。
  

　　
　　Connection_errors_max_connections
　　表示当服务器中连接数达到max_connections的限制后，连接数被拒绝的数量。
　　Connections               
　　表示尝试连接到mysql服务器的数量，无论成功或失败。
　　Max_used_connections  
　　从服务器启动开始，已同时被使用的最大连接数。
　　skip-external-locking作用
　　在Mysql Linux 的发行版中，默认存在一行skip-external-locking，它表示跳过外部锁定，与之相对，External-locking变量表示启用外 部锁定，用于多线程条件下对MyISAM数据表进行锁定，默认情况下mysql是禁用外部锁定的，在现实生产中，我们的业务环境是单服务器环境，不需要外 部锁定，所以将其禁用。
　　
　　key_buffer_size 在InnoDB引擎时无效
　　在 现实的生产环境中，我们曾对业务库进行过升级，虽然我们的业务库使用的是InnoDB引擎，但是其中仍然存在几张遗留的使用MyISAM存储引擎的表，设 置这个参数也是为了提供对于这几张表的访问性能，用于这几张表的索引更好的处理读和多写操作。另外，在之前windows上安装mysql 5.5时，会默认产生几个不同生产环境的my.ini文件，这个参数的配置也参阅了其中的一些配置。
　　table_open_cache=64 设置是否过小
　　这个参数的设置最好根据现实生产环境进行设置，在mysql命令行通过show global status like 'open%_tables%'，可以查到两个重要的参数，如下：
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　|  Open_tables   | 81    |
　　| Opened_tables | 88    |
　　+---------------+-------+
　　对与大多数的服务器设置，建议参考一下公式：Open_tables / Opened_tables >= 0.85；Open_tables / table_open_cache