mysql 参数配置优化详介绍

MySQL服务器环境：
　　CPU: 2颗四核Intel Xeon 2.00GHz
　　内存: 4GB DDR
　　硬盘: SCSI 146GB
说明：MyISAM 和 INNODB 相关参数选项组合

 [mysqld]
　　port = 3306
　　serverid = 1
　　socket = /tmp/mysql.sock
　　skip-locking # 避免MySQL的外部锁定，减少出错几率增强稳定性。
    skip-name-resolve #禁止MySQL对外部连接进行DNS解析，该选项可以消除MySQL进行DNS解析的时间,则所有远程主机连接授权都要使用IP地址方式。
　　back_log = 500
          /*
    　　back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。
        只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。
        你的操作系统在这个队列大小上有它自己的限制。
        试图设定back_log高于你的操作系统的限制将是无效的。
        当你观察你的主机进程列表，发现大量
         264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，
         就要加大 back_log 的值了。默认数值是50，我把它改为500。
     */
　　key_buffer_size = 384M  #用来存放索引区块的缓存值,建议128M以上，不要将其设置大于你可用内存的30%。
    /*
     指定用于索引的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，
     到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。
     对于内存在4GB左右的服务器该参数可设置为384M或512M。
     通过检查状态值Key_read_requests和Key_reads值,来知道key_buffer_size设置是否合理。
     比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好
     (上述状态值可以使用SHOW STATUS LIKE 'key_read%'获得)。
     注意：该参数值设置的过大反而会是服务器整体效率降低!
    */
　　max_allowed_packet = 32M #增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。
      /*例如:仅当你发出长查询或mysqld必须返回大的结果行时mysqld才会分配更多内存。
         该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，
         并确保不会因偶然使用大的信息包而导致内存溢出。
     */
　　table_cache = 2048 #指定表高速缓存的大小
    /*对于有1G内存的机器，推荐值是128－256
    每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中,这样可以更快地访问表内容。
    如果发现open_tables=table_cache，并且opened_tables在不断增长，则需要增加table_cache值
    (上述状态值可以使用SHOW STATUS LIKE 'Open%tables'获得)。
    注意，不能盲目地把table_cache设置成很大的值。
    需要确认在[mysqld_safe]中 "open-files-limit" 变量设置打开文件数量允许至少4096
    如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
    */
    sort_buffer_size = 8M #查询排序时所能使用的缓冲区大小。
    /*注意：该参数对应的分配内存是每连接独占,如果有100个连接，
     那么实际分配的总共排序缓冲区大小为100 × 8 = 800MB。
     所以，对于内存在4GB左右的服务器推荐设置为4-8M,同时也得参考系统SQL。
     一般按照内存可以设置为2M以上，推荐是16M(得参考系统SQL)，
     该选项对排序order by，group by起作用
    */
　　read_buffer_size = 4M #用来做MyISAM表全表扫描的缓冲大小,当全表扫描需要时,在对应线程中分配。和sort_buffer_size一样
    read_rnd_buffer_size = 16M
    /*当在排序之后,从一个已经排序好的序列中读取行时,行数据将从这个缓冲中读取来防止磁盘寻道.
      如果你增高此值,可以提高很多ORDER BY的性能,当需要时由每个线程分配
    */
