postgresql – Postgre性能不随着核心数量的增加而增加

前端之家收集整理的这篇文章主要介绍了postgresql – Postgre性能不随着核心数量的增加而增加前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我正在尝试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,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)

题:
为什么我们从2核到26核才有80 tps?

我在postgres irc上问了同样的问题.

社区肯定我是客户端的pgbench,他们建议在pgbench中使用-j4,每秒增加到23k.

猜你在找的Postgre SQL相关文章