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

Question

我使用VMWare环境来比较Postgres-xl9.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

在userver上：

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;

我确认了Postgres-XL中所有的表pgbench_*平均分布在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倍。

Answer 2

也许你应该尝试一个较大的“比例”值。我得到了和你相似的结果。然后我在Postgres-XL官方网站上找到了这个网页：http://www.postgres-xl.org/2016/04/postgres-xl-9-5-r1-released/eased/

上面写着：

除了在商业智能工作负载上证明了它的勇气之外，Postgres-XL在运行pgBench (基于TPC-B)基准测试时在OLTP工作负载上表现得非常好。在4节点(规模: 4000)配置中，与PostgreSQL相比，对于特定工作负载，XL的总拥有成本最多提高230% (延迟比较为-70%)，对于更新工作负载，吞吐量最多提高130% (延迟比较为-56%)。然而，它的伸缩性甚至比最大的单节点服务器要高得多。

所以我猜Postgres-XL在大数据量的情况下表现得很好。我现在就会进行测试来确认这一点。

Answer 3

Postgres-XL是一个集群服务器。单个事务在它上总是会稍微慢一些，但因为它可以扩展到大规模集群，使其能够同时处理更多数据，从而使其处理大型数据集的速度更快。

此外，根据您使用的配置选项，性能也会有很大的不同。