　　join_buffer_size = 8M #联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样
　　myisam_sort_buffer_size = 128M
    /*MyISAM表发生变化时重新排序所需的缓冲,此缓冲当MySQL需要在 REPAIR, OPTIMIZE, ALTER
      以及 LOAD DATA INFILE 到一个空表中引起重建索引时被分配，值不要设的太大
    */
  bulk_insert_buffer_size = 64M
    /*MyISAM 使用特殊的类似树的cache来使得突发插入(这些插入是,INSERT  SELECT, INSERT  VALUES (), (),
     以及 LOAD DATA INFILE) 更快. 此变量限制每个进程中缓冲树的字节数.
     设置为 0 会关闭此优化,为了最优化不要将此值设置大于 "key_buffer_size".
     当突发插入被检测到时此缓冲将被分配。
    */
  myisam_max_sort_file_size = 10G
    /*MySQL重建索引时所允许的最大临时文件的大小(当 REPAIR,ALTER TABLE或LOAD DATA INFILE).
      如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)
    */
  myisam_max_extra_sort_file_size = 10G
    /*如果被用来更快的索引创建索引所使用临时文件大于制定的值,那就使用键值缓冲方法.
      这主要用来强制在大表中长字串键去使用慢速的键值缓冲方法来创建索引.
    */
  myisam_repair_threads = 1
    /*如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们.
      这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择.
    */
  myisam_recover #自动检查和修复没有适当关闭的 MyISAM 表
　　query_cache_size = 64M #指定MySQL查询缓冲区的大小。  
     /*SHOW STATUS LIKE 'Qcache%';SHOW VARIABLES LIKE '%query_cache%';
       如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况;
  　　如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲;
      如果Qcache_free_blocks的值非常大，则表明缓冲区中碎片很多
    */
  query_cache_limit = 4M #只有小于此设定值的结果才会被缓冲,此设置用来保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖
　　thread_cache_size = 64
   /*
  　　可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，
      当断开连接的时候如果有空间，客户的线置在缓存中。
      如果有很多新的线程，为了提高性能可以这个变量值。
      通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用
   */
  transaction_isolation = REPEATABLE-READ #设定默认的事务隔离级别,可用的级别如下:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
  back_log = 500
    /*back_log是操作系统在监听队列中所能保持的连接数,队列保存了在MySQL连接管理器线程处理之前的连接.
     如果你有非常高的连接率并且出现"connection refused" 报错,你就应该增加此处的值.
     检查你的操作系统文档来获取这个变量的最大值.如果将back_log设定到比你操作系统限制更高的值,将会没有效果
    */

　　tmp_table_size = 256M 内部(内存中)临时表的最大大小,如果一个表增长到比此值更大,将会自动转换为基于磁盘的表,此限制是针对单个表的,而不是总和.
　　max_connections = 1000 #指定MySQL允许的最大连接进程数,其中一个连接将被SUPER权限保留作为管理员登录,即便已经达到了连接数的上限.
　　max_connect_errors = 100
    /*每个客户端连接最大的错误允许数量,如果达到了此限制,
     这个客户端将会被MySQL服务阻止直到执行了"FLUSH HOSTS" 或者服务重启,
     非法的密码以及其他在链接时的错误会增加此值.
     查看 "Aborted_connects" 状态来获取全局计数器
    */
