MySQL的my.cnf参数设定说明

原创 2012年03月30日 11:23:58
1.获取当前配置参数

要优化配置参数,首先要了解当前的配置参数以及运行情况。使用下列命令可以获得目前服务器使用的配置参数:

mysqld –verbose –help

mysqladmin variables extended-status –u root –p

在MySQL控制台里面,运行下列命令可以获取状态变量的值:

mysql> SHOW STATUS;

如果只要检查某几个状态变量,可以使用下列命令:

mysql> SHOW STATUS LIKE ‘[匹配模式]’; ( 可以使用%、?等 )

2.优化参数

参数优化基于一个前提,就是在我们的数据库中通常都使用InnoDB表,而不使用MyISAM表。在优化MySQL时,有两个配置参数是最重要的,即table_cachekey_buffer_size

table_cache

table_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tablesOpened_tables,可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。

对于有1G内存的机器,推荐值是128-256。

 

案例1:该案例来自一个不是特别繁忙的服务器

table_cache – 512

open_tables – 103

opened_tables – 1273

uptime – 4021421 (measured in seconds)

该案例中table_cache似乎设置得太高了。在峰值时间,打开表的数目比table_cache要少得多。

 

案例2:该案例来自一台开发服务器。

table_cache – 64

open_tables – 64

opened-tables – 431

uptime – 1662790 (measured in seconds)

虽然open_tables已经等于table_cache,但是相对于服务器运行时间来说,opened_tables的值也非常低。因此,增加table_cache的值应该用处不大。

 

案例3:该案例来自一个upderperforming的服务器

table_cache – 64

open_tables – 64

opened_tables – 22423

uptime – 19538

该案例中table_cache设置得太低了。虽然运行时间不到6小时,open_tables达到了最大值,opened_tables的值也非常高。这样就需要增加table_cache的值。

 

key_buffer_size

key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值Key_read_requestsKey_reads,可以知道key_buffer_size设置是否合理。比例key_reads /key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。

key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。

对于1G内存的机器,如果不使用MyISAM表,推荐值是16M(8-64M)。

 

案例1:健康状况

key_buffer_size – 402649088 (384M)

key_read_requests – 597579931

key_reads - 56188

案例2:警报状态

key_buffer_size – 16777216 (16M)

key_read_requests – 597579931

key_reads - 53832731

案例1中比例低于1:10000,是健康的情况;案例2中比例达到1:11,警报已经拉响。

 

优化query_cache_size

从4.0.1开始,MySQL提供了查询缓冲机制。使用查询缓冲,MySQL将SELECT语句和查询结果存放在缓冲区中,今后对于同样的SELECT语句(区分大小写),将直接从缓冲区中读取结果。根据MySQL用户手册,使用查询缓冲最多可以达到238%的效率。

通过检查状态值Qcache_*,可以知道query_cache_size设置是否合理(上述状态值可以使用SHOW STATUS LIKE ‘Qcache%’获得)。如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况,如果Qcache_hits的值也非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;如果Qcache_hits的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲。此外,在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。

与查询缓冲有关的参数还有query_cache_typequery_cache_limitquery_cache_min_res_unitquery_cache_type指定是否使用查询缓冲,可以设置为0、1、2,该变量是SESSION级的变量。query_cache_limit指定单个查询能够使用的缓冲区大小,缺省为1M。query_cache_min_res_unit是在4.1版本以后引入的,它指定分配缓冲区空间的最小单位,缺省为4K。检查状态值Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多,这就表明查询结果都比较小,此时需要减小query_cache_min_res_unit

 

开启二进制日志( Binary Log )

二进制日志包含所有更新数据的语句,其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。另外,如果做同步复制( Replication )的话,也需要使用二进制日志传送修改情况。

开启二进制日志,需要设置参数log-binlog_bin指定日志文件,如果不提供文件名,MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字索引,每次启动服务时,都会重新生成一个新的二进制文件。

