`
jaychang
  • 浏览: 734654 次
  • 性别: Icon_minigender_1
  • 来自: 嘉兴
社区版块
存档分类
最新评论

Mysql参数配置优化说明

 
阅读更多

转自:http://kangyang.blog.51cto.com/471772/580878

1.获取当前配置参数

要优化配置参数,首先要了解当前的配置参数以及运行情况。使用下列命令可以获得目前服务器使用的配置参数:
mysqld –verbose –help
mysqladmin variables extended-status –u root –p

 

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

 

如果只要检查某几个状态变量,可以使用下列命令:
mysql> SHOW STATUS LIKE ‘[匹配模式]’; ( 可以使用%、?等 )

2.优化参数

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

 

table_cache
table_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内 容。通过检查峰值时间的状态值Open_tables和Opened_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:该案例来自一个性能不佳的服务器
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_requests和 Key_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_type、query_cache_limit、query_cache_min_res_unit。 query_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-bin。log_bin指定日志文件,如果不提供文件名,MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字索引,每次启动服务时,都会重新生成一个新的二进制文件。
此外,使用log-bin-index可以指定索引文件;使用binlog-do-db可以指定记录的数据库;使用binlog-ignore-db可以 指定不记录的数据库。注意的是:binlog-do-db和binlog-ignore-db一次只指定一个数据库,指定多个数据库需要多个语句。而 且,MySQL会将所有的数据库名称改成小写,在指定数据库时必须全部使用小写名字,否则不会起作用。
在MySQL中使用SHOW MASTER STATUS命令可以查看目前的二进制日志状态。

 

开启慢查询日志( slow query log )
慢查询日志对于跟踪有问题的查询非常有用。它记录所有查过long_query_time的查询,如果需要,还可以记录不使用索引的记录。下面是一个慢查询日志的例子:
开启慢查询日志,需要设置参数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以后引入的参数,它指示记录不使用索引的查 询。

 

配置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 COMMIT statement (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

4.MyISAM存储引擎

下面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。

5.InnoDB存储引擎

参考了很多资料,都没有明确地表明如何优化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。

分享到:
评论

相关推荐

    【荐】mysql服务性能优化—my.cnf_my.ini配置说明详解(16G内存)

    通过对my.cnf/my.ini配置文件的各项关键参数进行中文注释与解析,帮助DBA及运维人员更好地理解每个配置项的作用及其对MySQL性能的影响。 #### 关键配置项详解 ##### 1. **[client]** - **port=3306**:设定MySQL...

    mysql服务性能优化my_cnf配置说明详解16G内存[参考].pdf

    MySQL 服务性能优化 My.cnf 配置说明详解(16G 内存) MySQL 是当前使用最广泛的开源数据库管理系统之一,高性能的 MySQL 服务器配置是每个开发者和 DBA 都需要关心的问题。下面是 MySQL 服务性能优化的 My.cnf ...

    mysql服务性能优化—my.cnf_my.ini配置说明详解(16G内存)

    MySQL服务性能优化主要依赖于配置文件my.cnf或my.ini的有效设定,特别是在内存充足的环境下,如16GB内存,合理的配置能显著提升数据库的处理能力。以下是对my.cnf配置文件中关键参数的详细解释: 1. `[client]`部分...

    MYSQL配置参数优化详解

    总的来说,MySQL参数优化是一个持续的过程,需要根据系统负载、硬件配置和应用特性进行综合考虑和调整。定期监控系统状态,如`SHOW STATUS`和`SHOW VARIABLES`,以及分析慢查询日志,可以帮助找到优化的方向。

    mysql性能优化.pptx

    MySQL性能优化是一个涵盖广泛的主题,涉及多个层面,包括SQL语句优化、索引优化、数据库表结构优化、系统级配置优化以及服务器硬件优化。以下是对这些方面进行详细说明: 1. **SQL语句优化** - **慢查询日志**:...

    MySQL参数说明

    MySQL 的参数配置在 my.cnf 文件中,参数和 SHOW VARIABLES 显示的变量一样名称,但是有些却不是,有些是在启动参数中设置,有些是必须用 SET 语句更改。而且多个版本中有些参数名称还不一样。 MySQL 的配置参数...

    mysql5.6性能优化

    优化的本质在于合理安排资源和调整系统参数,使得MySQL能够更加高效地运行,并且尽可能地节省系统资源。通过减少系统瓶颈、降低资源消耗以及提高系统响应速度来达到优化的目的。具体而言,优化可以从多个层面入手,...

    mysql5.5版本和配置文件的详细说明

    在实际操作中,理解配置文件并正确设置这些参数是优化MySQL服务器的关键。对于初学者,建议先从默认配置开始,随着对系统性能需求的了解,逐步调整和优化各个参数。同时,定期检查错误日志和监控性能指标也是确保...

    mysql参数配置

    ### MySQL参数配置详解 在MySQL数据库管理中,合理的参数配置对于提升系统性能、保障数据安全性具有重要作用。本文将根据提供的MySQL配置示例,详细介绍其中的关键参数及其作用。 #### 一、基本设置(Basic ...

    Mysqlforwindows之my.ini优化.pdf

    MySQL for Windows 的优化主要涉及到配置文件 `my.ini` 的调整,这是MySQL服务器的关键配置文档,用于定义各种性能参数。在Windows环境下,优化 `my.ini` 可以显著提升数据库的性能,减少延迟,并确保系统资源的有效...

    (编译版)linux系统mysql安装配置优化手册V2.1.docx

    【Linux系统MySQL安装配置优化手册V2.1】主要涵盖了在Linux环境下安装、配置和优化MySQL数据库的详细步骤。手册适用于不同版本的MySQL,如32位的5.1.48版本。以下是手册中的关键知识点: 1. **安装准备**: - 在...

    Mysql JDBC Url参数说明

    了解这些参数可以帮助开发者更好地定制与MySQL数据库的连接,优化性能,解决兼容性和安全问题。实际应用中,开发者应根据项目需求选择合适的参数组合。在阅读提供的`Mysql JDBC URL连接参数表.doc`文档后,可以获取...

    linux mysql5.7.13 配置以及配置文件

    以下是对 MySQL 5.7.13 在 Linux 上配置及配置文件的详细说明。 **1. 安装 MySQL 5.7.13** 在 Linux 系统中,通常通过包管理器(如 apt-get for Ubuntu/Debian 或 yum for CentOS/RHEL)来安装 MySQL。首先,确保...

    linux centos mysql配置文件说明.docx

    ### Linux CentOS MySQL 配置文件详解 ...综上所述,了解并合理配置这些参数对于优化MySQL在Linux CentOS环境下的性能具有重要意义。此外,还可以参考官方文档或其他资源获取更多关于MySQL配置的最佳实践。

    mysql8配置详细说明

    MySQL 8.0是当前广泛使用的数据库管理系统,其配置文件(通常为my.cnf或my.ini)对于优化数据库性能、确保稳定性和安全性至关重要。本文将详细介绍MySQL 8.0的配置选项,涵盖缓存大小、大小写敏感性、端口设置、缓冲...

    Mysql配置文件说明

    MySQL配置文件是数据库服务的核心配置文档,用于定义MySQL服务器的各种运行参数和设置,以优化数据库的性能、安全性和稳定性。本文将深入解析配置文件中的各项关键设置及其作用。 首先,`[mysqld]`部分是配置文件的...

    mysql_配置详细说明.docx

    MySQL配置详解涵盖了许多关键参数,这些参数直接影响到MySQL服务器的性能和稳定性。下面将详细解释这些参数的作用和调整策略。 1. **back_log**:这个参数定义了在MySQL暂时停止处理新的连接请求之前,可以堆积的...

    MySQL-5.7.20配置

    "MySQL配置.txt"文件可能包含了更多关于这些高级配置的详细说明,建议仔细阅读以深入了解MySQL的配置细节。 总之,配置MySQL-5.7.20涉及多个方面,从环境准备到具体服务的设定,都需要细心操作。正确配置MySQL不仅...

    MySql性能优化集合--满满的干货

    3. **MySQL服务器优化**:通过调整MySQL配置参数来提升服务器整体性能。 #### 二、MySQL性能参数 在进行MySQL性能优化之前,首先需要了解一些重要的性能指标,这些可以通过`SHOW STATUS`命令来查看: - **Slow_...

    MySQL5.7免安装版(附配置说明)

    同时,MySQL的灵活性也意味着您可以根据需要进一步配置和调整参数,以满足特定场景的需求。 总的来说,MySQL5.7免安装版是一个方便快捷的数据库解决方案,尤其适合开发者和小型项目使用。只要按照提供的说明文档...

Global site tag (gtag.js) - Google Analytics