Re: shared_buffers/effective_cache_size on 96GB server

On Wed, Oct 10, 2012 at 1:12 PM, Strahinja Kustudić
<strahinjak(at)nordeus(dot)com> wrote:

> @Claudio So you are basically saying that if I have set effective_cache_size
> to 10GB and I have 10 concurrent processes which are using 10 different
> indices which are for example 2GB,

It is the size of the table, not the index, which is primarily of
concern. However, that mostly factors into how postgres uses
effective_cache_size, not how you set it.

> it would be better to set the
> effective_cache size to 1GB?

If 10GB were the correct setting for a system with only one process
trying to run that type of query at a time, then 1 GB would be the
correct setting for 10 concurrent processing running that type of
query concurrently.

But, I think there is little reason to think that 10GB actually would
be the correct setting for the first case, so little reason to think
1GB is the correct setting in the 2nd case.

Since you have 96GB of RAM, I would think that 10GB is an appropriate
setting *already taking concurrency into account*, and would be too
low if you were not expecting any concurrency.

In any case, the setting of effective_cache size shouldn't affect
simple inserts or copies at all, since those operations don't use
large index range scans.

> Since if I leave it at 10GB each running
> process query planner will think the whole index is in cache and that won't
> be true? Did I get that right?

It isn't mostly about how much of the index is in cache, but rather
how much of the table is in cache.

>
> @Jeff I have 4 drives in RADI10. The database has around 80GB of indices.

That seems like a pretty small disk set for a server of this size.

Do you know what percentage of that 80GB of indices gets dirtied
during any given round of batch loading/updating? I think that that
could easily be your bottleneck, how fast you can write out dirtied
index pages, which are likely being written randomly rather than
sequentially.

> I'm not experiencing any slow downs, I would just like to increase the
> performance of update/insert, since it needs to insert a lot of data and to
> make the select queries faster since they are done on a lot of big tables.

I think these two things are in tension. The faster the inserts and
updates run, the more resources they will take away from the selects
during those periods. If you are doing batch copies, then as long as
one batch has finished before the next one needs to start, isn't that
fast enough? Maybe the goal should be to throttle the inserts so that
the selects see a more steady competition for IO.

> I
> am experiencing a lot of performance problems when autovacuum kicks in for a
> few big tables, since it slows downs things a lot.

You can tune the autovacuum to make them slower. But it sounds like
maybe you should have put more money into spindles and less into CPU
cores. (I think that is a very common situation to be in).

Cheers,

Jeff