1 测试环境

阿里云6代4核8G，PL0磁盘，ecs.g6.xlarge，2台

建议用Ubuntu 20.04，CentOS7的包太老

2 服务器

docker启动pg14

#!/bin/bash
NAME="postgres"
POSTGRES_USER="pg"
POSTGRES_PASS="pg123"

VOLUME="$HOME/docker_data/postgres"
mkdir -p $VOLUME 

docker ps -q -a --filter "name=$NAME" | xargs -I {} docker rm -f {}
docker run \
    --hostname $NAME \
    --name $NAME \
    -v $VOLUME:/var/lib/postgresql/data \
    --env POSTGRES_USER=$POSTGRES_USER \
    --env POSTGRES_PASSWORD=$POSTGRES_PASS \
    -p 5432:5432 \
    --detach \
    postgres:14-alpine

新建库

psql -U pg

CREATE DATABASE sbtest;
GRANT ALL PRIVILEGES ON DATABASE sbtest TO pg;

3 压测机

初始化

sysbench --db-driver=pgsql --pgsql-host=172.20.1.40 --pgsql-port=5432 --pgsql-user=pg --pgsql-password=pg123 --pgsql-db=sbtest /usr/share/sysbench/oltp_common.lua --tables=10 --table_size=100000 prepare

压测

sysbench --db-driver=pgsql --threads=32 --time=20 --report-interval=5 --pgsql-host=172.20.1.40 --pgsql-port=5432 --pgsql-user=pg --pgsql-password=pg123 /usr/share/sysbench/oltp_read_write.lua --tables=10 --table_size=100000 run

4 压测结果

pg14，10w

SQL statistics:
    queries performed:
        read:                            392994
        write:                           112249
        other:                           56161
        total:                           561404
    transactions:                        28063  (1400.78 per sec.)
    queries:                             561404 (28022.79 per sec.)
    ignored errors:                      8      (0.40 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          20.0324s
    total number of events:              28063

Latency (ms):
         min:                                    6.04
         avg:                                   22.82
         max:                                  145.95
         95th percentile:                       32.53
         sum:                               640446.52

Threads fairness:
    events (avg/stddev):           876.9688/35.28
    execution time (avg/stddev):   20.0140/0.01

My SQL 5.7，10w

SQL statistics:
    queries performed:
        read:                            371490
        write:                           106140
        other:                           53070
        total:                           530700
    transactions:                        26535  (1325.13 per sec.)
    queries:                             530700 (26502.50 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          20.0231s
    total number of events:              26535

Latency (ms):
         min:                                    6.72
         avg:                                   24.13
         max:                                  179.00
         95th percentile:                       44.98
         sum:                               640348.45

Threads fairness:
    events (avg/stddev):           829.2188/12.28
    execution time (avg/stddev):   20.0109/0.01

不难发现，pg14性能比MySQL 5.7略好，而且.95时间更稳

我们将测试数据扩大到50w(100w太慢，不想跑)

pg14，10w

SQL statistics:
    queries performed:
        read:                            355502
        write:                           101569
        other:                           50789
        total:                           507860
    transactions:                        25393  (1267.04 per sec.)
    queries:                             507860 (25340.71 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          20.0399s
    total number of events:              25393

Latency (ms):
         min:                                    7.43
         avg:                                   25.22
         max:                                  726.39
         95th percentile:                       33.12
         sum:                               640531.36

Threads fairness:
    events (avg/stddev):           793.5312/14.38
    execution time (avg/stddev):   20.0166/0.01

性能略微下降，10%左右，真的太稳了，95时间几乎没有变化。

再看看MySQL 5.7，50w

SQL statistics:
    queries performed:
        read:                            179662
        write:                           51332
        other:                           25666
        total:                           256660
    transactions:                        12833  (639.19 per sec.)
    queries:                             256660 (12783.78 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          20.0756s
    total number of events:              12833

Latency (ms):
         min:                                    6.67
         avg:                                   49.94
         max:                                  277.83
         95th percentile:                      114.72
         sum:                               640897.84

Threads fairness:
    events (avg/stddev):           401.0312/8.46
    execution time (avg/stddev):   20.0281/0.02

惨不忍睹！性能基本膝斩！

所以，结论如下：

• MySQL的"100w就要考虑做分库分表"，真的不是黑，是数据量扩展性太差。
• PG要稳得多，结合其各方面强大的功能，以及稳定出色的性能，"世界上最先进的开源关系型数据库"真的不是吹的。