4887王中王鉄算盘奖结果MySQL常见配置参数调优,

来源:http://www.smjxgs.com 作者:王中王高手论坛 人气:131 发布时间:2019-08-29
摘要:从这篇开始,讲innodb存储引擎中,对于几个重要的服务器参数配置。这些参数以innodb_xx开头。 mysql的各种参数有300余种,可以将其分为两类:一是缓存参数,二是个性化参数。对缓存参

从这篇开始,讲innodb存储引擎中,对于几个重要的服务器参数配置。这些参数以innodb_xx 开头。

mysql的各种参数有300余种,可以将其分为两类:一是缓存参数,二是个性化参数。对缓存参数的配置在一定程度内对mysql性能的影响是显著的。同时各种个性化参数的设置,可以使mysql表现出不同的性状。

mysql 优化调试命令  
1、mysqld --verbose --help
这个命令生成所有mysqld选项和可配置变量的列表
2、通过连接它并执行这个命令,可以看到实际上使用的变量的值:
mysql> SHOW VARIABLES;
还可以通过下面的语句看到运行服务器的统计和状态指标:
mysql>SHOW STATUS;
使用mysqladmin还可以获得系统变量和状态信息:
shell> mysqladmin variables
shell> mysqladmin extended-status
shell> mysqladmin flush-table 命令可以立即关闭所有不使用的表并将所有使用中的表标记为已经关闭,这样可以有效释放大多数使用中的内存。FLUSH TABLE在关闭所有表之前不返回结果。
 
swap -s检查可用交换区
 
mysql内存计算公式
 
mysql used mem = key_buffer_size query_cache_size tmp_table_size

  1. innodb_buffer_pool_size的设置

1.缓存参数

目前常用的存储引擎有两种,一是myisam,另一种是innodb。关于这两种存储引擎的异同这里就不做过多的介绍。使用存储引擎的不同,对参数的优化也会不一样。但有一些缓存参数是跨存储引擎的,就是无论使用何种存储引擎,它都会发挥其作用。下面将按三类对其进行详细的介绍。

  • innodb_buffer_pool_size innodb_additional_mem_pool_size
  • innodb_log_buffer_size
  • max_connections * (
    read_buffer_size read_rnd_buffer_size
  • sort_buffer_size join_buffer_size
  • binlog_cache_size thread_stack
    )
     
    在mysql 中输入如下命令,可自动计算自己的当前配置最大的内存消耗
     
    SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
    SHOW VARIABLES LIKE 'innodb_additional_mem_pool_size';
    SHOW VARIABLES LIKE 'innodb_log_buffer_size';
    SHOW VARIABLES LIKE 'thread_stack';
    SET @kilo_bytes = 1024;
    SET @mega_bytes = @kilo_bytes * 1024;
    SET @giga_bytes = @mega_bytes * 1024;
    SET @innodb_buffer_pool_size = 2 * @giga_bytes;
    SET @innodb_additional_mem_pool_size = 16 * @mega_bytes;
    SET @innodb_log_buffer_size = 8 * @mega_bytes;
    SET @thread_stack = 192 * @kilo_bytes;
    SELECT
    ( @@key_buffer_size @@query_cache_size @@tmp_table_size
  • @innodb_buffer_pool_size @innodb_additional_mem_pool_size
  • @innodb_log_buffer_size
  • @@max_connections * (
    @@read_buffer_size @@read_rnd_buffer_size @@sort_buffer_size
  • @@join_buffer_size @@binlog_cache_size @thread_stack
    ) ) / @giga_bytes AS MAX_MEMORY_GB;
     
     
    mysql关键参数设置
    Mysqld 数据库的参数设置有两种类型,
     
    一种是全局参数,影响服务器的全局操作;
    另一种是会话级参数,只影响当前的客户端连接的相关操作。
     
    服务器启动时,所有全局参数都初始化为默认值。可以在初始化文件或命令行中指定的选项来更改这些默认值。服务器启动后,通过连接服务器并执行 SET GLOBAL var_name 语句可以更改动态全局参数。要想更改全局参数,必须具有 SUPER 权限。全局参数的修改只对新的连接生效,已有的客户端连接并不会生效。
     
    服务器还可以为每个客户端连接维护会话级参数,客户端连接时使用相应全局参数的当前值对客户端会话参数进行初始化。客户可以通过 SET SESSION var_name 语句来更改动态会话参数。设置会话级参数不需要特殊权限,但每个客户端可以只更改自己的会话级参数,不能更改其它客户的会话级参数。
    不指定设置的参数类型时,默认设置的是会话级参数。  

         这个参数定义了innodb存储引擎的表数据和索引数据的最大内存缓冲区大小,和myisam不同,myisam的key_buffer_size只缓存索引键,而innodb_buffer_pool_size是同时为数据块和索引块做缓存的。这个特性与oracle是一样的,这个值设得越高,访问表中数据需要的磁盘i/o就越少(物理I/O)。在一个专用的数据库服务器上,可以设置这个参数达机器物理内存大小的50--80%。考虑点:在单独给 MySQL 使用的主机里,内存分配还包括系统使用,线程独享,myisam缓存等。还有允许的并发连接数。还有建议不要把它设置得太大,因为对物理内存的竞争可能在操作系统上导致内存调度。

