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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Tomas Vondra Sent: May-29-15 20:19 To: Daniel Begin; 'PT' Cc: pgsql-general@xxxxxxxxxxxxxx; 'Melvin Davidson' Subject: 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
