福州网站设计外包,长沙网站seo多少钱,山西seo基础教程,wordpress发布文章TPCH 22条SQL语句分析 - xibuhaohao - 博客园
TPCH模型规范、测试说明及22条语句 - zhjh256 - 博客园
TPC-DS 性能比较#xff1a;TiDB 与 Impala-PingCAP | 平凯星辰
揭秘Oracle TPC-H性能优化#xff1a;如何提升数据库查询速度#xff0c;揭秘实战技巧与挑战
引言
T…TPCH 22条SQL语句分析 - xibuhaohao - 博客园
TPCH模型规范、测试说明及22条语句 - zhjh256 - 博客园
TPC-DS 性能比较TiDB 与 Impala-PingCAP | 平凯星辰
揭秘Oracle TPC-H性能优化如何提升数据库查询速度揭秘实战技巧与挑战
引言
TPC-HTransaction Processing Performance Council H-Scale Performance Benchmark是一个广泛使用的数据库性能基准测试用于衡量数据库系统在高并发和大规模数据集上的查询性能。Oracle数据库作为企业级数据库的佼佼者其性能优化一直是数据库管理员和开发者关注的焦点。本文将深入探讨Oracle TPC-H性能优化揭示提升数据库查询速度的实战技巧与挑战。
一、TPC-H基准测试简介
TPC-H基准测试模拟了典型的在线事务处理OLTP场景包括订单处理、库存管理和客户服务等多个方面。它通过一系列复杂的SQL查询来测试数据库的性能。在TPC-H基准测试中查询的性能是衡量数据库系统效率的重要指标。
二、TPC-H性能优化技巧
1. 索引优化
索引是提升查询性能的关键。以下是一些索引优化的技巧
创建索引针对查询中频繁使用的列创建索引例如 CREATE INDEX idx_order_date ON orders (order_date); 监控索引使用情况定期检查索引的使用情况确保索引被有效利用。 SELECT * FROM v$index_usage; 2. 查询优化
查询优化是提升性能的直接方法。以下是一些查询优化的技巧
使用执行计划通过分析执行计划找出查询性能瓶颈。 EXPLAIN PLAN FOR SELECT * FROM orders WHERE order_date 2021-01-01; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 优化子查询将子查询转换为连接查询以提高性能。 SELECT d.department_name, COUNT(e.employee_id) AS employee_count FROM departments d JOIN employees e ON d.department_id e.department_id GROUP BY d.department_name; 3. 物理设计优化
分区表将大表分区可以提高查询性能。 CREATE TABLE orders ( order_id NUMBER, order_date DATE, ... ) PARTITION BY RANGE (order_date) ( PARTITION p1 VALUES LESS THAN (2021-01-01), PARTITION p2 VALUES LESS THAN (2022-01-01), ... ); 触发器和存储过程合理使用触发器和存储过程可以提高数据一致性和查询性能。 CREATE OR REPLACE TRIGGER trig_after_insert_order AFTER INSERT ON orders FOR EACH ROW BEGIN -- 触发器逻辑 END; 三、实战案例
以下是一个实际的TPC-H性能优化案例
假设在TPC-H基准测试中查询Q1找出每个供应商的总订单数的执行时间过长。通过分析执行计划我们发现orders表的supplier_id列未创建索引。在创建索引后查询性能得到了显著提升。
四、挑战与展望
TPC-H性能优化面临以下挑战 数据量庞大随着数据量的增长性能优化变得越来越复杂。 多并发查询在高并发环境下优化查询性能是一项挑战。
未来随着数据库技术的发展我们将有更多高效的工具和方法来提升TPC-H性能。
结语
Oracle TPC-H性能优化是数据库管理员和开发者必须掌握的技能。通过合理的索引优化、查询优化和物理设计优化可以有效提升数据库查询速度。在实际项目中不断总结经验持续优化是提升数据库性能的关键。 TiDB TPC-H 性能对比测试报告 - v5.4 MPP 模式对比 Greenplum 6.15.0 以及 Apache Spark 3.1.1
测试概况
本次测试对比了 TiDB v5.4 MPP 模式下和主流分析引擎例如 Greenplum 和 Apache Spark 最新版本在 TPC-H 100 GB 数据下的性能表现。结果显示TiDB v5.4 MPP 模式下相对这些方案有 2-3 倍的性能提升。
TiDB v5.0 中引入的 TiFlash 组件的 MPP 模式大大幅增强了 TiDB HTAP 形态。本文的测试对象如下
TiDB v5.4 MPP 执行模式下的列式存储Greenplum 6.15.0Apache Spark 3.1.1 Parquet
测试环境
硬件配置
实例类型实例数PD1TiDB1TiKV3TiFlash3
CPUIntel(R) Xeon(R) CPU E5-2630 v4 2.20GHz40 核内存189 GB磁盘NVMe 3TB * 2
软件版本
服务类型软件版本TiDB5.4Greenplum6.15.0Apache Spark3.1.1
配置参数
TiDB v5.4 配置
v5.4 的 TiDB 集群除以下配置项外均使用默认参数配置。所有 TPC-H 测试表均以 TiFlash 列存进行同步无额外分区和索引。
在 TiFlash 的 users.toml 配置文件中进行如下配置 [profiles.default] max_memory_usage 10000000000000
使用 SQL 语句设置以下会话变量 set tidb_isolation_read_enginestiflash; set tidb_allow_mpp1; set tidb_mem_quota_query 10 30;
Greenplum 配置
Greenplum 集群使用额外的一台 Master 节点部署共四台每台 Segment Server 部署 8 Segments每个 NVMe SSD 各 4 个总共 24 Segments。存储格式为 append-only / 列式存储分区键为主键。 log_statement all gp_autostats_mode none statement_mem 2048MB gp_vmem_protect_limit 16384
Apache Spark 配置
Apache Spark 测试使用 Apache Parquet 作为存储格式数据存储在 HDFS 上。HDFS 为三节点为每个节点指定两块 NVMe SSD 盘作为数据盘。通过 Standalone 方式启动 Spark 集群使用 NVMe SSD 盘作为 spark.local.dir 本地目录以借助快速盘加速 Shuffle Spill 过程无额外分区和索引。 --driver-memory 20G --total-executor-cores 120 --executor-cores 5 --executor-memory 15G
测试结果
注意
以下测试结果均为 3 次测试的平均值单位均为秒。
Query IDTiDB v5.4Greenplum 6.15.0Apache Spark 3.1.1 Parquet18.0864.130752.6422.534.7661211.8334.8415.6289813.39410.9412.883188.54512.2723.3544925.2361.326.0332.2175.9112.3126625.4586.7111.8244423.12944.1922.4014435.2107.1312.5107112.18112.182.622110.99122.887.979066.99136.8410.1587312.26141.694.793943.89153.2910.487859.82165.044.642626.761711.774.6524344.651812.8764.8764630.27194.758.086254.7208.8915.470168.42124.4439.0859434.83221.237.674764.59 以上性能图中蓝色为 TiDB v5.4红色为 Greenplum 6.15.0黄色为 Apache Spark 3.1.1纵坐标是查询的处理时间。纵坐标数值越低表示 TPC-H 性能越好。 TiDB TPC-H 性能对比测试报告 - v4.0 对比 v3.0
测试目的
对比 TiDB v4.0 和 v3.0 OLAP 场景下的性能。
因为 TiDB v4.0 中新引入了 TiFlash 组件增强 TiDB HTAP 形态本文的测试对象如下
v3.0 仅从 TiKV 读取数据。v4.0 仅从 TiKV 读取数据。v4.0 通过智能选择混合读取 TiKV、TiFlash 的数据。
测试环境 (AWS EC2)
硬件配置
服务类型EC2 类型实例数PDm5.xlarge3TiDBc5.4xlarge2TiKV TiFlashi3.4xlarge3TPC-Hm5.xlarge1
软件版本
服务类型软件版本PD3.0、4.0TiDB3.0、4.0TiKV3.0、4.0TiFlash4.0tiup-bench0.2
配置参数
v3.0
v3.0 的 TiDB 和 TiKV 均为默认参数配置。
变量配置 set global tidb_distsql_scan_concurrency 30; set global tidb_projection_concurrency 16; set global tidb_hashagg_partial_concurrency 16; set global tidb_hashagg_final_concurrency 16; set global tidb_hash_join_concurrency 16; set global tidb_index_lookup_concurrency 16; set global tidb_index_lookup_join_concurrency 16;
v4.0
v4.0 的 TiDB 为默认参数配置。
TiKV 配置 readpool.storage.use-unified-pool: false readpool.coprocessor.use-unified-pool: true
PD 配置 replication.enable-placement-rules: true
TiFlash 配置 logger.level: info learner_config.log-level: info
变量配置
注意
部分参数为 SESSION 变量。建议所有查询都在当前 SESSION 中执行。 set global tidb_allow_batch_cop 1; set session tidb_opt_distinct_agg_push_down 1; set global tidb_distsql_scan_concurrency 30; set global tidb_projection_concurrency 16; set global tidb_hashagg_partial_concurrency 16; set global tidb_hashagg_final_concurrency 16; set global tidb_hash_join_concurrency 16; set global tidb_index_lookup_concurrency 16; set global tidb_index_lookup_join_concurrency 16;
测试方案
硬件准备
为了避免 TiKV 和 TiFlash 争抢磁盘和 I/O 资源把 EC2 配置的两个 NVMe SSD 盘分别挂载为 /data1 及 /data2把 TiKV 的部署至 /data1TiFlash 部署至 /data2。
测试过程 通过 TiUP 部署 TiDB v4.0 和 v3.0。 通过 TiUP 的 bench 工具导入 TPC-H 10G 数据。 执行以下命令将数据导入 v3.0 tiup bench tpch prepare \ --host ${tidb_v3_host} --port ${tidb_v3_port} --db tpch_10 \ --sf 10 \ --analyze --tidb_build_stats_concurrency 8 --tidb_distsql_scan_concurrency 30 执行以下命令将数据导入 v4.0 tiup bench tpch prepare \ --host ${tidb_v4_host} --port ${tidb_v4_port} --db tpch_10 --password ${password} \ --sf 10 \ --tiflash \ --analyze --tidb_build_stats_concurrency 8 --tidb_distsql_scan_concurrency 30 运行 TPC-H 的查询。 下载 TPC-H 的 SQL 查询文件 git clone https://github.com/pingcap/tidb-bench.git cd tpch/queries 查询并记录耗时。 对于 TiDB v3.0使用 MySQL 客户端连接到 TiDB然后执行查询记录 v3.0 查询耗时。对于 TiDB v4.0使用 MySQL 客户端连接到 TiDB再根据测试的形态选择其中一种操作 设置 set session.tidb_isolation_read_engines tikv,tidb; 后再执行查询记录 v4.0 仅从 TiKV 读取数据的查询耗时。设置 set session.tidb_isolation_read_engines tikv,tiflash,tidb; 后再执行查询记录 v4.0 通过智能选择从 TiKV 和 TiFlash 混合读取数据的查询耗时。 提取整理耗时数据。
测试结果
注意
本测试所执行 SQL 语句对应的表只有主键没有建立二级索引。因此以下测试结果为无索引结果。
Query IDv3.0v4.0 TiKV Onlyv4.0 TiKV / TiFlash Automatically17.78s7.45s2.09s23.15s1.71s1.71s36.61s4.10s4.05s42.98s2.56s1.87s520.35s5.71s8.53s64.75s2.44s0.39s77.97s3.72s3.59s85.89s3.22s8.59s934.08s11.87s15.41s104.83s2.75s3.35s113.98s1.60s1.59s125.63s3.40s1.03s135.41s4.56s4.02s145.19s3.10s0.78s1510.25s1.82s1.26s162.46s1.51s1.58s1723.76s12.38s8.52s1817.14s16.38s16.06s195.70s4.59s3.20s204.98s1.89s1.29s2111.12s6.23s6.26s224.49s3.05s2.31s 以上性能图中蓝色为 v3.0红色为 v4.0仅从 TiKV 读黄色为 v4.0从 TiKV、TiFlash 智能选取纵坐标是查询的处理时间。纵坐标越低表示性能越好。
v4.0仅从 TiKV 读取数据即 TiDB 仅会从 TiKV 中读取数据。将该结果与 v3.0 的结果对比可得知TiDB、TiKV 升级至 4.0 版本后TPC-H 性能得到的提升幅度。v4.0从 TiKV、TiFlash 智能选取即 TiDB 优化器会自动根据代价估算选择是否使用 TiFlash 副本。将该结果与 v3.0 的结果对比可得知在 v4.0 完整的 HTAP 形态下TPC-H 性能得到的提升幅度。 【数据库】TiDB 5.3 TPCH 100G测试及思考
一、环境 tpc-H 100G数据 2.8版本 22个场景sql https://github.com/pingcap/tidb-bench/tree/master/tpch/queries 官方
节点数量3 CPUIntel(R) Xeon(R) CPU E5-2630 v4 2.20GHz40 核 内存189 GB 磁盘NVMe 3TB * 2 华为云
kc1.4xlarge.4 节点数量6 CPU16vCPUskunpeng 920 内存64 GB 磁盘极速SSDfio bs1M job10 w8783MiB/s,8782 IOPS 布局tikv和tiflash同一主机*3tidb和pd在同一主机*3绑2node 普通SSD
TaiShan 节点数量6 CPU128 CPUkunpeng 920 内存512 GB 磁盘普通SSDfio bs16k w250MB/s 布局 1、每台主机4块数据SSD共同加到一个tiflash中 2、24*TiDB3*PD24Tikv6Tiflash每个server端单独绑一个node一个node 32CPU 二、参数设置 tiup cluster edit-config t1 tiflash profiles.default.max_memory_usage 10000000000000 set global tidb_allow_mpp1; set tidb_isolation_read_enginestiflash; set tidb_mem_quota_query 10 30; 因为tidb_isolation_read_engines只能session级别通过mysql客户端登陆调用本地脚本方式执行 mysql -h127.0.0.1 -uroot -P4000 -D tpch ! \\. 1.sql ! 执行多次直到sql执行时间趋于稳定 三、测试结果 官方数据 华为云3副本Tiflash 华为云3副本tikvtiflash 普通SSD 6副本tikvtiflash Q1 8.08 6.6 1.68 Q2 2.53 2.48 3.09 Q3 4.84 4.649 15.76 Q4 10.94 5.589 19.1 8.27 Q5 12.27 11.02 6.97 Q6 1.32 1 0.55 Q7 5.91 4.0666 7.59 Q8 6.71 7.1 14.91 Q9 44.19 30.334 12.87 Q10 7.13 4.4 41.04 Q11 2.18 1.771 3.03 Q12 2.88 2.885 2.47 Q13 6.84 4.9 72.62 Q14 1.69 1.5 1.51 Q15 3.29 2.5-2.7 2.5-2.9 13.75 Q16 5.04 1.1 2.07 Q17 11.7 7.9 24.89 Q18 12.87 11.1 14.17 Q19 4.75 3.05 1.38 Q20 8.89 2.4 24.62 Q21 24.44 12.4 15.47 Q22 1.23 1.167 1.4 四、结果说明及一些猜想 Q4 select o_orderpriority,count(*) as order_count from orders where o_orderdate 1995-01-01 and o_orderdate date_add(1995-01-01, interval 3 month) and exists ( select * from lineitem where l_orderkey o_orderkey and l_commitdate l_receiptdate ) group by o_orderpriority order by o_orderpriority; tiflash5.589s tikvtiflashtidb默认配置19.1s explain analyze执行计划默认配置下表lineitem走了cache
Selection_24(Probe) | 3.98 | 14185840 | cop[tikv] | |time:5m56.6s, **loops:16883**, cop_task: {num: 3827, max: 697.5ms, min: 585.8µs, avg: 133ms, p95: 340ms, max_proc_keys: 17867, p95_proc_keys: 15901, tot_proc: 7m13.6s, tot_wait: 1m6.1s, rpc_num: 3827, rpc_time: 8m29s, copr_cache_hit_ratio: 0.24}, tikv_task:{proc max:530ms, min:0s, p80:180ms, p95:280ms, iters:39261, tasks:3827}, scan_detail: {total_process_keys: 21217578, total_process_keys_size: 4216268171, total_keys: 25622316, rocksdb: {delete_skipped_count: 0, key_skipped_count: 20513898, block: {**cache_hit_count: 32099284, read_count: 1750183, read_byte: 29.1 GB**}}} | lt(tpch.lineitem.l_commitdate, tpch.lineitem.l_receiptdate)
在强制tiflash下优化器重组了sql两张表直接做hashjoin
HashJoin_42 | 4471364.76 | batchCop[tiflash] || semi join, equal:[eq(tpch.orders.o_orderkey, tpch.lineitem.l_orderkey)]
一些猜想
1、TiDB优化器中cache hit权重更高 2、TiDB优化器之后是否会像Oracle加入硬件性能参数计算代价参考aux_stats$ Q9 tiflash和tikvtiflash差距不大表supplier在tikv中走的点查非常快 耗时上默认配置总时间更短 其他 AP还是看存储多盘在某些场景下貌似不能弥补性能上的差距 ———————————————— 版权声明本文为博主原创文章遵循 CC 4.0 BY-SA 版权协议转载请附上原文出处链接和本声明。 原文链接https://blog.csdn.net/xyq172291/article/details/122281719