　　wait_timeout = 10 #指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
　　thread_concurrency = 8 #该参数取值=CPU数量×2
　　skip-networking #开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项,否则将无法正常连接。
　　innodb_additional_mem_pool_size = 100M
    /*附加的内存池被InnoDB用来保存 metadata 信息
     如果InnoDB为此目的需要更多的内存,它会开始从OS这里申请内存.
     由于这个操作在大多数现代操作系统上已经足够快, 你一般不需要修改此值.
     SHOW INNODB STATUS 命令会显示当先使用的数量.
    */
  innodb_buffer_pool_size = 3G
    /*InnoDB使用一个缓冲池来保存索引和原始数据,不像 MyISAM.
     这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.
     在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%
     不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.
     注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,所以不要设置的太高
    */
  innodb_data_file_path = ibdata1:10M:autoextend  
    /*InnoDB 将数据保存在一个或者多个数据文件中成为表空间.
    如果你只有单个逻辑驱动保存你的数据,一个单个的自增文件就足够好了.
    其他情况下.每个设备一个文件一般都是个好的选择.
    你也可以配置InnoDB来使用裸盘分区 - 请参考手册来获取更多相关内容
    */
  innodb_data_home_dir = <directory> #设置此选项如果你希望InnoDB表空间文件被保存在其他分区,默认保存在MySQL的datadir中.
  innodb_file_io_threads = 4 #用来同步IO操作的IO线程的数量,此值在Unix下被硬编码为4,但是在Windows磁盘I/O可能在一个大数值下表现的更好.
  innodb_force_recovery=1
    /*如果你发现InnoDB表空间损坏,设置此值为一个非零值可能帮助你导出你的表.
     从1开始并且增加此值知道你能够成功的导出表.
    */
  innodb_thread_concurrency = 16 #在InnoDb核心内的允许线程数量.最优值依赖于应用程序,硬件以及操作系统的调度方式.过高的值可能导致线程的互斥颠簸.
  innodb_flush_log_at_trx_commit = 1
    /*如果设置为1 ,InnoDB会在每次提交后刷新(fsync)事务日志到磁盘上,这提供了完整的ACID行为.
      如果你愿意对事务安全折衷,并且你正在运行一个小的事物,你可以设置此值到0或者2来减少由事务日志引起的磁盘I/O
      0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘.
      2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上.
    */
  #innodb_fast_shutdown #加速InnoDB的关闭,这会阻止InnoDB在关闭时做全清除以及插入缓冲合并,这可能极大增加关机时间,但是取而代之的是InnoDB可能在下次启动时做这些操作。
  innodb_log_buffer_size = 8M
    /*用来缓冲日志数据的缓冲区的大小,当此值快满时,InnoDB将必须刷新数据到磁盘上。
      由于基本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言)
    */
  innodb_log_file_size = 256M
    /*在日志组中每个日志文件的大小,你应该设置日志文件总合大小到你缓冲池大小的25%~100%来避免在日志文件覆写
      log_file_size to 25% ~100% of buffer pool size
      上不必要的缓冲池刷新行为,不论如何, 请注意一个大的日志文件大小会增加恢复进程所需要的时间.
    */
  innodb_log_files_in_group = 3 #在日志组中的文件总数,通常来说2~3是比较好的
  #innodb_log_group_home_dir InnoDB的日志文件所在位置,默认是MySQL的datadir,你可以将其指定到一个独立的硬盘上或者一个RAID1卷上来提高其性能
  innodb_max_dirty_pages_pct = 90
    /*在InnoDB缓冲池中最大允许的脏页面的比例,
     如果达到限额, InnoDB会开始刷新他们防止他们妨碍到干净数据页面,
     这是一个软限制,不被保证绝对执行
    */
  #innodb_flush_method=O_DSYNC #InnoDB用来刷新日志的方法,表空间总是使用双重写入刷新方法,默认值是 "fdatasync", 另一个是 "O_DSYNC".
  innodb_lock_wait_timeout = 120
    /*在被回滚前,一个InnoDB的事务应该等待一个锁被批准多久,
     InnoDB在其拥有的锁表中自动检测事务死锁并且回滚事务,
     如果你使用 LOCK TABLES 指令, 或者在同样事务中使用除了InnoDB以外的其他事务安全的存储引擎,
     那么一个死锁可能发生而InnoDB无法注意到,这种情况下这个timeout值对于解决这种问题就非常有帮助
    */
  binlog_cache_size = 4M
    /*在一个事务中binlog为了记录SQL状态所持有的cache大小,
    如果你经常使用大的,多声明的事务,你可以增加此值来获取更大的性能,
    所有从事务来的状态都将被缓冲在binlog缓冲中然后在提交后一次性写入到binlog中。
    如果事务比此值大,会使用磁盘上的临时文件来替代,此缓冲在每个连接的事务第一次更新状态时被创建
    */
  max_heap_table_size = 64M #独立的内存表所允许的最大容量,为了防止意外创建一个超大的内存表导致用尽所有的内存资源
  join_buffer_size = 8M
  /*此缓冲被使用来优化全联合(full JOINs 不带索引的联合),类似的联合在极大多数情况下有非常糟糕的性能表现,
    但是将此值设大能够减轻性能影响,通过 "Select_full_join" 状态变量查看全联合的数量,当全联合发生时,在每个线程中分配
  */
  thread_concurrency = 16
    /*此允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量,
     此值只对于支持 thread_concurrency() 函数的系统有意义( 例如Sun Solaris),
     可以尝试使用 [CPU数量]*(2..4) 来作为thread_concurrency的值
    */
  ft_min_word_len = 4
    /*被全文检索索引的最小的字长,你也许希望减少它,如果你需要搜索更短字的时候,
      注意在你修改此值之后,你需要重建你的 FULLTEXT 索引
    */
  #memlock
    /*如果你的系统支持 memlock() 函数,你也许希望打开此选项用以让运行中的mysql在在内存高度紧张的时候,
      数据在内存中保持锁定并且防止可能被swapping out,此选项对于性能有益
    */
  thread_stack = 192K
    /*线程使用的堆大小,此容量的内存在每次连接时被预留,MySQL 本身常不会需要超过64K的内存,
     如果你使用你自己的需要大量堆的UDF函数或者你的操作系统对于某些操作需要更多的堆,
     你也许需要将其设置的更高一点
    */
  
