暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / heavydb启动预加载数据优化

heavydb启动预加载数据优化

原创 jieguo 2023-11-15
316

由于heavydb属于gpu内存数据库，如果系统重启或服务重启，那么第一次执行查询的速度会非常慢，原因是第一次需要将数据从磁盘加载到物理内存和gpu内存中编译解析等操作。为了避免这种问题，我们可以考虑在系统系统后执行预加载一些常用的查询，这样就能保证系统启动后，前端应用能够正常快速返回查询结果，从而提高用户体验。

处理办法如下：

1.编写文件，比如/var/lib/heavyai/precache

root@node13:/var/lib/heavyai/storage/log# cat /var/lib/heavyai/precache
USER admin lnlt {
select count(*) from addressinfo;
select id,code from addressinfo where regionid='210114' and code like '%xxxx%' LIMIT 100000;
}

注意格式要求：USER要大写 空格 用户名 空格 数据库名称
预热的SQL，我们一般可以从日志中获取到：/var/lib/heavyai/storage/log/heavydb.INFO

cat heavydb.INFO|grep query_str|grep -i select|uniq | awk '{
str="select"
pos = index($0, str)
if (pos > 0) {
rest = substr($0, pos + length(str))
print str rest
}
}'|sed 's/;.*/;/'|uniq

image.png

2.修改自启服务/lib/systemd/system/heavydb.service

image.png

root@node13:/var/lib/heavyai/storage/log# vi /lib/systemd/system/heavydb.service
[Unit]
Description=HEAVY.AI HeavyDB database server
After=network.target remote-fs.target

[Service]
Environment="VK_ICD_FILENAMES=/etc/vulkan/icd.d/nvidia_icd.json"
User=heavyai
Group=heavyai
WorkingDirectory=/opt/heavyai
ExecStart=/opt/heavyai/bin/heavydb --config /var/lib/heavyai/heavy.conf --db-query-list /var/lib/heavyai/precache
KillMode=control-group
SuccessExitStatus=143
LimitNOFILE=65536
Restart=always

[Install]
WantedBy=multi-user.target

3.重启数据库服务：

root@node13:/var/lib/heavyai/storage/log# systemctl daemon-reload
root@node13:/var/lib/heavyai/storage/log# systemctl stop heavydb
root@node13:/var/lib/heavyai/storage/log# systemctl start heavydb
root@node13:/var/lib/heavyai/storage/log# systemctl status heavydb
● heavydb.service - HEAVY.AI HeavyDB database server
     Loaded: loaded (/lib/systemd/system/heavydb.service; enabled; vendor preset: enabled)
     Active: active (running) since Wed 2023-11-15 13:52:53 CST; 4s ago
   Main PID: 185783 (heavydb)
      Tasks: 129 (limit: 149999)
     Memory: 1.9G
     CGroup: /system.slice/heavydb.service
             ├─185783 /opt/heavyai/bin/heavydb --config /var/lib/heavyai/heavy.conf --db-query-list /var/lib/heavyai/precache
             └─185792 -Xmx1024m -DLOG_DIR=/var/lib/heavyai/storage/log/ -jar /opt/heavyai/bin/calcite-1.0-SNAPSHOT-jar-with-dependencies.jar -e /opt/heavyai/QueryEngine/ -d /var/lib/h>

11月 15 13:52:53 node13 systemd[1]: Started HEAVY.AI HeavyDB database server.

检查启动日志可以看到，执行了预热的SQL：

root@node13:/var/lib/heavyai/storage/log# pwd
/var/lib/heavyai/storage/log
root@node13:/var/lib/heavyai/storage/log# tail -f heavydb.INFO

image.png
如果服务失败，则通过命令查看详细错误：

journalctl -xe

服务启动成功后，登录检查，可以看到数据已经load到内存中，占用了1712MB，查询速度也正常。
image.png

4.更多优化参考：

https://docs.heavy.ai/troubleshooting/optimizing-performance

root@node13:/var/lib/heavyai/storage/log# nvidia-smi -i 0 -q

==============NVSMI LOG==============

Timestamp                                 : Wed Nov 15 16:51:42 2023
Driver Version                            : 525.85.12
CUDA Version                              : 12.0

Attached GPUs                             : 1
GPU 00000000:82:00.0
    Product Name                          : NVIDIA A100-PCIE-40GB
    Product Brand                         : NVIDIA
    Product Architecture                  : Ampere
    Display Mode                          : Enabled
    Display Active                        : Disabled
    Persistence Mode                      : Disabled
    MIG Mode
        Current                           : Disabled
        Pending                           : Disabled
    .。。。。。
            Type                          : C
            Name                          : /opt/heavyai/bin/heavydb
            Used GPU Memory               : 4520 MiB

root@node13:/var/lib/heavyai/storage/log# nvidia-smi -i 0 -pm ENABLED
Enabled persistence mode for GPU 00000000:82:00.0.
All done.
root@node13:/var/lib/heavyai/storage/log# nvidia-smi -i 0 -q|grep Persistence
    Persistence Mode                      : Enabled
