mysql系统结构_3_Mysql_Learning_Notes
存储层,内存结构
- 全局(buferpool)
- 连接/会话(session)
- 针对每个会话/线程分配
- 按需动态分配,查询结束后释放
- 用于处理(缓冲,中转)查询结果
- 每个会话的缓冲区大小都不一样
mysql 内存占用主要分层情况
引擎层(内存):
- innodb buffer
- innodb log buffer
- key buffer(是myisam使用,具体参数为myisam_sort_buffer_size)
mysql server 层(内存):
- query cache(默认禁用)
- table(def)cache(全局表的缓存)
- 首先尝试从table cache中取table
- 当找到的TABLE实例是nam-locked的，或者一些线程正在flush tables，我们需要等待，直到锁释放
- 如果不存在这样的TABLE，我们需要创建TABLE，并将其加入到cache中这些操作都需要全局锁：LOCK_open,来保护table cache和磁盘上的表定义
- thread cache
- mdl cache (metadata_locks_cache_size)
连接/会话层(内存):
- net/read/join/sort/bulk insert buffer
- tmp/heap table (系统产生的临时表和用户创建的)
binlog cache
mysqld 进行消耗内存估算因素(大多数不会把所有算满,只是做最极端情况):
- global buffers(类似于SGA):
- innodb buffer pool
- innodb log buffer
- key buffer
- query cache
- table cache
- thread cache
- 会话/线程级分配的all thread buffers(类似于PGA)
- max_threads * (read buffer+
- read rnd buffer+
- sort buffer+
- join buffer+
- tmp table+
- binlog cache)
- 有时系统提示:buffer pool设置太大不能启动,这是一个通用报错,好多时候可能是因为版本问题等.
- 如何查看buffer pool 是否够用?
- show engine innnodb status,结果中专门有一段是:"BUFFER POOL AND MEMORY",可以从free buffers.
- show global status like 'innodb%buffer%';中可以innodb_buffer_pool_pages_free<10或innodb_buffer_pool_wait_free>0就代表严重不足.
- show global status like '%table%';中opened_tables和open_tables的数是不差距很大,如果大就代表table cache不够用,监控时,主要观测固定周期的opened_tables数量的增加情况.
- show global status like '%thread%';中thread_created、thread_connected的数是不差距很大,如果大就代表thread cache不够用.可以观测固定周期的thread_created数量的增加情况.
- show global status like '%sort%merg%';中sort_merge_passes的数量.
- show global status like '%tmp%table%';中created_tmp_tables的数量.更严重的是created_tmp_disk_tables
- 两个容易被设置很大的内存选项:
- 都是session级
- max_heap_table_size限制MEMORY表的最大容量,不管其他执行SQL产生的临时表,若内存不够用,则不允许写入新的数据,MEMORY表也不会转成磁盘表,只会告警超限后拒绝写入.
- tmp_table_size 不限制MEMORY表最大容量,如果执行SQL产生临时表超过tmp_table_size或max_heap_table_size,则会产生基于磁盘的临时表.
- 这2个选项特别容易分配较大,若有需要,可临时调大,不要修改全局值.
- 观察SQL执行过程(有没有创建临时等):
1.设置set profiling=1&set profiling_history_size=2
2.执行SQL(select benchmark(100000,pow(2,10))😉
3.use information_schema;
3.select Query_ID,state,DURATION from PROFILING order by query_id desc limit 1;(8.0以前可以直接用show profiles;查询)
root@localhost [information_schema]>select benchmark(100000,pow(2,10));
+-----------------------------+
| benchmark(100000,pow(2,10)) |
+-----------------------------+
| 0 |
+-----------------------------+
1 row in set (0.02 sec)
root@localhost [information_schema]>select Query_ID,state,DURATION from PROFILING order by query_id desc limit 1;
+----------+-------+----------+
| Query_ID | state | DURATION |
+----------+-------+----------+
| 3 | init | 0.000024 |
+----------+-------+----------+
1 row in set, 1 warning (0.00 sec)
root@localhost [information_schema]>show profiles;
+----------+------------+------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------------------------+
| 3 | 0.01043275 | select benchmark(100000,pow(2,10)) |
| 4 | 0.00082200 | select Query_ID,state,DURATION from PROFILING order by query_id desc limit 1 |
+----------+------------+------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
