金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > Mysql数据库服务器配置文件,详细参数说明

Mysql数据库服务器配置文件,详细参数说明

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-09 02:15

对于一套新的sqlserver服务器,我们首先要对它做一些必要的优化配置,确保在生产上比较长的时间段内可以比较稳定的,良好的运行。

以下是 Mysql数 据库服务器配置文件 /etc/my.cnf的详细配置。应用场合是 InnoDB引擎, 4核 CPU, 32位 SUSE。

mysql优化,mysql优化方案

1.启用InnoDB的每张表一个数据文件设置

InnoDB可以把每个数据库的信息保存在一个.idb数据文件中,每个.ibd文件代表自己的表空间,通过这样的方式可以更快地完成类

似“TRUNCATE”的数据库操作,当删除或截断一个数据库表时你也可以回收未使用的空间,这样配置的另一个好处是你可以将某些数据

 库表存放在一个单独的存储设备,这大大提升你磁盘的I/O负载

MySQL5.6及以上的版本默认启用了innodb_file_per_table=1

 

2.将MySQL数据分储到独立分区上,注意此设置在MariaDB上无效

建议使用单独的磁盘(最好是SSD)用于MySQL服务,创建新分区,格式化新分区,并挂载新分区这里略过,假设/ssd是我们新分区挂载的

目录,MySQL数据目录是/var/lib/mysql, 接下来复制整个数据目录到新分区中 cp -Rp /var/lib/mysql /ssd/  复制完成后重命名原来的数据目

录 mv /var/lib/mysql /var/lib/mysql-backup 接下来创建一个符号链接 ln -sv /ssd/mysql /var/lib/mysql

 

3.优化使用InnoDB的缓冲池,InnoDB引擎在内存中有一个缓冲池用于缓存数据和索引,这有助于你更快地执行MySQL/MariaDB查询语

句,在一个专用的机器上,你可能会把60%~70%的内存分配给innodb_buffer_pool_size,如果机器还运行了其它的服务应该重新考虑这个值

得设置

 

4.在MySQL中避免使用swappiness,‘交换’是一个当系统移动部分内存到一个称为‘交换空间’的特殊磁盘空间时的过程,当系统用完

物理内存后就会出现这种情况,系统将信息写入磁盘而不是释放一些内存,磁盘要比内存慢得多因此 sysctl -w vm.swappiness=0 关闭

swappiness

 

5.设置MySQL的最大连接数,只有当执行MySQL请求的时候才会建立连接,执行完成后会关闭连接并被新的连接取代,记住,太多的连

接会导致内存的使用量过高并且会锁住你的MySQL服务器,一般小网站100~200,较大可能需要500~800甚至更多,可以动态地改变

max_connections的值而无需重启MySQL服务器 set global max_connections = 500;

 

6.配置MySQL的线程缓存数量,当客户端断开连接时,如果当前线程数小于thread_cache_size,它的线程将被放入缓存中,下一个请求通

过使用缓存池中的线程来完成,要提高服务器的性能,你可以将此值设置相对高一些,查看线程缓存命中率 show status like

‘threads_created’; show status like 'connections'; 使用以下公式来计算线程池的命中率 100 - ((threads_created / connections) *

100) 如果得到一个较低的数字,意味着大多数mysql连接使用新的线程,而不是从缓存中加载,这时需要增加thread_cache_size值,这里有

一个好处是可以动态地改变thread_cache_size,而无需重启MySQL服务,通过以下方法实现 mysql > set global thread_cache_size = 16;

 

7.禁用MySQL的DNS反向查询,在配置文件中添加以下选项 skip_name_resolve

 

 

8.配置MySQL的查询缓存容量,如果你有很多重复的查询并且数据不经常改变---请使用缓存查询,query_cache_size 设置为GB级会降低服

务器的性能,原因是更新过程中线程需要锁定缓存,通常设置为200~300MB应该足够了,网站比较小可以先给64M并在以后及时去增

加,在配置文件中添加如下配置 query_cache_type = 1 query_cache_size = 64M  query_cache_limit = 256K  query_cache_min_res_unit = 2k

 

 

9.配置临时表容量和内存表最大容量,tmp_table_size 和 max_heap_table_size 这两个变量的大小应该相同,它们可以让你避免磁盘写

入,tmp_table_size 是内置内存表的最大空间,如果表的大小超出限值将会被转换为磁盘上的MyISAM表,这会影响数据库的性能,建议

在服务器上设置这两个值为没GB内存给64M, tmp_table_size= 64M  max_heap_table_size= 64M

 

10.启用MySQL慢查询日志

 

11.检查MySQL的空闲连接,空闲连接会消耗资源,空闲连接是出于‘sleep’状态并且保持了很长一段时间的连接,使用 mysqladmin

processlist -uroot -p | grep “sleep” 查看空闲连接 set global wait_timeout= 60 要重启也有效则需修改配置文件,默认的值为28800秒

 

12.有时候MySQL/MariaDB 数据库中的表很容易崩溃,尤其是服务器意外关机,文件系统突然崩溃或复制过程中仍然访问数据库,有一

个称为‘mysqlcheck’的免费开源工具,它会自动检查,修复和优化linux中数据库的所有表  mysqlcheck --auto-repair --check --optimize -

-all-databases

mysqlcheck --auto-repair --check --optimize databasename

 

1.启用 InnoDB 的每张表一个数据文件设置 InnoDB 可以把每个数据库的信息保存在一个 .idb 数据文件中,每个 .ibd 文件...

max_connections:

允许客户端并发连接的最大数量,默认值是151,一般将该参数设置为500-2000