此外,使用log-bin-index可以指定索引文件;使用binlog-do-db可以指定记录的数据库;使用binlog-ignore-db可以指定不记录的数据库。注意的是:binlog-do-dbbinlog-ignore-db一次只指定一个数据库,指定多个数据库需要多个语句。而且,MySQL会将所有的数据库名称改成小写,在指定数据库时必须全部使用小写名字,否则不会起作用。

在MySQL中使用SHOW MASTER STATUS命令可以查看目前的二进制日志状态。

 

开启慢查询日志( slow query log )

慢查询日志对于跟踪有问题的查询非常有用。它记录所有查过long_query_time的查询,如果需要,还可以记录不使用索引的记录。下面是一个慢查询日志的例子:

开启慢查询日志,需要设置参数log_slow_querieslong_query_timeslog-queries-not-using-indexeslog_slow_queries指定日志文件,如果不提供文件名,MySQL将自己产生缺省文件名。long_query_times指定慢查询的阈值,缺省是10秒。log-queries-not-using-indexes是4.1.0以后引入的参数,它指示记录不使用索引的查询。

 

配置InnoDB

相对于MyISAM表来说,正确配置参数对于InnoDB表更加关键。其中,最重要的参数是innodb_data_file_path。它指定表数据和索引存储的空间,可以是一个或者多个文件。最后一个数据文件必须是自动扩充的,也只有最后一个文件允许自动扩充。这样,当空间用完后,自动扩充数据文件就会自动增长(以8MB为单位)以容纳额外的数据。例如:

innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend

两个数据文件放在不同的磁盘上。数据首先放在ibdata1中,当达到900M以后,数据就放在ibdata2中。一旦达到50MB,ibdata2将以8MB为单位自动增长。

如果磁盘满了,你需要在另外的磁盘上面增加一个数据文件。为此,你需要查看最后一个文件的尺寸,然后计算最接近的整数(MB)。然后手工修改该文件的大小,并添加新的数据文件。例如:假设ibdata2已经有109MB数据,那么可以修改如下:

innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:109M;/disk3/ibdata3:500M:autoextend

 

flush_time

如果系统有问题并且经常锁死或重新引导,应将该变量设置为非零值,这将导致服务器按flush_time 秒来刷新表的高速缓存。用这种方法来写出对表的修改将降低性能,但可减少表讹误或数据丢失的机会。

一般使用缺省值。

 

Binlog_cache_size

The size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has binary log enabled(--log-bin option). If you often use big, multiple-statement transactions, you can increase this to get more performance. The Binlog_cache_use and Binlog_cache_disk_use status variables can be useful for tuning the size of this variable.

 

3.存储引擎

在MYSQL 3.23.0版本中,引入了MyISAM存储引擎。它是一个非事务型的存储引擎,成为了MYSQL的缺省存储引擎。但是,如果使用设置向导来设置参数,则它会把InnoDB作为缺省的存储引擎。InnoDB是一个事务型的存储引擎。

创建表的时候,可以为表指定存储引擎,语法如下:

CREATE TABLE t (i INT) ENGINE = MyISAM

CREATE TABLE t (i INT) TYPE = MyISAM

如果没有指定,则使用缺省的存储引擎。也可以使用ALTER TABLE来更换表引擎,语法如下:

ALTER TABLE t ENGINE = MyISAM

同一数据库中可以包含不同存储引擎的表。

 

事务型表具有以下特点:

?        Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.

?        You can combine many statements and accept them all at the same time with the COMMITstatement (if autocommit is disabled).

?        You can execute ROLLBACK to ignore your changes (if autocommit is disabled).

?        If an update fails, all your changes will be restored. (With non-transaction-safe tables, all changes that have taken place are permanent.)

?        Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads.

 

非事务型表具有以下优点:

?        Much faster

?        Lower disk space requirements

?        Less memory required to perform updates

4MyISAM存储引擎

下面MyISAM的参数是MySQL手册推荐的参数,据说适应于大部分情况。对于如何监视参数设置是否合理,仍然没有头绪。

max_connections=200

read_buffer_size=1M

