MySQL[PGA] query_cache_size

query_cache_size

  • Mysql高速查询缓存(简称QC)

  • 将SELECT语句和查询结果存放再缓冲区中,若有同样的SELECT语句(区分大小写),将直接从缓冲区中读取结果。

  • show global status like 'Qcache_%' 查看QC,可以知道QC设置是否合理

  • 如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓存不够的情况

  • 如果Qcache_hits的值非常大,则表明查询缓存使用非常频繁,此时需要增加缓冲大小

  • 如果Qcache_hits的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲

  • 在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓存(ex:SELECT SQL_NO_CACHE * FROM XX WHERE YY;)

  • query_cache_limit = 2M, 不缓存查过2M的查询结果

  • query_cache_min_res_unit = 512K, 设置每个QC单元大小,提高QC利用率(Qcache_queries_in_cache不大,但Qcache_free_memory较大,就需要减小query_cache_min_res_unit)

关闭查询缓存:

query_cache_type=0

query_cache_size=0

Qcache_free_memory:查询缓存的内存大小,通过这个参数可以很清晰的知道当前系统的查询内存是否够用,是多了,还是不够用,DBA可以根据实际情况做出调整。 
Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大,缓存效果越理想。 
Qcache_inserts: 表示多少次未命中然后插入,意思是新来的SQL请求在缓存中未找到,不得不执行查询处理,执行查询处理后把结果insert到查询缓存中。这样的情况的次 数,次数越多,表示查询缓存应用到的比较少,效果也就不理想。当然系统刚启动后,查询缓存是空的, 这很正常。 
Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值,用户可以适当的调整缓存大小。 
Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。 
Qcache_queries_in_cache:当前缓存中缓存的查询数量。 
Qcache_total_blocks:当前缓存的block数量。

 

可参考:MYSQL 中query_cache_size小结

http://jackyrong.iteye.com/blog/2173523

在MySQL里QC是由一个全局锁在控制,每次更新QC的内存块都需要进行锁定。
例如,一次查询结果是20KB,当前 query_cache_min_res_unit 值设置为 4KB(默认值就是4KB,可调整),那么么本次查询结果共需要分为5次写入QC,每次都要锁定,可见其成本有多高。

线上环境到底要不要开启query cache

http://blog.csdn.net/liqfyiyi/article/details/50178565

MySQL[PGA] sort_buffer_size

Sort_Buffer_Size 是一个connection级参数,在每个connection第一次需要使用这个buffer的时候,一次性分配设置的内存。

Sort_Buffer_Size 并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。

文档说“On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation” 

据说Sort_Buffer_Size 超过2KB的时候,就会使用mmap() 而不是 malloc() 来进行内存分配,导致效率降低。(在一个10年的文章看到的 不知道现在准不准确)

MySQL> show variables like ‘%sort_buffer_size%’; 

默认256K

每个session 需要做一个排序分配的一个buffer,sort_buffer_size 不指定任何的存储引擎,

如果你看到很多的ort_merge_passes per second

mysql> SHOW GLOBAL STATUS like ‘%sort%’; 
+——————-+———-+ 
| Variable_name | Value | 
+——————-+———-+ 
| Sort_merge_passes | 1490 | 
| Sort_range | 1086174 | 
| Sort_rows | 23988490 | 
| Sort_scan | 213850 | 
+——————-+———-+ 
4 rows in set (0.00 sec)

你可以考虑增加sort_buffer_size 来加速ORDER BY 或者GROUP BY 操作,不能通过查询或者索引优化的。

在MySQL 5.6.4 优化器尝试解决需要多少空间,但可以分配更多,

达到极限。 在MySQL 5.6.4, 优化器分配整个buffer 即使如果根本不需要所有。

在任何情况下, 设置它大于需要的全局会减慢很多的查询。最后是作为一个会话设置来增加,

只有对需要大量的内存的会话, 在Linux上,有阀值为256KB 和2MB ,大的值可能显著的减慢内存分配,

因此你应该考虑下面中的一个值。

mysql> SET GLOBAL sort_buffer_size = 1024*1024; 
Query OK, 0 rows affected (0.00 sec)

mysql> quit 
.