新的sqlserver服务器上安装的sqlserver版本,可以选择sqlserver2014 企业版本,这个版本相对稳定了,相对也比较新的版本。

 

max_connect_errors:

如果客户端尝试连接的错误数量超过这个参数设置的值,则服务器不再接受新的客户端连接。可以通过清空主机的缓存来解除服务器的这种阻止新连接的状态,通过FLUSH HOSTS或mysqladmin flush-hosts命令来清空缓存。这个参数的默认值是100,一般将该参数设置为100000。

sqlserver配置优化:

[client]

interactive_timeout:

Mysql关闭交互连接前的等待时间,单位是秒,默认是8小时,建议不要将该参数设置超过24小时,即86400

1、最小,最大内存配置

port        = 3306

wait_timeout:

Mysql关闭非交互连接前的等待时间,单位是秒,默认是8小时,建议不要将该参数设置超过24小时,即86400

最小内存一般设置1~2G,满足最小运行的状态的配置。

socket      = /tmp/mysql.sock

skip_name_resolve:

如果这个参数设为OFF,则MySQL服务在检查客户端连接的时候会解析主机名;如果这个参数设为ON,则MySQL服务只会使用IP,在这种情况下,授权表中的Host字段必须是IP地址或localhost。
这个参数默认是关闭的

最大内存,这个一定要设置,一般如果是独立的一个数据库服务器,规划操作系统的内存使用资源,其他全部给到sqlserver,这个如果不

# The MySQL server

back_log:

MySQL服务器连接请求队列所能处理的最大连接请求数,如果队列放满了,后续的连接才会拒绝。当主要的MySQL线程在很短时间内获取大量连接请求时,这个参数会生效。接下来,MySQL主线程会花费很短的时间去检查连接,然后开启新的线程。这个参数指定了MySQL的TCP/IP监听队列的大小。如果MySQL服务器在短时间内有大量的连接,可以增加这个参数。

设置默认是服务器的最大内存,如果出现一个峰值,正好突破服务器的最大内存,那么数据库服务器有宕机的可能性。

[mysqld]

文件相关参数

2、内存锁定页

bind-address = 192.168.0.11

sync_binlog:

控制二进制日志被同步到磁盘前二进制日志提交组的数量。当这个参数为0的时候,二进制日志不会被同步到磁盘;当这个参数设为0以上的数值时,就会有设置该数值的二进制提交组定期同步日志到磁盘。当这个参数设为1的时候,所有事务在提交前会被同步到二进制日志中,因而即使MySQL服务器发生意外重启,任何二进制日志中没有的事务只会处于准备状态,这会导致MySQL服务器自动恢复以回滚这些事务。这样就会保证二进制日志不会丢失事务,是最安全的选项;同时由于增加了磁盘写,这对性能有一定降低。将这个参数设为1以上的数值会提高数据库的性能,但同时会伴随数据丢失的风险。建议将该参数设为2、4、6、8、16。

关于内存锁定页的作用和配置的步骤,我们直接可以参考下官方文档:

port         = 3306

expire_logs_days:

二进制日志自动删掉的时间间隔。默认值为0,代表不会自动删除二进制日志。想手动删除二进制日志,可以执行 PURGE BINARY LOGS。

启动内存锁定页

socket       = /tmp/mysql.sock

max_binlog_size:

二进制日志文件的最大容量,当写入的二进制日志超过这个值的时候,会完成当前二进制的写入,向新的二进制日志写入日志。这个参数最小值时4096字节;最大值和默认值时1GB。相同事务中的语句都会写入同一个二进制日志,当一个事务很大时,二进制日志实际的大小会超过max_binlog_size参数设置的值。如果max_relay_log_size参数设为0,则max_relay_log_size参数会使用和max_binlog_size参数同样的大小。建议将此参数设为512M。

如果sqlserver服务器是一个独立的数据库服务器,这个配置不设置也没问题

 

local_infile:

是否允许客户端使用LOAD DATA INFILE语句。如果这个参数没有开启,客户端不能在LOAD DATA语句中使用LOCAL参数。

3、配置“对即时负载的优化”

##设置表的默认类型。和 default-storage-engine同义

open_files_limit:

操作系统允许MySQL服务打开的文件数量。这个参数实际的值以系统启动时设定的值、max_connections和table_open_cache为基础,使用下列的规则:

  • 10 + max_connections + (table_open_cache * 2)
  • max_connections * 5
  • MySQL启动时指定open_files_limit的值

SP_Configure 'show advanced options', 1

default-table-type = innodb

缓存控制参数

Reconfigure

 

binlog_cache_size:

在事务中二进制日志使用的缓存大小。如果MySQL服务器支持所有的存储引擎且启用二进制日志,每个客户端都会被分配一个二进制日志缓存。如果数据库中有很多大的事务,增大这个缓存可以获得更好的性能。

Binlog_cache_use和Binlog_cache_disk_use这两个参数对于binlog_cache_size参数的优化很有用。
binlog_cache_size参数只设置事务所使用的缓存,非事务SQL语句所使用的缓存由binlog_stmt_cache_size系统参数控制。建议不要将这个参数设为超过64MB,以防止客户端连接多而影响MySQL服务的性能。

GO

# 以下是网络传输配置

max_binlog_cache_size:

如果一个事务需要的内存超过这个参数,服务器会报错"Multi-statement transaction required more than 'max_binlog_cache_size' bytes"。这个参数最大的推荐值是4GB,这是因为MySQL不能在二进制日志设为超过4GB的情况下正常的工作。建议将该参数设为binlog_cache_size*2。

