初窥openGauss-之参数自调优X-Tuner

最新推荐文章于 2024-08-26 16:32:13 发布

openGaussMan

最新推荐文章于 2024-08-26 16:32:13 发布

阅读量379

点赞数 6

文章标签：数据库 openGauss

本文链接：https://blog.csdn.net/2301_79720856/article/details/137080772

版权

初窥 openGauss 之参数自调优 X-Tuner

TPC-H 是一个面向分析型业务(AP)的基准测试，它由一系列热点查询组成，这些热点查询都是高度复杂的，因此执行时间往往都比较长。在本次实验测试中，将手动向数据库加载 TPC-H 数据，并保存在名为 tpch 的数据库中。默认 TPC-H 数据库的表缺少索引，数据库的参数并没有做任何优化，因此执行效率会比较差。本实验比较浅显，使用 openGauss 的参数自调优(X-Tuner：gs_xtuner)功能，对数据库进行参数优化，以提升数据库运行性能，让大家对 X-Tuner 参数自调优有一个初步的了解。

环境信息

OS：CentOS Linux release 7.6.1810

openGauss：2.0.0

CPU：1core

Memory：4GB

测试数据脚本清单如下：

[omm@lab01 ~]$ ls -l ~/tpch-kit-back/
total 1076780
-rw------- 1 omm dbgrp  24196144 Apr 24 15:39 customer.tbl
-rw------- 1 omm dbgrp      3814 Apr 24 15:39 dss.ddl
-rw------- 1 omm dbgrp 753862072 Apr 24 15:39 lineitem.tbl
-rw------- 1 omm dbgrp       287 May 25 10:52 load.sh
-rw------- 1 omm dbgrp      2199 Apr 24 15:16 nation.tbl
-rw------- 1 omm dbgrp 170452161 Apr 24 15:16 orders.tbl
-rw------- 1 omm dbgrp  10553197 Apr 24 15:11 out0
-rw------- 1 omm dbgrp 118184616 Apr 24 15:10 partsupp.tbl
-rw------- 1 omm dbgrp  23935125 Apr 24 15:11 part.tbl
drwx------ 3 omm dbgrp      4096 Apr 24 15:39 queries
-rw------- 1 omm dbgrp       384 Apr 24 15:07 region.tbl
-rw------- 1 omm dbgrp   1399184 Apr 24 15:07 supplier.tbl

1. 配置 pip，并安装 setuptools-rust 模块

[root@lab01 ~]# wget https://bootstrap.pypa.io/get-pip.py
[root@lab01 ~]# python3 get-pip.py
[root@lab01 ~]# pip -V pip 21.1.2 from /usr/local/lib/python3.6/site-packages/
pip (python 3.6)
[root@lab01 ~]# pip install setuptools-rust

2. 安装依赖包

[omm@lab01 xtuner]$ pip install joblib
[omm@lab01 xtuner]$ pip install threadpoolctl

3. 创建数据库并导入数据

-- 创建数据库tpch
[omm@lab01 ~]$ gsql -d postgres -p 26000 -c "create database tpch with encoding='UTF-8';"
-- 创建测试表
 [omm@lab01 ~]$ gsql -d tpch -p 26000 -f ~/tpch-kit-back/dss.ddl
 -- 加载测试数据并统计分析
[omm@lab01 ~]$ vi load.sh
---------------------------------------
for i in `ls *.tbl`; do
table=${i/.tbl/}
    echo "Loading $table..."
    sed 's/|$//' $i > /tmp/$i
    gsql -d tpch -p 26000 -c "TRUNCATE $table"
    gsql -d tpch -p 26000 -c "\\copy $table FROM '/home/omm/tpch-kit-back/$i' CSV DELIMITER '|'"
    gsql -d tpch -p 26000 -c "ANALYZE $table"
done
 ---------------------------------------
sh load.sh

4. 编辑 requirements.txt 文件

[omm@lab01 ~]$ cd /gauss/app/bin/dbmind/xtuner/
[omm@lab01 xtuner]$ vi requirements.txt
---------------------------------------
删除:
    tensorflow>=2.2.0
    keras-rl2
 ---------------------------------------

5. 生成 gs_xtuner 参数调优工具(需要连接外网)

[omm@lab01 ~]$ cd /gauss/app/bin/dbmind/xtuner
[omm@lab01 xtuner]$ python3 setup.py install --user