read_rnd_buffer_size=8M

sort_buffer_size=1M

Read_buffer_size

Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you might want to increse this value.

Read_rnd_buffer_size

When reading rows in sorted order after a sort, the rows are read through this buffer to avoid disk seeks. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.

Bulk_insert_buffer_size

该参数于4.0.3中引入。MyISAM使用一个树型的缓冲区来加速大量的插入,如INSERT…SELECT,INSERT…VALUES(…),VALUES(…),…,LOAD DATA INFILE等。该参数指定了缓冲区的大小。缺省值为8M,设置为0则表示不使用该优化。

如果不使用MyISAM表,则可以将其设置为0。

5InnoDB存储引擎

参考了很多资料,都没有明确地表明如何优化InnoDB参数,以及如何监视这些参数设置是否合理,只有根据MySQL用户手册上面的介绍来进行设置。

innodb_buffer_pool_size

对于InnoDB表来说,innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器,最大可以把该值设置成物理内存的80%。

根据MySQL手册,对于2G内存的机器,推荐值是1G(50%)。

innodb_flush_log_at_trx_commit

该值指定InnoDB记录日志的方式。如果设置为1,则每个事务提交的时候,MySQL都会将事务日志写入磁盘。如果设置为0或者2,则大概每秒中将日志写入磁盘一次。(还不清楚0和2的区别)

实际测试发现,该值对插入数据的速度影响非常大,设置为2时插入10000条记录只需要2秒,设置为0时只需要1秒,而设置为1时则需要229秒。因此,MySQL手册也建议尽量将插入操作合并成一个事务,这样可以大幅提高速度。

根据MySQL手册,在存在丢失最近部分事务的危险的前提下,可以把该值设为0。

innodb_log_file_size

The size of each log file in a log group. The default is 5MB. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But large log files also mean that recovery will be slower in case of a crash.

根据MySQL手册,推荐值是innodb_buffer_pool_size的25%。

注意:在重新设置该值时,好像要把原来的文件删除掉。

innodb_log_buffer_size

The size of the buffer that InnoDB uses to write to the log files on disk. Sensible values range from 1MB to 8MB. The default is 1MB. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger will save disk I/O.

根据MySQL手册,推荐值是8M

innodb_additional_mem_pool_size

该参数指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小。缺省值是1M。通常不用太大,只要够用就行,应该与表结构的复杂度有关系。如果不够用,MySQL会在错误日志中写入一条警告信息。

根据MySQL手册,对于2G内存的机器,推荐值是20M。

 

SHOW INNODB STATUS

显示InnoDB存储引擎的状态

====================================================

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的值,用基于内存的临时表代替基于磁盘的  

Aborted_clients 由於客戶沒有正確關閉連接已經死掉,已經放棄的連接數量。
Aborted_connects 嘗試已經失敗的MySQL伺服器的連接的次數。
Connections 試圖連接MySQL伺服器的次數。
Created_tmp_tables 當執行語句時,已經被創造了的隱含臨時表的數量。
Delayed_insert_threads 正在使用的延遲插入處理器執行緒的數量。
Delayed_writes INSERT DELAYED寫入的行數。
Delayed_errors INSERT DELAYED寫入的發生某些錯誤(可能重複鍵值)的行數。
Flush_commands 執行FLUSH命令的次數。
Handler_delete 請求從一張表中刪除行的次數。
Handler_read_first 請求讀入表中第一行的次數。
Handler_read_key 請求數字基於鍵讀行。
Handler_read_next 請求讀入基於一個鍵的一行的次數。
Handler_read_rnd 請求讀入基於一個固定位置的一行的次數。
Handler_update 請求更新表中一行的次數。
Handler_write 請求向表中插入一行的次數。
Key_blocks_used 用於關鍵字緩存的塊的數量。
Key_read_requests 請求從緩存讀入一個鍵值的次數。
Key_reads 從磁碟物理讀入一個鍵值的次數。
Key_write_requests 請求將一個關鍵字塊寫入緩存次數。
Key_writes 將一個鍵值塊物理寫入磁碟的次數。
Max_used_connections 同時使用的連接的最大數目。
Not_flushed_key_blocks 在鍵緩存中已經改變但是還沒被清空到磁碟上的鍵塊。
Not_flushed_delayed_rows INSERT DELAY隊列中等待寫入的行的數量。
Open_tables 打開表的數量。
Open_files 打開文件的數量。
Open_streams 打開流的數量(主要用於日誌記載)
Opened_tables 已經打開的表的數量。
Questions 發往伺服器的查詢的數量。
Slow_queries 要花超過long_query_time時間的查詢數量。
Threads_connected 當前打開的連接的數量。
Threads_running 不在睡眠的執行緒數量。
Uptime 伺服器工作了多少秒。

