myrocks统计信息

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: --- title: MySQL ・ myrocks ・ myrocks统计信息 author: 张远 --- # 概述 mysql查询优化主要是在代价统计分析的基础上进行的。合理的代价模型和准确的代价统计信息决定了查询优化的优劣。myrocks基于mysql5.6, 目前的代价模型依赖的主要因素是IO和CPU,mysql5.7及以上的版本代价模型做了较多改进,具体可以参考[这里](

title: MySQL ・ myrocks ・ myrocks统计信息

author: 张远

概述

mysql查询优化主要是在代价统计分析的基础上进行的。合理的代价模型和准确的代价统计信息决定了查询优化的优劣。myrocks基于mysql5.6, 目前的代价模型依赖的主要因素是IO和CPU,mysql5.7及以上的版本代价模型做了较多改进,具体可以参考这里
IO主要跟数据量和缓存相关,而CPU主要跟参与排序比较的记录数相关。 因此mysql5.6的统计信息的指标主要是数据量和记录数。例如:
table scan:全表扫描统计信息包括数据量和记录数。
index scan:索引统计信息,索引键值分布情况,即cardinality。
range scan:索引范围扫描统计信息,一定范围内的记录数和数据量。

统计信息

mysql5.6 代价计算都是在server层完成,且代价只关心引擎层的数据量和行数,没有考虑不同引擎存储方式的差异,其代价也会存在差异。相对来说,5.7的代价统计方式更为合理。
对server层来说,不同存储引擎都应提供以下统计信息

  • 索引的大小
  • 索引的总行数
  • 索引的键值分布, 不同长度前缀的键值分布
  • 一定范围内的记录数

下面分别介绍innodb和rocksdb的统计信息

InnoDB统计分析

统计信息存储

innodb的统计信息可以通过下列表查询

information.statistics
mysql.innodb_table_stats
mysql.innodb_index_stats 

实际上innodb的统计信息持久化在mysql.innodb_table_stats和mysql.innodb_index_stats这两个表中

CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0

 CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0