1.1 跨引擎缓存参数优化

这类缓存参数是针对查询的优化,优化方向是sql、表、日志、线程对象的缓存优化。具体说明如下:

(1)、max_connections:
允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加,否则,你将经常看到 too many connections 错误。 默认数值是100,我把它改为1024 。

-- innodb缓存区大小(kb)
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

1.1.1 query cache

query cache将已经执行过的sql和结果集放在缓存区中,如果再有同样的select语句(区分大小写),将直接从缓存区中读取,这样能大大提高query语句的查询效率,但是,如果要query的表经常被更新,则会导致在cache中的sql失效,这时使用query cache不但不能提高效率反而会使数据库的性能变得更差。所以使用query cache时需要对这点留意。

使用query cache时,需要先将query_cache_type设置为ON(打开查询缓存)。同时需要对要缓存的结果集的大小进行限制。query_cache_limit=1M(最大结果集为1M),query_cache_min_res_unit=1K(结果集最小为1K),不在这个范围内的结果集将不会被缓存。query cahce的大小以字节为单位,须为1024的整数倍,建议为系统内存的1/8,不超过256M。通过查看query的global status来查看设置是否合理:

Qcache_free_blocks:目前还处于空闲状态的 Query Cache 中内存 Block 数目

Qcache_free_memory:目前还处于空闲状态的 Query Cache 内存总量

Qcache_hits:Query Cache 命中次数

Qcache_inserts:向 Query Cache 中插入新的 Query Cache 的次数,也就是没有命中的次数

Qcache_lowmem_prunes:当 Query Cache 内存容量不够,需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数

Qcache_not_cached:没有被 Cache 的 SQL 数,包括无法被 Cache 的 SQL 以及由于

Qcache_queries_in_cache:目前在 Query Cache 中的 SQL 数量

Qcache_total_blocks:Query Cache 中总的 Block 数量

命中率: [Qcache_hits /( Qcache_hits  Qcache_inserts)]*100%

如果命中率高,且Qcache_free_memory大则说明query_cache_size设置过大

如果命中率低,且Qcache_lowmem_prunes大则说明query_cache_size设置过小

 

(2)、record_buffer:
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,你可能想要增加该值。默认数值是131072(128k),我把它改为16773120 (16m)

4887王中王鉄算盘奖结果 1  SELECT 268435456/1024.0/2014.0=130M。

1.1.2 table cache

为了解决打开表描述文件符太过频繁的问题,mysql在系统中实现了一个table cache机制,用来cache打开的所有表文件的描述符。通过这样的方式来减少因为频繁打开关闭文件描述符所带来的资源消耗。

table cache的设置与mysql设置的最大连接数成正比,其比例值等于一个connection打开多少表,计算方式如下:

table_cache=max_connection*N

使用flush table来关闭所有文件描述符,通过查看table open状态来查看参数设置是否合理,合理的设置应该如下:

open_tables/opened_tables>=0.85

open_tables/table_cache<=0.95

(3)、key_buffer_size:
为了最小化磁盘的 I/O , MyISAM 存储引擎的表使用键高速缓存来缓存索引,这个键高速缓存的大小则通过 key-buffer-size 参数来设置。如果应用系统中使用的表以 MyISAM 存储引擎为主,则应该适当增加该参数的值,以便尽可能的缓存索引,提高访问的速度。

查看内存大小:
[root@xuegod64 ~]# cat /proc/meminfo

1.1.3 join buffer(线程独有)

当join查询的类型是all,index,range或者index_merge的时候就会使用到join buffer。实际上参与join的每个表都会用到join buffer,所以一条sql会用到至少两个join buffer。join buffer在5.1.23的版本前的最大值是4G,但之后,除了windows外,其它64位的平台可以超出4G的限制。系统默认为128k,但是如果join语句多,建议为1M,内存充足则可以增加到2M。