EXEC sp_configure 'optimize for ad hoc workloads',1

#连接进程帧听队列的大小

binlog_stmt_cache_size:

这个参数决定二进制日志处理非事务性语句的缓存。如果MySQL服务支持任何事务性的存储引擎且开启了二进制日志,每个客户端连接都会被分配二进制日志事务和语句缓存。如果数据库中经常运行大的事务,增加这个缓存可以获得更好的性能。

Reconfigure

back_log = 50

table_open_cache:

所有线程能打开的表的数量。

 当新查询执行时,query_hash值会在内存中生成,而不是整个执行计划,当相同的查询第二次执行的时候,SQLServer会查找是否已经存在这个query_hash,如果不存在,执行计划将保存在缓存中。这样就使得仅执行一次的查询将不会保存执行计划到缓存中。所以强烈建议打开这个配置。这个配置不造成任何负面影响,但是可以节省计划缓存的空间

 

thread_cache_size:

MySQL服务缓存以重用的线程数。当客户端断开连接的时候,如果线程缓存没有使用满,则客户端的线程被放入缓存中。如果有客户端断开连接后再次连接到MySQL服务且线程在缓存中,则MySQL服务会优先使用缓存中的线程;如果线程缓存没有这些线程,则MySQL服务器会创建新的线程。如果数据库有很多的新连接,可以增加这个参数来提升性能。如果MySQL服务器每秒有上百个连接,可以增大thread_cache_size参数来使MySQL服务器使用缓存的线程。通过检查Connections和Threads_created状态参数,可以判断线程缓存是否足够。这个参数默认的值是由下面的公式来决定的:8

  • (max_connections / 100)

建议将此参数设置为300~500。线程缓存的命中率计算公式为(1-thread_created/connections)*100%,可以通过这个公式来优化和调整thread_cache_size参数。

 

#之前有过经验, connections超过 370的时候, 线程的栈空间被占满, mysqld无法工作,所以限制为 200

query_cache_size:

为查询结果所分配的缓存。默认这个参数是没有开启的。这个参数的值应设为整数的1024倍,如果设为其他值则会被自动调整为接近此数值的1024倍。这个参数最小需要40KB。建议不要将此参数设为大于256MB,以免占用太多的系统内存。

4、数据文件,日志文件,tempdb文件分离

max_connections=200

金沙棋牌app手机下载,query_cache_min_res_unit:

查询缓存所分配的最小块的大小。默认值是4096(4KB)。

一般数据库服务器有多个物理磁盘,可以将数据文件,日志文件,tempdb文件分散到不同的磁盘上去,从而增加I/O能力。

 

query_cache_type:

设置查询缓存的类型。当这个参数为0或OFF时,则MySQL服务器不会启用查询缓存;当这个参数为1或ON时,则MySQL服务器会缓存所有查询结果(除了带有SELECT SQL_NO_CACHE的语句);当这个参数为2或DEMAND时,则MySQL服务器只会缓存带有SELECT SQL_CACHE的语句。

特别对于tempdb文件,尽量放在性能比较高的磁盘上

#连接 500次发生错 误之后,屏蔽从该 host的所有连接

sort_buffer_size:

每个会话执行排序操作所分配的内存大小。想要增大max_sort_length参数,需要增大sort_buffer_size参数。如果在SHOW GLOBAL STATUS输出结果中看到每秒输出的Sort_merge_passes状态参数很大,可以考虑增大sort_buffer_size这个值来提高ORDER BY 和 GROUP BY的处理速度。建议设置为1~4MB。当个别会话需要执行大的排序操作时,在会话级别增大这个参数。

 

max_connect_errors = 500

read_buffer_size:

为每个线程对MyISAm表执行顺序读所分配的内存。如果数据库有很多顺序读,可以增加这个参数,默认值是131072字节。这个参数的值需要是4KB的整数倍。这个参数也用在下面场景中:

  • 当执行ORDER BY操作时,缓存索引到临时文件(不是临时表)中;
  • 执行批量插入到分区表中;
  • 缓存嵌套查询的执行结果。

5、索引文件,数据文件分离

 

read_rnd_buffer_size:

这个参数用在MyISAM表和任何存储引擎表随机读所使用的内存。当从MyISAM表中以键排序读取数据的时候,扫描的行将使用这个缓存以避免磁盘的扫描。将这个值设到一个较大的值可以显著提升ORDER BY的性能。然后,这个参数会应用到所有的客户端连接,所有不应该将这个参数在全局级别设为一个较大的值;在运行大查询的会话中,在会话级别增大这个参数即可。

默认索引文件,数据文件是放在同一个数据文件组的,可以分成不同的文件组,从而实现索引文件与数据文件分离,索引文件可以放在

#通信缓冲区的最大长度。包或任何生成的 /中间字符串的最大大小。

join_buffer_size:

MySQL服务器用来作普通索引扫描、范围索引扫描和不使用索引而执行全表扫描这些操作所用的缓存大小。通常,获取最快连接的方法是增加索引。当不能增加索引的时候,使全连接变快的方法是增大join_buffer_size参数。对于执行全连接的两张表,每张表都被分配一块连接内存。对于没有使用索引的多表复杂连接,需要多块连接内存。通常来说,可以将此参数在全局上设置一个较小的值,而在需要执行大连接的会话中在会话级别进行设置。默认值是256KB。

性能比较高的磁盘上,这样对查询效率会有比较好的提高。这个根据实际情况来做,因为这个需要把原有的索引全部重新建立一遍。

max_allowed_packet = 32M

