Postgres-XL 9.5 集群与单个 PostgreSQL 9.5 的性能对比

Question

我使用 VMWare 环境来比较 Postgres-XL 9.5 和 PostgreSQL 9.5 的性能。

我按照 Creating a Postgres-XL cluster 的说明构建 Postgres-XL 集群

Physical HW:
    M/B: Gigabyte H97M-D3H
    CPU: Intel i7-4790 @3.60Mhz
    RAM: 32GB DDR3 1600
    HD: 2.5" Seagate SSHD ST1000LM014 1TB
Infra:
  VMWare ESXi 6.0
VM:
    DB00~DB05:
        CPU: 1 core, limit to 2000Mhz
        RAM: 2GB, limit to 2GB
        HD: 50GB
        Advanced CPU Hyperthread mode: any
        OS: Ubuntu 16.04 LTS x64 (all packages are upgraded to the current version with apt-update; apt-upgrade)
        PostgreSQL 9.5+173 on DB00
        Postgres-XL 9.5r1.2 on DB01~DB05

    userver: (for executing pgbench)
        CPU: 2 cores,
        RAM: 4GB,
        HD: 50GB
        OS: Ubuntu 14.04 LTS x64
Role:
    DB00: Single PostgreSQL
    DB01: GTM
    DB02: Coordinator Master
    DB03~DB05: datanode master dn1~dn3

DB01~DB05中的postgresql.conf

shared_buffers = 128MB
dynamic_shared_memory_type = posix  
max_connections = 300
max_prepared_transactions = 300
hot_standby = off
# Others are default values

DB00的postgresql.conf是

max_connections = 300
shared_buffers = 128MB
max_prepared_transactions = 300
dynamic_shared_memory_type = sysv
#Others are default values

在用户端:

pgbench -h db00 -U postgres -i -s 10 -F 10 testdb;
pgbench -h db00 -U postgres -c 30 -t 60 -j 10 -r testdb;

pgbench -h db02 -U postgres -i -s 10 -F 10 testdb;
pgbench -h db02 -U postgres -c 30 -t 60 -j 10 -r testdb;

我确认所有表 pgbench_* 在 Postgres-XL 中平均分布在 dn1~dn3 之间

pgbench 结果:

Single PostgreSQL 9.5: (DB00)

    starting vacuum...end.
    transaction type: TPC-B (sort of)
    scaling factor: 10
    query mode: simple
    number of clients: 30
    number of threads: 10
    number of transactions per client: 60
    number of transactions actually processed: 1800/1800
    tps = 1263.319245 (including connections establishing)
    tps = 1375.811566 (excluding connections establishing)
    statement latencies in milliseconds:
            0.001084        \set nbranches 1 * :scale
            0.000378        \set ntellers 10 * :scale
            0.000325        \set naccounts 100000 * :scale
            0.000342        \setrandom aid 1 :naccounts
            0.000270        \setrandom bid 1 :nbranches
            0.000294        \setrandom tid 1 :ntellers
            0.000313        \setrandom delta -5000 5000
            0.712935        BEGIN;
            0.778902        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
            3.022301        SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
            3.244109        UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
            7.931936        UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
            1.129092        INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
    4.159086        END;

_

Postgres-XL 9.5
    starting vacuum...end.
    transaction type: TPC-B (sort of)
    scaling factor: 10
    query mode: simple
    number of clients: 30
    number of threads: 10
    number of transactions per client: 60
    number of transactions actually processed: 1800/1800
    tps = 693.551818 (including connections establishing)
    tps = 705.965242 (excluding connections establishing)
    statement latencies in milliseconds:
            0.003451        \set nbranches 1 * :scale
            0.000682        \set ntellers 10 * :scale
            0.000656        \set naccounts 100000 * :scale
            0.000802        \setrandom aid 1 :naccounts
            0.000610        \setrandom bid 1 :nbranches
            0.000553        \setrandom tid 1 :ntellers
            0.000536        \setrandom delta -5000 5000
            0.172587        BEGIN;
            3.540136        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
            0.631834        SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
            6.741206        UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
            17.539502       UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
            0.974308        INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
        10.475378       END;

我的问题是，为什么 Postgres-XL 的 TPS 和其他索引(如 INSERT、UPDATE)远不如 PostgreSQL？我认为 Postgres-XL 的性能应该比 PostgreSQL 更好，不是吗？

Answer 1

Postgres-XL 设计用于在多个物理节点上运行。在 VMWare 上运行它是一个很好的教育练习，但不应期望它会显示任何性能提升。您正在添加虚拟化开销和集群软件的开销。 joyeu回答的网页测试使用了4台物理机。假设单个节点的性能提升是基于同一台机器，您会认为这是硬件的 4 倍，性能提升 2.3 倍。