索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),到你能负担得起那样多。如果你使它太大,系统将开始换页并且真的变慢了。默认数值是8388600(8m),我的mysql主机有2gb内存,所以我把它改为 402649088(400mb)。
默认情况下,所有的索引都使用相同的键高速缓存,当访问的索引不在缓存中时,使用 LRU ( Least Recently Used 最近最少使用)算法来替换缓存中最近最少使用的索引块。为了进一步避免对键高速缓存的争用,从 MySQL5.1 开始,可以设置多个键高速缓存,并为不同的索引键指定使用的键高速缓存。下面的例子演示如何修改高速键缓存的值,如何设置多个键高速缓存,以及如何为不同的索引指定不同的缓存:
显示当前的参数大小,为16M:
mysql> show variables like 'key_buffer_size';
----------------- -------
| Variable_name | Value |
----------------- -------
| key_buffer_size | 16384 |
----------------- -------
1 row in set (0.00 sec)
修改参数值到200M:
mysql> set global key_buffer_size=204800;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'key_buffer_size';
----------------- --------
| Variable_name | Value |
----------------- --------
| key_buffer_size | 204800 |
----------------- --------
1 row in set (0.00 sec)
上面介绍的是默认的键缓存,下面介绍如何设置多个键缓存:
设置 hot_cache 的键缓存 100M , cold_cache 的键缓存 100M ,另外还有 200M 的默认的键缓存。如果索引不指定键缓存,则会放在默认的键缓存中。
mysql> set global hot_cache.key_buffer_size=102400;
Query OK, 0 rows affected (0.00 sec)
mysql> set global cold_cache.key_buffer_size= 1024 00;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'key_buffer_size';
----------------- --------
| Variable_name | Value |
----------------- --------
| key_buffer_size | 204800 |
----------------- --------
1 row in set (0.00 sec)
如果要显示设置的多键缓存的值,可以使用:
mysql> SELECT @@global.hot_cache.key_buffer_size;
------------------------------------
| @@global.hot_cache.key_buffer_size |
------------------------------------
| 102400 |
------------------------------------
1 row in set (0.03 sec)
mysql> SELECT @@global.cold_cache.key_buffer_size;
-------------------------------------
| @@global.cold_cache.key_buffer_size |
-------------------------------------
| 102400 |
-------------------------------------
1 row in set (0.00 sec)
指定不同的索引使用不同的键缓存:
mysql> CACHE INDEX test1 in hot_cache;
------------ -------------------- ---------- ----------
| Table | Op | Msg_type | Msg_text |
------------ -------------------- ---------- ----------
| test .test1 | assign_to_keycache | status | OK |
------------ -------------------- ---------- ----------
1 row in set (0.00 sec)
mysql> CACHE INDEX test2 in hot_cache;
------------ -------------------- ---------- ----------
| Table | Op | Msg_type | Msg_text |
------------ -------------------- ---------- ----------
| test .test2 | assign_to_keycache | status | OK |
------------ -------------------- ---------- ----------
1 row in set (0.00 sec)
通常在数据库刚刚启动的时候,需要等待数据库热起来,也就是等待数据被缓存到缓存区中,这段时间数据库会因为 buffer 的命中率低而导致应用的访问效率不高。使用键高速缓存的时候,可以通过命令将索引预加载到缓存区中,大大缩短了数据库预热的时间。具体的操作方式是:
mysql> LOAD INDEX INTO CACHE test1,test2 IGNORE LEAVES;
------------ -------------- ---------- ----------
| Table | Op | Msg_type | Msg_text |
------------ -------------- ---------- ----------
| test .test1 | preload_keys | status | OK |
| test .test2 | preload_keys | status | OK |
------------ -------------- ---------- ----------
2 rows in set (3.89 sec)
如果已经使用 CACHE INDEX 语句为索引分配了一个键高速缓冲,预加载可以将索引块放入该缓存,否则,索引块将被加载到默认的键高速缓冲。  

    4887王中王鉄算盘奖结果 2

1.1.4 sort buffer(线程独有)

当sql需要进行排序操作时会用到sort buffer。通过增大sort buffer的大小可以提高order by或group by的处理性能。系统默认为2M,最大限制和join buffer一样,一般设置在2mb~4mb之间可以满足大多数应用的需求。

4)、back_log:
要求 mysql 能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。
back_log 值指出在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的tcp/ip连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。
当你观察你的主机进程列表,发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | null | connect | null | login | null 的待连接进程时,就要加大 back_log 的值了。默认数值是50,我把它改为500。

  上面内存约等于2031912/1024.0=1984M。
2. buffer_pool 运行参数

1.1.5 Binlog_cache_size(线程独有)

二进制日志缓存支持事务存储引擎并且服务器启用了二进制日志的前提下为每个客户端分配内存。通过binlog_cache_use和binlog_cache_disk_use来判断当前binlog_cache_size是否合适,默认值1M

与binlog_cache_size对应,max_binlog_cache_size,代表binlog能够使用的最大的cache的大小。注意,当执行多语句事务的时候,max_binlog_cache_size不够大的话,系统会保出”multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”的错误。

(5)、interactive_timeout:
服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 client_interactive 选项的客户。 默认数值是28800,我把它改为7200。

-- 下面是buffer_pool 运行相关参数
SHOW STATUS LIKE  'Innodb_buffer_pool_%';

1.1.6 Thread_cache_size

Thread cache池中应该存放的连接线程数,当系统最初启动的时候,并不会马上就创建thread_cache_size池中,而是随着连接线程的创建及使用,慢慢的将用完的连接线程存入其中。当到达指定值后,mysql就不会再续保存用完的连接线程了。在长连接的环境中我们不需要将thread_cache_size参数设置太大,一般来说50~100就可以了。

Thread cache命中率:thread_cache_hit=(connections-threads_created)/connections * 100%,thread cache命中率应该保持在90%左右甚至更高的比率才正常。