net_buffer_length:

每个客户端线程和连接缓存和结果缓存交互,每个缓存最初都被分配大小为net_buffer_length的容量,并动态增长,直至达到max_allowed_packet参数的大小。当每条SQL语句执行完毕后,结果缓存会缩小到net_buffer_length大小。不建议更改这个参数,除非你的系统有很少的内存,可以调整这个参数。如果语句需要的内存超过了这个参数的大小,则连接缓存会自动增大。net_buffer_length参数最大可以设置到1MB。不能在会话级别设置这个参数。

 

 

max_allowed_packet:

网络传输时单个数据包的大小。默认值是4MB。包信息缓存的初始值是由net_buffer_length指定的,但是包可能会增长到max_allowed_packet参数设置的值。如果要使用BLOB字段或长字符串,需要增加这个参数的值。这个参数的值需要设置成和最大的BLOB字段一样的大小。max_allowed_packet参数的协议限制是1GB。这个参数应该是1024整数倍。

6、日志文件

在查询之间将通信缓冲区重设为该值。如果语句超出该长度,缓冲区自动扩大,直到 max_allowed_packet字节。

bulk_insert_buffer_size:

MyISAM表使用一种特殊的树状缓存来提高批量插入的速度,如INSERT ... SELECT,INSERT ... VALUES (...),(...), ...,对空表执行执行LOAD DATA INFILE。这个参数每个线程的树状缓存大小。将这个参数设为0会关闭这个参数。这个参数的默认值是8MB。

 日志文件的管理,大家可以看下官方文档 管理事务日志的大小 

net_buffer_length = 8K

max_heap_table_size:

这个参数设置用户创建的MEMORY表允许增长的最大容量,这个参数用来MEMORY表的MAX_ROWS值。设置这个参数对已有的MEMORY表没有影响,除非表重建或执行ALTER TABLE、TRUNCATE TABLE语句。

这个参数也和tmp_table_size参数一起来现在内部in-memory表的大小。如果内存表使用频繁,可以增大这个参数的值。

 日志文件要根据实际的生产上的增长情况,设定初始大小和增长的大小,如果是数据库迁移,可以根据正在运行的生产环境的情况,

 

tmp_table_size:

内部内存临时表的最大内存。这个参数不会应用到用户创建的MEMORY表。如果内存临时表的大小超过了这个参数的值,则MySQL会自动将超出的部分转化为磁盘上的临时表。在MySQL 5.7.5版本,internal_tmp_disk_storage_engine存储引擎将作为磁盘临时表的默认引擎。在MySQL 5.7.5之前的版本,会使用MyISAM存储引擎。如果有很多的GROUP BY查询且系统内存充裕,可以考虑增大这个参数。

 来设定日志文件的初始大小,增加的方式与大小

# 以下是查询优化

innodb_buffer_pool_dump_at_shutdown:

指定在MySQL服务关闭时,是否记录InnoDB缓存池中的缓存页,以缩短下次重启时的预热过程。通常和innodb_buffer_pool_load_at_startup参数搭配使用。innodb_buffer_pool_dump_pct参数定义了保留的最近使用缓存页的百分比。

 可以根据 建议脚本 来设定。

#避免外部锁定 (默认 是 ON). 对应 variable的 skip_external_locking

innodb_buffer_pool_dump_now:

立刻记录InnoDB缓冲池中的缓存页。通常和innodb_buffer_pool_load_now搭配使用。

 

skip-locking

innodb_buffer_pool_load_at_startup:

指定MySQL服务在启动时,InnoDB缓冲池通过加载之前的缓存页数据来自动预热。通常和innodb_buffer_pool_dump_at_shutdown参数搭配使用。

7、tempdb文件

 

innodb_buffer_pool_load_now:

立刻通过加载数据页来预热InnoDB缓冲池,无需重启数据库服务。可以用来在性能测试时,将缓存改成到一个已知的状态;或在数据库运行报表查询或维护后,将数据库改成到一个正常的状态。
MyISAM参数key_buffer_size:所有线程所共有的MyISAM表索引缓存,这块缓存被索引块使用。增大这个参数可以增加索引的读写性能,在主要使用MyISAM存储引擎的系统中,可设置这个参数为机器总内存的25%。如果将这个参数设置很大,比如设为机器总内存的50%以上,机器会开始page且变得异常缓慢。
可以通过SHOW STATUS 语句查看 Key_read_requests,Key_reads,Key_write_requests, and Key_writes这些状态值。正常情况下Key_reads/Key_read_requests 比率应该小于0.01。数据库更新和删除操作频繁的时候,Key_writes/Key_write_requests 比率应该接近1。

tmpdb是比较重要的,临时表,建立索引,数据库运行中的中间变量都会用到tempdb

#索引缓冲区 (仅作用 于 MYISAM表和临时表 ),决定了数据库索引处理的速度

key_cache_block_size:

key缓存的块大小,默认值是1024字节。

tempdb的文件建立个数,要按照实际的生产环境情况来定,下面有个参照表:

key_buffer_size = 10M

myisam_sort_buffer_size:

在REPAIR TABLE、CREATE INDEX 或 ALTER TABLE操作中,MyISAM索引排序使用的缓存大小。

基于 DTU 的服务层的 tempdb 大小