6. 执行快速推荐命令（基于已经作业执行的信息进行推荐，信息来源 pg_stat_database 等）

[omm@lab01 xtuner]$ gs_xtuner recommend --db-name tpch --db-user omm --host ***.***.***.*** --host-user omm --port 26000
Please input the password of database:
Please input the password of host:
Start to recommend knobs. Just a moment, please.
************************************* Knob Recommendation Report ****************************************
INFO:
+---------------------------------------+----------------------+
|                 Metric                |        Value         |
 +---------------------------------------+----------------------+
|             workload_type             |          ap          |
|         dirty_background_bytes        |          0           |
|          current_locks_count          |         0.0          |
|      current_prepared_xacts_count     |         0.0          |
|         rollback_commit_ratio         |         0.0          |
|         average_connection_age        |       0.004575       |
| checkpoint_proactive_triggering_ratio | 0.00863557858376511  |
|         fetched_returned_ratio        | 0.055316264644388206 |
|             cache_hit_rate            |  0.5028061903026831  |
|              os_cpu_count             |          1           |
|          current_connections          |         1.0          |
|        checkpoint_avg_sync_time       |   1.07037996545769   |
|            write_tup_speed            |   101.161719229361   |
|                used_mem               |     131846656.0      |
|           all_database_size           |   2292057.41015625   |
|      shared_buffer_heap_hit_rate      |  25.917067253117217  |
|            current_free_mem           |       3270760        |
|             temp_file_size            |   3573.07285767967   |
|                 uptime                |   38.3688171772222   |
|              os_mem_total             |       3879956        |
|  checkpoint_dirty_writing_time_window |        450.0         |
|            read_write_ratio           |  47.82294541597867   |
|             read_tup_speed            |   4837.86775193848   |
|             max_processes             |         503          |
|          track_activity_size          |        503.0         |
|          search_modify_ratio          |  658741.9884425476   |
|                ap_index               |         7.5          |
|      shared_buffer_toast_hit_rate     |   76.6304347826087   |
|               block_size              |         8.0          |
|      shared_buffer_tidx_hit_rate      |   82.7893175074184   |
|       shared_buffer_idx_hit_rate      |   97.6601060219748   |
|           enable_autovacuum           |         True         |
|                is_64bit               |         True         |
|                 is_hdd                |         True         |
|              load_average             |  [1.19, 0.82, 0.8]   |
+---------------------------------------+----------------------+
p.s: The unit of storage is kB.
WARN:
[0].
    The number of CPU cores is a little small. Please do not run too high concurrency.
    You are recommended to set max_connections based on the number of CPU cores.
    If your job does not consume much CPU, you can also increase it.
[1].
    The value of wal_buffers is a bit high. Generally, an excessively large value does not bring better performance.
    You can also set this parameter to -1.
    The database automatically performs adaptation.
*********************************** Recommended Knob Settings **********************************************
+---------------------------+-----------+--------+---------+---------+
|            name           | recommend |  min   |   max   | restart |
+---------------------------+-----------+--------+---------+---------+
|       shared_buffers      |   121256  | 72752  |  139448 |   True  |
|      max_connections      |    134    |   15   |   269   |   True  |
|    effective_cache_size   |  2909967  | 121256 | 2909967 |  False  |
|        wal_buffers        |    3789   |  2048  |   3789  |   True  |
|      random_page_cost     |    3.0    |  2.0   |   3.0   |  False  |
| default_statistics_target |    1000   |  100   |   1000  |  False  |
+---------------------------+-----------+--------+---------+---------+
注意：修改该推荐值之前，请确保硬件条件满足，否则可能会造成数据库无法启动的问题。

7. [可选]迭代推荐命令（全局搜索算法，迭代式执行，每轮执行约 2 分钟）

1> 修改配置文件

vi /home/omm/.local/lib/python3.6/site-packages/openGauss_xtuner-2.0.0-py3.6.egg/tuner/xtuner.conf
 -------------------------------------------------
修改如下行：
    max_iterations = 3 （从100轮改为3）
    benchmark_path = /home/omm/queries
------------------------------------------------

2> 执行命令，观察 Reward 数值变化，粉色输出的轮次为当前较优数值