mysql> show variables like ‘%sort_buffer%’; 
+————————-+———+ 
| Variable_name | Value | 
+————————-+———+ 
| innodb_sort_buffer_size | 1048576 | 
| myisam_sort_buffer_size | 8388608 | 
| sort_buffer_size | 1048576 | 
+————————-+———+ 
3 rows in set (0.00 sec)

sort_buffer_size=1M

MySQL[PGA] MyISAM key_buffer_size

MyISAM表的索引只需打开一次,多个线程间共享

数据文件则是每个线程各自打开

key_buffer_size 只缓存MyISAM的索引数据,不缓存行数据

行数据是由操作系统的OS page cache来进行缓存的

如果只要使用MyISAM引擎,推荐设置可用RAM的20%~50%

更多的内存留给OS执行OS page cache,缓存从*.MYD读取的行数据

因为热点数据不会太多20%~50%就够了

如果key_buffer_size设置太大,会造成内存空间浪费,设置太小又会造成缓存命中率太低

可以先看当前*.MYI(索引文件)总大小,设置对应的key buffer size

如果总的索引文件只有1个G 那我们就设置成1个G 

如果索引文件太大了 那就遵循28原则(20%的热点数据产生80%的访问量),或者37开

然后再观察命中率,如果太低了就把参数调第一点,如果太高了 就调高一点

计算公式(更多时候是看读的命中率):

key_buffer_read_hits=(1-Key_reads/Key_read_requests)*100%

key_buffer_write_hits=(1-Key_writes/Key_write_requests)*100%

Key_reads:发生物理读的次数(不是通过key_buffer读索引的次数)

Key_read_requests:总的请求次数

即便全是innodb表,没有用MyISAM,也是要设置该值用于缓存临时表的索引,推荐32M

设置key_buffer_size的方法:

1.my.cnf中:key_buffer_size=4G(32-bit系统下最大4G, 64-bit下可以超过4G)

hot_cache.key_buffer_size=2G

cold_cache.key_buffer_size=2G

可以设置多个key buffer 

set global k1.key_buffer_size=1G

set global k2.key_buffer_size=1G

将索引load到执指定的key buffer

CACHE INDEX db1.t1, db1.t2, db2.t3 IN hot_cache;

CACHE INDEX db1.t4, db2.t5, db2.t6 IN cold_cache;

2.mysql命令行中:set global key_baffer_size = 4*1024*1024*2014

MySQL[PGA] tmp_table_size

不负责限制MEMORY/HEAP表最大容量

如果执行SQL产生临时表超过tmp_table_size/max_heap_table_size

则会产生基于磁盘的MyISAM表

5.7可以执行生成Innodb表

通过设置tmp_table_size选项来增加一张临时表的大小,例如做高级GROUP BY操作生成的临时表。如果调高该值,MySQL同时将增加heap表的大小,可达到提高联接查询速度的效果,建议尽量优化查询,要确保查询过程中生成的临时表在内存中,避免临时表过大导致生成基于硬盘的MyISAM表。

mysql> show global status like ‘created_tmp%‘;

+——————————–+———+

| Variable_name   | Value |

+———————————-+———+

| Created_tmp_disk_tables | 21197 |

| Created_tmp_files   | 58  |

| Created_tmp_tables  | 1771587 |

+——————————–+———–+

每次创建临时表,Created_tmp_tables增加,如果临时表大小超过tmp_table_size,则是在磁盘上创建临时表,Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数,比较理想的配置是:

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% =1.20%,应该相当好了

默认为16M,可调到64-256最佳,线程独占,太大可能内存不够I/O堵塞

如果动态页面要调大点,100M以上,如果网站大部分都是静态内容,一般64M足够。

tmp_table_size优化

数据库连接突然增多到1000的问题

查看了一下,未有LOCK操作语句。

但是明显有好多copy to tmp table的SQL语句,这条语读的时间比较长,且这个表会被加读锁,相关表的update语句会被排进队列。如果多执行几次这样的copyt to tmp table 语句,会造成更多的语句被阻塞。
连接太多造成mysql处理慢。