(6)、sort_buffer:
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order by或group by操作。默认数值是2097144(2m),我把它改为 16777208 (16m)。

  4887王中王鉄算盘奖结果 3

1.1.7 Read_buffer_size(线程独有)

以sequential scan方式扫描表数据时候使用的buffer。系统默认128kb,最大2GB,设置的值必须是4KB的倍数,否则系统会自动改成小于设置值的4kb的倍数。一般来说,可以适当调大该参数看是否能够 改善全表扫描的性能。在不同平台上可能会有不同表现,所以该参数的设置最好是在真是环境上面通 过多次更改测试调整,才能选找到一个最佳值

(7)、table_cache:
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是64,我把它改为512。

Innodb_buffer_pool_pages_total 

缓存池页总数目。
共占用了16382 页 。单位page

Innodb_buffer_pool_pages_free

缓存池剩余的页数目。
在16382 页中有2000页没有使用。 单位page

Innodb_buffer_pool_pages_data

缓存池中包含数据的页的数目,包括脏页。
14273个页含有数据。单位page

Innodb_buffer_pool_read_requests

innodb进行逻辑读的数量。
529670886次请求读。单位次数

Innodb_buffer_pool_reads

进行逻辑读取时无法从缓冲池中获取而执行单页读取的次数。
941147次是物理I/0读取。单位次数

Innodb_buffer_pool_write_requests

写入 InnoDB 缓冲池的次数。
48606702次请求写入。单位次数

Innodb_buffer_pool_read_ahead_rnd

记录进行随机读的时候产生的预读次数。
0次

Innodb_buffer_pool_read_ahead

预读到innodb buffer pool里次数。
1465370次。  单位page

Innodb_buffer_pool_read_ahead_evicted

预读的页数,但是没有被读取就从缓冲池中被替换的页的数量,一般用来判断预读的效率。 0次

Innodb_buffer_pool_wait_free

数据要写入buffer pool的时候,需要等待空闲页的次数。是指缓存池里没有干净页的时候读取或创建页,要先等待页被刷新。
2927次。 单位次数。

Innodb_buffer_pool_pages_dirty

buffer pool缓存池中脏页的数目。
0次。单位是page

Innodb_buffer_pool_pages_flushed

buffer pool缓存池中刷新页请求的数目。
15437744次。单位page

Innodb_buffer_pool_pages_misc

buffer pool缓存池中当前页已经被用作管理用途或hash index而不能用作为普通数据页的数目。
109次。单位page

Innodb_buffer_pool_pages_old

在旧区域存放着多少个页。
5249次。单位page

Innodb_buffer_pool_pages_made_young

移动到新区域的有多少个页。
353059次。单位page

Innodb_buffer_pool_pages_made_not_young

没有移动到新区域的有多少个页。
31725809次。单位page

1.1.8 Read_rnd_buffer_size(线程独有)

以random scan方式扫描表数据时候使用的buffer。默认256kb,最大4gb。一般来讲,该值适当 调大对提高order by操作的性能有一定的效果

 

(8)、thread_cache_size:
可以复用的保存在中的线程的数量。如果有,新的线程从缓存中取得,当断开连接的时候如果有空间,客户的线置在缓存中。如果有很多新的线程,为了提高性能可以这个变量值。通过比较 connections 和 threads_created 状态的变量,可以看到这个变量的作用。我把它设置为 80。

  Buffer Pool使用率:14328.0/16382.0 *100=87.46%
  缓存读命中率: (529670886-941147)/529670886.0 *100 =99.82%
  实际占用空间是:16382 *16(页单位)*1024=268402688 字节。 上面给buffer_pool_size分配的是268435456字节。

1.2 myisam缓存参数优化

影响myisam存储引擎效率的缓存参数是key_buffer_size(索引缓存大小)。用来缓存myisam表的索引。32为平台不要超过2G,64位平台不要超过4G,一般设置为可用内存的30%-40%,请至少保留16~32M的大小,以适应给予磁盘临时表所需。

对于key_buffer_size的设置可以通过三个指标来计算,第一个是索引的总大小,第二个是系统可用物理内存,第三个是系统当前的key cache命中率。

Key_size=key_number*(key_length 4)/0.67

Max_key_buffer_size<系统可用物理内存 - 线程使用的内存(Thread_usage)

Thread_usage=max_connections*(sort_buffer_size join_buffer_size read_buffer_size read_rnd_buffer_size thread_stack)

通过以下三个比率数据,就可以知道key cache设置是否合理:

Key_buffer使用率=(1-key_blocks_used/(key_blocks_used key_blocks_unused))*100%

Key_buffer_read_hitratio=(1-key_reads/key_read_requests)*100%

Key_buffer_write_hitratio=(1-key_writes/key_4887王中王鉄算盘奖结果,write_requests)*100%

一般来说key_buffer使用率应该在99%以上,key_buffer_read_hitratio也应该尽可能地高

(9)mysql的搜索功能
用mysql进行搜索,目的是能不分大小写,又能用中文进行搜索
只需起动mysqld时指定 --default-character-set=gb2312

  1. 设置buffer_pool参数

    -- 从134217728设置成268435456 (另一台mysql) SET GLOBAL innodb_buffer_pool_size= 268435456