[omm@lab01 ~]$ time gs_xtuner tune --db-name tpch --db-user omm --host localhost --host-user omm --port 26000
Please input the password of database:
Please input the password of host:
Start to recommend knobs. Just a moment, please.
WARN: The database may restart several times during tuning, continue or not [yes|no]:yes
2021-05-26 11:09:12,710: Recorder is starting.
|   iter    |  target   | random... |
-------------------------------------
2021-05-26 11:10:58,017: [0] Current reward is -102.935543, knobs: {'random_page_cost': '2.64'}.
2021-05-26 11:10:58,018: [0] Best reward is -102.935543, knobs: {'random_page_cost': '2.64'}.
2021-05-26 11:10:58,018: [1] Database metrics: [0.6400000000000001, 0.6007798155874045, 0.65].
2021-05-26 11:10:58,018: [1] Benchmark score: -102.899098, used mem: 36444544 kB, reward: -102.935543.
|  1        | -102.9    |  0.6426   |
2021-05-26 11:12:30,939: [1] Current reward is -91.541441, knobs: {'random_page_cost': '2'}.
2021-05-26 11:12:30,941: [1] Best reward is -91.541441, knobs: {'random_page_cost': '2'}.
2021-05-26 11:12:30,941: [2] Database metrics: [0.0, 0.6107552017890537, 2.6].
2021-05-26 11:12:30,942: [2] Benchmark score: -91.504996, used mem: 36444544 kB, reward: -91.541441.
|  2        | -91.54    |  0.003251 |
2021-05-26 11:13:38,617: [2] Current reward is -66.684871, knobs: {'random_page_cost': '2.46'}.
2021-05-26 11:13:38,618: [2] Best reward is -66.684871, knobs: {'random_page_cost': '2.46'}.
2021-05-26 11:13:38,618: [3] Database metrics: [0.45999999999999996, 0.621014394376401, 3.47].
2021-05-26 11:13:38,618: [3] Benchmark score: -66.648426, used mem: 36444544 kB, reward: -66.684871.
|  3        | -66.68    |  0.4565   |
2021-05-26 11:14:53,250: [3] Current reward is -73.748742, knobs: {'random_page_cost': '2.9'}.
2021-05-26 11:14:53,252: [3] Best reward is -66.684871, knobs: {'random_page_cost': '2.46'}.
2021-05-26 11:14:53,252: [4] Database metrics: [0.8999999999999999, 0.6286889335789447, 3.65].
2021-05-26 11:14:53,252: [4] Benchmark score: -73.712297, used mem: 36444544 kB, reward: -73.748742.
|  4        | -73.75    |  0.9016   |
2021-05-26 11:15:58,798: [4] Current reward is -64.467620, knobs: {'random_page_cost': '2.45'}.
2021-05-26 11:15:58,799: [4] Best reward is -64.467620, knobs: {'random_page_cost': '2.45'}.
2021-05-26 11:15:58,799: [5] Database metrics: [0.4500000000000002, 0.633784310797396, 3.45].
2021-05-26 11:15:58,799: [5] Benchmark score: -64.431175, used mem: 36444544 kB, reward: -64.467620.
|  5        | -64.47    |  0.4544   |
2021-05-26 11:16:59,097: [5] Current reward is -59.161970, knobs: {'random_page_cost': '2.43'}.
2021-05-26 11:16:59,099: [5] Best reward is -59.161970, knobs: {'random_page_cost': '2.43'}.
2021-05-26 11:16:59,099: [6] Database metrics: [0.43000000000000016, 0.6393591990442545, 3.91].
2021-05-26 11:16:59,099: [6] Benchmark score: -59.125525, used mem: 36444544 kB, reward: -59.161970.
|  6        | -59.16    |  0.4304   |
2021-05-26 11:18:08,157: [6] Current reward is -67.964937, knobs: {'random_page_cost': '2.39'}.
2021-05-26 11:18:08,158: [6] Best reward is -59.161970, knobs: {'random_page_cost': '2.43'}.
2021-05-26 11:18:08,158: [7] Database metrics: [0.3900000000000001, 0.6445245622485726, 4.05].
2021-05-26 11:18:08,158: [7] Benchmark score: -67.928493, used mem: 36444544 kB, reward: -67.964937.
|  7        | -67.96    |  0.3854   |
2021-05-26 11:19:11,917: [7] Current reward is -62.842104, knobs: {'random_page_cost': '2.43'}.
2021-05-26 11:19:11,918: [7] Best reward is -59.161970, knobs: {'random_page_cost': '2.43'}.
2021-05-26 11:19:11,918: [8] Database metrics: [0.43000000000000016, 0.6489102035318035, 3.5].
2021-05-26 11:19:11,918: [8] Benchmark score: -62.805659, used mem: 36444544 kB, reward: -62.842104.
 |  8        | -62.84    |  0.4301   |
