整个数据库有足够的RAM ..成本不谈，这会是最快的吗？ [英] Enough RAM for entire Database.. cost aside, is this going to be fastest?

问题描述

你好，


对不起这个新问题。


简单来说，我的问题：

- ----------------

我希望数据库我正在努力达到

12的顺序-16千兆字节，我有兴趣尽可能多地了解

如何在linux系统上尽可能快地实现这一目标。我之前没有运行这么大的数据库。数据库的性质使得连续查询很可能导致缓存性能不佳。


我对磁盘，操作系统有很多经验，缓存，虚拟

内存，RAM等等 - 只是没有一个很快运行庞大的数据库！

---------------- ---


我已经阅读了所有性能调整和配置的东西，但是

有一个我无法得到的基本问题回答：


我的问题：

--------------------

如果我有能力让整个数据库驻留在RAM中（在
postgresql共享缓冲区缓存中，交换到实际内存中）而不会影响

其他服务在机器上，有什么原因我不应该这样做，除了成本之外还有b $ b吗？ （！）

--------------------

基本上，我发现它几乎不可能预测''多少内存是什么？

''。我知道我不需要*整个*数据库坐在RAM中，而且这个答案的很多

取决于很多东西 - IO的速度，

查询等等。但是当你达到一定数量的RAM时，（具体地说，

金额，不需要换掉任何东西），那么事情肯定会有点

更确定......还是他们？


所以，我可以用例如16 GB共享缓冲区缓存设置postgresql

并期望postgresql后端进程像风一样飞（CPU，RAM

和磁盘写入速度允许）？


我明白写入会延迟如果以某种方式设置

，更新的进展，那没关系 - 我真的只是想知道是否有一些

*其他*边界将会挡路。我已经读过我应该能够配置一个linux盒子（带有正确的处理器）以便能够处理高达64GB的内存，并且我需要一块b $ b的内存。意识到更多深奥的盒子，比如SGI Altix 3000，它们可以远远高于b $ b，但也许这太过分了......


如果有任何资源在那里，指向其他经验的其他人试图强迫一个庞大的数据库主要从RAM运行，我将会感激这些链接。

感谢链接。


非常感谢

Andy

____

Hello,

Sorry for this newbish question.

Briefly, my problem:
------------------
I expect the database I''m working on to reach something in the order of
12-16 Gigabytes, and I am interested in understanding as much as I can about
how I can make this go as fast as possible on a linux system. I haven''t run
such a large database before. The nature of the database is such that
successive queries are very likely to lead to poor cache performance.

I have lots of experience with disks, operating systems, caching, virtual
memory, RAM etc. - just none running gargantuan databases very quickly!
-------------------

I''ve read all the performance tuning and configuration stuff I can, but
there is one basic question I can''t get an answer to:

My question:
--------------------
If I can afford to have the entire database residing in RAM (within the
postgresql shared buffer cache, swapped into real memory) without impacting
other services on the machine, is there any reason why I shouldn''t do it,
other than cost? (!)
--------------------
Basically, I''m finding it almost impossible to predict ''how much RAM is
right''. I know I don''t need the *entire* database to sit in RAM, and a lot
of this answer depends on a lot of things - the speed of IO, the nature of
queries etc. But when you get to a certain amount of RAM, (specifically, the
amount where nothing needs to be swapped out), then surely things get a bit
more certain... or do they?

So, could I, for example, setup postgresql with a 16 GB shared buffer cache
and expect the postgresql backend processes to fly like the wind (CPU, RAM
and disk write speed permitting)?

I understand that writes can delay the progression of updates if setup in a
certain way, and that''s ok - I''m really just wondering if there are some
*other* boundaries that will get in the way. I''ve read that I should be able
to configure a linux box (with the right processors) to address up to 64GB
of RAM, and I''m aware of more esoteric boxes like the SGI Altix 3000 which
can go far higher, but maybe that''s overkill..

If there are any resources out there that point to other experiences of
others trying to coerce a huge database to run largely from RAM, I''d be
grateful for the links.

Many thanks
Andy
____

推荐答案

Andy B写道：

我的问题：
--------------------
如果我能够让整个数据库驻留在RAM中（在
postgresql共享缓冲区缓存中，交换到实际内存中）而不影响机器上的其他服务，我有什么理由不应该'除了费用之外，还要这样做吗？ （！）
--------------------


没有理由不这样做。但是，如果仍然存在分歧，那将是多么的分歧。你没有为postgresql分配16GB的共享缓冲区。

这不会给你你需要的性能。

基本上，我发现它几乎是不可能的预测有多少RAM正确。我知道我不需要*整个*数据库坐在RAM中，这个答案很多取决于很多东西 - IO的速度，
查询的性质等等。但是当你达到一定数量的RAM时（具体来说，没有什么需要换掉的话），那么事情肯定会更加确定......或者他们会这样做吗？


是的。如果您的磁盘上的数据库大小为+ 256MB，那么您应该为一台专用的数据库服务器机器/

完成。所以，我可以用例如设置postgresql 16 GB共享缓冲区缓存
并期望postgresql后端进程像风一样飞行（CPU，RAM
和磁盘写入速度允许）？


嗯..你可以分配32MB的共享缓冲区，但仍然可以实现你想要的效果。 Postgresql永远不会获得16GB的共享缓冲区。在最近几个小时内看到其他帖子