1.3 innodb缓存参数优化

innodb的缓存参数主要是两个方面,一类是数据索引(innodb_buffer_pool)结构,另一类是日志(Innodb_log_buffer)。

(10)、wait_timeout:
服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800,我把它改为7200。
 
(11)、innodb_thread_concurrency:
你的服务器CPU有几个就设置为几,默认为8。
 
(12)、query_cache_size  与 query_cache_limit
QueryCache 之后所带来的负面影响:
a) Query 语句的hash 运算以及hash 查找资源消耗。当我们使用Query Cache 之后,每条SELECT
类型的Query 在到达MySQL 之后,都需要进行一个hash 运算然后查找是否存在该Query 的
Cache,虽然这个hash 运算的算法可能已经非常高效了,hash 查找的过程也已经足够的优化
了,对于一条Query 来说消耗的资源确实是非常非常的少,但是当我们每秒都有上千甚至几千
条Query 的时候,我们就不能对产生的CPU 的消耗完全忽视了。
b) Query Cache 的失效问题。如果我们的表变更比较频繁,则会造成Query Cache 的失效率非常
高。这里的表变更不仅仅指表中数据的变更,还包括结构或者索引等的任何变更。也就是说我
们每次缓存到Query Cache 中的Cache 数据可能在刚存入后很快就会因为表中的数据被改变而被
清除,然后新的相同Query 进来之后无法使用到之前的Cache。
c) Query Cache 中缓存的是Result Set ,而不是数据页,也就是说,存在同一条记录被Cache 多
次的可能性存在。从而造成内存资源的过渡消耗。当然,可能有人会说我们可以限定Query
Cache 的大小啊。是的,我们确实可以限定Query Cache 的大小,但是这样,Query Cache 就很
容易造成因为内存不足而被换出,造成命中率的下降。
 
QueryCache 的正确使用:
虽然Query Cache 的使用会存在一些负面影响,但是我们也应该相信其存在是必定有一定价值。我
们完全不用因为Query Cache 的上面三个负面影响就完全失去对Query Cache 的信心。只要我们理解了
Query Cache 的实现原理,那么我们就完全可以通过一定的手段在使用Query Cache 的时候扬长避短,重
发发挥其优势,并有效的避开其劣势。
首先,我们需要根据Query Cache 失效机制来判断哪些表适合使用Query 哪些表不适合。由于Query
Cache 的失效主要是因为Query 所依赖的Table 的数据发生了变化,造成Query 的Result Set 可能已经
有所改变而造成相关的Query Cache 全部失效,那么我们就应该避免在查询变化频繁的Table 的Query 上
使用,而应该在那些查询变化频率较小的Table 的Query 上面使用。MySQL 中针对Query Cache 有两个专
用的SQL Hint(提示):SQL_NO_CACHE 和SQL_CACHE,分别代表强制不使用Query Cache 和强制使用
Query Cache。我们完全可以利用这两个SQL Hint,让MySQL 知道我们希望哪些SQL 使用Query Cache 而
哪些SQL 就不要使用了。这样不仅可以让变化频繁Table 的Query 浪费Query Cache 的内存,同时还可以
减少Query Cache 的检测量。
其次,对于那些变化非常小,大部分时候都是静态的数据,我们可以添加SQL_CACHE 的SQL Hint,
强制MySQL 使用Query Cache,从而提高该表的查询性能。
最后,有些SQL 的Result Set 很大,如果使用Query Cache 很容易造成Cache 内存的不足,或者将
之前一些老的Cache 冲刷出去。对于这一类Query 我们有两种方法可以解决,一是使用SQL_NO_CACHE 参
数来强制他不使用Query Cache 而每次都直接从实际数据中去查找, 另一种方法是通过设定
“query_cache_limit”参数值来控制Query Cache 中所Cache 的最大Result Set ,系统默认为
1M(1048576)。当某个Query 的Result Set 大于“query_cache_limit”所设定的值的时候,Query
Cache 是不会Cache 这个Query 的。

  由于SHOW VARIABLES下的参数都是静态值。当mysql重启时,上面的缓存设置将失效。

1.3.1 innodb_buffer_pool

innodb_buffer_pool不同于key_buffer的地方是它不仅缓存索引还会缓存实际的数据。所以完全相同的数据库,使用innodb存储引擎可以使用更多的内存来缓存数据库相关的信息。

关于innodb_buffer_pool_size的设置,建议设置为系统物理内存的50%-80%之间,最好是比innodb的tablespace多10%的空间。

通过以下两个比率来查看innodb_buffer_pool_size的值是否设置过大:

使用率:(innodb_buffer_pool_pages_data/innodb_buffer_pool_pages_total)*100% >90%

命中率:(innodb_buffer_pool_read_requests-innodb_buffer_pool_reads)/

innodb_buffer_pool_read_requests * 100%  >90%

