Re: 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