copy to tmp talbe 语句产生的原因是查询需要Order By 或者Group By等需要用到结果集时,参数中设置的临时表的大小小于结果集的大小时,就会将该表放在磁盘上,这个时候在硬盘上的IO要比内销差很多。所耗费的时间也多很多。另外Mysql的另外一个参数max_heap_table_size比tmp_table_size小时,则系统会把max_heap_table_size的值作为最大的内存临时表的上限,大于这个时,改写硬盘。
我们的mysql这两个参数为:

tmp_table_size 33554432 (33.5M)
max_heap_table_size 16777216 (16.7M)
比较小。
建议增加到上百M。我们的内存应该够吧。

另外join_buffer_size(影响 表之间join性能的缓存)为131072 (131K)较小,可以增加一点。

[root@mail ~]# vi /etc/my.cnf

[mysqld]
tmp_table_size=200M

mysql> show processlist; 
mysql> show columns from wp_posts;

SQL 语句的第一个 LEFT JOIN ON 子句中: LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid _mydata 的 userid 被参与了条件比较运算。为 _mydata 表根据字段 userid 建立了一个索引: mysql> ALTER TABLE `_mydata` ADD INDEX ( `userid` )  增加 tmp_table_size 值。
mysql 的配置文件中,tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小,MySQL产生一个 The table tbl_name is full 形式的错误,如果你做很多高级 GROUP BY 查询,增加 tmp_table_size 值。 这是 mysql 官方关于此选项的解释:

tmp_table_size

This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory.
对 WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的条件判断中用到的字段,应该根据其建立索引INDEX。
索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大,花费时间越多。如果表对于查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要考虑所有数据。如果一个表有1000行,这比顺序读取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。
根据 mysql 的开发文档:

索引 index 用于: 
快速找出匹配一个WHERE子句的行 
当执行联结(JOIN)时,从其他表检索行。 
对特定的索引列找出MAX()或MIN()值 
如果排序或分组在一个可用键的最左面前缀上进行(例如,ORDER BY key_part_1,key_part_2),排序或分组一个表。如果所有键值部分跟随DESC,键以倒序被读取。 
在一些情况中,一个查询能被优化来检索值,不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀,为了更快,值可以从索引树被检索出来。
假定你发出下列SELECT语句:

mysql> select * FROM tbl_name WHERE col1=val1 AND col2=val2;如果一个多列索引存在于col1和col2上,适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上,优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。
一般动态设置tmp_table_size的大小的时候,要使用:

set global tmp_table_size=64*1024*1024
set global tmp_table_size=64M
#1232 – Incorrect argument type to variable 'tmp_table_size'

MySQL内存表heap使用总结

MySQL内存表使用哈希散列索引把数据保存在内存中,因此具有极快的速度,适合缓存中小型数据库,但是使用上受到一些限制。

1、heap对所有用户的连接是可见的,这使得它非常适合做缓存。

2、仅适合使用的场合。heap不允许使用xxxTEXT和xxxBLOB数据类型;只允许使用=和<=>操作符来搜索记录(不允许& lt;、>、<=或>=);不支持auto_increment;只允许对非空数据列进行索引(not null)。
注:操作符 “<=>” 说明:NULL-safe equal.这个操作符和“=”操作符执行相同的比较操作,不过在两个操作码均为NULL时,其所得值为1而不为NULL,而当一个操作码为NULL时,其所得值为0而不为NULL。

3、一旦服务器重启,所有heap表数据丢失,但是heap表结构仍然存在,因为heap表结构是存放在实际数据库路径下的,不会自动删除。重启之后,heap将被清空,这时候对heap的查询结果都是空的。

4、如果heap是复制的某数据表,则复制之后所有主键、索引、自增等格式将不复存在,需要重新添加主键和索引,如果需要的话。

5、对于重启造成的数据丢失,有以下的解决办法:
a、在任何查询之前,执行一次简单的查询,判断heap表是否存在数据,如果不存在,则把数据重新写入,或者DROP表重新复制某张表。这需要多做一次查询。不过可以写成include文件,在需要用该heap表的页面随时调用,比较方便。
b、对于需要该heap表的页面,在该页面第一次且仅在第一次查询该表时,对数据集结果进行判断,如果结果为空,则需要重新写入数据。这样可以节省一次查询。
c、更好的办法是在mysql每次重新启动时自动写入数据到heap,但是需要配置服务器,过程比较复杂,通用性受到限制。
蓝草目前采用的是第二种办法。

