Re: effective_io_concurrency on EBS/gp2

[Claudio Freire]

On Mon, Feb 5, 2018 at 8:26 AM, Vitaliy Garnashevich
 wrote:
>> I mean, that the issue is indeed affected by the order of rows in the
>> table. Random heap access patterns result in sparse bitmap heap scans,
>> whereas less random heap access patterns result in denser bitmap heap
>> scans. Dense scans have large portions of contiguous fetches, a
>> pattern that is quite adversely affected by the current prefetch
>> mechanism in linux.
>>
>
> Thanks for your input.
>
> How can I test a sparse bitmap scan? Can you think of any SQL commands which
> would generate data and run such scans?
>
> Would a bitmap scan over expression index ((aid%1000)=0) do a sparse bitmap
> scan?

If you have a minimally correlated index (ie: totally random order),
and suppose you have N tuples per page, you need to select less (much
less) than 1/Nth of the table.

Re: SV: bad plan using nested loops

[Tomas Vondra]

On 02/02/2018 10:02 AM, Johan Fredriksson wrote:
> tor 2018-02-01 klockan 20:34 + skrev Johan Fredriksson:
>>> Johan Fredriksson  writes:
 Bad plan: https://explain.depesz.com/s/avtZ
 Good plan: https://explain.depesz.com/s/SJSt
 Any suggestions on how to make the planner make better decisions
 for
 this query?
>>>
>>> Core of the problem looks to be the misestimation here:
>>>
>>>    Index Only Scan using shredder_cgm1 on
>>> public.cachedgroupmembers cachedgroupmembers_4
>>> (cost=0.43..2.33 rows=79 width=8) (actual time=0.020..0.903
>>> rows=1492 loops=804)
>>>  Output: cachedgroupmembers_4.memberid,
>>> cachedgroupmembers_4.groupid,
>>> cachedgroupmembers_4.disabled
>>>  Index Cond: ((cachedgroupmembers_4.memberid =
>>> principals_1.id) AND
>>> (cachedgroupmembers_4.disabled = 0))
>>>  Heap Fetches: 5018
>>>
>>> Probably, memberid and disabled are correlated but the planner
>>> doesn't
>>> know that, so it thinks the index condition is way more selective
>>> than it
>>> actually is.  In PG 10, you could very possibly fix that by
>>> installing
>>> extended statistics on that pair of columns.  See
>>>
>>> https://www.postgresql.org/docs/current/static/planner-stats.html#P
>>> LANNER-STATS-EXTENDED
>>
>> I'm not sure what you mean by correlated, but there are only a
>> handful (164 when I check it) disabled groupmembers out of total 7.5
>> million.
>> I'll give CREATE STATISTICS on those columns a shot and see if it
>> gets any better.
> 
> It looks like you are right, Tom. There actually exists full
> correlation between memberid, groupid and disabled.
> 
> rt4=# SELECT stxname, stxkeys, stxdependencies FROM pg_statistic_ext;
>  stxname  | stxkeys |   stxdependencies
> ---+-+--
>  cgm_stat2 | 2 6 | {"2
> => 6": 1.00}
>  cgm_stat1 | 3 6 | {"3 => 6": 1.00}
> (2 rows)
> 
> However, this does not help the planner. It still picks the bad plan.
> 

Yeah :-( Unfortunately, we're not using the extended statistics to
improve join cardinality estimates yet. PostgreSQL 10 can only use them
to improve estimates on individual tables, and judging by the progress
on already submitted improvements, it doesn't seem very likely to change
in PostgreSQL 11.

regards
Tomas

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

Re: OT: Performance of VM

[Andreas Kretschmer]

Am 05.02.2018 um 17:22 schrieb Andrew Kerber:
Oracle has a problem with transparent hugepages, postgres may well 
have the same problem, so consider disabling transparent hugepages. 


yes, that's true.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: OT: Performance of VM

[Andreas Kretschmer]

Am 05.02.2018 um 14:14 schrieb Thomas Güttler:
What do you suggest to get some reliable figures? 


sar is often recommended, see 
https://blog.2ndquadrant.com/in-the-defense-of-sar/.


Can you exclude other reasons like vacuum / vacuum freeze?



Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

OT: Performance of VM

[Thomas Güttler]

This is a bit off-topic, since it is not about the performance of PG itself.

But maybe some have the same issue.

We run PostgreSQL in virtual machines which get provided by our customer.

We are not responsible for the hypervisor and have not access to it.

The IO performance of our application was terrible slow yesterday.

The users blamed us, but it seems that there was something wrong with the 
hypervisor.

For the next time I would like to have reliable figures, to underline my guess that the hypervisor (and not our 
application) is the bottle neck.


I have the vague strategy to make some io performance check every N minutes and 
record the numbers.

Of course I could do some dirty scripting, but I would like to avoid to re-invent things. I guess this was already 
solved by people which have more brain and more experience than I have :-)


What do you suggest to get some reliable figures?

Regards,
  Thomas Güttler

--
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines

Re: effective_io_concurrency on EBS/gp2

[Vitaliy Garnashevich]

I mean, that the issue is indeed affected by the order of rows in the
table. Random heap access patterns result in sparse bitmap heap scans,
whereas less random heap access patterns result in denser bitmap heap
scans. Dense scans have large portions of contiguous fetches, a
pattern that is quite adversely affected by the current prefetch
mechanism in linux.



Thanks for your input.

How can I test a sparse bitmap scan? Can you think of any SQL commands 
which would generate data and run such scans?


Would a bitmap scan over expression index ((aid%1000)=0) do a sparse 
bitmap scan?


Regards,
Vitaliy