Postgres性能不会随着核心数量的增加而提高

Question

我正在尝试postgres google-cloud-sql并加载了一个简单的学校模式

 CREATE TABLE school ( 
    id SERIAL NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
);
CREATE TABLE class ( 
    id SERIAL NOT NULL PRIMARY KEY,
    name TEXT,
    school_id INTEGER NOT NULL REFERENCES school
);
CREATE TABLE student ( 
    id SERIAL NOT NULL PRIMARY KEY,
    name TEXT,
    class_id INTEGER NOT NULL REFERENCES class
);
-- ALL id and foreign keys have indexs

加载〜1500万行，1500班，每堂课500级，每班200个学生。

之后创建一个简单的PGBENCH脚本

setrandom sId1 1 20000000
setrandom sId2 1 20000000
setrandom sId3 1 20000000
select count(*) from school s 
join class c on s.id=c.school_id 
join student stu on c.id=stu.class_id where s.id=:sId1;
select count(*) from school s 
join class c on s.id=c.school_id 
join student stu on c.id=stu.class_id where s.id=:sId2;
select count(*) from school s 
join class c on s.id=c.school_id 
join student stu on c.id=stu.class_id where s.id=:sId3;

现在用

运行脚本

pgbench -c 90 -f ./sql.sql  -n -t 1000

2核，7.5 GB，90客户 -

OUTPUT:
number of transactions actually processed: 90000/90000
tps = 1519.690555 (including connections establishing)
tps = 2320.408683 (excluding connections establishing

26核，30 GB，90客户 -

number of transactions actually processed: 90000/90000
tps = 1553.721286 (including connections establishing)
tps = 2405.664795 (excluding connections establishing)

问题：为什么我们只有80 TP从2个核心增加到26个核？

Answer 1

我在Postgres IRC上问了同样的问题。

社区确定我最大化客户端PGBench，他们建议在PGBench中使用-j4，而TPS则增加到每秒钟23K

Answer 2

，因为单个SELECT仅在一个核心上运行的一个过程中运行。添加额外的内核的功能是允许执行多个同时操作。因此，如果您要在数据库上扔1,000个同时查询，它们将在26个内核上更快地执行。