统计方法

  • 索引大小

      从segment描述项直接得到索引占用的page数(btr_get_size),索引总数据量=总page数*page大小。 索引大小的计算是比较精确的。
  • 索引键值分布

      通过扫描所有数据的方式来统计键值分布虽然得到的数据是准确的,但是非常耗时。因此innodb是通过采样的方式来实现的,参数innodb_stats_persistent_sample_pages、innodb_stats_sample_pages  、innodb_stats_transient_sample_pages可以控制采样的page数。一般来说采样的page越分散,数据越准确。  
    
     采样有两种方式,transient方式和persistent方式。
      1) transient方式:快速但不精确(dict_stats_update_transient)
       从根开始每层随机取一条记录到下一层,直到叶子节点。这样采样得到page过于随机,采样page可能出现比较集中的情况,极端情况下多次采样的page有可能是重复的。

    屏幕快照 2016-12-07 上午11.50.07.png

  2) persistent方式:慢但相对精确(dict_stats_update_persistent)
  presistent方式分为两个阶段。第一阶段,找到一个合适的层次(非叶子层)用于分段,这个层次的不同值个数须>=10*采样页个数即N_DIFF_REQUIRED(index))。第二阶段,在找到的层次上进行分段,分段个数为N(N<=采样数),再从每个分段随机取记录向下层找采样页,如果下层节点所有记录都相等,那么采样可以提前结束,不需要一直向下找到叶子节点,因为叶子节点中记录必定也是相同的。
  persistent方式采样比较分散,但第一阶段分段可能比较耗时,如果索引区分度不高,可能需要到Level=1层才分段。
  ![innodb persistent stat.png](http://ata2-img.cn-hangzhou.img-pub.aliyun-inc.com/762e2938771669fe073aeb6f61a8c34a.png)
  遍历采样页可以得到采样页的键值分布情况,从segment描述项可以得到叶子节点page数,再根据叶子节点page数和采样页比例可以得出最终的键值分布情况。
  • 总行数

       前面已经计算出主键索引的分布情况, 总行数=主键不同值的个数。
      
  • 范围统计

      范围统计,先从B树中查找起始值和结束值,并记录查找路径,从而每层的范围能够确定下来。

    有一个规律是,上层范围内的记录数等于下层范围内的page数

每层最多读取10个page,此层每页记录平均数=读取的记录数/读取的page数。
假如此层范围内page数>10, 那么范围内的记录数=此层每页记录平均数*上层的范围内的记录数。
下层范围内的记录数依赖于上层范围内的记录数。这样每层计算直到叶子层。
innodb records in range.png

统计信息更新

以下情况会触发统计信息更新

  • analyze table
  • 距离上一次更新统计信息,发生变化的行数超过一定数值时自动更新(transient:1/16, persistent :1/10)
  • create table/truncate table 会初始化统计信息
  • 查询information_schema.tables information_schema.statistic(innodb_stats_on_metadata=ON)

Rocksdb统计分析

统计信息存储

从server层来看,rocksdb统计信息存储在rocksdb数据字典NDEX_STATISTICS中

key: Rdb_key_def::INDEX_STATISTICS(0x6) + global_index_id
value: version, {materialized PropertiesCollector::IndexStats}

实际包含以下信息

struct Rdb_index_stats
{
 ......
  GL_INDEX_ID m_gl_index_id;
  int64_t m_data_size, m_rows, m_actual_disk_size;
  int64_t m_entry_deletes, m_entry_single_deletes;
  int64_t m_entry_merges, m_entry_others;
  std::vector<int64_t> m_distinct_keys_per_prefix;
 ......
}

NDEX_STATISTICS并没有像innodb统计信息一样提供mysql 下的表来查询,但我们仍可以从information_schema.statistic查看部分统计信息。

从rocksdb层来看,统计信息在每个SST file meta中都单独保存了自己的统计信息
rocksdb sst indexstat meta.png

而数据字典NDEX_STATISTICS的数据是汇总了memtable和所有sstable统计信息后的数据。

统计方法

memtable 每插入一行数据会统计行数(num_entries_)和数据量(data_size_)
memtable flush时会将SST 统计信息持久化到SST的meta中。
compact时新的统计信息也会持久化到新生成的SST的meta中。

  • 范围分布

    范围分布需从memtable和sstable中查找

    rocksdb records in range.png

    查找memtable(skiplist),一个估算规则是, 下层范围内节点数=上层节点数*branching_factor。根据此规则可以估算memtable范围内的数据。

skiplist in range.png
相关代如下

template <typename Key, class Comparator>
uint64_t SkipList<Key, Comparator>::EstimateCount(const Key& key) const {
  uint64_t count = 0;

  Node* x = head_;
  int level = GetMaxHeight() - 1;
  while (true) {
    assert(x == head_ || compare_(x->key, key) < 0);
    Node* next = x->Next(level);
    if (next == nullptr || compare_(next->key, key) >= 0) {
      if (level == 0) {
        return count;
      } else {
        // Switch to next list
        count *= kBranching_;
        level--;
      }
    } else {
      x = next;
      count++;
    }
  }
}

查sstable,先定位每层范围涉及的sstable,再估算范围内的数据大小。如果某个sstable全包含在范围内,则大小可以直接从sstable 的meta中获取;如果sstable只是半包含,那么需要计算范围在sstable中的offset,从而得到sstable中被包含的数据大小。
screenshot.png

 算出每层的范围内的数据大小,汇总得到范围内的总大小。
 范围内的总行数=范围内的sstable总大小*sstable总行数/sst总大小 + memtable范围内的总行数。

官方代码存在bug,已提交给官方,详见这里

  • 总行数

       总行数=memtable中行数+sstable中行数
       memtable中行数估算方法同上一节
    

官方代码实现假设记录大小为100字节(ROCKSDB_ASSUMED_KEY_VALUE_DISK_SIZE),stats.records = sz/ROCKSDB_ASSUMED_KEY_VALUE_DISK_SIZE; 实际没有必要,实际上是可以通过上面的方法估算的。

   sstable中行数只需要从meta中获取并汇总即可。
   而实际上如果每此从memtable估算行数还是有一定开销的。所以,官方在仅有memtable而没有sstable的情况下才估算memtable的行数。而对于memtable和sstable共存的情况则只考虑sstable,忽略memtable中行数。

这里应该可以优化,比如可以考虑immutable memtable的行数

  • 总大小

       不需要统计memtable,只需要汇总sstable meta中的大小。
    
  • 键值分布

       每个sstable meta有键值分布信息,只需要汇总即可。
    

这里也存在一个bug

   对于memtable,如果仅有memtableer没有sstable,那么键值分布只是简单的给了初始值。
    // Fake cardinality implementation. For example, (idx1, idx2, idx3) index
    // will have rec_per_key for (idx1)=4, (idx1,2)=2, and (idx1,2,3)=1.
    // rec_per_key for the whole index is 1, and multiplied by 2^n if
    // n suffix columns of the index are not used.
    x = 1 << (k->actual_key_parts-j-1);

而对于memtable和sstable共存的情况则只考虑sstable,忽略memtable的键值分布

统计信息更新

  • 实例启动时会从数据字典INDEX_STATISTICS读取并初始化所有索引统计信息。
  • analyze table 汇总memtable和所有sstable的统计信息,并持久化到数据字典INDEX_STATISTICS。
  • flush memtable/compact 都会更新内存统计信息,并不持久化。
    flush memtable 新文件的统计信息会merge加入内存统计信息中。
    compact时会去掉老文件的统计信息,同时加上新生成文件的统计信息。
    屏幕快照 2016-12-07 上午11.50.29.png
  • 后台线程会定时持久化统计信息到数据字典INDEX_STATISTICS

总结

rocksdb和innodb统计信息有很多相似之处,但rocksdb sstable单独维护了统计信息,因此rocksdb的统计信息收集比innodb更快也更精确。同时,我们也看到了rocksdb的统计信息还有需要改进的地方,官方也逐步在完善。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6月前
分区表统计信息收集
分区表统计信息收集
44 1
|
1月前
|
SQL 算法 关系型数据库
浅析MySQL优化器统计信息
本文基于MySQL 8.0.34版本的源代码,详细介绍了MySQL中统计信息的计算和更新机制。文章首先概述了`records_per_key`统计信息在代价估计和Join Reorder算法中的重要性,接着了InnoDB统计信息的存储和计算方法,包括表级和索引级的统计信息。文章还介绍了统计信息的采样算法,特别是重要性采样在减少估计方差中的应用。此外,文章讨论了统计信息的更新时机,包括手动更新和自动更新。最后,文章简要介绍了直方图和其它统计信息,如表在内存中的占比估计,并通过实例展示了如何使用optimizer trace来分析查询优化过程。希望本文能帮助读者更好地理解MySQL的优化器。
|
存储 机器学习/深度学习 算法
一文带你了解MySQL之InnoDB统计数据是如何收集的
我们前边唠叨查询成本的时候经常用到一些统计数据,比如通过show table status可以看到关于表的统计数据,通过show index可以看到关于索引的统计数据,那么这些统计数据是怎么来的呢?它们是以什么方式收集的呢?本章将聚焦于InnoDB存储引擎的统计数据收集策略,看完本章后家就会明白为啥前边老说InnoDB的统计信息是不精确的估计值了
570 0
|
存储 关系型数据库 索引
myrocks记录格式分析
--- title: MySQL · myrocks · myrocks记录格式分析 author: 张远 --- # 概况 rocksdb作为KV存储引擎,那么myrocks记录最终会以kv的形式存储在rocksdb中。MySQL中的表一般由若干索引组成, 在innodb存储引擎中,每个索引对应一颗B树,而在rocksdb存储引擎中,索引对应于rocksdb中一段连续范围的数据。
3914 0
|
存储 关系型数据库 MySQL
|
数据库 索引 数据可视化
如何查看表和索引的统计信息
原文:如何查看表和索引的统计信息     这几天要求做一个服务器的统计信息,主要针对表和索引。下面我就简单分享几个查询数据表和索引统计信息的方法: 1.使用T-SQL 语句实现: select schema_name(t.
1204 0
|
关系型数据库 MySQL 索引
|
监控 关系型数据库 MySQL
MyRocks监控信息
--- title: MySQL · myrocks · myrocks监控信息 author: 张远 --- rocksdb本身提供了丰富的监控信息,myrocks通过information_schema下的表和show命令等将这些信息展示出来,下面主要以示例的形式来简单介绍下 先创建测试表 ``` CREATE TABLE t1 (a INT, b CHAR(8), pk
2228 0