SLO 最大 tempdb 数据文件大小 (MB) tempdb 数据文件数 最大 tempdb 数据大小 (MB)
“基本” 14,225 @shouldalert 14,225
S0 14,225 @shouldalert 14,225
S1 14,225 @shouldalert 14,225
S2 14,225 @shouldalert 14,225
S3 32,768 @shouldalert 32,768
S4 32,768 2 65,536
S6 32,768 3 98,304
S7 32,768 6 196,608
S9 32,768 12 393,216
S12 32,768 12 393,216
P1 32,768 12 393,216
P2 32,768 12 393,216
P4 32,768 12 393,216
P6 32,768 12 393,216
P11 32,768 12 393,216
P15 32,768 12 393,216
高级弹性池(所有 DTU 配置) 14,225 12 170,700
标准弹性池(所有 DTU 配置) 14,225 12 170,700
基本弹性池(所有 DTU 配置) 14,225 12 170,700
 

 

   DTU是一个资源度量单位,详细可以参考 这个文章 DTU和eTUD

   一般可以先尝试建8个对应的tempdb的文件。

   下面的脚本可以查询当前tempdb的大小和增长参数,迁移数据的时候,可以根据下面的脚本查询,来设定

   新的数据库的tempdb的初始大小,和增长的大小

        SELECTnameAS FileName,

size*1.0/128AS FileSizeinMB, CASE max_size WHEN0THEN'Autogrowth is off.'WHEN-1THEN'Autogrowth is on.'ELSE'Log file grows to a maximum size of 2 TB.'END, growth AS'GrowthValue', 'GrowthIncrement' = CASEWHEN growth = 0THEN'Size is fixed.'WHEN growth > 0AND is_percent_growth = 0THEN'Growth value is in 8-KB pages.'ELSE'Growth value is a percentage.'ENDFROM tempdb.sys.database_files; GO

 

8、建立自动化维护job

可以建立一些日常的自动化维护job,比如自动清理索引碎片,自动更新索引统计信息等等

这些可以参考一些其他sqlserver管理方面的资料,不再详细叙述.

 

9、其他

sqlserver里面还有很多其他一些配置项,比如cpu的使用个数,并行度设置,索引创建时的内存等等。

可以通过查询sys.configurations 

查询所有的sqlserver的配置项,一般这些配置项可以先按默认值,根据生产中的实际情况再调整.

 

 

myisam_max_sort_file_size:

当重建MyISAM索引的时候,例如执行REPAIR TABLE、 ALTER TABLE、 或 LOAD DATA INFILE命令,MySQL允许使用的临时文件的最大容量。如果MyISAM索引文件超过了这个值且磁盘还有充裕的空间,增大这个参数有助于提高性能。

myisam_repair_threads:如果这个参数的值大于1,则MyISAM表在执行Repair操作的排序过程中,在创建索引的时候会启用并行,默认值为1。
InnoDB参数innodb_buffer_pool_size:InnDB存储引擎缓存表和索引数据所使用的内存大小。默认值是128MB。在以InnDB存储引擎为主的系统中,可以将这个参数设为机器物理内存的80%。同时需要注意:

  • 设置较大物理内存时是否会引擎页的交换而导致性能下降;
  • InnoDB存储引擎会为缓存和控制表结构信息使用部分内存,因而实际花费的内存会比设置的值大于10%;
  • 这个参数设置的越大,初始化内存池的时间越长。在MySQL 5.7.5版本,可以以chunk为单位增加或减少内存池的大小。chunk的大小可以通过innodb_buffer_pool_chunk_size参数设定,默认值是128MB。内存池的大小可以等于或是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的整数倍。

 

#所有线程打开的表的数目 (一个表使用 2个文件描述符 ),表数量多,就要大一些。增大该值可以增加 mysqld需要的文件描述符的数量。可以检查 Opened_tables(已经打开的表的数量 )状 态变量来检查你是否需要增加表缓存。

innodb_buffer_pool_instances:

InnoDB缓存池被分成的区域数。对于1GB以上大的InnoDB缓存,将缓存分成多个部分可以提高MySQL服务的并发性,减少不同线程读缓存页的读写竞争。每个缓存池有它单独的空闲列表、刷新列表、LRU列表和其他连接到内存池的数据结构,它们被mutex锁保护。这个参数只有将innodb_buffer_pool_size参数设为1GB或以上时才生效。建议将每个分成的内存区域设为1GB大小。

table_cache = 2048

innodb_max_dirty_pages_pct:

当Innodb缓存池中脏页所占的百分比达到这个参数的值时,InnoDB会从缓存中向磁盘写入数据。默认值是75。

 

innodb_thread_concurrency:

InnoDB存储引擎可以并发使用的最大线程数。当InnoDB使用的线程超过这参数的值时,后面的线程会进入等待状态,以先进先出的算法来处理。等待锁的线程不计入这个参数的值。这个参数的范围是0~1000。默认值是0。当这个参数为0时,代表InnoDB线程的并发数没有限制,这样会导致MySQL创建它所需要的尽可能多的线程。设置这个参数可以参考下面规则:

  • 如果用户线程的并发数小于64,可以将这个参数设为0;
  • 如果系统并发很严重,可以先将这个参数设为128,然后再逐渐将这个参数减小到96, 80, 64或其他数值,直到找到性能较好的一个数值。

#每个排序线程分配的缓冲区的大小。增加该值可以加快 ORDER BY或 GROUP BY操 作

innodb_flush_method:

指定刷新数据到InnoDB数据文件和日志文件的方法,刷新方法会对I/O有影响。如果这个参数的值为空,在类Unix系统上,这个参数的默认值为fsync;在Windows系统上,这个参数的默认值为async_unbuffered。在类Unix系统上,这个参数可设置的值如下:

  • fsync:InnoDB使用fsync()系统函数来刷新数据和日志文件,fsync是默认参数。
  • O_DSYNC:InnoDB使用O_SYNC函数来打开和刷新日志文件,使用fsync()函数刷新数据文件
  • littlesync:这个选项用在内部性能的测试,目前MySQL尚不支持,使用这个参数又一定的风险
  • nosync:这个选项用在内部性能的测试,目前MySQL尚不支持,使用这个参数又一定的风险
  • O_DIRECT:InnoDB使用O_DIRECT(或者directio()在Solaris)函数打开数据文件,使用fsync()刷新数据文件和日志文件
  • O_DIRECT_NO_FSYNC:在刷新I/O时,InnoDB使用O_DIRECT方式。

在有RAID卡和写缓存的系统中,O_DIRECT有助于避免InnoDB缓存池和操作系统缓存之间的双重缓存。在InnoDB数据和日志文件放在SAN存储上面的系统,默认值或O_DSYNC方法会对以读为主的数据库起到加速作用。

sort_buffer_size = 5M

innodb_data_home_dir:

InnoDB系统表空间所使用的数据文件的物理路径,默认路径是MySQL数据文件路径。如果这个参数的值为空,可以在innodb_data_file_path参数里使用绝对路径

 

innodb_data_file_path:

InnoDB数据文件的路径和大小。

#用于完全联接的缓冲区的大小 (当不使用索引的时候使用联接操作 )。

innodb_file_per_table:

当这个参数启用的时候,InnoDB会将新建表的数据和索引单独存放在.ibd格式的文件中,而不是存放在系统表空间中。当这张表被删除或TRUNCATE时,InnoDB表所占用的存储会被释放。这个设定会开启InnoDB的一些其他特性,比如表的压缩。当这个参数关闭的时候,InnoDB会将表和索引的数据存放到系统表空间的ibdata文件中,这会有一个问题,因为系统表空间不会缩小,这样设置会导致空间无法回放。

#一般情况获得快速联接的最好方法是添加索引。当增加索引时不可能通过增加 join_buffer_size值来获得快速完全联接。

innodb_undo_directory:

InnoDB undo日志所在表空间的物理路径。和innodb_undo_logs、innodb_undo_tablespaces参数配合,来设置undo日志的路径,默认路径是数据文件路径。

#将为两个表之间的每个完全联接分配联接缓冲区。对于多个表之间不使用索引的复杂联接,需要多联接缓冲 区。

innodb_undo_logs:

指定InnoDB使用的undo日志的个数。在MySQL 5.7.2版本,32个undo日志被临时表预留使用,并且这些日志存放在临时表表空间(ibtmp1)中。如果undo日志只存放在系统表空间中,想要额外分配供数据修改事务用的undo日志,innodb_undo_logs参数必须设置为32以上的整数。如果你配置了单独的undo表空间,要将innodb_undo_logs参数设为33以上来分配额外供数据修改事务使用的undo日志。每个undo日志最多可以支持1024个事务。如果这个参数没有设置,则它会设为默认值128。

join_buffer_size = 8M

innodb_undo_tablespaces:

undo日志的表空间文件数量。默认,所有的undo日志都是系统表空间的一部分。因为在运行大的事务时,undo日志会增大,将undo日志设置在多个表空间中可以减少一个表空间的大小。undo表空间文件创建在innodb_undo_directory参数指定的路径下,以undoN格式命名,N是以0开头的一系列整数。undo表空间的默认大小为10M。需要在初始化InnoDB前设置innodb_undo_tablespaces这个参数。在MySQL 5.7.2版本,在128个undo日志中,有32个undo日志是为临时表所预留的,有95个undo日志供undo表空间使用。

 

innodb_log_files_in_group:

InnoDB日志组包含的日志个数。InnoDB以循环的方式写入日志。这个参数的默认值和推荐值均是2。日志的路径由innodb_log_group_home_dir参数设定。

#服务器应缓存多少线程以便重新使用。当客户端断开连接时,如果线程少于 thread_cache_size,则客户端的线程被放入缓存。

innodb_log_group_home_dir:

InnoDB重做日志文件的物理路径,重做日志的数量由innodb_log_files_in_group参数指定。如果不指定任何InnoDB日志参数,MySQL默认会在MySQL数据文件路径下面创建两个名为ib_logfile0、ib_logfile1的两个重做日志文件,它们的大小由innodb_log_file_size参数设定。

#如果状态变量 Threads_created(创建用来处理连接的线程数 )较大,你可能要增加 thread_cache_size值。缓 存访问率的计算方法 Threads_created/Connections(试图连接到 (不管是否成功 )MySQL服务器的连接数 )

innodb_log_file_size:

日志组中每个日志文件的字节大小。所有日志文件的大小(innodb_log_file_size * innodb_log_files_in_group)不能超过512GB。

thread_cache_size = 8

innodb_log_buffer_size:

InnoDB写入磁盘日志文件所使用的缓存字节大小。如果innodb_page_size参数为32K,则默认值是8MB;如果innodb_page_size参数为64K,则默认值是16MB。如果日志的缓存设置较大,则MySQL在处理大事务时,在提交事务前无需向磁盘写入日志文件。建议设置此参数为4~8MB。

 

innodb_flush_log_at_trx_commit:

当提交相关的I/O操作被批量重新排列时,这个参数控制提交操作的ACID一致性和高性能之间的平衡。可以改变这个参数的默认值来提升数据库的性能,但是在数据库宕机的时候会丢失少量的事务。这个参数的默认值为1,代表数据库遵照完整的ACID模型,每当事务提交时,InnoDB日志缓存中的内容均会被刷新到日志文件,并写入到磁盘。当这个参数为0时,InnDB日志缓存大概每秒刷新一次日志文件到磁盘。当事务提交时,日志缓存不会立刻写入日志文件,这样的机制不会100%保证每秒都向日志文件刷新日志,当mysqld进程宕掉的时候可能会丢失持续时间为1秒左右的事务数据。当这个参数为2时,当事务提交后,InnoDB日志缓存中的内容会写入到日志文件且日志文件,日志文件以大概每秒一次的频率刷新到磁盘。在MySQL 5.6.6版本,InnoDB日志刷新频率由innodb_flush_log_at_timeout参数决定。通常将个参数设为1。

#使应用程序向线程系统提供需要同时运行的期望的线程数目的提示

innodb_flush_log_at_timeout:

写入或刷新日志的时间间隔。这个参数是在MySQL 5.6.6版本引入的。在MySQL 5.6.6版本之前,刷新的频率是每秒刷新一次。innodb_flush_log_at_timeout参数的默认值也是1秒刷新一次。

thread_concurrency = 8

innodb_lock_wait_timeout:

InnDB事务等待行锁的时间长度。默认值是50秒。当一个事务锁定了一行,这时另外一个事务想访问并修改这一行,当等待时间达到innodb_lock_wait_timeout参数设置的值时,MySQL会报错"ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction",同时会回滚语句(不是回滚整个事务)。如果想回滚整个事务,需要使用--innodb_rollback_on_timeout参数启动MySQL。在高交互性的应用系统或OLTP系统上,可以减小这个参数来快速显示用户的反馈或把更新放入队列稍后处理。在数据仓库中,为了更好的处理运行时间长的操作,可以增大这个参数。这个参数只应用在InnoDB行锁上,这个参数对表级锁无效。这个参数不适用于死锁,因为发生死锁时,InnoDB会立刻检测到死锁并将发生死锁的一个事务回退。

 

innodb_fast_shutdown:

InnoDB关库模式。如果这个参数为0,InnoDB会做一个缓慢关机,在关机前会做完整的刷新操作,这个级别的关库操作会持续数分钟,当缓存中的数据量很大时,甚至会持续几个小时;如果数据库要执行版本升级或降级,需要执行这个级别的关库操作,以保证所有的数据变更都写入到数据文件。如果这个参数的值是1(默认值),为了节省关库时间,InnoDB会跳过新操作,而是在下一次开机的时候通过crash recovery方式执行刷新操作。如果这个参数的值是2,InnoDB会刷新日志并以冷方式关库,就像MySQL宕机一样,没有提交的事务会丢失,在下一次开启数据库时,crash recovery所需要的时间更长;在紧急或排错情形下,需要立刻关闭数据库时,会使用这种方式停库。

#为缓存查询结果分配的内存的数量,加快查询的速度。默认值是 0,即禁用查询缓存。

query_cache_size = 64M

 

#不要缓存大于该值的结果,默认值是 1048576(1MB)。

query_cache_limit = 2M

 

#ft_min_word_len和 ft_max_word_len表示 FULLTEXT索 引的最小和最大字长

ft_min_word_len = 4

 

#每个线程的堆栈大小

thread_stack = 192K

 

#事务隔离级别。 READ-UNCOMMITTED: 未提交读 会出现脏 读、不可重复读、幻读 ( 隔离级别最低,并发性能高 ) ; READ-COMMITTED: 提交读 会出现不可 重复读、幻读问题(锁定正在读取的行); REPEATABLE-READ: 默认值,可重复读 会出幻读(锁定所读取的所有行) ; SERIALIZABLE: 序列化 保证所有的 情况不会发生(锁表)

transaction_isolation = REPEATABLE-READ

 

#联合查询或者 view的 时候,可能会建立临时表,如果在磁盘上建的话,会导致性能低,设置此值可以一定程度上加快速度

tmp_table_size = 64M

 

#每个线程连续扫描时为扫描的每个表分配的缓冲区的大小 ,如果进行多次连续扫描,可能需要增加该值

read_buffer_size = 1M

 

#当排序后按排序后的顺序读取行时,则通过该缓冲区读取行,避免搜索硬盘。将该变量设置为较大的值可以 大大改进 ORDER BY的性能。但是,这是为每个客户端分配的缓冲区, 因此你不应将全局变量设置为较大的值。相反,只为需要运行大查询的客户端更改会话变量。

read_rnd_buffer_size = 2M

 

# 以下是主从同步配置

# Replication Master Server (default)

#主机是 1。备机从 2开始。

server-id    = 1

 

#启动数据库更新二进制日志记录,日志文件名前缀为 mysql-bin

log-bin=mysql-bin

 

#在事务过程中容纳二进制日志 SQL语句的缓存大小,

#二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志 (–log-bin选项 )的前提下为 每个客户端分配的内存。

#如果经常使用大的,多语句事务,可以增加该值以获得更有的性能

binlog_cache_size = 5M

 

#是否记录慢查询,默认 OFF。用 long_query_time变量的值来确定“慢查 询”。

log_slow_queries

 

#超过 2S的 query将记 slow query日志

long_query_time = 2

 

#没有使用索引的查询也作为慢速查询记录到慢速日志里

#或者用 log-queries-not-using-indexes

log_long_format

 

#告诉主服务器,如果当前的数据库 (即 USE选定的数据 库 )是 db_name,不应将更新保存到二进制日志中。

binlog-ignore-db = information_schema

binlog-ignore-db = cluster

binlog-ignore-db = mysql

 