(13)、innodb_buffer_pool_size
innodb_buffer_pool_size 定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和 MyISAM 存储引擎不同, MyISAM 的 key_buffer_size 只能缓存索引键,而 innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小,可以有效的减少 InnoDB 类型的表的磁盘 I/O 。在一个以 InnoDB 为主的专用数据库服务器上,可以考虑把该参数设置为物理内存大小的 60%-80%
 
InnoDB占用的内存,除innodb_buffer_pool_size用于存储页面缓存数据外,另外正常情况下还有大约8%的开销,主要用在每个缓存页帧的描述、adaptive hash等数据结构,如果不是安全关闭,启动时还要恢复的话,还要另开大约12%的内存用于恢复,两者相加就有差不多21%的开销。

[root@xuegod64 ~]# systemctl stop mysqld.service
[root@xuegod64 ~]# systemctl start  mysqld.service

1.3.2 Innodb_additional_mem_pool_size

Innodb_additional_mem_pool_size用于存放innodb的字典信息和其他一些内部结构所需要的内存空间。Innodb表越多,需要的空间就越大,系统默认为1M。

一个常规的几百个innodb表的mysql,如果不是每个表都是有上百个字段的话,20M内存就足够,设置超过实际所需要的内存并没有太大的意义,只是浪费内存而已。

 

这样,12G的innodb_buffer_pool_size,最多的时候InnoDB就可能占用到14.5G(12G X 21%)的内存,再加上操作系统用的几百M,近千个线程堆栈,就差不多16G了。
 
MAX_QUERIES_PER_HOUR 用来限制用户每小时运行的查询数量:
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_connections_per_hour 5;
(db用户在dbname的数据库上控制用户每小时打开新连接的数量为5个)
 
MAX_USER_CONNECTIONS 限制有多少用户连接MYSQL服务器:
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_user_connections 2;
(db用户在dbname的数据库账户一次可以同时连接的最大连接数为2个)

  重启后还是134217728.如下图
  4887王中王鉄算盘奖结果 4

1.3.3 innodb_log_buffer

innodb事物日志所使用的缓存,系统默认为1M,一般来说如果不是编写负载非常高且以大事物居多的话8M以内的大小完全够用。

 

MAX_UPDATES_PER_HOUR 用来限制用户每小时的修改数据库数据的数量:
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_updates_per_hour 5;
(db用户在dbname的数据库上控制用户每小时修改更新数据库的次数为5次)
MAX_USER_CONNECTIONS 用来限制用户每小时的修改数据库数据的数量:
mysql> grant all on dbname。* to db@localhost identified by “123456”
With MAX_QUERIES_PER_HOUR 20 ;指mysql单个用户的最大连接数
(db用户在dbname的数据库上控制用户每小时的连接数为20个)
 
调优举例
针对my.cnf文件进行优化:
[mysqld]
skip-locking(取消文件系统的外部锁)
skip-name-resolve(不进行域名反解析,注意由此带来的权限/授权问题)
key_buffer_size = 256M(分配给MyISAM索引缓存的内存总数)对于内存在4GB左右的服务器该参数可设置为256M或384M。
  注意:该参数值设置的过大反而会是服务器整体效率降低!
  max_allowed_packet = 4M(允许最大的包大小)
  thread_stack = 256K(每个线程的大小)
  table_cache = 128K(缓存可重用的线程数)
  back_log = 384(临时停止响应新请求前在短时间内可以堆起多少请求,如果你需要在短时间内允许大量连接,可以增加该数值)
  sort_buffer_size = 2M(分配给每个线程中处理排序)
  read_buffer_size = 2M(读取的索引缓冲区大小)
  join_buffer_size = 2M(分配给每个线程中处理扫描表连接及索引的内存)
  myisam_sort_buffer_size = 64M(myisam引擎排序缓冲区的大小)
  table_cache = 512(缓存数据表的数量,避免重复打开表的开销)
  thread_cache_size = 64(缓存可重用线程数,见笑创建新线程的开销)
  query_cache_size = 64M(控制分配给查询缓存的内存总量)
  tmp_table_size = 256M(指定mysql缓存的内存大小)
  max_connections = 768(最大连接数)指mysql整个的最大连接数
max_connect_errors = 10000(最大连接错误数据)
  wait_timeout = 10(超时时间,可以避免攻击)
  thread_concurrency = 8(根据cpu数量来设置)
  skip-bdb 禁用不必要的引擎
  skip-networking(关闭mysql tcp/ip连接方式)
  Log-slow-queries = /var/log/mysqlslowqueries.log
  long_query_time = 4(设定慢查询的时间)
  skip-host-cache(提高mysql速度的)
  open_files_limit = 4096(打开文件数)
interactive_timeout = 10(服务器在关闭它前在一个交互连接上等待行动的秒数)
max_user_connections = 500(最大用户连接数)
 
key_buffer_size                 默认为218       调到128最佳
query_cache_size 
tmp_table_size                  默认为16M        调到64-256最挂

  要永久改变,需要在操作系统里使用vim my.cnf 来修改. 如下图所示, 去掉#重新定义值。

