Postgres-XL安装测试简单记录

./configure --prefix=/vg/wonders/wonders/db/postgresql/pgxl

make make install cd contrib/ make make install

编译好之后拖到一体机

报错:

ERROR: `flex' is missing on your system. It is needed to create the

file `gtm_opt_scanner.c'.

yum install -y flex

 

启动停止

pgxc_ctl -c /vg/wonders/wonders/user/postgres/pgxc_ctl/pgxc_ctl.conf start all

pgxc_ctl -c /vg/wonders/wonders/user/postgres/pgxc_ctl/pgxc_ctl.conf stop all

 

节点查看

SELECT oid, * FROM pgxc_node;

 

 

启动失败

pgxc_ctl -c /vg/wonders/wonders/user/postgres/pgxc_ctl/pgxc_ctl-3.conf "stop -m immediate all"

 

 

一体机重启,重新配ssh免密

cd /vg/share/ftp/kkk/

tar -zxvf ssh.tar.gz -C /

tar -zxvf ssh-keygen-tools.tar.gz -C /

 

chmod 777 /dev/urandom

chmod 777 /dev/random

chmod 777 /dev/tty

 

deluser postgres

 

mkdir -p /vg/wonders/wonders/user/postgres

adduser postgres -h /vg/wonders/wonders/user/postgres

chown postgres.postgres /vg/wonders/wonders/user/postgres

 

ssh-keygen -t rsa

cd ~/.ssh

touch authorized_keys

chmod 600 authorized_keys

cat id_rsa.pub

vi authorized_keys

复制每个id_rsa.pub,写入authorized_keys

执行ssh到各节点,yes,写入known_hosts

测试成功

 

 

cp -r /vg/share/ftp/kkk/pgxl/ /vg/wonders/wonders/db/postgresql/

chown -R postgres.postgres /vg/wonders/wonders/db/postgresql/pgxl/

chmod -R 755 /vg/wonders/wonders/db/postgresql/pgxl/

chmod 777 /vg/wonders/wonders/db/postgresql/

vi /etc/profile

export PATH=/vg/wonders/wonders/db/postgresql/pgxl/bin:$PATH

export LD_LIBRARY_PATH=/vg/wonders/wonders/db/postgresql/pgxl/lib:$LD_LIBRARY_PATH

source /etc/profile

 

pgxc_ctl

prepare

修改pgxc_ctl.conf 默认在用户目录下

初始化

//pgxc_ctl -c /vg/wonders/wonders/db/postgresql/pgxl/pgxc_ctl.conf init all

pgxc_ctl -c /vg/wonders/wonders/user/postgres/pgxc_ctl/pgxc_ctl.conf init all

pgxc_ctl -c /vg/wonders/wonders/user/postgres/pgxc_ctl/pgxc_ctl.conf init all

pgxc_ctl -c /vg/wonders/wonders/user/postgres/pgxc_ctl/pgxc_ctl-4.conf start all

pgxc_ctl -c /vg/wonders/wonders/user/postgres/pgxc_ctl/pgxc_ctl-3.conf init all

pgxc_ctl -c /vg/wonders/wonders/user/postgres/pgxc_ctl/pgxc_ctl-3.conf start all

执行时自动ssh到节点,提示找不到pg_ctl等各种命令环境变量没加载

vi ~/.bashrc

export PATH=/vg/wonders/wonders/db/postgresql/pgxl/bin:$PATH

export LD_LIBRARY_PATH=/vg/wonders/wonders/db/postgresql/pgxl/lib:$LD_LIBRARY_PATH

source ~/.bashrc

执行 还是不行

vi ~/.bash_profile

if [ -f ~/.bashrc ]; then . ~/.bashrc fi

还是不行

那就一个一个ln

ln -s /vg/wonders/wonders/db/postgresql/pgxl/bin/gtm_ctl /usr/bin

ln -s /vg/wonders/wonders/db/postgresql/pgxl/bin/initdb /usr/bin

ln -s /vg/wonders/wonders/db/postgresql/pgxl/bin/pg_ctl /usr/bin

ln -s /vg/wonders/wonders/db/postgresql/pgxl/bin/gtm /usr/bin

ln -s /vg/wonders/wonders/db/postgresql/pgxl/bin/initgtm /usr/bin

ln -s /vg/wonders/wonders/db/postgresql/pgxl/bin/gtm_proxy /usr/bin

 

ln -s /vg/wonders/wonders/db/postgresql/pgxl/bin/initgtm /usr/bin

ln -s /vg/wonders/wonders/db/postgresql/pgxl/bin/initgtm /usr/bin

ln -s /vg/wonders/wonders/db/postgresql/pgxl/bin/initgtm /usr/bin

 

 

 

 

报错,没有调用ssh免密,调用的dropbear的dbclient无限提示输入密码

export LD_LIBRARY_PATH=/vg/wonders/wonders/db/postgresql/pgxl/lib:/vg/wonders/wonders/db/postgresql/postgreCluster/openssl/lib:$LD_LIBRARY_PATH

