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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