#告诉从服务器限制默认数据库 (由 USE所选择 )为 db_name的 语句的复制

replicate-do-db = test1

replicate-do-db = test2

 

#跨库更新表要用到如下配置。告诉从服务器线程限制复制更新的表匹配指定的数据库和表名模式的语句。模 式可以包含‘ %’和‘ _’通配符,与 LIKE模 式匹配操作符具有相同的含义。要指定多个表,应多次使用该选项,每个表使用一次。该选项可以跨数据库进行更新。

replicate-wild-do-table = test1.%

replicate-wild-do-table = test2.%

 

#当 Slave从 Master数据库读取日志时更新新写入日志中 。如果只启动 log-bin 而没有启动 log-slave-updates则 Slave只记录针对自己数据库操作的更新

log-slave-updates

 

# 以下是 InnoDB 引擎配置

#InnoDB 不会自己建立目录,必须自己使用操作系统命令建立相应的目录。检查你的 MySQL 服务程序在 datadir 目录里 有足够的权限建立文件 (mysql用户组拥有 )。

#这是 InnoDB表 的目录共用设置。如果没有设置, InnoDB 将使用 MySQL的 datadir 目录为缺省目录。如果设定一个空字串 ,可以在 innodb_data_file_path 中设定绝对路径。

innodb_data_home_dir = /data/dbdata/

 

#单独指定数据文件的路径与大小。数据文件的完整路径由 innodb_data_home_dir 与这里所设定值的组合。 文件大小以 MB 单 位指定。因此在文件大小指定后必有“ M”。 InnoDB 也支持缩写“ G”, 1G = 1024M。从 3.23.44 开始,在那些支持大文件的操作系统上可以设置数据文件大小大于 4 GB。而在另一些操作系统上数据文件必须小于 2 GB。

#如果用 autoextend 选项描述最后一个数据文件,当 InnoDB 用尽所有表自 由空间后将会自动扩充最后一个数据文件,每次增量为 8MB

#InnoDB是不会注意你的 OS文 件尺寸限制的, 在一些文件系统中你可能要设定最大容量 2G限制:

#innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M

#这里应当预先创建好 10个 2048M的文件,目前还没有创建

innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:2000M;ibdata8:2000M;ibdata9:2000M;ibdata10:2000M:autoextend

 

#InnoDB 日志文件的路径。如果没有明确指定将默认在 MySQL 的 datadir 目 录下建立两个 5 MB 大小的 ib_logfile0和 ib_logfile1文件。

#将日志文件与数据文件分别放在不同的物理硬盘中对提高性能通常是很有益的

innodb_log_group_home_dir = /data/dbdata/

 

#主要用来存储表结构和数据字典,表越多要求内存就越大

innodb_additional_mem_pool_size = 16M

 

# You can set .._buffer_pool_size up to 50 – 80 %

# of RAM but beware of setting memory usage too high

#表和索引数据的内存缓冲区,越大性能越高。但不能超过物理内存的 50%。若 64位 OS,该值可以更大

#32位 OS受 2G内存的限制,不能将内存使用设置太高, glibc 会把进程堆增长到线程堆栈之上,这将会使服务器崩溃。下面的接近或超过于 2G 将会很危险:

#innodb_buffer_pool_size + key_buffer +    max_connections * (sort_buffer_size + record_buffer_size + binlog_cache_size) + max_connections * 2 MB

#每个线程将使用 2MB(MySQL AB 二进制版本为 256 KB)的堆栈,在最坏的环境下还会使用 sort_buffer_size + record_buffer_size 的附加内存。

innodb_buffer_pool_size = 1G

 

#InnoDB 中的文件 I/O 线 程。 通常设置为 4

innodb_file_io_threads = 4

 

#并发线程。 2*(内 核数量 +磁盘数量 )

innodb_thread_concurrency = 16

 

#1表示每次事务结束都写日志并刷新磁盘; 2表示每次事务写日志但不刷新磁盘 (每秒刷新 ); 0(默认值 )表示每秒 写日志并刷新磁盘。 0表示最多丢失 1秒的数据,但性能最好。

innodb_flush_log_at_trx_commit = 0

 

#InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志 缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交 (commit)。 因此,如果有大的事务,设置大的日志缓冲可以减少磁盘 I/O。

innodb_log_buffer_size = 8M

 

# Set .._log_file_size to 25 % of buffer pool size

#日志文件大小设置。增大该值可减少刷新日志频率,但会延长 mysql启动和灾难恢复的时间。

#日志组中的每个日志文件的大小 (单位 MB)。如果 n 是日志组 (innodb_log_files_in_group)中日志文件的数目,那么理想的数值为 1M 至缓冲池 (innodb_log_buffer_size)大小的 1/n。较 大的值,可以减少刷新缓冲池的次数,从而减少磁盘 I/O。但是大的日志 文件意味着在崩溃时需要更长的时间来恢复数据。

innodb_log_file_size = 256M

 

#日志组中的日志文件数目。 InnoDB 以环型方式 (circular fashion)写入文件。数值 3 被 推荐使用

innodb_log_files_in_group = 3

 

#InnoDB行锁导致的死锁等待时间 (默认值是 50S)

innodb_lock_wait_timeout = 30

 

#是否支持分布式事务,关闭以提高性能 (默认是 ON)

innodb_support_xa = OFF

 

[mysqldump]

quick

max_allowed_packet = 16M

 

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

 

[isamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

 

[myisamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

 

[mysqlhotcopy]

interactive-timeout

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:Mysql数据库服务器配置文件,详细参数说明

关键词:

上一篇:没有了

下一篇:没有了