2.个性化参数

mysql的个性化参数很多,下面只介绍对mysql影响较大的参数。

innodb_thread_concurrency=8       你的服务器CPU有几个就设置为几,默认为8
 
table_cache=1024 物理内存越大,设置就越大.默认为2402,调到512-1024最佳
innodb_additional_mem_pool_size=8M   默认为2M
innodb_flush_log_at_trx_commit=0 等到innodb_log_buffer_size列队满后再统一储存,默认为1
innodb_log_buffer_size=4M          默认为1M

  4887王中王鉄算盘奖结果 5

2.1 服务器级别的参数

log_bin= /var/log/mysql/mysql-bin.log 

打开二进制日志,/var/log/mysql/二进制日志的位置,mysql-bin为二进制日志名

max_binlog_size=256M

二进制日志的大小设为512或1G,不能超过1G。该大小并不能严格限制binlog的大小,尤其是当binlog 比较靠近尾部而又遇到一个较大事务的时候,为了保证事务完整性,系统不做切换日志的动作。

Sync_binlog  = 0

这个参数对mysql系统来说至关重要,它不仅影响binlog对mysql所带来的性能损耗,而且还影响到 mysql中数据的完整性,系统默认设置为0。Sync_binlog=0,事务提交后,mysql不做fsync之类的磁盘 同步指令刷新binlog——cache中的信息到磁盘,而让filesystem自行决定什么时候做同步,或者cache 满了之后才同步到磁盘。Sync_binlog=n,当每进行n次事务提交后,mysql进行一次fsync之类的磁盘同 步指令来见binlog_cache中的数据写入磁盘。

Binlog_format=mixed

二进制日志的存储格式statement、row、mixed

Expire_logs_days=10

控制mysql binlog日志的保存期限

log_slow_queries = mysql-slow

Slow query log功能对系统性能的整体影响没有binlog那么大,带来的io损耗也比较小。但是系统需 要计算每一条query的执行时间,在cpu方面会有所消耗。在cpu资源比较紧张的情况下,可以在大部 分时间关闭该功能, 默认关闭。

#等价于

slow_query_log=on

slow_query_log_file=mysql-slow

Long_query_time = 0.05

该值决定,超过多少时间才可以算做是慢查询,单位为秒。

Sql_mode=""

mysql服务器的sql模式:非严格模式。

使用非严格模式时,存储的字符超过他们定义的长度时候,如果不是在sql服务器的严格模式(STRICT_ALL_TABLES)下,都会 自动截取合适的字段存储,而不会出现错误。但是,如果是中文的话同样要报错误:)比如定义char(4), 然后insert (‘c哈哈’).

Lower_case_table_names=0

Mysql在window环境下默认是忽略大小写的,而linux环境中则相反。通过lower_case_table_names 来解决由于大小写带来的数据库移植问题。0:区分大小写,1:不区分大小写

Back_log = 50

在mysql的连接请求等待队列中允许存放的最大连接请求数,默认值为50

Max_connections=500

整个mysql允许的最大连接数。这个参数主要影响整个mysql应用的并发处理能力,一般来说,只要 mysql主机性能允许,都将该参数设置得尽可能大一点。500~800是比较合适的值

Max_allowed_packet=1M

在网络传输中,一次消息数量的最大值。系统默认为1M,最大值是1GB,必须设定为1024的倍数, 单位为字节。

Thread_stack=192K

当mysql创建一个新的连接线程的时候,需要给他分配一定大小的内存堆栈空间,以便存放客户端的 请求query以及自身的各种状态和处理信息。系统默认值192KB

Skip_external_locking

跳过外部锁定,external-locking用于多进程条件下为myisam数据表进行锁定。如果有多台服务器使 用同一个数据库目录(不建议),那么每台服务器都必须开启external-locking

Thread_concurrency=4

设置该值的正确与否,对mysql的性能影响很大,在多个cpu(多核)工作的情况下,错误地设置了该 值,会导致mysql不能充分利用cpu,出现同一时刻只能使用一个cpu的情况。Thread_concurrency应 设为cpu核数的2倍。一个双核cpu,thread_concurrency=4

Transaction_isolation

事务的隔离级别,read uncommited 、read commited、 

repeatable read(innodb默认隔离级别)、serializable

Tmp_table_size

断开mysql的连接后系统会自动删除临时表中的数据,在一个数据库连接里面发行多次sql的话系统是 不会自动清空临时表数据的。系统默认32M,如果有很多高级group by查询,增加 tmp_table_size的值。Tmp_table_size不宜过大,否则一旦超过此限制,copy to disk的过程确实会很 长。查询需要order by或者group by等需要用到结果集时,参数中设置的临时表的大小小于结果集的 大小时,就会将该表放在磁盘上,造成io负载。另外如果Max_heap_table_size比tmp_table_size小的 话,会把Max_heap_table_size作为最大的内存临时表限制。

Skip_name_resolve

禁止mysql server对外部连接进行DNS解析,使用这一选项可以消除mysql进行DNS解析的时间。 注意,启动该选项后,所有主机连接授权都要使用ip地址方式