6、一些预期可能用到的sql语句

//如果表存在,则删除
DROP TABLE IF EXISTS `abc`;
//复制整张表xyz为heap表abc(包含所有数据)
CREATE TABLE `abc` type=heap select * from `xyz`;
//添加主键id
ALTER TABLE `abc` ADD PRIMARY KEY (`id`);
//添加索引username
ALTER TABLE `abc` ADD INDEX `abc` (`username`);

MYSAIM 写入会加锁导致读取&写入等待

对MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;

对 MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作;

MyISAM表的读操作与写操作之间,以及写操作之间是串行的!

当一个线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新操作。

其他线程的读、写操作都会等待,直到锁被释放为止

MySQL体系结构

image.png

image.png

thread pool是在连接层处理

image.png

这里的授权是这个用户对某个表某个列是否有权限

image.png

image.png

查询缓存是全局锁 不建议使用查询缓存

关闭查询缓存:

query_cache_type=0

query_cache_size=0

2个参数都要设置

 

mysql proxy 是在最开始的连接层

handlersocket,可以绕过连接层,sql层,直接操作Innodb存储引擎所以效率非常高,后来基本上都不使用了,NOSQL流行起来了

经典的mysql体系结构图

2.jpg

  • 最前端的是各种语言与mysql相互连接的API或者是接口协议

  • 连接的pool,mysql不支持 只是用这个来表示 可以用自己开的proxy 或者是官方的proxy实现

  • SQL Interface :接收用户所有的sql指令

  • 解析器(Parser):把sql命令解析 (YACC这个语法解析器来解析sql语法)

    • 将SQL语句分解成数据结构,并将这个结构传递到后续步骤,以后SQL语句的传递和处理就是基于这个结构的。

    • 如果在分解构成中遇到错误,那么就说明这个sql语句是不合理的。

  • Optimizer查询优化器

  • 查询缓存

    • query_cache, key_bufer,  innodb_buffer

  • 再下面一层是存储引擎层-插拔式

Cache&Buffer的组成

image.png

上面的框:tmp_table_size & max_heap_table_size 线程创建分配 这个是会话级的内存结构

下面的框:全局内存,程序启动就分配,只分配一次,全局公用

PGA如果分配过高,可能会导致OOM(内存溢出)

mysql使用总内存=global buffers + thread_buffers

image.png

image.png

MySQL innodb引擎和myisam的引擎执行对比测试

http://www.itpub.net/thread-1902289-1-1.html

MySQL 利用硬件资源特点

CPU的利用特点

  • <5.1 多核心支持较弱

  • 5.1 可以利用4个核

  • 5.5 可以利用24个核

  • 5.6 可以利用64个核

  • 每个连接对应一个线程,每个并发query只能使用到一个核

内存利用特点:

  • 类似ORACLE的SGA,PGA模式,注意PGA不宜分配过大

  • 内存管理简单,有效。在高TPS,高并发环境下,可增加物理内存以减少物理IO,提高并发性能

  • 官方分支锁并发竞争比较严重,MariaDB,Percona进行优化

  • 有类似ORACLE library cache的query cache,但效过不佳,建议关闭

  • 执行计划没有缓存(类似ORACLE library cache)

  • 通常内存建议按热点数据总量的15%-20%来规划,专用单实例则可以分配物理内存的50%~70%左右

  • 类似K-V简单数据,采用memcached,Redis等NOSQL来缓存

磁盘

  • undo log的I/O特征:顺序写,随机读

  • Redo log,Binlog的I/O特性:顺序写,顺序读

  • 数据文件的I/O特性:随机写,随机读

  • OLTP业务以随机IO为主,建议加到内存,尽量合并随机IO为顺序IO

  • OLAP业务以顺序IO为主,极大内存的同时增加硬盘数量提高顺序IO性能

  • MyISAM是堆组织表(HOT),InnoDB是索引组织表(IOT)

  • InnoDB相比MyISAM更消耗磁盘空间