Re: Planner cost adjustments

Thank Tomas,
Here are few answers to your questions/comments

- Write cache is the on-drive write cache and I agree on the risks... I
plugged them on a UPS.
- Spread over is done with tablespaces
- 30% & 40% ratios are that 3 indexes/10 and 4 tables/10 are larger than
10GB
- Confused comments about random_page_cost setting? Well, I was just citing
the doc I found that was confusing for me (at this stage!-)
- Sadly I haven't provided any example queries? Well you are right. When I
found this strange behavior a couple of weeks ago, I did not questioned
myself too much and did not keep comparison stats, but as things are moving
forward, I am expecting the problem to rise again so I asked the list hoping
there was a simple "just do that" answer floating around. However, as PT
suggested, at one point I will have to run new tests and I'll then let you
know :-)
- Why are you tuning temp_buffers? Simply because looking at others config
(on the web) they tend to have more than the standard value while dealing
with large and complex queries
- Shouldn't you tune shared_buffers instead? Yes but I forgot to mention
that...
Shared_buffers = 128MB, since it seems that "on Windows the useful range is
64MB to 512MB".

Best regards,
Daniel

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tomas Vondra
Sent: May-29-15 20:19
To: Daniel Begin; 'PT'
Cc: pgsql-general(at)postgresql(dot)org; 'Melvin Davidson'
Subject: Re: [GENERAL] Planner cost adjustments

Hi,

On 05/29/15 22:56, Daniel Begin wrote:
> Omg! I was not expecting such a step-by-step procedure, thanks!
> I'll follow the guide :-)
>
> Since I was about to provide a bit of context as asked by Tomas, here
> it is for those who are interested...
> Best regards,
> Daniel
>
> A bit of the required context...
> I am running all this on my personal PC: Windows 64b, i7 chip, 16GB ram.
> The PostgreSQL 9.3 cluster is spread over 3X3TB external drives with
> write caching. Most tables are static (no insert).
>
> My largest table looks like this...
> Records composed of: 3 bigint, 2 boolean, 1 timestamp and 1 geography
type.
> Number of records: 3870130000
> Table size: 369GB
> Indexes size: 425GB
> - btree(primary key): 125GB
> - btree(another field): 86GB
> - gist(geography): 241GB
>

Huh, I haven't really expected that. Especially on a Windows laptop with
external drives (I assume 7.2k SATA drives connected using USB or maybe
eSATA?). Write cache is the on-drive write cache? Not really a good idea to
leave that enabled (volatile cache, so a risk of data loss or data
corruption).

Also, what do you mean by "spread over"? Are you using tablespaces or some
sort of RAID?

> Overall, 40% of my table and 30% of indexes do not fit in cache >
(effective_cache_size=10GB) but looking at mostly used tables and >
indexes, more than 90% of what I use doesn't fit.

I don't really understand how you compute the 40% and 30%? You have ~800GB
of data+indexes, and only 16GB of RAM, so that's more like 2% of the
database size. Or do you measure the hit ratios somehow?

> On one hand, according to the documentation
> (http://www.postgresql.org/docs/9.3/static/runtime-config-query.html)
> with a cache rate like mine, I should probably increase
> random_page_cost to better reflect the true cost of random storage reads.

I don't follow. Haven't you said in the first post that the database often
chooses sequential scans while index scans are way faster?
Increasing random_page_cost will only push if further towards sequential
scans, making it worse.

> On the other hand however, I found that...
> (https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server)
> "This is not where you should start to search for plan problems.
> Thet random_page_cost is pretty far down this list (at the end in
> fact). If you are getting bad plans, this shouldn't be the first thing
> you look at, even though lowering this value may be effective.
> Instead, you should start by making sure autovacuum is working
> properly, that you are collecting enough statistics, and that you have
> correctly sized the memory parameters for your server--all the things
> gone over above. After you've done all those much more important
> things, ifyou're still getting bad plans then you should see if
> lowering random_page_cost is still useful."

Well, so maybe you're at the point when tuning random_page_cost is the right
next step ... but sadly you haven't provided any example queries, so it's
hard to say. Can you choose a few queries and run EXPLAIN ANALYZE on them
(and post it to explain.depesz.com, and only put the links here)?

> Please find below some the database config's parameters that might be
> of interest...

> Best regards,
> Daniel
>
> General config parameters I have modified temp_buffers = 512MB

Why are you tuning temp_buffers? Shouldn't you tune shared_buffers instead?
I'm not very familiar with Windows, and I vaguely remember issues with
larger shared_buffers values, but AFAIK that improved in the recent
releases.

> work_mem = 16MB
> maintenance_work_mem = 256MB
> checkpoint_segments = 64
> checkpoint_completion_target = 0.8
> effective_cache_size = 10GB
> logging_collector = on
> track_counts = on
> autovacuum = on

Otherwise, I don't see anything terribly misconfigured.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general