Slave_skip_errors

使用slave_skip_errors=error_code设置跳过重复的记录。Error_code设置过大或过小都会无效。通 过show slave statusG;的last_error里获取才是正确的。但是以slave-skip-errors条件运行得mysql默 认在以后所有的从mysql上有错误,都自动跳过。

Replicate_ignore_db=dbtest

主从时忽略同步指定数据库,用replicate_do_db和replicate_ignore_db时有一个隐患,跨库更新时会 出错,会忽略use dbtest下的sql,而这些sql可能是修改其它库的内容,如:update mysql.user……。可 以使用replicate_wild_do_table和replicate_wild_ignore_table来代替如

replicate_wild_do_table=test.%

或replicate_wild_ignore_table=mysql.%这样就可以避免出现上述问题了

Relay_log

中继日志

delayed_queue_size

此参数在执行其他insert delayed语句的客户机阻塞以前,确定来自insert delayed语句的放入队列的 行的数目。增加这个参数的值使服务 能从这种请求中接受更多的行,因而客户机可以继续执行而不 阻塞。

read_buffer_size=4M                   默认为64K
read_rnd_buffer_size   随机读 缓存区  默认为256K
sort_buffer_size=32M                   默认为256K
max_connections=1024                 默认为1210
thread_cache_size=120             默认为60
 
性能测试 1、mysql 自带测试工具
shell> perl -MCPAN -e shell
cpan> install DBI
cpan> install DBD::mysql
shell> cd sql-bench
shell> perl run-all-tests --server=server_name
server_name是一个支持的服务器。要获得所有选项和支持的服务器,调用命令:
shell> perl run-all-tests --help
2、mysqlreport
 
 
参考文档


 

2.2 myisam存储引擎级别的参数

Query_cache_wlock_invalidate

针对myisam存储引擎,设置当有write lock在某个table上面的时候,读请求是要等待write lock释 放资源之后再查询还是允许直接从query cache中读取数据。这个选项指是否缓存其他连接已经锁定 的表,默认值是off

Key_cache_block_size=1024

设置cache block的大小,限定将‘.myi’文件中的index block被读入时候的file block的大小

Concurrent_insert=0

Myisam默认读锁是read local,使用read local时concurrent_insert设置才有效

此参数可以控制myisam存储引擎查询和插入操作的并发进行。

0:不允许并发插入

1:如果表中没有被删除的行,myisam允许一个进程读表的同时,另一个进程从表尾插入记录

2:无论myisam表中是否有空洞,都允许在表尾插入记录。

Low-priority-updates

给myisam给予读请求优先的权力

Max_write_lock_count=100

设置一个合适的值,当一个表的读锁到达这个值后,mysql就暂时将写请求的优先级降低,给读进程 一个获得锁的机会。

Bulk_insert_buffer_size=8388608

myisam存储引擎在进行批量插入时会用到的缓存参数。批量插入有:

insert into …value(…),(…),(…);

load data infile ….

Insert into …select …

您可能感兴趣的文章:

  • MySQL性能参数详解之Skip-External-Locking参数介绍
  • MySQL性能参数详解之Max_connect_errors 使用介绍
  • MySQL修改tmpdir参数
  • my.ini优化mysql数据库性能的十个参数(推荐)
  • mysql数据存储过程参数实例详解

2.3 innodb存储引擎级别的参数

Innodb_max_dirty_pages_pct=75

当脏数据达到多少时,刷新innodb_buffer_pool,此参数的设置一方面可以加速数据的处理,另 一方面也可能导致数据的不一致。

Innodb_flush_log_at_trx_commit

控制innodb事务日志刷新方式的参数

0 每个一秒刷新

1 每次事务结束同步

2 每次事务结束同步,由于调用了文件系统的写入操作,而文件系统是有缓存的,所以并没有真正同 步,但性能最好

Innodb_log_files_in_group=2

指定几个日志组,默认是2

Innodb_log_file_size

指定innodb日志文件大小

Innodb_file_io_threads=4

文件读写io数,这个参数只在window下起作用,linux下只会等于4    ---废弃了吗?

Innodb_file_per_table   

使用单独的表空间

innodb_doublewrite

使用的是一种较为独特的flush实现技术,主要作用是为了通过减少文件同步次数提高io性能的情况 下,提高系统crash或者断电情况下数据的安全性,避免写入的数据不完整。

innodb_adaptive_hash_index

不是为了改善磁盘io的性能,而是为了提高buffer pool中的数据的访问效率,就是给buffer pool中的 数据做的索引。

Innodb_thread_concurrency=0

默认设置为0,让系统自己控制

Innodb_lock_wait_timeout = 50

该参数主要用于出现死锁的时候等待指定时间后回滚

本文由4887王中王鉄算盘奖结果发布于王中王高手论坛,转载请注明出处:4887王中王鉄算盘奖结果MySQL常见配置参数调优,

关键词:

上一篇:数据类型,数值类型

下一篇:没有了

最火资讯