關於上面的一些注釋:

如果Opened_tables太大,那麼你的table_cache變數可能太小。如果key_reads太大,那麼你的key_cache可能太小。緩存命中率可以用key_reads/key_read_requests計算。如果Handler_read_rnd太大,那麼你很可能有大量的查詢需要MySQL掃描整個表或你有沒正確使用鍵值的聯結(join)。
版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

MySQL 5.6 my.cnf 参数说明

# 以下选项会被MySQL客户端应用读取。 # 注意只有MySQL附带的客户端应用程序保证可以读取这段内容。 # 如果你想你自己的MySQL应用程序获取这些值。 # 需要在MySQL客户端库初始...
  • jjwen
  • jjwen
  • 2016-05-26 15:01
  • 102

MySQL配置文件my.cnf针对innodb 4g RAM以上 配置参数详细说明

MySQL配置文件my.cnf 例子最详细翻译,可以保存做笔记用。 #BEGIN CONFIG INFO #DESCR: 4GB RAM, 只使用InnoDB, ACID, 少量的连接, 队列负载...

MySQL 5.6 my.cnf 参数说明

# 以下选项会被MySQL客户端应用读取。 # 注意只有MySQL附带的客户端应用程序保证可以读取这段内容。 # 如果你想你自己的MySQL应用程序获取这些值。 # 需要在MySQL客户端库初始化的时...

MySQL服务器my.cnf参数说明

#cat my.cnf # MySQL client library initialization. [client] port            = 3306 socket       ...
  • celte
  • celte
  • 2014-07-31 20:48
  • 577

mysql配置模板(my-*.cnf)参数详细说明

mysql安装成功后有几个默认的配置模板,列表如下: my-huge.cnf : 用于高端产品服务器,包括1到2GB RAM,主要运行mysql my-innodb-heavy-4G.ini : ...

MySQL 5.6 my.cnf 参数说明

# 以下选项会被MySQL客户端应用读取。 # 注意只有MySQL附带的客户端应用程序保证可以读取这段内容。 # 如果你想你自己的MySQL应用程序获取这些值。 # 需要在MySQL客户端库初始化的时...

MySQL之——MySQL5.6 my.cnf 参数说明(附上自己服务器上真实配置文件)

# 以下选项会被MySQL客户端应用读取。 # 注意只有MySQL附带的客户端应用程序保证可以读取这段内容。 # 如果你想你自己的MySQL应用程序获取这些值。 # 需要在MySQL客户端库初始化的时...

mysql配置文件my.cnf部分参数详解

mysql配置文件my.cnf部分参数详解

MySQL my.cnf参数配置优化详解

# 以下选项会被MySQL客户端应用读取。 # 注意只有MySQL附带的客户端应用程序保证可以读取这段内容。 # 如果你想你自己的MySQL应用程序获取这些值。 # 需要在MySQL客户端库初始...
  • svod
  • svod
  • 2011-12-14 17:20
  • 211

MySQL my.cnf参数配置优化详解

转载自互联网: PS:本配置文件针对Dell R710,双至强E5620、16G内存的硬件配置。CentOS 5.6 64位系统,MySQL 5.5.x 稳定版。适用于日IP 50-100w,PV ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)