export PATH=/vg/wonders/wonders/db/postgresql/pgxl/bin:$PATH

 

在193上验证,用postgres用户执行没问题

vi ~/.bashrc export PGHOME=/home/postgres/pgxl export PGUSER=postgres export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH export PATH=$PGHOME/bin:$PATH

source ~/.bashrc

 

postgres用户设置

ssh免密

mkdir /home/postgres

chown postgres.postgres /home/postgres/

chown postgres.postgres /vg/wonders/wonders/db/postgresql/pgxl

 

rm -rf coord/

rm -rf datanode/

 

 

export PATH=/vg/wonders/wonders/db/postgresql/pgxl/bin:$PATH

export PATH=/vg/wonders/wonders/db/postgresql/postgreCluster/openssl/bin:$PATH

 

pgxc_ctl -c /home/postgres/pgxc_ctl/pgxc_ctl.conf init all

 

 

 

非root用户ssh登录错误

ssh -v ... 调试模式执行

debug1: read_passphrase: can't open /dev/tty: Permission denied

debug1: permanently_drop_suid: 1000

ssh_askpass: exec(/usr/libexec/openssh/ssh-askpass): No such file or directory

Host key verification failed.

 

dev/tty改成777即可

 

ln -s /usr/bin/ssh /usr/bin/dbclient

 

vi /vg/wonders/wonders/db/postgresql/coord/pg_hba.conf

vi /vg/wonders/wonders/db/postgresql/datanode/pg_hba.conf

host all all 0.0.0.0/0 md5

 

 

cpu占用很多,但是跑的慢

 

 

 

 

 

参数优化

 

Coordinator节点postgresql.conf：

 

max_connections = 500 # 允许的最大并发连接数

shared_buffers = 32GB # 用于缓存数据的内存（推荐内存的1/4)

effective_cache_size = 64GB

 

temp_buffers = 1GB

work_mem = 1GB

dynamic_shared_memory_type = sysv

max_files_per_process = 2000

effective_io_concurrency = 8

max_worker_processes = 64

max_pool_size = 1600

pool_conn_keepalive = 600

max_prepared_transactions = 500

 

 

Datanode节点postgresql.conf：

 

max_connections = 1600

shared_buffers = 32GB

temp_buffers = 1GB

dynamic_shared_memory_type = sysv

effective_io_concurrency = 8

max_worker_processes = 32

random_page_cost = 1.5

effective_cache_size = 64GB

 

Gtm节点gtm.conf

 

keepalives_idle = 120

keepalives_interval = 30

keepalives_count = 50

 

修改所有coord和datanode的pg_hba.conf

 

 

查询对比

 

235单机 4.6 XL集群 2.4

 

 

235单机 19 XL集群 15

 

 

235单机 5.2 XL集群 30

 

 

 

 

 

 

2019-12-31 11:26:24.822 CST [30125] LOG: node (coord1:16384) down! Trying ping

2019-12-31 11:26:24.825 CST [30125] LOG: Health map updated to reflect HEALTHY node (coord1)

2019-12-31 11:26:54.873 CST [30125] LOG: failed to connect to node, connection string (host=192.168.14.240 port=5434 dbname=test user=postgres application_name='pgxc:coord2' sslmode=disable options='-c remotetype=coordinator -c parentnode=coord2 -c DateStyle=iso,mdy -c timezone=prc -c geqo=on -c intervalstyle=postgres -c lc_monetary=C'), connection error (fe_sendauth: no password supplied

)

2019-12-31 11:26:54.873 CST [30125] WARNING: can not connect to node 16384

2019-12-31 11:26:54.873 CST [30125] WARNING: Health map updated to reflect DOWN node (16384)

2019-12-31 11:26:54.873 CST [30125] LOG: Pooler could not open a connection to node 16384

2019-12-31 11:26:54.873 CST [1374] LOG: failed to acquire connections

2019-12-31 11:26:54.873 CST [1374] WARNING: failed to receive file descriptors for connections

2019-12-31 11:26:54.873 CST [1374] ERROR: Failed to get pooled connections

2019-12-31 11:26:54.873 CST [1374] HINT: This may happen because one or more nodes are currently unreachable, either because of node or network failure.

Its also possible that the target node may have hit the connection limit or the pooler is configured with low connections.

Please check if all nodes are running fine and also review max_connections and max_pool_size configuration parameters

2019-12-31 11:26:54.873 CST [1374] CONTEXT: automatic analyze of table "test.test.test

2019-12-31 11:27:24.921 CST [30125] LOG: node (coord1:16384) down! Trying ping

2019-12-31 11:27:24.924 CST [30125] LOG: Health map updated to reflect HEALTHY node (coord1)

 

 

 

 

 

 

 

pgbench 测试

在test库生成测试表

pgbench -i -p 5411 test

1亿数据

pgbench -i -s 1000 -p 5411 test

