查看: 688|回复: 1

[mysql] mysql配置参数详解

 关闭 [复制链接]

该用户从未签到

发表于 2016-4-10 19:45:25 | 显示全部楼层 |阅读模式

mysql

mysql


  • skip-external-locking
    跳过外部锁定,当外部锁定起作用的时候,每个进程访问数据表,则必须等待之前的进程完成操作并解除锁定,由于服务器访问数据库时经常需要等待解锁,所以在单服务器环境下开启外部锁定则会让MySQL性能下降,所以默认开启本项跳过外部锁定


  • key_buffer_size
    myisam表缓冲区的大小。这个大小的设置,可以通过sql语句show global status like 'key_read%';,然后用Key_reads /Key_read_requests 的值,如果这个值很大。就需要调大key_buffer_size的大小了。理论上来说,key_buffer_size越小越好,但是过小的话,容易造成内存浪费,一般来说能达到0.001以下就已经非常的好了。


  • max_allowed_packet
    server可接收数据包的小大,如果发送的SQL语句超过了这个允许的大小,系统则会自动限制掉,导致本次查询直接失败。


  • table_open_cache
    该值的设置可使用sql语句show status like 'open%tables';查看open_tables的值,如果跟table_open_cache相等,则就需要增加了。但是也要注意不能设置的太高。因为系统有文件描述符的限制,如果设置的太高也会造成性能的不稳定或者连接失败,所以要检测不能超过文件描述符的限制。比如打开myisam需要两个文件描述符。


  • sort_buffer_size
    排序缓冲。我们可以通过增大该值来提高order by 或者 group by 的处理性能,一般设置2M或者4M之前尅满足大多数应用的需求,需要注意的是每次线程都会创建自己独立的缓冲区,而不是整个系统共享的缓冲区,不要因为设置的过大而造成系统的内存不足。


  • net_buffer_length
    TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行。但有需要时,可以动态扩大到 max_allowed_packet的大小。mysqldump在导出的时候,创建达到net_buffer_lengh长度的sql行。建议默认就ok。


  • read_buffer_size
    设置以顺序扫描的方式扫描表数据的时候分配的缓冲区的大小。每个线程进行顺序扫描的时候都会产生该buffer,而且同一个Query中如果有多个表进行全表扫描,会产生多个该buffer。如果有很多顺序扫描,可以增大该值。默认值为128K,必须为4K的倍数。(不仅仅针对MYISAM,这个针对所有引擎)


  • read_rnd_buffer_size
    (随机读缓冲区大小)这个参数用在sort查询之后,以保证获取以顺序的方式获取到查询的数据。如果你有很多order by 查询语句,增长这值能够提升性能。默认256K


  • myisam_sort_buffer_size
    当对MyISAM表执行repair table或创建索引时,用以缓存排序索引;设置太小时可能会遇到” myisam_sort_buffer_size is too small”


  • thread_cache_size
    设置Thread Cache池中可以缓存的连接池线程最大数量,可设置为0-16384,MySQL<= 5.6.7默认为0,MySQL>= 5.6.8默认值为-1(自动调整)。1GB内存我们配置为8,2GB内存我们配置为16,4GB或4GB以上内在我们配置为64。当客户端断开时,客户端的线程将被缓存,如果当前缓存数少于thread_cache_size时,只有在缓存中没找到的时候才创建新的线程。默认值可以遵循公式:8 + (max_connections / 100),上限为100。


  • query_cache_size
    指定Mysql查询缓存缓冲区的大小,可以通过在Mysql控制台观察,如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况,如果Qcache_hits的值非常大,则表明查询缓冲使用的非常频繁。需要注意的是即使query_cache_type设置为0,query_cache_size设置的内存还是会被分配。MySQL<= 5.6.7默认值为0,MySQL>= 5.6.8默认值为1M。


  • tmp_table_size
    设置内部临时表最大值,如果超过该值,则会将临时表写入磁盘,其范围为1KB至4GB。如果你需要作很多的高级GROUP BY查询而且同时你有很多内存的时候,可以增大这个值。这个值不适用用户创建的内存表。


  • explicit_defaults_for_timestamp
    在5.6.6之后,声明为NOT NULL且没有默认子句的TIMESTAMP列是没有默认值的。往数据表中插入列,又没有给TIMESTAMP列赋值时,如果是严格SQL模式,会抛出一个错误,如果严格SQL模式没有启用,该列会赋值为’0000-00-00 00:00:00′,同时出现一个警告。(这和MySQL处理其他时间类型数据一样,如DATETIME),当该值为true的时候,则是关闭该警告。


  • skip-networking
    开启该选项可以彻底关闭MYSQL的TCP/IP连接方式,如果WEB服务器是以远程连接的方式访问MYSQL的数据库服务器,则不要开启该选项,否则将无法正常连接。


  • max_connections
    指定MYSQL允许的最大连接进程数,如果在访问程序时经常出现TOO MANY CONNECTIONS的错误提示,则需要增大该参数值。默认值151。SHOW VARIABLES LIKE '%max_connections%';通过查看SHOW STATUS LIKE '%conn%';来设置。不能一味增大该值,否则内存吃不消


  • max_connect_errors
    如果某一客户端尝试连接此MySQL服务器,但是失败(如密码错误等等)指定的次数,则MySQL会无条件强制阻止此客户端连接。如果希望重置此计数器的值,则必须重启MySQL服务器或者执行flush hosts;,当这一客户端成功连接一次MySQL服务器后,针对此客户端的max_connect_errors会清零。


  • open_files_limit
    MySQL打开的文件描述符限制。默认1024。当错误日志显示:Too many open files,则就需要调高该值了。


  • log-bin
    指定该值则表示启动时,mysqld写入包含所有更新数据的sql命令的日志文件,如果没有给出值,则默认名为-bin后面所跟的主机名


  • binlog_format
    指定mysql的复制模式:①基于sql语句的复制statement;②基于行的复制row;③混合模式复制mixed(一般的复制使用statement,对于该模式无法复制的则使用row保存)


  • server-id
    mysql同步的数据中包含server-id,用于标识该语句最初是从哪个server写入。每一个同步中的slave在master上都对应一个master线程,该线程就是通过slave的server-id来标识的;每个slave在master端最多有一个master线程,如果两个slave的server-id 相同,则后一个连接成功时,前一个将被踢掉。


  • expire_logs_days
    设置二进制日志的过期天数,超过天数的日志则自动删除,当值是0的时候表示永不过期。


  • default_storage_engine
    设置默认采用的存储引擎。默认是innodb


  • innodb_data_home_dir
    指定存放数据文件的路径,如果没有指定,系统则在MySQL的datadir目录下创建数据文件,如果为空,则可以在innodb_data_file_path中指定一个决定路径,innodb不创建目录,所以在启动前请确认指定的目录是否已经存在,如果不存在,则需要进行创建。


  • innodb_data_file_path
    指定所有innodb数据文件的路径,还指定了初始大小配置,最大分配以及超出起始分配界限是否应当增加文件的大小。格式如下:path-to-datafile:size-allocation[:autoextend[:max-size-allocation]],本配置ibdata1:10M:autoextend则指定了数据文件的名称是idbata1,初始大小为10M,超出起始大小后自动增加文件的大小。


  • innodb_log_group_home_dir
    事务被提交并写入到表空间磁盘文件上之前,事务数据存储位置。通常这个目录设置与innodb_data_home_dir变量相同,但是为了获得最大性能,建议分离两个目录到单独的物理存储阵列上,这样可以保证IO资源不起冲突,利于服务器处理大量高并发连接。


  • innodb_buffer_pool_size
    定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和 MyISAM 存储引擎不同, MyISAM 的 key_buffer_size 只能缓存索引键,而 innodb_buffer_pool_size 却可以缓存数据块和索引键。一般来说,它越大Innodb的吞吐量(单位:tps)就越高。所以,在不影响服务器上其他程序正常工作的情况下,这个值总是越大越好。适当的增加这个参数的大小,可以有效的减少 InnoDB 类型的表的磁盘 I/O 。在一个以 InnoDB 为主的专用数据库服务器上,可以考虑把该参数设置为物理内存大小的 60%-80%。但是当innodb做crash recovery的时候,大的pool buffer会让recovery奇慢无比。 一种折衷的解决方法就是:启动的时候用小的pool buffer,恢复完成以后改用大的pool bufer。默认值128M,如果是专用mysql服务器,可设置为操作系统的内存的70%~80%,如果还有nginx等,可以设置为50%。


  • innodb_log_file_size
    每个日志文件在日志组中的大小,不过innodb_log_file_size * innodb_log_files_in_group的值不能超过一个略小于512GB的最大值。通常设置256MB就足够了。


  • innodb_log_buffer_size
    这是InnoDB存储引擎的事务日志所使用的缓冲区。InnoDB 在写事务日志的时候,为了提高性能,也是先将信息写入 Innofb Log Buffer 中,当满足 innodb_flush_log_trx_commit 参数所设置的相应条件(或者日志缓冲区写满)之后,才会将日志写到文件(或者同步到磁盘)中。可以通过 innodb_log_buffer_size 参数设置其可以使用的最大内存空间。一个大的日志缓冲区能够在事务提交前不将日志写入磁盘,所以,如果你一个事物更新多行数据,设置这个值能有效节省IO。默认8M。


  • innodb_flush_log_at_trx_commit
    抱怨Innodb比MyISAM慢100倍?那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘,这是很费时的。特别是使用电池供电缓存(Battery backed up cache)时。设成1对于很多运用,特别是从MyISAM表转过来的是可以的,它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘,所以你一般不会丢失超过1-2秒的更新。设成0会更快一点,但安全方面比较差,即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。


  • innodb_lock_wait_timeout
    该值指的是事务等待获取资源等待的最长时间,超过这个时间还未分配到资源则会返回应用失败。参数的时间单位是秒,最小可设置为1s(一般不会设置得这么小),最大可设置1073741824秒(34年,一条语句锁等待超过30分钟估计业务该有反馈了)