[mysqldump]
  # 不要在将内存中的整个结果写入磁盘之前缓存. 在导出非常巨大的表时需要此项
  quick
  max_allowed_packet = 16M #服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB字段一起工作时相当必要),每个连接独立的大小,大小动态增加

[mysql]
  no-auto-rehash
  # 仅仅允许使用键值的 UPDATEs 和 DELETEs .
  #safe-updates

[isamchk]
  key_buffer = 512M
  sort_buffer_size = 512M
  read_buffer = 8M
  write_buffer = 8M

[myisamchk]
  key_buffer = 512M
  sort_buffer_size = 512M
  read_buffer = 8M
  write_buffer = 8M

[mysqlhotcopy]
  interactive-timeout

[mysqld_safe]
  open-files-limit = 8192 #增加每个进程的可打开文件数量,警告: 确认你已经将全系统限制设定的足够高.

################################################################################################################
--开启慢查询日志，需要设置参数log_slow_queries、long_query_times、log-queries-not-using-indexes。
　　log_slow_queries指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。
    long_query_times指定慢查询的阈值，缺省是10秒。
    log-queries-not-using-indexes是4.1.0以后引入的参数，它指示记录不使用索引的查询。
    设置long_query_time=10 外附上使用show status命令查看mysql状态相关的值及其含义：
　　  使用show status命令
  　　含义如下:
  　　aborted_clients 客户端非法中断连接次数
  　　aborted_connects 连接mysql失败次数
  　　com_xxx xxx命令执行次数,有很多条
  　　connections 连接mysql的数量
  　　Created_tmp_disk_tables 在磁盘上创建的临时表
  　　Created_tmp_tables 在内存里创建的临时表
  　　Created_tmp_files 临时文件数
  　　Key_read_requests The number of requests to read a key block from the cache
  　　Key_reads The number of physical reads of a key block from disk
  　　Max_used_connections 同时使用的连接数
  　　Open_tables 开放的表
  　　Open_files 开放的文件
  　　Opened_tables 打开的表
  　　Questions 提交到server的查询数
  　　Sort_merge_passes 如果这个值很大,应该增加my.cnf中的sort_buffer值
  　　Uptime 服务器已经工作的秒数

　　提升性能的建议:
　　1.如果opened_tables太大,应该把my.cnf中的table_cache变大
　　2.如果Key_reads太大,则应该把my.cnf中key_buffer_size变大.可以用Key_reads/Key_read_requests计算出cache失败率
　　3.如果Handler_read_rnd太大,则你写的SQL语句里很多查询都是要扫描整个表,而没有发挥索引的键的作用
　　4.如果Threads_created太大,就要增加my.cnf中thread_cache_size的值.可以用Threads_created/Connections计算cache命中率
　　5.如果Created_tmp_disk_tables太大,就要增加my.cnf中tmp_table_size的值,用基于内存的临时表代替基于磁盘的