pgbench -i -s 1000 -p 5434 postgres

进行测试

-T测试时间秒

-n 测试运行前不执行vacuum

-c 模拟的客户数量,具体指并发运行的数据库会话数

-r   在基准结束后，报告平均的每个命令的每语句等待时间（从客户端的角度来说是执行时间）。

-j    pgbench中的工作者线程数量。在多 CPU 机器上使用多于一个线程会有用。客户端会尽可能均匀地分布到可用的线程上。默认为 1。

-S，则只有select语句会执行

 

pgbench -h 192.168.14.240 -p5411 -c30 -T300 -n test

pgbench -h 192.168.14.240 -p5411 -c30 -T300 -n -S test

pgbench -h 192.168.14.240 -p5411 -c30 -j8 -T300 -n test

 

pgbench -p5434 -c60 -T300 -n -j16 postgres -f pgbench-select.sql

 

      默认的事务脚本在每个事务中执行如下七条语句：

BEGIN;

UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

END;

      如果你指定了-N，第4步和第5步不会执行。如果指定了-S，则只有select语句会执行

 

 

尝试添加参数测试

persistent_datanode_connections = on

运行对比

pgbench -p5434 -c100 -T300 -n -j16 -S test

transaction type: <builtin: select only>

scaling factor: 1000

query mode: simple

number of clients: 100

number of threads: 16

duration: 300 s

number of transactions actually processed: 12587490

latency average = 2.383 ms

tps = 41956.865600 (including connections establishing)

tps = 41958.111540 (excluding connections establishing)

比加之前快了,之前29000 快了30%

 

 

已经测试过三节点,四节点,各种参数修改测试

1.默认配置下测试性能

结果一样,差别细微,测试查询时发现带宽都满了,可能是瓶颈所在,但是插入时带宽占用少,依然慢

 

2.测试带gtm-proxy的性能

更慢了

 

3.测试xl9.5的性能

 

cp -r /vg/share/ftp/kkk/pgxl95/ /vg/wonders/wonders/db/postgresql/

chown -R postgres.postgres /vg/wonders/wonders/db/postgresql/pgxl95/

chmod -R 755 /vg/wonders/wonders/db/postgresql/pgxl95/

 

vi /etc/profile

export PATH=/vg/wonders/wonders/db/postgresql/pgxl95/bin:$PATH

export LD_LIBRARY_PATH=/vg/wonders/wonders/db/postgresql/pgxl95/lib:$LD_LIBRARY_PATH

source /etc/profile

 

rm -rf /usr/bin/gtm_ctl

rm -rf /usr/bin/initdb

rm -rf /usr/bin/pg_ctl

rm -rf /usr/bin/gtm

rm -rf /usr/bin/initgtm

rm -rf /usr/bin/gtm_proxy

 

 

ln -s /vg/wonders/wonders/db/postgresql/pgxl95/bin/gtm_ctl /usr/bin

ln -s /vg/wonders/wonders/db/postgresql/pgxl95/bin/initdb /usr/bin

ln -s /vg/wonders/wonders/db/postgresql/pgxl95/bin/pg_ctl /usr/bin

ln -s /vg/wonders/wonders/db/postgresql/pgxl95/bin/gtm /usr/bin

ln -s /vg/wonders/wonders/db/postgresql/pgxl95/bin/initgtm /usr/bin

ln -s /vg/wonders/wonders/db/postgresql/pgxl95/bin/gtm_proxy /usr/bin

 

4.kernel内核参数修改

vi /etc/sysctl.conf 修改无效

ipcs -ls

echo "50100 12825600 50100 2560" > /proc/sys/kernel/sem

 

网络流量查看

/vg/wonders/wonders/db/postgresql/postgreCluster/nethogs-0.8.1/nethogs

 

插入

44707

select count(*) from tb_his_mz_reg_____t

 

 

54安装pgxl单节点

vi ~/.bashrc

export PATH=/home/kkk/pgxl/bin:$PATH

export LD_LIBRARY_PATH=/home/kkk/pgxl/lib:$LD_LIBRARY_PATH

source ~/.bashrc

 

ln -s /kk_test/pgxl/bin/gtm_ctl /usr/bin

ln -s /kk_test/pgxl/bin/initdb /usr/bin

ln -s /kk_test/pgxl/bin/pg_ctl /usr/bin

ln -s /kk_test/pgxl/bin/gtm /usr/bin

ln -s /kk_test/pgxl/bin/initgtm /usr/bin

ln -s /kk_test/pgxl/bin/gtm_proxy /usr/bin

 

pgbench -i -s 1000 -p 5434 postgres

pgbench -p5434 -c60 -T180 -n -j16 postgres -f /home/kkk/pgbench/pgbench-select.sql

 

pgxc_ctl -c /home/kkk/pgxc_ctl-1n54.conf init all

 

集群插入时

 

 

 

单机

vacuum pgbench_accounts 258.072s

集群

vacuum pgbench_accounts 91.431s