调整postgresql [英] Tunning postgresql

问题描述

Hello list，


我在RH 8上有7.3.4，服务器硬件是双处理器Intel Xeon 2.4

Ghz，2G RAM。我正在阅读关于修改的内容，并希望得到一些帮助

来自你，我更改了一些默认值和性能

增加了一点但我认为我仍然可以从那个盒子里获得更多。


在kernel和postgresql.conf中设置的正确值应该是什么？
获得最大性能，实际上内核共享内存是：

cat / proc / sys / kernel / shmmax

268435456

cat / proc / sys / kernel / shmall

268435456

postgresql.conf包含修改后的这些配置：


shared_buffers = 17000 #min max_connections * 2或16,8KB每个

max_fsm_relations = 400＃min 10，fsm是免费空间地图，~40

max_fsm_pages = 80000 #min 1000，fsm是免费空间地图，~6

max_locks_per_transaction = 64＃min 10

sort_mem = 16384＃min 64，KB大小

effective_cache_size = 1700000＃通常每个8KB


仍然不太清楚如何结合这些参数来获得postgresql的
最大性能，对此有任何帮助或评论

会非常感谢。


谢谢，

-
$ b $bJosuéMaldonado。


---------------------------（广播结束）----------------- ----------

提示7：别忘了增加免费空间地图设置

Hello list,

I have 7.3.4 on RH 8, server hardware is a dual processor Intel Xeon 2.4
Ghz, 2G RAM. I was reading about tunning and would like to get some help
from you, I changed some of the default values and the performance
increased a little but I think still I can get more from that box.

What should be the right values to set in kernel and postgresql.conf to
get maximum performance, actually kernel share memory is:
cat /proc/sys/kernel/shmmax
268435456
cat /proc/sys/kernel/shmall
268435456

postgresql.conf contains these configurations modified:

shared_buffers = 17000 # min max_connections*2 or 16, 8KB each
max_fsm_relations = 400 # min 10, fsm is free space map, ~40
max_fsm_pages = 80000 # min 1000, fsm is free space map, ~6
max_locks_per_transaction = 64 # min 10
sort_mem = 16384 # min 64, size in KB
effective_cache_size = 1700000 # typically 8KB each

Still don''t understand very well how to combine these parameters to gain
maximun performance for postgresql, any help or comment about this
would be very appreciated.

Thanks,
--
Josué Maldonado.

---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings

推荐答案

2003年11月19日星期三，14：25，Josu？ Maldonado写道：

On Wed, 2003-11-19 at 14:25, Josu?? Maldonado wrote:

Hello列表，

我在RH 8上有7.3.4，服务器硬件是双处理器Intel Xeon 2.4
Ghz，2G RAM。我正在阅读有关调音的内容，并想从你那里得到一些帮助，我改变了一些默认值并且性能稍微增加了一些，但我想我仍然可以从那个盒子里获得更多。 cat / proc / sys / kernel / shmmax
268435456
cat / proc / sys / kernel / shmall
268435456

postgresql.conf包含这些配置修改：

shared_buffers = 17000＃min max_connections * 2或16，每个8KB
max_fsm_relations = 400＃min 10，fsm是免费空间地图，~40
max_fsm_pages = 80000 #min 1000，fsm是免费空间地图，~6
max_locks_per_transaction = 64＃min 10
sort_mem = 16384＃min 64，大小以KB为单位
effective_cache_size = 1700000＃通常每个8KB

仍然不太清楚如何组合这些参数来获得最大的postgresql性能，任何关于这个的帮助或评论都将非常感激。

谢谢，

Hello list,

I have 7.3.4 on RH 8, server hardware is a dual processor Intel Xeon 2.4
Ghz, 2G RAM. I was reading about tunning and would like to get some help
from you, I changed some of the default values and the performance
increased a little but I think still I can get more from that box.

What should be the right values to set in kernel and postgresql.conf to
get maximum performance, actually kernel share memory is:
cat /proc/sys/kernel/shmmax
268435456
cat /proc/sys/kernel/shmall
268435456

postgresql.conf contains these configurations modified:

shared_buffers = 17000 # min max_connections*2 or 16, 8KB each
max_fsm_relations = 400 # min 10, fsm is free space map, ~40
max_fsm_pages = 80000 # min 1000, fsm is free space map, ~6
max_locks_per_transaction = 64 # min 10
sort_mem = 16384 # min 64, size in KB
effective_cache_size = 1700000 # typically 8KB each

Still don''t understand very well how to combine these parameters to gain
maximun performance for postgresql, any help or comment about this
would be very appreciated.

Thanks,

从长时间的实验中说，你做得好多了，确保你的索引和查询最佳，可以搞砸

的缓冲空间。一旦你选择了合理的价值，缓冲空间调整可能会让你获得几个百分点的b $ b性能;查询调优可以让你