root@node13:/var/lib/heavyai/storage/log# nvidia-smi
Wed Nov 15 16:53:20 2023       
+-----------------------------------------------------------------------------+
| NVIDIA-SMI 525.85.12    Driver Version: 525.85.12    CUDA Version: 12.0     |
|-------------------------------+----------------------+----------------------+
| GPU  Name        Persistence-M| Bus-Id        Disp.A | Volatile Uncorr. ECC |
| Fan  Temp  Perf  Pwr:Usage/Cap|         Memory-Usage | GPU-Util  Compute M. |
|                               |                      |               MIG M. |
|===============================+======================+======================|
|   0  NVIDIA A100-PCI...  On   | 00000000:82:00.0 Off |                    0 |
| N/A   38C    P0    39W / 250W |   4527MiB / 40960MiB |      0%      Default |
|                               |                      |             Disabled |
+-------------------------------+----------------------+----------------------+
                                                                               
+-----------------------------------------------------------------------------+
| Processes:                                                                  |
|  GPU   GI   CI        PID   Type   Process name                  GPU Memory |
|        ID   ID                                                   Usage      |
|=============================================================================|
|    0   N/A  N/A     29808      G   /usr/lib/xorg/Xorg                  4MiB |
|    0   N/A  N/A    209672      C   /opt/heavyai/bin/heavydb         4520MiB |
+-----------------------------------------------------------------------------+
root@node13:/var/lib/heavyai/storage/log# nvidia-smi -pm 0
Disabled persistence mode for GPU 00000000:82:00.0.
All done.
root@node13:/var/lib/heavyai/storage/log# nvidia-smi
Wed Nov 15 17:04:18 2023       
+-----------------------------------------------------------------------------+
| NVIDIA-SMI 525.85.12    Driver Version: 525.85.12    CUDA Version: 12.0     |
|-------------------------------+----------------------+----------------------+
| GPU  Name        Persistence-M| Bus-Id        Disp.A | Volatile Uncorr. ECC |
| Fan  Temp  Perf  Pwr:Usage/Cap|         Memory-Usage | GPU-Util  Compute M. |
|                               |                      |               MIG M. |
|===============================+======================+======================|
|   0  NVIDIA A100-PCI...  Off  | 00000000:82:00.0 Off |                    0 |
| N/A   39C    P0    40W / 250W |   4527MiB / 40960MiB |      0%      Default |
|                               |                      |             Disabled |
+-------------------------------+----------------------+----------------------+
                                                                               
+-----------------------------------------------------------------------------+
| Processes:                                                                  |
|  GPU   GI   CI        PID   Type   Process name                  GPU Memory |
|        ID   ID                                                   Usage      |
|=============================================================================|
|    0   N/A  N/A     29808      G   /usr/lib/xorg/Xorg                  4MiB |
|    0   N/A  N/A    209672      C   /opt/heavyai/bin/heavydb         4520MiB |
+-----------------------------------------------------------------------------+
root@node13:/var/lib/heavyai/storage/log# nvidia-smi -pm 1
Enabled persistence mode for GPU 00000000:82:00.0.
All done.
root@node13:/var/lib/heavyai/storage/log# nvidia-smi
Wed Nov 15 17:04:27 2023       
+-----------------------------------------------------------------------------+
| NVIDIA-SMI 525.85.12    Driver Version: 525.85.12    CUDA Version: 12.0     |
|-------------------------------+----------------------+----------------------+
| GPU  Name        Persistence-M| Bus-Id        Disp.A | Volatile Uncorr. ECC |
| Fan  Temp  Perf  Pwr:Usage/Cap|         Memory-Usage | GPU-Util  Compute M. |
|                               |                      |               MIG M. |
|===============================+======================+======================|
|   0  NVIDIA A100-PCI...  On   | 00000000:82:00.0 Off |                    0 |
| N/A   39C    P0    40W / 250W |   4527MiB / 40960MiB |      0%      Default |
|                               |                      |             Disabled |
+-------------------------------+----------------------+----------------------+
                                                                               
+-----------------------------------------------------------------------------+
| Processes:                                                                  |
|  GPU   GI   CI        PID   Type   Process name                  GPU Memory |
|        ID   ID                                                   Usage      |
|=============================================================================|
|    0   N/A  N/A     29808      G   /usr/lib/xorg/Xorg                  4MiB |
|    0   N/A  N/A    209672      C   /opt/heavyai/bin/heavydb         4520MiB |
+-----------------------------------------------------------------------------+
root@node13:/var/lib/heavyai/storage/log# nvidia-smi -i 0 -q|grep Persistence
    Persistence Mode                      : Enabled
root@node13:/var/lib/heavyai/storage/log#
heavydb
最后修改时间：2023-11-15 17:06:06
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论