On Wed, Oct 10, 2012 at 1:12 PM, Strahinja Kustudić <strahinjak@xxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
