求一个 postgresql 优化教程。

[sjmcefc2]

现在 postgresql 使用发现性能有点差，insert 比较慢。copy 也比较费时间。 不知道 e3 1240 这种 cpu，32g 内存，最终能优化成什么样子？能应对多大的查询？ 如何优化？如何衡量性能？ 求大神指点迷津。

[kxjhlele]

看看德哥的 GitHub

[NaVient]

https://github.com/digoal/blog/blob/master/201805/20180522_03.md

[cstj0505]

要想写入快把 share_mem 调大些，效果最明显。
然后再就是调整调整 io 参数，这个参考德哥比较好。
不过 pginsert、copy 已经够快了，我笔记本同样的数据测试，写 SSD，pg insert 单线程写能到 7w 行 /s，mysql 猜不到 2 万。
copyin 更是能把硬盘 io 写满，看看是不是你 io 的瓶颈。

[cstj0505]

对了，pg10 的分区表写入速度也会比较捉急，最好是对着分区直接写

[sjmcefc2]

机器配置:E3-1241V3, 32G memory,HDD(LVM,7200rpm).
操作系统 :RHEL 7.4 64bit


shared_buffers = 8G

maintenance_work_mem = 2GB
checkpoint_timeout = 5min
max_wal_size = 8GB
min_wal_size = 4GB

wal_level = minimal
archive_mode = off
max_wal_senders =0

effective_cache_size = 4GB
work_mem = 209715kB
上面是机器情况和参数变化。
@cstj0505 说的 share-men 在我机器上要调整到多大？
怎么计算每秒 insert 多少行？如果 7W 行 /s，那么 100 亿行也用不了多久？这样一行的数据有多少 k 呢？我的比较复杂，日志一大串。有时候发现 insert 时候的 cpu 其实很空闲，如何才能压榨机器到极致？
分区表确实感觉慢，如何对着分区表直接写？
这种集中数据装入，频繁查询的应用怎么调优惠比较好？

@cstj0505
@NaVient 感谢提供教程。

[cstj0505]

@sjmcefc2
shared_buffers 1/4 内存差不多，你可以用 fio 或者 dd 测一下你硬盘的持续 io 性能。再看看你写的时候 iostat 看看实际是多少。如果写不满，那就是数据库配置问题。不过即使默认的配置 pg 的持续写入性能还是非常高的，基本上也能接近 io 的最大能力。

你如果是分区表，只好不要直接写主表，按照你分区规则或者去系统中查找到对应的子表，然后把对应的数据直接 insert 子表，记得批量提交。用 copyin 的话性能会更快。

[cstj0505]

@sjmcefc2 上次我发过一个帖子，我同事也是机械盘 3，4 亿条 400 多秒
https://www.v2ex.com/t/408181#reply112

[sjmcefc2]

@cstj0505 非常感谢您的热心解答。
我用 time dd if=/dev/zero of= /testw.dbf bs=4k count=100000 测试,
发现 410mb 的时候,2.4G/s,很棒,但是超过 6.6G,也就是 count=1600000 的时候,速度下降到 1.8GB/s,
1800000 的时候(7.4G),速度下降到 1.5GB/s,
1900000 的时候(7.8G),速度为 1.4GB/s,
2000000 的时候(8.2G),速度为 1.3GB/s
2500000 的时候(10G),速度为 950MB/s
3000000(12g),速度为 600MB。
用了 lvm 来管理硬盘,感觉有点怪异。超过 6.6G 之后,拷贝性能下降非常大,是不是我文件系统有点问题?
第一次观察这个值,不知道大家的是什么情况。该如何解决这个问题呢?
3,4 亿条 400 多秒,太让人羡慕了。那个帖子也很有价值。

[cstj0505]

@sjmcefc2 忘了说了，linux 写磁盘的话会先写的缓存，所以文件小的话直接落在内存缓存了，看起来速度比较快。你测试的时候最好是写入大于内存的文件，我们一般 1.5 或者 2 倍，然后还有直接写磁盘不落缓存的参数，你可以看看，这样是比较接近于真是 io 的速度。

你如果用分区表的话，可以换普通表试试。

[sjmcefc2]

@cstj0505 非常感谢您的提示。我的机器 32g 内存，是不是说可以用 32g*2=64g 的来测试，那样的话估计速度就很慢了。现在 12g 都只有 600mb 了，我很多要导入的文件要 40g。有点想知道，我这样的硬盘速度是不是有点不太正常？大家真正的 io 速度是多少呢

[sjmcefc2]

@cstj0505
这样的 iostat,是不是说根本没有发挥出机器性能?
看着写入才 156kb/s,用 top 看资源,cpu 基本都是空闲的。
pgAdmin4 的 dashboard 上面,显示 1000 transaction per second。不过不太知道 pgadmin 的 dashboard 有什么含义。

大家能帮忙给看下问题在哪里呢?谢谢。

Linux 3.10.0-693.el7.x86_64 (localhost.localdomain) 2018 年 07 月 21 日 x86_64 (8 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.16 0.00 0.10 0.01 0.00 99.73

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

sdb 0.50 1.00 156.12 191276 29798188

sda 0.36 2.69 1.26 513513 240387

sde 0.06 0.01 0.00 2668 0

sdd 0.10 0.59 0.61 113024 116556

sdc 0.07 0.01 2.38 2692 454700

dm-0 0.21 2.35 1.21 448851 230066

dm-1 0.00 0.01 0.00 2228 0

dm-2 0.52 1.73 159.16 329496 30377680

[cstj0505]

@sjmcefc2 一般机械盘的顺序写入的话是在 150MB/s 左右。

你写入的时候是用 jdbc 批量写入的吗。jdbc 有个 copy manager 的 api，你可以查一下用法，基本上能接近你的硬盘 io 上限