Re: Setting effective_cache size

[Laurenz Albe]

Nandakumar M wrote:
> According to https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server :
> 
> > effective_cache_size should be set to an estimate of how much memory is 
> > available for disk caching by the operating system and within the database 
> > itself, after taking into account what's used by the OS itself and other 
> > applications.
> 
> I intend to run a java application and postgres server in the same
> server machine. The java application requires 2 GB RAM max.
> 
> Considering that our server machine has 4 GB RAM, should I reduce the
> effective_cache_size to say 768 MB or am I better off with the default
> 4 GB value?
> 
> This is particularly confusing because in this thread Tom Lane says
> the following
> 
> > I see no problem with a value of say 4GB;
> > that's very unlikely to be worse than the pre-9.4 default (128MB) on any 
> > modern machine.
> 
> PS : I got the value 768 MB from https://pgtune.leopard.in.ua/#/ by
> giving 1 GB as the amount of memory postgres can use.

I would set effective_cache_size to 2GB or a little lower.

This is a number that tells the optimizer how likely it is to
find index data cached if the index is used repeatedly, so it
is not important to get the value exactly right.

Yours,
Laurenz Albe

Re: How can sort performance be so different

[Bob Jolliffe]

Hi Peter

I did check out using ICU and the performance does indeed seem
comparable with C locale:

EXPLAIN ANALYZE select * from chart order by name COLLATE "lo-x-icu";
QUERY PLAN
---
 Sort  (cost=1470.65..1504.24 rows=13436 width=1203) (actual
time=82.752..85.723 rows=13436 loops=1)
   Sort Key: name COLLATE "lo-x-icu"
   Sort Method: quicksort  Memory: 6253kB
   ->  Seq Scan on chart  (cost=0.00..549.36 rows=13436 width=1203)
(actual time=0.043..12.634 rows=13436 loops=1)
 Planning time: 1.610 ms
 Execution time: 96.060 ms
(6 rows)

The Laos folk have confirmed that the sort order with C locale was not
correct.  So setting the ICU locale seems to be the way forward.

The problem is that this is a large java application with a great
number of tables and queries.  Also it is used in 60+ countries not
just Laos.  So we cannot simply modify the queries or table creation
scripts directly such as in the manner above.  I was hoping the
solution would just be to set a default locale on the database
(perhaps even und-x-icu) but I see now that this doesn't seem to be
currently possible with postgresql 10 ie. set the locale on database
creation to a *-icu locale.

Is this also a limitation on postgresql 11?  (Upgrading would be possible)

Any other workarounds worth trying?  The magnitude of this issue is
significant - 1000x slower on these basic sorts is crippling the
application, probably also in a number of other queries.

Regards
Bob

On Wed, 30 Jan 2019 at 23:54, Peter Geoghegan  wrote:
>
> On Wed, Jan 30, 2019 at 3:57 AM Bob Jolliffe  wrote:
> > (i) whether the sort order makes sense for the Laos names; and
> > (ii) what the locale settings are on the production server where the
> > problem was first reported.
> >
> > There will be some turnaround before I get this information.  I am
> > guessing that the database is using "en_US" rather than anything Laos
> > specific.  In which case "C" would probably be no worse re sort order.
> > But will know better soon.
> >
> > This has been a long but very fruitful investigation.  Thank you all for 
> > input.
>
> If you can find a way to use an ICU collation, it may be possible to
> get Laotian sort order with performance that's a lot closer to the
> performance you see with the C locale. The difference that you're
> seeing is obviously explainable in large part by the C locale using
> the abbreviated keys technique. The system glibc's collations cannot
> use this optimization.
>
> I believe that some locales have inherently more expensive
> normalization processes (comparisons) than others, but if you can
> effective amortize the cost per key by building an abbreviated key, it
> may not matter that much. And, ICU may be faster than glibc anyway.
>
> --
> Peter Geoghegan