mysql 高性能2

42.mysql 配置的工作原理 [代码片段] 43.变量导致的副作用: [代码片段] 44.内存使用 [代码片段] 系统的glibc库也可能限制每次分配内存的大小;innodb_buffer_pool

42.mysql 配置的工作原理

查看使用配置文件路径:which mysqld —verbose —help | grep -A 1 ‘Default options’ 变量 query_cache_size 全局变量 sort_buffer_size 默认全局,但是每个线程也可以单独设置 join_buffer_size 默认全局,但是每个线程也可以单独设置 set sort_buffer_size= value(局部); set GLOBAL sot_buffer_size =value(全局) table_cache:表可以被缓存的数量 key_buffer_size:字节为单位

43.变量导致的副作用:

key_buffer_size:一次性可以为键缓冲区分配所指定的空间,使用时才会分配 table_cache_size: 下次线程打开表时,才会起作用;tableNum44.内存使用 1.确定可以使用内存的上限 2.确定每个连接 mysql 需要使用多少内存, 3.确定操作系统需要使用多少内存才够 4.把剩下的内存全部交给 mysql 缓存

系统的 glibc库也可能限制每次分配内存的大小;innodb_buffer_pool

45.缓存

Innodb 缓冲池 innodb 日志文件和 myisam 数据的操作系统缓存 myisam 键缓存 查询缓存 无法收工配置的缓存:二进制日志和表定义文件的操作系统缓存

46.innodb 缓冲池 buffer pool(innotop 监控缓冲)

缓存,索引,行的数据,自适应哈希索引,插入缓冲,锁,以及其他内部的数据结构 缓冲池很大时,重启也许需要很久,可以用 percona server 功能重新载入缓冲池的页,从而节省时间(或者进行全表扫描,索引扫描) init_file 实现全表扫描,索引扫描;文件中包含多条 sql 命令,每一条单独一行,不允许使用注释

46.myisam key cache(键缓存)

在分配 key_buffer_size 之前,需要了解 myisam 实际占用多少磁盘空间 select sum(index_length) from information_schema.tables where engine=‘misaim’; 或者 du -sch `find /path/mysql/data/directory/ -name “*.MYI”` key_buffer_size 不要超过索引的总大小或者不超过操作系统缓存保留总内存的25%-50%,以更小为佳 myisam 的索引都缓存在键缓存中,可以创建多个命名的键缓冲 key_buffer_1.key_buffer_size = 1G key_buffer_2.key_buffer_size = 1G cache index t1,t2 in key_buffer_1;|| load index into cache t1,t2;将 t1,t2缓存在 key_buffer_1 任何没明确指定映射到哪个键缓冲的索引,在 mysql 第一次需要访问.myi 文件时,都会被分配到默认缓冲区

计算缓冲区使用率:

100-((key_block_unused*key_cache_block_size)*100/key_buffer_size)

每秒缓存未命中率:

key_reads : mysqladmin extended-status -r -i 10 | grep key_reads key_reads / uptime

myisam 采用系统缓存来缓存数据文件,通常数据文件比索引大;因此把更多的内存保留给操作系统是有意义的

key block size (键缓存块)

块大小影响了 myisam,操作系统缓存以及文件系统之间的交互

myisam_block_size,key_block_size

48.线程缓存 thread cache

线程缓存保存那些当前没有与连接关联但是准备为后面新的连接服务的线程 thread_cache_size 制定了 mysql 可以保存在缓存中的线程数 观察 threads_connected,设置 thread_cache_size

49.表缓存 table cache

表缓存可以重用资源,表缓存分离为:打开表的缓存(table_open_cache),表定义缓存(table_definition_cache) opened_tables 状态变量很大或者在增长,那可能是因为表缓存不够大,可以增加 table_cache(表创建和删除临时表时,这个都会不停的增长)

inndb. 数据字典(data dictionary)

innodb 打开.ibd 文件的数量也是有限制的,innodb_open_files(设置的足够大)

innodb 打开表和打开文件之间没有直接的练习,为每个 .ibd 文件使用单个全局的文件描述符

myisam,用表缓存来持有打开表的文件描述符

Innodb 事务日志:

使用日志来减少提交事务时的开销

Innodb 用日志把随机的 I/O 变成了顺序 I/O,Innodb 的日志是环形方式写的,
当写到日志的尾部,会重新跳转到开头继续写,但是不会覆盖还没有应用道德数据文件的日志记录

日志文件大小: innodb_log_file_size; innodb_log_files_in_group

修改文件大小:

完全关闭 mysql,将旧的日志文件移动到其他地方保存,然后重新配置参数,重启,,一定要保证 mysql 关闭干净

percona server 的 innodb_recovery_stats 可以帮助从执行蹦贵恢复的立场来理解服务器的工作负载

innodb_log_buffer_size 日志缓冲区(1M-8M) innodb_os_log_written 查看 innodb 对日志文件写出了多少数据 innodb_flush_log_at_trx_commit:控制日志缓冲刷新的频繁程度(会话级变量,UShi 全局变量) 0,日志缓冲写入日志文件,每秒钟刷新,但是事务提交时不做任何事 1,日志缓冲写入日志文件,每次事务提交都刷新到持久化存储 2’日志缓冲写入日志文件,并不刷新, (0,2,区别:如果 mysql 进程挂了,2不会丢失任何事物,如果整个服务器挂了,还是可能会丢失一些事务) ，