digoal
2023-03-18
PostgreSQL , PolarDB , docker , nvidia cuda 容器 , pg-strom
《记录下 在 debian 中部署 nvidia cuda 容器》
《PostgreSQL GPU 加速(HeteroDB pg_strom) (GPU计算, GPU-DIO-Nvme SSD, 列存, GPU内存缓存)》
假设已经配置好了容器, 进入容器
docker exec -it -u root --privileged -w /root cuda /bin/bash
配置nvidia
root@7d89f9042288:~# nvidia-smi -pm 1
Enabled persistence mode for GPU 00000000:01:00.0.
All done.
root@7d89f9042288:~# nvidia-smi -am 1
Enabled Accounting Mode for GPU 00000000:01:00.0.
All done.
root@7d89f9042288:~# nvidia-smi
Sat Mar 18 13:14:59 2023
+-----------------------------------------------------------------------------+
| NVIDIA-SMI 525.89.02 Driver Version: 525.89.02 CUDA Version: 12.1 |
|-------------------------------+----------------------+----------------------+
| 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 GeForce ... On | 00000000:01:00.0 Off | N/A |
| N/A 28C P8 N/A / N/A | 0MiB / 2048MiB | 0% Default |
| | | N/A |
+-------------------------------+----------------------+----------------------+
+-----------------------------------------------------------------------------+
| Processes: |
| GPU GI CI PID Type Process name GPU Memory |
| ID ID Usage |
|=============================================================================|
| No running processes found |
+-----------------------------------------------------------------------------+
安装postgresql 15.2
su - postgres
vi ~/.bashrc
# add by digoal
alias rm='rm -i'
alias cp='cp -i'
alias ll='ls -larth'
alias mv='mv -i'
export PATH=/home/postgres/pg15.2/bin:$PATH
export PGDATA=/home/postgres/pgdata
export PGUSER=postgres
export PGHOST=$PGDATA
export PGPORT=1921
export PGDATABASE=postgres
export LC_ALL=en_US.UTF-8
. ~/.bashrc
wget https://ftp.postgresql.org/pub/source/v15.2/postgresql-15.2.tar.bz2
tar -jxvf postgresql-15.2.tar.bz2
cd postgresql-15.2
env CC=clang CXX=clang++ ./configure --prefix=/home/postgres/pg15.2 --with-icu
env CC=clang CXX=clang++ make world -j 8
env CC=clang CXX=clang++ make install-world
初始化数据库实例
initdb -D $PGDATA -U postgres -E UTF8 --locale-provider icu --icu-locale=C --lc-collate=C --lc-ctype=en_US.UTF-8
vi $PGDATA/postgresql.auto.conf
listen_addresses = '0.0.0.0'
port = 1921
max_connections = 1000
superuser_reserved_connections = 3
unix_socket_directories = '., /tmp'
tcp_keepalives_idle = 30
tcp_keepalives_interval = 10
tcp_keepalives_count = 3
client_connection_check_interval = 30
shared_buffers = 1GB
maintenance_work_mem = 1GB
dynamic_shared_memory_type = posix
vacuum_cost_delay = 0
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 500
bgwriter_lru_multiplier = 5.0
effective_io_concurrency = 0
max_parallel_workers_per_gather = 0
max_parallel_maintenance_workers = 2
wal_level = minimal
synchronous_commit = off
wal_compression = on
wal_buffers = -1
wal_writer_delay = 10ms
checkpoint_timeout = 15min
checkpoint_completion_target = 0.5
max_wal_size = 4GB
min_wal_size = 1GB
archive_mode = off
max_wal_senders = 0
random_page_cost = 1.1
effective_cache_size = 8GB
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 10MB
log_truncate_on_rotation = on
log_lock_waits = on
log_recovery_conflict_waits = on
log_statement = 'ddl'
log_timezone = 'Asia/Shanghai'
autovacuum = on
autovacuum_freeze_max_age = 800000000
autovacuum_multixact_freeze_max_age = 900000000
autovacuum_vacuum_cost_delay = 0ms
vacuum_freeze_table_age = 650000000
vacuum_freeze_min_age = 500000000
vacuum_failsafe_age = 1600000000
vacuum_multixact_freeze_table_age = 650000000
vacuum_multixact_freeze_min_age = 50000000
vacuum_multixact_failsafe_age = 1600000000
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
启动数据库实例
pg_ctl start
安装pg-strom
cd ~
git clone --depth 1 -b v3.5 https://github.com/heterodb/pg-strom
cd pg-strom
make clean
make distclean
env CC=clang CXX=clang++ USE_PGXS=1 make -j 4
遇到bug:
/usr/local/cuda/bin/nvcc -I /home/postgres/pg15.2/include/server --fatbin --maxrregcount=128 --gpu-architecture=compute_60 --gpu-code=sm_60,sm_61,sm_70,sm_75,sm_80,sm_86 --threads 6 --source-in-ptx -lineinfo --relocatable-device-code=true -o src/cuda_misclib.fatbin src/cuda_misclib.cu
Segmentation fault (core dumped)
make: *** [Makefile:232: src/cuda_misclib.fatbin] Error 255
env CC=clang CXX=clang++ USE_PGXS=1 make install
如果安装pg-strom正常, 继续配置即可
vi $PGDATA/postgresql.auto.conf
shared_buffers = 4GB
work_mem = 1GB
max_worker_processes = 100
max_parallel_workers_per_gather = 4
shared_preload_libraries = 'pg_strom'
pg_ctl restart -m fast
postgres=# create extension pg_strom;