=====================================
2021-05-26 11:19:11,926: The tuning process is complete. The best reward is -59.161970, best knobs are:
{'random_page_cost': '2.43'}.
 ****************************************** Knob Recommendation Report *************************************
INFO:
+---------------------------------------+-----------------------+
|                 Metric                |         Value         |
+---------------------------------------+-----------------------+
|             workload_type             |           ap          |
|         dirty_background_bytes        |           0           |
|          current_locks_count          |          0.0          |
|      current_prepared_xacts_count     |          0.0          |
|         rollback_commit_ratio         | 0.0002477694554770677 |
|         average_connection_age        |        0.004734       |
| checkpoint_proactive_triggering_ratio |  0.00938967136150235  |
|         fetched_returned_ratio        |  0.09276922373936373  |
|                 uptime                |   0.224322521666667   |
|             cache_hit_rate            |   0.6006356117493342  |
|              os_cpu_count             |           1           |
|          current_connections          |          1.0          |
|        checkpoint_avg_sync_time       |    1.06359368331199   |
|          search_modify_ratio          |   1007080.6984163317  |
|             max_processes             |          137          |
|          track_activity_size          |         137.0         |
|           all_database_size           |    2292057.41015625   |
|             temp_file_size            |    2694.18229367111   |
|            current_free_mem           |        3298680        |
|      shared_buffer_heap_hit_rate      |   36.42339765350299   |
|                used_mem               |       36444544.0      |
|              os_mem_total             |        3879956        |
|  checkpoint_dirty_writing_time_window |         450.0         |
|                ap_index               |          7.5          |
|      shared_buffer_toast_hit_rate     |   74.11273486430062   |
|             read_tup_speed            |    7942.47638202933   |
|               block_size              |          8.0          |
|            read_write_ratio           |   80.26596656844558   |
|      shared_buffer_tidx_hit_rate      |   84.41330998248687   |
|       shared_buffer_idx_hit_rate      |   96.54182833084825   |
|            write_tup_speed            |    98.9516516216125   |
|           enable_autovacuum           |          True         |
|                is_64bit               |          True         |
|                 is_hdd                |          True         |
|              load_average             |   [0.62, 1.08, 1.13]  |
 +---------------------------------------+-----------------------+
p.s: The unit of storage is kB.
WARN:
[0]. The number of CPU cores is a little small. Please do not run too high concurrency. You are recommended to set max_connections based on the number of CPU cores. If your job does not consume much CPU, you can also increase it.
BAD:
[0]. The value of wal_buffers is too high. Generally, a large value does not bring better performance. ********************************************* Recommended Knob Settings ********************************************
+---------------------------+-----------+--------+---------+---------+
|            name           | recommend |  min   |   max   | restart |
+---------------------------+-----------+--------+---------+---------+
|      random_page_cost     |    2.43   |  2.0   |   3.0   |  False  |
|       shared_buffers      |   121256  | 72752  |  139448 |   True  |
|      max_connections      |    134    |   15   |   269   |   True  |
|    effective_cache_size   |  2909967  | 121256 | 2909967 |  False  |
|        wal_buffers        |    3789   |  1894  |   3789  |   True  |
| default_statistics_target |    1000   |  100   |   1000  |  False  |
+---------------------------+-----------+--------+---------+---------+
real    10m12.961s user    0m6.827s sys     0m1.076s

openGaussMan

关注

6
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
初窥openGauss-之参数自调优X-Tuner

TPC-H 是一个面向分析型业务(AP)的基准测试，它由一系列热点查询组成，这些热点查询都是高度复杂的，因此执行时间往往都比较长。在本次实验测试中，将手动向数据库加载 TPC-H 数据，并保存在名为 tpch 的数据库中。默认 TPC-H 数据库的表缺少索引，数据库的参数并没有做任何优化，因此执行效率会比较差。本实验比较浅显，使用 openGauss 的参数自调优(X-Tuner：gs_xtuner)功能，对数据库进行参数优化，以提升数据库运行性能，让大家对 X-Tuner 参数自调优有一个初步的了解。
复制链接

扫一扫