查看内容

mysql数据库引擎调优

  • 2019-12-12 14:15
  • 新浦京计算机网络
  • Views

新浦京澳门娱乐, 

索引中的限制

• Defragmenting tables

2、Query Cache失效问题(当表的更新频繁时会造成非常高的失效率

 

        4. 通过打开 delay_key_write 功能,减少 IO 同步的操作,提高写入性能。

• Using the SHOW ENGINE command

        这里我的 init file 中首先设置了两个 Key Cache ( hot cache 和 cold cache )各为 16M ,然后分别将 top_message 这个变动很少的表的索引 Cache 到 Hot Cache ,再将 event 这个变动非常频繁的表的索引Cache 到了 Cold Cache 中,最后再通过 LOAD INDEX INTO CACHE 命令预加载了 top_message,groups 这两个表所有索引的所有节点以及 event 和 user 这两个表索引的非叶子节点数据到 Key Cache 中,以提高系统启动之初的响应能力。

• Compressing tables

    通过Buffer Pool 的实时状态信息来确定InnoDB的Buffer Pool的使用是否高效:

innodb_fast_shutdown  减少关机时间;

    net_buffer_length(网络传输缓存):默认16KB基本够用

作者 桔子

        >show veriables like 'log_slow%';

• Monitoring logfiles

    SET UNIQUE_CHECK=0/1

• Preloading key caches

MySQL 官方建议在比较繁忙的系统上一般可以设置三个 Key Cache :

--------------------innodb---------------

LOAD INDEX INTO CACHE example.top_message,example.event IGNORE LEAVES

myisamlog 日志变化分析。.  

    参数合理性判断指标:

• Tuning your tables for performance

        key_read_requests  Cache Block被请求读取的总次数

 

  www.2cto.com  

• Other parameters to consider

    #查看query 6所使用的CPU IO资源情况

myisampack 压缩表。

 

innodb_lock_wait_timeout 默认锁超时;

        key_blocks_unused 目前未被使用的Cache Block数目

myisam_ftdump 分析全文索引。  

    uptime              启动时间

  www.2cto.com  

        除此之外,我们还可以通过相同的方式打开和关闭 innodb_tablespace_monitor, innodb_lock_monitor, innodb_table_monitor 这三种监控功能

• Monitoring tablespaces

 

---------------myisam----------------

    com_delete       删除操作次数

 

    com_update     更新操作次数

 

        >show variables like 'long_query%';

 

    Innodb 存储引擎的物理存储结构:

 

    2、MySQL Cluster

mysql数据库引擎调优

新浦京澳门官网,一个 Cold Cache 使用 20% 的大小用来存放更新很频繁的表的索引;

myisamchk 表性能分析。  

    >show status like 'Qcache%';

• Monitoring the buffer pool

 

 

        持续获取状态信息的方法: create table innodb_monitor(a int) engine=innodb; 

 

    内存分配为如下几大部分:

• Using the MyISAM utilities

 

 

        read_rnd_buffer_size : 512KB

 

MyISAM引擎优化

 

    com_select        查询操作次数

 

查询效率测试工具 mysqlslap

  www.2cto.com  

        read_buffer_size : 1MB

• Optimizing disk storage  

        3. 在执行 CREATE INDEX 或者 REPAIR TABLE 等需要大的排序操作的之前可以通过调整 session 级别的 myisam_sort_buffer_size 参数值来提高排序操作的效率。

 

性能调优——Query Cache

 

    InnoDB 性能监控

• Other parameters to consider:

一个 Warm Cache 使用剩下的 60% 空间,作为整个系统默认的 Key Cache ;

• Storing a table in index order

    Thread_Cache_hit = (Connections - Threads_created) / Connections * 100%;

---------------myisam---------------- Optimizing disk storage Tuning your tables for performance ANALYZE TABLE, OPTIMIZE TABLE,REPAIR TABLE。 Using the...

    4、Innodb Buffer Pool 最大可用量: 8GB - 800MB - 2GB - 1.5GB = 3.7GB

• Using INFORMATION_SCHEMA tables

MySQL系统调优及问题查找

• Monitoring the key cache

    MyISAM引擎适用场景:以读为主的非事务性数据系统,对数据的准确性要求不高时有优异的性能表现。

 

        key_write_requests,  Cache Block被请求修改的总次数

innodb_thread_concurrency 调整并发线程0-->独立硬盘分区+cpu内核数;

    慢查询

 

    4、使用不等于( != 或 <>) 的时候无法使用索引

• Using multiple key caches

    3、在 Procedure , Function 以及 Trigger 中的 Query 不能被 Cache ;

 

SET GLOBAL hot_cache.key_buffer_size=16777216

批量导入数据时候: AUTOCOMMIT;暂时停止外键和唯一值限制。

        key_cache_division_limit: LRU链表中的Hot Area和Warm Area的分界值(范围1-100),系统默认100,及只有Warm Cache。

• Using InnoDB monitors

    OPTIMIZE TABLE table1[,table2...]

  ANALYZE TABLE, OPTIMIZE TABLE,REPAIR TABLE。

  www.2cto.com  

    禁用/启用索引

一个 Hot Cache 使用 20% 的大小用来存放使用非常频繁且更新很少的表的索引;

 

    Thread Cache 命中率:(应该保持在90%以上)

MySQL 高可用性方案

        key_cache_aeg_threshold: 控制 Cache Block从Hot Area降到 Warm Area的限制

  www.2cto.com  

    EXPLAIN select * from group_message 

        2. 设置 myisam_max_[extra]_sort_file_size 足够大,对 REPAIR TABLE 的效率可能会有较大改善。

    FORCE INDEX(idx_group_message_author_subject) 

 

    性能参数:

    ANALYZE TABLE table1 [,table2 ...]

    >show status like '%thread%';

    com_insert        插入操作次数

    执行一些SQL语句后就可以查看query的profile 信息

        5. 通过调整 bulk_insert_buffer_size 来提高 INSERT...SELECT... 这样的 bulk insert 操作的整体性能, LOAD DATA INFILE... 的性能也可以得到改善。当然,在设置此参数的时候,也不应该一味的追求很大,很多时候过渡追求极端反而会影响系统整体性能,毕竟系统性能是从整体来看的,而不能仅仅针对某一个或者某一类操作。

        Key_buffer_write_HitRatio = ( 1- Key_writes / key_Write_requests ) * 100%

 

        thread_statck : 512KB

Profiling的使用

    >show status like 'Innodb_buffer_pool_%';

    应对措施:

    Innodb 存储引擎和 MyISAM 存储引擎最大区别主要有四点,第一点是缓存机制,第二点是事务支持 ,第三点是锁定实现,最后一点就是数据存储方式的差异。在整体性能表现方面, Innodb 和 MyISAM 两个存储引擎在不同的场景下差异比较大,主要原因也正是因为上面这四个主要区别所造成的。  www.2cto.com  

        3、对那些Result Set较大的的Query要么使用SQL_NO_CACHE,强制不使用CACHE,或者通过设置query_cache_limit参数来控制query中cache的最大Result Set,系统默认为1M,大于此设定值的Result Set将不会Cache。

    >show status like 'connections';

[email protected]:~$ cat /usr/local/mysql/etc/init.sql

    其他可以优化的地方    

    假设是一台单独给 MySQL 使用的主机,物理内存总大小为 8G , MySQL 最大连接数为 500 ,同时还使用了 MyISAM 存储引擎,这时候我们的整体内存该如何分配呢?

    show_queries    慢查询次数

    2、 所有子查询中的外部查询 SQL 不能被 Cache ;

 

        1. 通过 OPTIMIZE 命令来整理 MyISAM 表的文件。这就像我们使用 Windows 操作系统会每过一段时间后都会做一次磁盘碎片整理,让系统中的文件尽量使用连续空间,提高文件的访问速度。 MyISAM 在通过 OPTIMIZE 优化整理的时候,主要也是将因为数据删除和更新造成的碎片空间清理,使整个文件连续在一起。一般来说,在每次做了较大的数据删除操作之后都需要做一次 OPTIMIZE 操作。而且每个季度都应该有一次 OPTIMIZE 的维护操作。

    ALTER TABLE table_name ENGINE = Innodb;

 

        Key_buffer_read_HitRatio = ( 1- Key_reads / key_read_requests ) * 100%

        2、对那些大部分时候都是静态的数据指定SQL_CACHE,使用CACHE。

    InnoDB 使用的 Clustered Index,索引和数据绑定在一起,重排序是不现实的.所以不支持 MyISAM 式的 OPTIMIZE,而是绑定到了ALTER TABLE 命令上面.可以通过执行以下语句来整理碎片,提高索引速度:

        join_buffer_size : 1MB

    1、 5.1.17 之前的版本不能 Cache 帮定变量的 Query ,但是从 5.1.17 版本开始, Query Cache 已经开始支持帮定变量的 Query 了;

性能调优——其他常用优化

 

    max_user_connections(每个用户允许的最大连接数):一般不做限制

    1、MyISAM引擎索引长度总和不能超过1000字节

 

上一篇:从MYSQL到JDBC(一) 下一篇:没有了