Tom。

如果有任何资源可以指向其他人试图胁迫庞大数据库的其他经历主要是从RAM运行，我会感激链接。

My question:
--------------------
If I can afford to have the entire database residing in RAM (within the
postgresql shared buffer cache, swapped into real memory) without impacting
other services on the machine, is there any reason why I shouldn''t do it,
other than cost? (!)
--------------------
There is no reason why you should not do it. How remains to be a point of
disagreement though. You don''t allocate 16GB of shared buffers to postgresql.
That won''t give you performance you need.
Basically, I''m finding it almost impossible to predict ''how much RAM is
right''. I know I don''t need the *entire* database to sit in RAM, and a lot
of this answer depends on a lot of things - the speed of IO, the nature of
queries etc. But when you get to a certain amount of RAM, (specifically, the
amount where nothing needs to be swapped out), then surely things get a bit
more certain... or do they?
Yes. If you have database size on disk + 256MB, then you should be done for a
dedicated database server machine/
So, could I, for example, setup postgresql with a 16 GB shared buffer cache
and expect the postgresql backend processes to fly like the wind (CPU, RAM
and disk write speed permitting)?
Umm.. you could assign 32MB of shared buffers and still achieve the effect you
want. Postgresql would not ever get 16GB of shared buffers. See other post by
Tom in last few hours.
If there are any resources out there that point to other experiences of
others trying to coerce a huge database to run largely from RAM, I''d be
grateful for the links.

好​​吧，我没有随时都运行那么大但是它很容易猜到。一个拥有16GB内存和64位Linux的

opteron将以最便宜的价格为您提供最优惠的价格。添加你选择的磁盘阵列。


HTH


Shridhar


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

提示3：如果通过Usenet发布/阅读，请发送适当的

subscribe-nomail命令给 ma ******* @ postgresql.org ，以便您的

消息可以干净地通过邮件列表

Well, I haven''t run it that large anytime but it is very easy to guess. An
opteron with 16GB of RAM and a 64 bit linux will get you there in cheapest
fashion. Add the disk array of your choice.

HTH

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

你好Shridhar，


感谢您的回复。

Hello Shridhar,

Thanks for the reply.

有没理由你不应该这样做。但是如何仍然存在分歧。你没有为
postgresql分配16GB的共享缓冲区。那不会给你你需要的表现。

There is no reason why you should not do it. How remains to be a point of
disagreement though. You don''t allocate 16GB of shared buffers to postgresql. That won''t give you performance you need.

我认为在另一个帖子中，汤姆也在暗指这一点。什么是关于

共享缓冲区缓存行为，当它非常大的时候会降低效率？ （假设它占用的地址空间被分配到RAM

页面）


有没有一个好地方我可以找一些其深度细节< br $> b $ b行为？


非常感谢，

Andy

I think in the other thread, Tom was alluding to this too. What is it about
the shared buffer cache behaviour that makes it inefficient when it is very
large? (assuming that the address space it occupies is allocated to RAM
pages)

Is there a good place I could look for some in depth details of its
behaviour?

Many thanks,
Andy

<发布&邮寄>


Andy B写道：

<posted & mailed>

Andy B wrote:

你好Shridhar，

感谢您的回复。

Hello Shridhar,

Thanks for the reply.

没有理由不这样做。但是如何仍然存在分歧。你没有为postgresql分配16GB的共享缓冲区。

There is no reason why you should not do it. How remains to be a point of
disagreement though. You don''t allocate 16GB of shared buffers to postgresql.

那不会给你你需要的表现。

That won''t give you performance you need.

我想在另一个帖子里汤姆也暗指这一点。什么是关于共享缓冲区缓存行为，当它非常大时会使其效率低下？ （假设它占用的地址空间被分配给RAM页面）

I think in the other thread, Tom was alluding to this too. What is it
about the shared buffer cache behaviour that makes it inefficient when it
is very large? (assuming that the address space it occupies is allocated
to RAM pages)

并不是说使缓存更大是低效的，它''缓存是什么
没有按照你的想法使用。 Postgres不会尝试创建自己的最近使用的数据的大型持久缓存，因为操作系统（特别是Linux，特别是在Opteron上，并为64位编译）在缓存方面真的很好吗？b $ b更好。事实上，其他那些放牧计划和实施

安全性，优化资源的使用就是操作系统的用途。

我能找到一个好地方吗？其行为的深度细节？


此列表的档案中有一些深度。我会开始回寻讨论effective_cache_size的问题，因为这涉及到*耗费*操作系统正在进行的缓存工作的
。
非常感谢，
Andy

It''s not that making the cache bigger is inefficient, it''s that the cache is
not used the way you are thinking. Postgres does not try to create its own
large persistent cache of recently used data, because the OS (especially
Linux, and especially on an Opteron and compiled for 64 bit) is really much
better at caching. In fact, other that herding programs and implementing
security, optimizing the use of resources is what the OS is for.

Is there a good place I could look for some in depth details of its
behaviour?
There''s a good bit of depth in the archives of this list. I would start
searching back for discussions of effective_cache_size, as that is involved
in *costing* the caching job that the OS is doing.

Many thanks,
Andy

--miker

--miker

这篇关于整个数据库有足够的RAM ..成本不谈，这会是最快的吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！