每日归档： 2017年5月25日

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

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

MyISAM表的读操作与写操作之间，以及写操作之间是串行的！

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

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

待学习

thread pool是在连接层处理

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

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

关闭查询缓存：

query_cache_type=0

query_cache_size=0

2个参数都要设置

 

mysql proxy 是在最开始的连接层

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

经典的mysql体系结构图

• 最前端的是各种语言与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的组成

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

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

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

mysql使用总内存=global buffers + thread_buffers

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

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

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更消耗磁盘空间

记录一下 给自己设置个锚点 以后补充内容

metadata lock5.5.3 引入

当事务访问处理过程中执行DDL操作将会等到metadata lock执行完毕才能操作

如果没有访问这个表 是可以被ddl操作的

没数据的情况下可以ddl操作

在事务里不要用DDL 有隐式提交 

坑：会话2执行drop 操作 wait 再开会话3执行select也是操作不了的 要等会话2的drop执行完了才能查询

所以很容易把线上库卡住

解决办法：kill掉 drop

线上DB不要轻易做alter table 

在生产中做DDL操作时请冷静思考一下

演示：

begin这里只是为了模拟事务

如果事务中一个sql跑的很慢也会出现这种情况

我开启了2个会话

在会话1里申明一个显示的事务

在会话2里drop leo_copy表

就会看到这个时候drop不掉le_copy表

state是“Waiting for table metadata lock”