数量级。


Stephen

------------ ---------------（播出结束）---------------------------

提示6：您是否搜索了我们的列表档案？

http ：//archives.postgresql.org

Speaking from long experimentation, you''re much, much better off making
sure your indices and queries are optimal that messing around with
buffer space. Buffer space tuning might get you a few percent
performance once you pick a reasonable value; query tuning can get you
orders of magnitude.

Stephen
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Josué Maldonado wrote:

Hello列表，
postgresql.conf包含修改后的这些配置：

shared_buffers = 17000 #min max_connections * 2或16,8KB每个
max_fsm_relations = 400＃min 10，fsm是免费空间地图，~40
max_fsm_pages = 80000＃min 1000，fsm是免费空间地图，~6
max_locks_per_transaction = 64＃min 10 sort_mem = 16384＃min 64，大小以KB为单位
effective_cache_size = 1700000＃通常每个8KB

Hello list,
postgresql.conf contains these configurations modified:

shared_buffers = 17000 # min max_connections*2 or 16, 8KB each
max_fsm_relations = 400 # min 10, fsm is free space map, ~40
max_fsm_pages = 80000 # min 1000, fsm is free space map, ~6
max_locks_per_transaction = 64 # min 10
sort_mem = 16384 # min 64, size in KB
effective_cache_size = 1700000 # typically 8KB each

这些似乎很合理...... ap艺术从有效的缓存大小，

可能砍掉零：


effective_cache_size = 170000＃约1.2G


我想知道原来的设置，你的ram的6倍（如果我的算术是

ok）*可能*导致有趣的优化器选择....


接下来要做的是检查查询的EXPLAIN输出，并且b $ b考虑可能的智能访问计划（例如索引，
部分索引）然后可能是聪明的数据重组（例如

集群，基于触发器的汇总摘要）如果你还需要更多

速度。


问候


马克

------------ ---------------（播出结束）---------------------------

提示4：不要杀死-9''邮政局长

These seem pretty reasonable... apart from the effective cache size,
maybe chop off a zero :

effective_cache_size = 170000 # about 1.2G

I wonder if the original setting, 6 times your ram (if my arithmetic is
ok) *may* result in funny optimizer choices....

The thing to do next is examine EXPLAIN outputs for your queries, and
consider what smarter access plans might be possible (e.g. indexes,
partial indexes) and then maybe clever data re-orgizations (e.g.
clusters, trigger based summaries of aggregates) if you still need more
speed.

regards

Mark
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster

嗨Stephen，

斯蒂芬罗伯特诺里斯写道：

Hi Stephen,

Stephen Robert Norris wrote:

从长时间的实验来看，你做得好多了，确保你的指数和查询更好使用缓冲区空间是最佳的。一旦选择合理的值，缓冲区空间调整可能会使您获得几个百分点的性能;查询调优可以让你获得数量级。

Speaking from long experimentation, you''re much, much better off making
sure your indices and queries are optimal that messing around with
buffer space. Buffer space tuning might get you a few percent
performance once you pick a reasonable value; query tuning can get you
orders of magnitude.

我调查了我的查询并且所有使用索引并且似乎很快，但是当

我的客户端应用程序访问pg数据似乎有点慢。我安装了

MSDE（mssql限制版）并将相同的数据从pg复制到MSDE，我很惊讶，因为运行同样的查询并使用相同的数据和MSDE 似乎要快一点。

描述在previus消息中pg性能增加了一点但

仍然存在与MSDE没什么大不同，考虑到硬件，pg

是在一个真正的服务器（双Xeon 2.4 Ghz，2G RAM，3个36G SCSI驱动器在

RAID5上）和服务器没有投入生产，MSDE处于AMD Athlon 1Gh

，256RAM y应该期望从pg获得更好的性能。我想知道ODBC

是否会影响性能如此之高，我目前的ODBC驱动程序是7.03.02。


谢谢


-

Josu？马尔多纳多。


---------------------------（播出结束）---- -----------------------

提示4：不要杀死-9''postmaster

I tunned my querys and all uses indexes and seems to be fast, but when
my client app acccess the pg data it seems a little slow. I installed
MSDE (mssql limited version) and copied the same data from pg to MSDE, I
was surprised cuz running the same query with the same data and MSDE
seems to be a little bit faster, after the changes in postgresql.conf
described in the previus message pg perfomance increased a little but
still there is no big difference against MSDE, considering hardware, pg
is in a real server (dual Xeon 2.4 Ghz, 2G RAM, 3 36G SCSI drives on a
RAID5) and the server is not in production, MSDE is in a AMD athlon 1Gh
with 256RAM y should expect better performace from pg. I wonder if ODBC
could be affect performance so high, my current ODBC driver is 7.03.02.

Thanks

--
Josu?? Maldonado.

---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster

这篇关于调整postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！