一、MySQL变量
在数据库中,变量分为系统变量(以‘@@’开头)以及用户自定义变量。系统变量又可以分为全局系统变量以及会话系统变量,静态变量属于特殊的全局系统变量。
全局系统变量(global)和会话系统变量(session),有时也把全局系统变量称为全局变量,把会话系统变称为local变量或者系统会话变量。Mysql服务成功启动后,如果没有mysql客户机连接mysql服务器,那么mysql服务器内存中的系统变量全部是全局系统变量。
每个mysql客户机成功连接mysql服务器后,都会产生与之对应的会话。会话期间,mysql服务实例会在mysql服务器内存中生成与该会话对应的会话系统变量,这些会话系统变量的初始值是全局系统变量值的复制。由于各会话在会话期间所做的操作不尽相同,为了标记各个会话,会话系统变量又新增12个变量,例如,会话系统变量pseudo_thread_id用于标记当前会话的mysql连接ID。
会话系统变量的特点在于,它仅仅用于定义当前会话的属性,会话期间,当前会话对某个会话系统变量值得修改,不会影响其他会话同一个会话系统变量的值,即MySQL客户机1的会话系统变量值不会被MySQL客户机2看到或修改;MySQL客户机1关闭或者MySQL客户机1与服务器断开连接后,与MySQL客户机1相关的所有会话系统变量将自动释放,以便于节省MySQL服务器的内存。
全局系统变量的特点在于,它是用于定义MySQL服务实例的属性、特点,会话1对某个全局系统变量值得修改会导致会话2中同一个全局系统变量值得修改。然而,它只影响更改后连接的客户的从该全局变量初始化的相应会话变量。不影响目前已经连接的客户端的会话变量(即使客户端执行SET GLOBAL语句也不影响)。
查看系统变量值“show global variables;”命令即可查看全局系统变量值;使用“show session variable;”即可查看与当前会话相关的所有会话系统变量以及所有的全局系统变量。
在MySQL中,有一些系统变量仅仅是会话系统变量。例如,MySQL连接ID会话系统变量pseudo_thread_id,可以使用下面两种方法查看该会话系统变量的值。
Show session variables like ‘pseudo_thread_id’;
Show variables like ‘pseudo_thread_id’;
在MySQL中,有一些系统变量即是全局系统变量,又是会话系统变量。例如,系统变量character_set_client既是全局系统变量,又是会话系统变量。
备注:作为MySQL编码规范,MySQL中的系统变量以两个“@”开头,其中“@@global”仅仅用于标记全局系统变量,“@@session”仅仅用于标记会话系统变量。“@@”首先标记会话系统变量,如果会话系统变量不存在,则标记全局系统变量。例如,查看InnoDB_data_file_path而言,可以使用“select @@global.innodb_data_file_path;”。
设置系统变量的值
方法一:修改MySQL源代码,然后对MySQL配置文件,继而修改MySQL系统变量的值(适用于MySQL高级用户);
方法二:通过修改MySQL配置文件,继而修改MySQL系统变量的值(需要重新启动MySQL服务);
方法三:在MySQL服务运行期间,使用“set”命令重新设置系统变量的值,如果将一个系统值设置为MySQL默认值,可以使用"default"关键字作为值;例如,set @@global.innodb_file_per_table=default;注意set命令不会导致 my.cnf 配置文件的内容发生变化。
如果重新设置全局系统变量值(例如InnoDB_file_per_table)的值,可以使用下面两种方法(global关键字不能省略)
Set @@global.innodb_file_per_table=ON;
Set global innodb_file_per_table=ON;
重新设置会话系统变量(session)的值,有四种方法:
Set @@session.pseudo_thread_id=5;
Set session pseudo_thread_id=5;
Set @@pseudo_thread_id=5;
Set pseudo_thread_id=5;
对于大部分的系统变量而言,可以在MySQL服务运行期间通过“set”命令重新设置其值。在MySQL中还有一些特殊的全局变量(例如log_in tmpdir version datadir),在MySQL服务实例运行期间它们的值不能动态修改,不能使用set命令进行重新设置的,这种变量称为静态变量,数据库管理员可以使用方法一或方法二对静态变量的值重新设置。
The MySQL server maintains many system variables that indicate how it is configured. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an option file. Most of them can bechanged dynamically while the server is running by means of the SET statement,which enables you to modify operation of the server without having to stop and restart it. You can refer to system variable values in expressions.
MySQL 维护很多变量来控制其工作属性,我们可以通过命令行参数传递给mysqld进程,或者写到配置文件中。
MySQL 服务器变量的分类
按作用域分类:
1、全局变量(有权限的用户)
SHOW GLOBAL VARIABLES;
2、会话变量
SHOW [SESSION] VARIABLES;
按生效时间划分:
1、动态:可即时调整
2、静态:写在配置文件中,或者通过参数传递给mysqld
动态调整参数生效方式:
全局变量:对当前会话无效,只对新建立的会话有效
会话变量:即时生效,但只对当前会话有效。
服务器变量:@@var
SELECT @@global.sql_mode
SET [GLOBAL | SESSION] var = 'value'
用户变量
可以先在用户变量中保存值然后在以后引用它;这样可以将值从一个语句传递到另一个语句。用户变量与连接有关。也就是说,一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时,该客户端连接的所有变量将自动释放。
用户变量的形式为@var_name,其中变量名var_name可以由当前字符集的文字数字字符、‘.’、‘_’和‘$’组成。 默认字符集是cp1252 (Latin1)。可以用mysqld的--default-character-set选项更改字符集。用户变量名对大小写不敏感。
设置用户变量的一个途径是执行SET语句:
SET @var_name = expr [, @var_name = expr] ...
对于SET,可以使用=或:=作为分配符。分配给每个变量的expr可以为整数、实数、字符串或者NULL值。
也可以用语句代替SET来为用户变量分配一个值。在这种情况下,分配符必须为 := 而不能用 =,因为在非SET语句中=被视为一个比较 操作符:
mysql> SET @t1=0, @t2=0, @t3=0;
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 |
+----------------------+------+------+------+
| 5 | 5 | 1 | 4 |
+----------------------+------+------+------+
用户变量可以用于表达式中。目前不包括明显需要文字值的上下文中,例如SELECT语句的LIMIT子句,或者LOAD DATA语句的IGNORE number LINES子句。
如果使用没有初始化的变量,其值是NULL。
如果用户变量分配了一个字符串值,其字符集和校对规则与该字符串的相同。用户变量的可压缩性(coercibility)是隐含的。(即为表列值的相同的可压缩性(coercibility)。
注释:在SELECT语句中,表达式发送到客户端后才进行计算。这说明在HAVING、GROUP BY或者ORDER BY子句中,不能使用包含SELECT列表中所设的变量的表达式。例如,下面的语句不能按期望工作:
mysql> SELECT (@aa:=id) AS a,(@aa+3) AS b 从tbl_name HAVING b=5;
HAVING子句中引用了SELECT列表中的表达式的别名,使用@aa。不能按期望工作:@aa不包含当前行的值,而是前面所选的行的id值。
一般原则是不要在语句的一个部分为用户变量分配一个值而在同一语句的其它部分使用该变量。可能会得到期望的结果,但不能保证。
设置变量并在同一语句中使用它的另一个问题是变量的默认结果的类型取决于语句前面的变量类型。下面的例子说明了该点:
mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;
对于该 SELECT语句,MySQL向客户端报告第1列是一个字符串,并且将@a的所有访问转换为字符串,即使@a在第2行中设置为一个数字。执行完SELECT语句后,@a被视为下一语句的一个数字。
要想避免这种问题,要么不在同一个语句中设置并使用相同的变量,要么在使用前将变量设置为0、0.0或者''以定义其类型。
未分配的变量有一个值NULL,类型为字符串。
系统变量
MySQL可以访问许多系统和连接变量。当服务器运行时许多变量可以动态更改。这样通常允许你修改服务器操作而不需要停止并重启服务器。
mysqld服务器维护两种变量。全局变量影响服务器整体操作。会话变量影响具体客户端连接的操作。
当服务器启动时,它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。服务器启动后,通过连接服务器并执行SET GLOBAL var_name语句,可以动态更改这些全局变量。要想更改全局变量,必须具有SUPER权限。
服务器还为每个连接的客户端维护一系列会话变量。在连接时使用相应全局变量的当前值对客户端的会话变量进行初始化。对于动态会话变量,客户端可以通过SET SESSION var_name语句更改它们。设置会话变量不需要特殊权限,但客户端只能更改自己的会话变量,而不能更改其它客户端的会话变量。
对于全局变量的更改可以被访问该全局变量的任何客户端看见。然而,它只影响更改后连接的客户的从该全局变量初始化的相应会话变量。不影响目前已经连接的客户端的会话变量(即使客户端执行SET GLOBAL语句也不影响)。
可以使用几种语法形式来设置或检索全局或会话变量。下面的例子使用了sort_buffer_sizeas作为示例变量名。
要想设置一个GLOBAL变量的值,使用下面的语法:
mysql> SET GLOBAL sort_buffer_size=value;
mysql> SET @@global.sort_buffer_size=value;
要想设置一个SESSION变量的值,使用下面的语法:
mysql> SET SESSION sort_buffer_size=value;
mysql> SET @@session.sort_buffer_size=value;
mysql> SET sort_buffer_size=value;
LOCAL是SESSION的同义词。
如果设置变量时不指定GLOBAL、SESSION或者LOCAL,默认使用SESSION。参见13.5.3节,“SET语法”。
要想检索一个GLOBAL变量的值,使用下面的语法:
mysql> SELECT @@global.sort_buffer_size;
mysql> SHOW GLOBAL VARIABLES like 'sort_buffer_size';
要想检索一个SESSION变量的值,使用下面的语法:
mysql> SELECT @@sort_buffer_size;
mysql> SELECT @@session.sort_buffer_size;
mysql> SHOW SESSION VARIABLES like 'sort_buffer_size';
这里,LOCAL也是SESSION的同义词。
当你用SELECT @@var_name搜索一个变量时(也就是说,不指定global.、session.或者local.),MySQL返回SESSION值(如果存在),否则返回GLOBAL值。
对于SHOW VARIABLES,如果不指定GLOBAL、SESSION或者LOCAL,MySQL返回SESSION值。
当设置GLOBAL变量需要GLOBAL关键字但检索时不需要它们的原因是防止将来出现问题。如果我们移除一个与某个GLOBAL变量具有相同名字的SESSION变量,具有SUPER权限的客户可能会意外地更改GLOBAL变量而不是它自己的连接的SESSION变量。如果我们添加一个与某个GLOBAL变量具有相同名字的SESSION变量,想更改GLOBAL变量的客户可能会发现只有自己的SESSION变量被更改了。
在启动MySQL 时,我们可以手工的在命令后面指定相关的参数:
# mysqld_safe \ --basedir=/usr/local/mysql \ --datadir=/mydata/data \ --user=mysql \ --pid-file=/var/lib/mysql/mysql.pid \ --socket=/var/lib/mysql/mysql.sock \ --port=3306 &
这里是我们的默认的一个参数值,我们可以通过如下命令来查看MySQL的默认参数的具体值:
(1)To see the values that a serverwill use based on its compiled-in defaults and any option files that it reads,use this command:
# mysqld --verbose --help
(2)To see the values that a serverwill use based on its compiled-in defaults, ignoring the settings in any optionfiles, use this command:
# mysqld --no-defaults --verbose --help
二、MySQL配置文件
每次启动都手工的指定参数比较麻烦,我们可以把相关的参数配置到参数文件里,在MySQL启动时会读取这些文件,参数文件的位置:
On Unix, Linux and Mac OS X, MySQL programsread startup options from the following files, in the specified order (topitems are used first).
注意上表中参数文件的顺序,MySQL 会按照从上往下的顺序优先使用上面的参数文件。
~ represents the current user's home directory (the value of $HOME).
SYSCONFDIR representsthe directory specified with the SYSCONFDIR option to CMake when MySQL wasbuilt. By default, this is the etc directory located under the compiled-ininstallation directory.
MYSQL_HOME is an environment variable containing the path to the directory in which theserver-specific my.cnf file resides. If MYSQL_HOME is not set and you start theserver using the mysqld_safe program, mysqld_safe attempts to set MYSQL_HOME asfollows:
(1)Let BASEDIR and DATADIR representthe path names of the MySQL base directory and data directory, respectively.
(2)If there is a my.cnf file in DATADIR but not in BASEDIR, mysqld_safe sets MYSQL_HOME to DATADIR.
(3)Otherwise, if MYSQL_HOME is not setand there is no my.cnf file in DATADIR, mysqld_safe sets MYSQL_HOME to BASEDIR.
In MySQL 5.5,use of DATADIR as the location for my.cnf is deprecated.
--在MySQL 5.5中,my.cnf 文件已经不放在DATADIR目录下。
Typically, DATADIR is /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation. Note that this is the data directory location that wasspecified at configuration time, not the one specified with the --datadir optionwhen mysqld starts. Use of --datadir at runtime has no effect on where theserver looks for option files, because it looks for them before processing anyoptions.
我们也可以这样查看:
[root@bogon ~]# mysql --help | grep my.cnf order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
假设4个配置文件都存在,同时使用--defaults-extra-file指定了参数文件,如果这时有一个 "参数变量"在5个配置文件中都出现了,那么后面的配置文件中的参数变量值会覆盖前面配置文件中的参数变量值,就是说会使用~/.my.cnf中设置的值。
也就是说,当有多个配置文件同时存在时,MySQL会以读取到的最后一个配置文件中的参数为准。越靠后读取,那么作用的优先级越高,这是linux的通用规则。
*****注意*****
如果使用./bin/mysqld_safe 守护进程启动mysql数据库时,使用了 --defaults-file=<配置文件的绝对路径>参数,这时只会使用这个参数指定的配置文件。
--defaults-file=# Only read default options from the given file #. --defaults-extra-file=# Read this file after the global files are read.
客户端: mysql, mysqladmin, mysqldump, mysqlimport, mysqlcheck
服务器: mysqld, mysqld_safe, mysqld_multi
客户端与服务器 共享同一个配置文件:my.cnf
my.cnf 配置文件,可以指定如下[mysqld] 、[mysqld_safe]、[client]、[mysql] 等配置段, 分别定义服务器,以及客户端工具的工作属性。
当然,配置文件中的参数基本都可以在命令行上指定。
在命令行上通常使用减号 - 连接: default-extra-file
在配置文件中: 使用减号或者下划线都可以,比如,default-extra-file 或者 default_extra_file 都可以
建议在配置文件中,都是用下划线,保持统一性。
# mysqld --help --verbose
Mysql-5.5.32是Mysql5.5系列中最后一个版本,也是最后一个有配置文件的版本,为什么这么说呢,用过5.6的博友都知道,在mysql5.6中已经不提供配置文件选择,只有一个默认的配置文件
#BEGIN CONFIG INFO #DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries #TYPE: SYSTEM #END CONFIG INFO # # This is a MySQL example config file for systems with 4GB of memory # running mostly MySQL using InnoDB only tables and performing complex # queries with few connections. # # MySQL programs look for option files in a set of # locations which depend on the deployment platform. # You can copy this option file to one of those # locations. For information about these locations, see: # http://dev.mysql.com/doc/mysql/en/option-files.html # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option. # # More detailed information about the individual options can also be # found in the manual. # # # The following options will be read by MySQL client applications. # Note that only client applications shipped by MySQL are guaranteed # to read this section. If you want your own MySQL client program to # honor these values, you need to specify it as an option during the # MySQL client library initialization. # # 以下选项会被MySQL客户端应用读取, 注意只有MySQL附带的客户端应用程序保证可以读取这段内容,如果你想你自己的MySQL应用程序获取这些值,需要在MySQL客户端库初始化的时候指定这些选项 [client] #user = [user_name] # 登陆用户名 #password = [your_password] #mysql客户端连接mysql时的密码 port = 3306 #mysql客户端连接时的默认端口 socket = /tmp/mysql.sock #与mysql服务器本地通信所使用的socket文件路径 # *** Application-specific options follow here *** # # The MySQL server # [mysqld] # generic configuration options #一般配置选项 port = 3306 #mysql服务器监听的默认端口 socket = /tmp/mysql.sock #socket本地通信套接字文件路径 # back_log is the number of connections the operating system can keep in # the listen queue, before the MySQL connection manager thread has # processed them. If you have a very high connection rate and experience # "connection refused" errors, you might need to increase this value. # Check your OS documentation for the maximum value of this parameter. # Attempting to set back_log higher than your operating system limit # will have no effect. # back_log 是操作系统在监听队列中所能保持的连接数, # 队列保存了在MySQL连接管理器线程处理之前的连接. # 如果你有非常高的连接率并且出现“connection refused”报错, # 你就应该增加此处的值. # 检查你的操作系统能打开文件数来获取这个变量的最大值. # 如果将back_log设定到比你操作系统限制更高的值,将会没有效果 back_log = 50 # Don't listen on a TCP/IP port at all. This can be a security # enhancement, if all processes that need to connect to mysqld run # on the same host. All interaction with mysqld must be made via Unix # sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # 不在TCP/IP端口上进行监听. # 如果所有的进程都是在同一台服务器连接到本地的mysqld, # 这样设置将是增强安全的方法 # 所有mysqld的连接都是通过Unix sockets 或者命名管道进行的. # 注意在windows下如果没有打开命名管道选项而只是用此项 # (通过 “enable-named-pipe” 选项) 将会导致mysql服务没有任何作用! #skip-networking #默认是没有开启的 # The maximum amount of concurrent sessions the MySQL server will # allow. One of these connections will be reserved for a user with # SUPER privileges to allow the administrator to login even if the # connection limit has been reached. # MySQL 服务器所允许的同时会话数的上限 # 其中一个连接将被SUPER权限保留作为管理员登录. # 即便已经达到了连接数的上限. max_connections = 100 # Maximum amount of errors allowed per host. If this limit is reached, # the host will be blocked from connecting to the MySQL server until # "FLUSH HOSTS" has been run or the server was restarted. Invalid # passwords and other errors during the connect phase result in # increasing this value. See the "Aborted_connects" status variable for # global counter. # 每个客户端连接最大的错误允许数量,如果达到了此限制. # 这个客户端将会被MySQL服务阻止直到执行了”FLUSH HOSTS” 或者服务重启 # 非法的密码以及其他在链接时的错误会增加此值. # show status 查看 “Aborted_connects” 状态来获取全局计数器. max_connect_errors = 10 # The number of open tables for all threads. Increasing this value # increases the number of file descriptors that mysqld requires. # Therefore you have to make sure to set the amount of open files # allowed to at least 4096 in the variable "open-files-limit" in # section [mysqld_safe] # 所有线程所打开表的数量. # 增加此值就增加了mysqld所需要的文件描述符的数量 # 这样你需要确认在[mysqld_safe]中 “open-files-limit” 变量设置打开文件数量允许至少4096 table_open_cache = 2048 # Enable external file level locking. Enabled file locking will have a # negative impact on performance, so only use it in case you have # multiple database instances running on the same files (note some # restrictions still apply!) or if you use other software relying on # locking MyISAM tables on file level. # 允许外部文件级别的锁. 打开文件锁会对性能造成负面影响 # 所以只有在你在同样的文件上运行多个数据库实例时才使用此选项(注意仍会有其他约束!) # 或者你在文件层面上使用了其他一些软件依赖来锁定MyISAM表 #external-locking #默认是没有开启的 # The maximum size of a query packet the server can handle as well as # maximum query size server can process (Important when working with # large BLOBs). enlarged dynamically, for each connection. # 服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB字段一起工作时相当必要) # 每个连接独立的大小.大小动态增加 max_allowed_packet = 16M # The size of the cache to hold the SQL statements for the binary log # during a transaction. If you often use big, multi-statement # transactions you can increase this value to get more performance. All # statements from transactions are buffered in the binary log cache and # are being written to the binary log at once after the COMMIT. If the # transaction is larger than this value, temporary file on disk is used # instead. This buffer is allocated per connection on first update # statement in transaction # 在一个事务中binlog为了记录SQL状态所持有的cache大小 # 如果你经常使用大的,多声明的事务,你可以增加此值来获取更大的性能. # 所有从事务来的状态都将被缓冲在binlog缓冲中然后在提交后一次性写入到binlog中 # 如果事务比此值大, 会使用磁盘上的临时文件来替代. # 此缓冲在每个连接的事务第一次更新状态时被创建 binlog_cache_size = 1M # Maximum allowed size for a single HEAP (in memory) table. This option # is a protection against the accidential creation of a very large HEAP # table which could otherwise use up all memory resources. # 独立的内存表所允许的最大容量. # 此选项为了防止意外创建一个超大的内存表导致永尽所有的内存资源. max_heap_table_size = 64M # Size of the buffer used for doing full table scans. # Allocated per thread, if a full scan is needed. #MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分#配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁,#并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。 read_buffer_size = 2M # When reading rows in sorted order after a sort, the rows are read # through this buffer to avoid disk seeks. You can improve ORDER BY # performance a lot, if set this to a high value. # Allocated per thread, when needed. #是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需#要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。 read_rnd_buffer_size = 16M # Sort buffer is used to perform sorts for some ORDER BY and GROUP BY # queries. If sorted data does not fit into the sort buffer, a disk # based merge sort is used instead - See the "Sort_merge_passes" # status variable. Allocated per thread if sort is needed. # 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序 # 如果排序后的数据无法放入排序缓冲, # 一个用来替代的基于磁盘的合并分类会被使用 # 查看 “Sort_merge_passes” 状态变量. # 在排序发生时由每个线程分配 sort_buffer_size = 8M # This buffer is used for the optimization of full JOINs (JOINs without # indexes). Such JOINs are very bad for performance in most cases # anyway, but setting this variable to a large value reduces the # performance impact. See the "Select_full_join" status variable for a # count of full JOINs. Allocated per thread if full join is found # 此缓冲被使用来优化全联合(full JOINs 不带索引的联合). # 类似的联合在极大多数情况下有非常糟糕的性能表现, # 但是将此值设大能够减轻性能影响. # 通过 “Select_full_join” 状态变量查看全联合的数量 # 当全联合发生时,在每个线程中分配 join_buffer_size = 8M # How many threads we should keep in a cache for reuse. When a client # disconnects, the client's threads are put in the cache if there aren't # more than thread_cache_size threads from before. This greatly reduces # the amount of thread creations needed if you have a lot of new # connections. (Normally this doesn't give a notable performance # improvement if you have a good thread implementation.) # 我们在cache中保留多少线程用于重用 # 当一个客户端断开连接后,如果cache中的线程还少于thread_cache_size, # 则客户端线程被放入cache中. # 这可以在你需要大量新连接的时候极大的减少线程创建的开销 # (一般来说如果你有好的线程模型的话,这不会有明显的性能提升.) thread_cache_size = 8 # This permits the application to give the threads system a hint for the # desired number of threads that should be run at the same time. This # value only makes sense on systems that support the thread_concurrency() # function call (Sun Solaris, for example). # You should try [number of CPUs]*(2..4) for thread_concurrency # 此允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量. # 此值只对于支持 thread_concurrency() 函数的系统有意义( 例如Sun Solaris). # 你可可以尝试使用 [CPU数量]*(2..4) 来作为thread_concurrency的值 thread_concurrency = 8 # Query cache is used to cache SELECT results and later return them # without actual executing the same query once again. Having the query # cache enabled may result in significant speed improvements, if your # have a lot of identical queries and rarely changing tables. See the # "Qcache_lowmem_prunes" status variable to check if the current value # is high enough for your load. # Note: In case your tables change very often or if your queries are # textually different every time, the query cache may result in a # slowdown instead of a performance improvement. # 查询缓冲常被用来缓冲 SELECT 的结果并且在下一次同样查询的时候不再执行直接返回结果. # 打开查询缓冲可以极大的提高服务器速度, 如果你有大量的相同的查询并且很少修改表. # 查看 “Qcache_lowmem_prunes” 状态变量来检查是否当前值对于你的负载来说是否足够高. # 注意: 在你表经常变化的情况下或者如果你的查询原文每次都不同, # 查询缓冲也许引起性能下降而不是性能提升. query_cache_size = 64M # Only cache result sets that are smaller than this limit. This is to # protect the query cache of a very large result set overwriting all # other query results. # 只有小于此设定值的结果才会被缓冲 # 此设置用来保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖. query_cache_limit = 2M # Minimum word length to be indexed by the full text search index. # You might wish to decrease it if you need to search for shorter words. # Note that you need to rebuild your FULLTEXT index, after you have # modified this value. # 被全文检索索引的最小的字长. # 你也许希望减少它,如果你需要搜索更短字的时候. # 注意在你修改此值之后, # 你需要重建你的 FULLTEXT 索引 ft_min_word_len = 4 # If your system supports the memlock() function call, you might want to # enable this option while running MySQL to keep it locked in memory and # to avoid potential swapping out in case of high memory pressure. Good # for performance. # 如果你的系统支持 memlock() 函数,你也许希望打开此选项用以让运行中的mysql在在内存高度紧张的时候,数据在内存中保持锁定并且防止可能被swapping out # 此选项对于性能有益 #memlock # Table type which is used by default when creating new tables, if not # specified differently during the CREATE TABLE statement. # 当创建新表时作为默认使用的表类型, # 如果在创建表示没有特别执行表类型,将会使用此值 default-storage-engine = MYISAM # Thread stack size to use. This amount of memory is always reserved at # connection time. MySQL itself usually needs no more than 64K of # memory, while if you use your own stack hungry UDF functions or your # OS requires more stack for some operations, you might need to set this # to a higher value. # 线程使用的堆大小. 此容量的内存在每次连接时被预留. # MySQL 本身常不会需要超过64K的内存 # 如果你使用你自己的需要大量堆的UDF函数 # 或者你的操作系统对于某些操作需要更多的堆, # 你也许需要将其设置的更高一点. thread_stack = 192K # Set the default transaction isolation level. Levels available are: # 设定默认的事务隔离级别.可用的级别如下: # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE transaction_isolation = REPEATABLE-READ # Maximum size for internal (in-memory) temporary tables. If a table # grows larger than this value, it is automatically converted to disk # based table This limitation is for a single table. There can be many # of them. # 内部(内存中)临时表的最大大小 # 如果一个表增长到比此值更大,将会自动转换为基于磁盘的表. # 此限制是针对单个表的,而不是总和. tmp_table_size = 64M # Enable binary logging. This is required for acting as a MASTER in a # replication configuration. You also need the binary log if you need # the ability to do point in time recovery from your latest backup. # 打开二进制日志功能. # 在复制(replication)配置中,作为MASTER主服务器必须打开此项 # 如果你需要从你最后的备份中做基于时间点的恢复,你也同样需要二进制日志. log-bin=mysql-bin # binary logging format - mixed recommended #设定记录二进制日志的格式,有三种格式,基于语句 statement、 基于行 row、 混合方式 mixed binlog_format=mixed # If you're using replication with chained slaves (A->B->C), you need to # enable this option on server B. It enables logging of updates done by # the slave thread into the slave's binary log. # 如果你在使用链式从服务器结构的复制模式 (A->B->C), # 你需要在服务器B上打开此项. # 此选项打开在从线程上重做过的更新的日志, # 并将其写入从服务器的二进制日志. #log_slave_updates # Enable the full query log. Every query (even ones with incorrect # syntax) that the server receives will be logged. This is useful for # debugging, it is usually disabled in production use. # 打开查询日志. 所有的由服务器接收到的查询 (甚至对于一个错误语法的查询) # 都会被记录下来. 这对于调试非常有用, 在生产环境中常常关闭此项. #log #默认是没有开启的,会影响服务器性能 # Print warnings to the error log file. If you have any problem with # MySQL you should enable logging of warnings and examine the error log # for possible explanations. # 将警告打印输出到错误log文件. 如果你对于MySQL有任何问题 # 你应该打开警告log并且仔细审查错误日志,查出可能的原因. #log_warnings # Log slow queries. Slow queries are queries which take more than the # amount of time defined in "long_query_time" or which do not use # indexes well, if log_short_format is not enabled. It is normally good idea # to have this turned on if you frequently add new queries to the # system. # 记录慢速查询. 慢速查询是指消耗了比 “long_query_time” 定义的更多时间的查询. # 如果 log_long_format 被打开,那些没有使用索引的查询也会被记录. # 如果你经常增加新查询到已有的系统内的话. 一般来说这是一个好主意 slow_query_log # All queries taking more than this amount of time (in seconds) will be # trated as slow. Do not use "1" as a value here, as this will result in # even very fast queries being logged from time to time (as MySQL # currently measures time with second accuracy only). # 所有的使用了比这个时间(以秒为单位)更多的查询会被认为是慢速查询. # 不要在这里使用”1″, 否则会导致所有的查询,甚至非常快的查询页被记录下来(由于MySQL 目前时间的精确度只能达到秒的级别). long_query_time = 2 # *** Replication related settings # *** 主从复制相关的设置 # Unique server identification number between 1 and 2^32-1. This value # is required for both master and slave hosts. It defaults to 1 if # "master-host" is not set, but will MySQL will not function as a master # if it is omitted. # 唯一的服务辨识号,数值位于 1 到 2^32-1之间. # 此值在master和slave上都需要设置. # 如果 “master-host” 没有被设置,则默认为1, 但是如果忽略此选项,MySQL不会作为master生效. server-id = 1 # Replication Slave (comment out master section to use this) #复制的Slave (去掉master段的注释来使其生效) # # To configure this host as a replication slave, you can choose between # two methods : #为了配置此主机作为复制的slave服务器,你可以选择两种方法: # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: #使用 CHANGE MASTER TO 命令 (在我们的手册中有完整描述) - # 语法如下: # # CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, # MASTER_USER=<user>, MASTER_PASSWORD=<password> ; # # where you replace <host>, <user>, <password> by quoted strings and # <port> by the master's port number (3306 by default). # 你需要替换掉 , , 等被尖括号包围的字段以及使用master的端口号替换 (默认3306). # Example: 案例 # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR 或者 # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # changes in this file to the variable values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # #设置以下的变量. 不论如何, 在你选择这种方法的情况下, 然后第一次启动复制(甚至不成功的情况下, # 例如如果你输入错密码在master-password字段并且slave无法连接), # slave会创建一个 master.info 文件,并且之后任何对于包含在此文件内的参数的变化都会被忽略 # 并且由 master.info 文件内的内容覆盖, 除非你关闭slave服务, 删除 master.info 并且重启slave 服务. # 由于这个原因,你也许不想碰一下的配置(注释掉的) 并且使用 CHANGE MASTER TO (查看上面) 来代替 # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted # 所需要的唯一id号位于 2 和 2^32 – 1之间 # (并且和master不同) # 如果master-host被设置了.则默认值是2 # 但是如果省略,则不会生效 #server-id = 2 # # The replication master for this slave – required # 复制结构中的master – 必须 #master-host = <hostname> # # The username the slave will use for authentication when connecting # to the master – required # 当连接到master上时slave所用来认证的用户名 – 必须 #master-user = <username> # # The password the slave will authenticate with when connecting to # the master – required # 当连接到master上时slave所用来认证的密码 – 必须 #master-password = <password> # # The port the master is listening on. # optional - defaults to 3306 # master监听的端口. # 可选 – 默认是3306 #master-port = <port> # Make the slave read-only. Only users with the SUPER privilege and the # replication slave thread will be able to modify data on it. You can # use this to ensure that no applications will accidently modify data on # the slave instead of the master # 使得slave只读.只有用户拥有SUPER权限和在上面的slave线程能够修改数据. # 你可以使用此项去保证没有应用程序会意外的修改slave而不是master上的数据 #read_only #*** MyISAM Specific options #*** MyISAM 相关选项 # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. Even if you're not using # MyISAM tables, you should still set it to 8-64M as it will also be # used for internal temporary disk tables. # 关键词缓冲的大小, 一般用来缓冲MyISAM表的索引块. # 不要将其设置大于你可用内存的30%, # 因为一部分内存同样被OS用来缓冲行数据 # 甚至在你并不使用MyISAM 表的情况下, 你也需要仍旧设置起 8-64M 内存由于它同样会被内部临时磁盘表使用. key_buffer_size = 32M # MyISAM uses special tree-like cache to make bulk inserts (that is, # INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA # INFILE) faster. This variable limits the size of the cache tree in # bytes per thread. Setting it to 0 will disable this optimisation. Do # not set it larger than "key_buffer_size" for optimal performance. # This buffer is allocated when a bulk insert is detected. # MyISAM 使用特殊的类似树的cache来使得突发插入 # (这些插入是,INSERT … SELECT, INSERT … VALUES (…), (…), …, 以及 LOAD DATA # INFILE) 更快. 此变量限制每个进程中缓冲树的字节数. # 设置为 0 会关闭此优化. # 为了最优化不要将此值设置大于 “key_buffer_size”. # 当突发插入被检测到时此缓冲将被分配. bulk_insert_buffer_size = 64M # This buffer is allocated when MySQL needs to rebuild the index in # REPAIR, OPTIMIZE, ALTER table statements as well as in LOAD DATA INFILE # into an empty table. It is allocated per thread so be careful with # large settings. # 此缓冲当MySQL需要在 REPAIR, OPTIMIZE, ALTER 以及 LOAD DATA INFILE 到一个空表中引起重建索引时被分配. # 这在每个线程中被分配.所以在设置大值时需要小心. myisam_sort_buffer_size = 128M # The maximum size of the temporary file MySQL is allowed to use while # recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE. # If the file-size would be bigger than this, the index will be created # through the key cache (which is slower). # MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE). # 如果文件大小比此值更大,索引会通过键值缓冲创建(更慢) myisam_max_sort_file_size = 10G # If a table has more than one index, MyISAM can use more than one # thread to repair them by sorting in parallel. This makes sense if you # have multiple CPUs and plenty of memory. # 如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们. # 这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择. myisam_repair_threads = 1 # Automatically check and repair not properly closed MyISAM tables. # 自动检查和修复没有适当关闭的 MyISAM 表. myisam_recover # *** INNODB Specific options *** # *** INNODB 相关选项 *** # Use this option if you have a MySQL server with InnoDB support enabled # but you do not plan to use it. This will save memory and disk space # and speed up some things. # 如果你的MySQL服务包含InnoDB支持但是并不打算使用的话, # 使用此选项会节省内存以及磁盘空间,并且加速某些部分 #skip-innodb # Additional memory pool that is used by InnoDB to store metadata # information. If InnoDB requires more memory for this purpose it will # start to allocate it from the OS. As this is fast enough on most # recent operating systems, you normally do not need to change this # value. SHOW INNODB STATUS will display the current amount used. # 附加的内存池被InnoDB用来保存 metadata 信息 # 如果InnoDB为此目的需要更多的内存,它会开始从OS这里申请内存. # 由于这个操作在大多数现代操作系统上已经足够快, 你一般不需要修改此值. # SHOW INNODB STATUS 命令会显示当先使用的数量. innodb_additional_mem_pool_size = 16M # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. # InnoDB使用一个缓冲池来保存索引和原始数据, 不像 MyISAM. # 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少. # 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80% # 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸. # 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制, # 所以不要设置的太高. innodb_buffer_pool_size = 2G # InnoDB stores data in one or more data files forming the tablespace. # If you have a single logical drive for your data, a single # autoextending file would be good enough. In other cases, a single file # per device is often a good choice. You can configure InnoDB to use raw # disk partitions as well - please refer to the manual for more info # about this. # InnoDB 将数据保存在一个或者多个数据文件中成为表空间. # 如果你只有单个逻辑驱动保存你的数据,一个单个的自增文件就足够好了. # 其他情况下.每个设备一个文件一般都是个好的选择. # 你也可以配置InnoDB来使用裸盘分区 – 请参考手册来获取更多相关内容 innodb_data_file_path = ibdata1:10M:autoextend # Set this option if you would like the InnoDB tablespace files to be # stored in another location. By default this is the MySQL datadir. # 设置此选项如果你希望InnoDB表空间文件被保存在其他分区. # 默认保存在MySQL的datadir中. #innodb_data_home_dir = <directory> # Number of IO threads to use for async IO operations. This value is # hardcoded to 8 on Unix, but on Windows disk I/O may benefit from a # larger number. # 用来同步IO操作的IO线程的数量. This value is # 此值在Unix下被硬编码为8,但是在Windows磁盘I/O可能在一个大数值下表现的更好. innodb_write_io_threads = 8 innodb_read_io_threads = 8 # If you run into InnoDB tablespace corruption, setting this to a nonzero # value will likely help you to dump your tables. Start from value 1 and # increase it until you're able to dump the table successfully. # 如果你发现InnoDB表空间损坏, 设置此值为一个非零值可能帮助你导出你的表. # 从1开始并且增加此值知道你能够成功的导出表. #innodb_force_recovery=1 # Number of threads allowed inside the InnoDB kernel. The optimal value # depends highly on the application, hardware as well as the OS # scheduler properties. A too high value may lead to thread thrashing. # 在InnoDb核心内的允许线程数量. # 最优值依赖于应用程序,硬件以及操作系统的调度方式. # 过高的值可能导致线程的互斥颠簸. innodb_thread_concurrency = 16 # If set to 1, InnoDB will flush (fsync) the transaction logs to the # disk at each commit, which offers full ACID behavior. If you are # willing to compromise this safety, and you are running small # transactions, you may set this to 0 or 2 to reduce disk I/O to the # logs. Value 0 means that the log is only written to the log file and # the log file flushed to disk approximately once per second. Value 2 # means the log is written to the log file at each commit, but the log # file is only flushed to disk approximately once per second. # 如果设置为1 ,InnoDB会在每次提交后刷新(fsync)事务日志到磁盘上, # 这提供了完整的ACID行为. # 如果你愿意对事务安全折衷, 并且你正在运行一个小的食物, 你可以设置此值到0或者2来减少由事务日志引起的磁盘I/O # 0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘. # 2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上. innodb_flush_log_at_trx_commit = 1 # Speed up InnoDB shutdown. This will disable InnoDB to do a full purge # and insert buffer merge on shutdown. It may increase shutdown time a # lot, but InnoDB will have to do it on the next startup instead. # 加速InnoDB的关闭. 这会阻止InnoDB在关闭时做全清除以及插入缓冲合并. # 这可能极大增加关机时间, 但是取而代之的是InnoDB可能在下次启动时做这些操作. #innodb_fast_shutdown # The size of the buffer InnoDB uses for buffering log data. As soon as # it is full, InnoDB will have to flush it to disk. As it is flushed # once per second anyway, it does not make sense to have it very large # (even with long transactions). # 用来缓冲日志数据的缓冲区的大小. # 当此值快满时, InnoDB将必须刷新数据到磁盘上. # 由于基本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言) innodb_log_buffer_size = 8M # Size of each log file in a log group. You should set the combined size # of log files to about 25%-100% of your buffer pool size to avoid # unneeded buffer pool flush activity on log file overwrite. However, # note that a larger logfile size will increase the time needed for the # recovery process. # 在日志组中每个日志文件的大小. # 你应该设置日志文件总合大小到你缓冲池大小的25%~100% # 来避免在日志文件覆写上不必要的缓冲池刷新行为. # 不论如何, 请注意一个大的日志文件大小会增加恢复进程所需要的时间. innodb_log_file_size = 256M # Total number of files in the log group. A value of 2-3 is usually good # enough. # 在日志组中的文件总数. # 通常来说2~3是比较好的. innodb_log_files_in_group = 3 # Location of the InnoDB log files. Default is the MySQL datadir. You # may wish to point it to a dedicated hard drive or a RAID1 volume for # improved performance # InnoDB的日志文件所在位置. 默认是MySQL的datadir. # 你可以将其指定到一个独立的硬盘上或者一个RAID1卷上来提高其性能 #innodb_log_group_home_dir # Maximum allowed percentage of dirty pages in the InnoDB buffer pool. # If it is reached, InnoDB will start flushing them out agressively to # not run out of clean pages at all. This is a soft limit, not # guaranteed to be held. # 在InnoDB缓冲池中最大允许的脏页面的比例. # 如果达到限额, InnoDB会开始刷新他们防止他们妨碍到干净数据页面. # 这是一个软限制,不被保证绝对执行. innodb_max_dirty_pages_pct = 90 # The flush method InnoDB will use for Log. The tablespace always uses # doublewrite flush logic. The default value is "fdatasync", another # option is "O_DSYNC". # InnoDB用来刷新日志的方法. # 表空间总是使用双重写入刷新方法 # 默认值是 “fdatasync”, 另一个是 “O_DSYNC”. #innodb_flush_method=O_DSYNC # How long an InnoDB transaction should wait for a lock to be granted # before being rolled back. InnoDB automatically detects transaction # deadlocks in its own lock table and rolls back the transaction. If you # use the LOCK TABLES command, or other transaction-safe storage engines # than InnoDB in the same transaction, then a deadlock may arise which # InnoDB cannot notice. In cases like this the timeout is useful to # resolve the situation. # 在被回滚前,一个InnoDB的事务应该等待一个锁被批准多久. # InnoDB在其拥有的锁表中自动检测事务死锁并且回滚事务. # 如果你使用 LOCK TABLES 指令, 或者在同样事务中使用除了InnoDB以外的其他事务安全的存储引擎 # 那么一个死锁可能发生而InnoDB无法注意到. # 这种情况下这个timeout值对于解决这种问题就非常有帮助. innodb_lock_wait_timeout = 120 [mysqldump] # Do not buffer the whole result set in memory before writing it to # file. Required for dumping very large tables # 不要在将内存中的整个结果写入磁盘之前缓存. 在导出非常巨大的表时需要此项 quick max_allowed_packet = 16M [mysql] no-auto-rehash # Only allow UPDATEs and DELETEs that use keys. # 仅仅允许使用键值的 UPDATEs 和 DELETEs . #safe-updates [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] # Increase the amount of open files allowed per process. Warning: Make # sure you have set the global system limit high enough! The high value # is required for a large number of opened tables # 增加每个进程的可打开文件数量. # 警告: 确认你已经将全系统限制设定的足够高! # 打开大量表需要将此值设大 open-files-limit = 8192
参考其他博友文章:
配置文件优化(根据实际情况优化)
说明,上文中我对my-innodb-heavy-4G.cnf中默认的所有选项进行了说明,下面我就根据我们公司的实际情况进行优化!
1.服务器的运行环境
硬件服务器:Dell R710,双至强E5620 CPU、16G内存、6*500G硬盘
操作系统:CentOS5.5 X86_64 系统
Mysql版本:MySQL 5.5.32
适用于:日IP 100-200W ,日PV 200-500W 的站点
2.具体优化配置如下
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8 #设置客户端的字符编码
[mysqld]
# generic configuration options
port = 3306
socket = /tmp/mysql.sock
#*** char set ***
character-set-server = utf8 #设置服务器端的字符编码
#*** network ***
back_log = 512
#skip-networking #默认没有开启
max_connections = 3000
max_connect_errors = 30
table_open_cache = 4096
#external-locking #默认没有开启
max_allowed_packet = 32M
max_heap_table_size = 128M
# *** global cache ***
read_buffer_size = 8M
read_rnd_buffer_size = 64M
sort_buffer_size = 16M
join_buffer_size = 16M
# *** thread ***
thread_cache_size = 16
thread_concurrency = 8
thread_stack = 512K
# *** query cache ***
query_cache_size = 128M
query_cache_limit = 4M
# *** index ***
ft_min_word_len = 8
#memlock #默认没有开启
default-storage-engine = INNODB
transaction_isolation = REPEATABLE-READ
# *** tmp table ***
tmp_table_size = 64M
# *** bin log ***
log-bin=mysql-bin
binlog_cache_size = 4M
binlog_format=mixed
#log_slave_updates #默认没有开启
#log #默认没有开启,此处是查询日志,开启会影响服务器性能
log_warnings #开启警告日志
# *** slow query log ***
slow_query_log
long_query_time = 10
# *** Replication related settings
server-id = 1
#server-id = 2
#master-host = <hostname>
#master-user = <username>
#master-password = <password>
#master-port = <port>
#read_only
#*** MyISAM Specific options
key_buffer_size = 128M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
# *** INNODB Specific options ***
#skip-innodb #默认没有开启
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 6G #注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制, 所以不要设置的太高.
innodb_data_file_path = ibdata1:10M:autoextend
#innodb_data_home_dir = <directory>
innodb_write_io_threads = 8
innodb_read_io_threads = 8
#innodb_force_recovery=1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
#说明:innodb_flush_log_at_trx_commit = 2 如果是游戏服务器,建议此值设置为2;如果是对数据安全要求极高的应用,建议设置为1;设置为0性能最高,但如果发生故障,数据可能会有丢失的危险!默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘,这是很费时的。特别是使用电池供电缓存(Battery backed up cache)时。设成2对于很多运用,特别是从MyISAM表转过来的是可以的,它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘,所以你一般不会丢失超过1-2秒的更新。设成0会更快一点,但安全方面比较差,即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。
#innodb_fast_shutdown
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 2048M
sort_buffer_size = 2048M
read_buffer = 32M
write_buffer = 32M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 10240
MySQL 配置文件的优化是根据线上环境的实际需要进行优化,不能随便没有根据的进行优化,写这篇博文就是给博友们一些参考!
4.MySQL状态查看的常用命令
mysql> show status; #显示状态信息
mysql> show variables; #显示系统变量
mysql> show engines; #查看所有引擎
mysql> show engine innodb status; #显示InnoDB存储引擎的状态
转载于:https://blog.51cto.com/skypegnu1/1441266