默认安装时这个值是50s
  • innodb_autoinc_lock_mode
    为了解决自增主键锁表的问题而引入该值。该值为0时每次申请自增主键时需要锁表;该值为1的时候会对insert语句进行预判,如果可预判需要的ID则执行新方式,申请轻量锁,如果不可预判,则申请表锁;该值为2则全部申请轻量锁,这样可保证并发最高,但是会发生成的自增值不一致的情况。默认是1。


  • max_allowed_packet
    设定在网络传输中一次消息传输量的最大值,MySQL<= 5.6.5系统默认值为1M,MySQL>= 5.6.6默认值为4M,取值范围(1M~1G),必须设定为1024的倍数,单位为字节。


  • no-auto-rehash
    在终端中不启用tab补齐功能。当改为auto-rehash则再次登录MySQL则就可以使用命令自动补全了。在windows版本中不可用。这是windows版本的一个bug。


  • TA的每日心情
    擦汗
    2018-2-2 09:46
  • 签到天数: 278 天

    连续签到: 1 天

    [LV.8]以坛为家I

    发表于 2016-4-11 10:14:36 | 显示全部楼层

    评分

    参与人数 1黑豆 -10 收起 理由
    blackhatcn -10 灌水!

    查看全部评分

    您需要登录后才可以回帖 登录 | 注册

    本版积分规则

    站长推荐上一条 /1 下一条