Re: 8.2 Autovacuum BUG ?

[pavan95]

Hi all,

Regarding this archive log generation found one observation.  

A table named abc_table id found to be archived every 9'th and 39'th minute.
We are able to find number of tuples deleted from the pg_stat_user_tables
view. 

But to my wonder the number of tuple inserts are shown 0.  How can there be
any delete without any inserts.

It was found that the table is having 2060 rows, where in which all rows are
getting deleted in every 9'th and 39'th minute of an hour. It implies that
those deleted should be inserted before the delete operation.

Also performed vacuum freeze on that table before 9'th minute of an hour it
generated 36 archive logs, and when I tried to do the same operation after
9'th minute(say 11'th minute of the same hour), it is generating the same
number of archive logs.

This is possible only if  the entire table gets updated/recreated.  Now my
final doubt is why the tuple inserts in pg_stat_user_tables is showing 0,
when corresponding deletes are existing?

Please find the below outputs FYR.


--Steps performed on production server:--

--1. Found Count Of Rows in Production
--**
prod_erp=# select count(*) from abc_table;;
 count
---
  2060
(1 row)

--2. Issued 'Select pg_stat_reset();'

--3. Before Delete Statements (Before JAN 31'st 2018 14:09 Hrs)
--

Issued:

select * from pg_stat_user_tables where relname ='abc_table';
-[ RECORD 1 ]-+
relid | 550314
schemaname| public
relname   | abc_table
seq_scan  | 2
seq_tup_read  | 4120
idx_scan  | 0
idx_tup_fetch | 0
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup| 0
n_dead_tup| 0
last_vacuum   |
last_autovacuum   |
last_analyze  |
last_autoanalyze  |
vacuum_count  | 0
autovacuum_count  | 0
analyze_count | 0
autoanalyze_count | 0


--4. After Delete Statements (Before JAN 31'st 2018 14:09 Hrs)
--

select * from pg_stat_user_tables where relname ='abc_table';
-[ RECORD 1 ]-+
relid | 550314
schemaname| public
relname   | abc_table
seq_scan  | 3
seq_tup_read  | 6180
idx_scan  | 2060
idx_tup_fetch | 2060
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 2060
n_tup_hot_upd | 0
n_live_tup| 0
n_dead_tup| 0
last_vacuum   |
last_autovacuum   |
last_analyze  |
last_autoanalyze  |
vacuum_count  | 0
autovacuum_count  | 0
analyze_count | 0
autoanalyze_count | 0


--5. After Delete Statements (Before JAN 31'st 2018 14:39 Hrs)
--  

select * from pg_stat_user_tables where relname ='abc_table';
-[ RECORD 1 ]-+
relid | 550314
schemaname| public
relname   | abc_table
seq_scan  | 4
seq_tup_read  | 8240
idx_scan  | 4120
idx_tup_fetch | 4120
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 4120
n_tup_hot_upd | 0
n_live_tup| 0
n_dead_tup| 0
last_vacuum   |
last_autovacuum   |
last_analyze  |
last_autoanalyze  |
vacuum_count  | 0
autovacuum_count  | 0
analyze_count | 0
autoanalyze_count | 0


--6. After Delete Statements (Before JAN 31'st 2018 15:09 Hrs)
--  


select * from pg_stat_user_tables where relname ='abc_table';
-[ RECORD 1 ]-+
relid | 550314
schemaname| public
relname   | abc_table
seq_scan  | 5
seq_tup_read  | 10300
idx_scan  | 6180
idx_tup_fetch | 6180
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 6180
n_tup_hot_upd | 0
n_live_tup| 0
n_dead_tup| 0
last_vacuum   |
last_autovacuum   |
last_analyze  |
last_autoanalyze  |
vacuum_count  | 0
autovacuum_count  | 0
analyze_count | 0
autoanalyze_count | 0



As said above if we compare n_tup_del value in steps 4,5,6 it says us that
entire table is getting deleted(correct me if I'm wrong), but n_tup_ins is
0. 

Regards,
Pavan



--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Re: effective_io_concurrency on EBS/gp2

[hzzhangjiazhi]

HI      I think this parameter will be usefull when the storage using RAID stripe , otherwise turn up this parameter is meaningless when only has one device。






发自网易邮箱大师


On 2/1/2018 04:29，Vitaliy Garnashevich wrote： 


I've done some more tests. Here they are all:io1, 100 GB SSD, 1000 IOPSeffective_io_concurrency=0 Execution time: 40333.626 mseffective_io_concurrency=1 Execution time: 163840.500 mseffective_io_concurrency=2 Execution time: 162606.330 mseffective_io_concurrency=4 Execution time: 163670.405 mseffective_io_concurrency=8 Execution time: 161800.478 mseffective_io_concurrency=16 Execution time: 161962.319 mseffective_io_concurrency=32 Execution time: 160451.435 mseffective_io_concurrency=64 Execution time: 161763.632 mseffective_io_concurrency=128 Execution time: 161687.398 mseffective_io_concurrency=256 Execution time: 160945.066 mseffective_io_concurrency=256 Execution time: 161226.440 mseffective_io_concurrency=128 Execution time: 161977.954 mseffective_io_concurrency=64 Execution time: 159122.006 mseffective_io_concurrency=32 Execution time: 154923.569 mseffective_io_concurrency=16 Execution time: 160922.819 mseffective_io_concurrency=8 Execution time: 160577.122 mseffective_io_concurrency=4 Execution time: 157509.481 mseffective_io_concurrency=2 Execution time: 161806.713 mseffective_io_concurrency=1 Execution time: 164026.708 mseffective_io_concurrency=0 Execution time: 40196.182 msgp2, 100 GB SSDeffective_io_concurrency=0 Execution time: 40262.781 mseffective_io_concurrency=1 Execution time: 98125.987 mseffective_io_concurrency=2 Execution time: 55343.776 mseffective_io_concurrency=4 Execution time: 52505.638 mseffective_io_concurrency=8 Execution time: 54954.024 mseffective_io_concurrency=16 Execution time: 54346.455 mseffective_io_concurrency=32 Execution time: 55196.626 mseffective_io_concurrency=64 Execution time: 55057.956 mseffective_io_concurrency=128 Execution time: 54963.510 mseffective_io_concurrency=256 Execution time: 54339.258 msio1, 1 TB SSD, 3000 IOPSeffective_io_concurrency=0 Execution time: 40691.396 mseffective_io_concurrency=1 Execution time: 87524.939 mseffective_io_concurrency=2 Execution time: 54197.982 mseffective_io_concurrency=4 Execution time: 55082.740 mseffective_io_concurrency=8 Execution time: 54838.161 mseffective_io_concurrency=16 Execution time: 52561.553 mseffective_io_concurrency=32 Execution time: 54266.847 mseffective_io_concurrency=64 Execution time: 54683.102 mseffective_io_concurrency=128 Execution time: 54643.874 mseffective_io_concurrency=256 Execution time: 42944.938 msgp2, 1 TB SSDeffective_io_concurrency=0 Execution time: 40072.880 mseffective_io_concurrency=1 Execution time: 83528.679 mseffective_io_concurrency=2 Execution time: 55706.941 mseffective_io_concurrency=4 Execution time: 55664.646 mseffective_io_concurrency=8 Execution time: 54699.658 mseffective_io_concurrency=16 Execution time: 54632.291 mseffective_io_concurrency=32 Execution time: 54793.305 mseffective_io_concurrency=64 Execution time: 55227.875 mseffective_io_concurrency=128 Execution time: 54638.744 mseffective_io_concurrency=256 Execution time: 54869.761 msst1, 500 GB HDDeffective_io_concurrency=0 Execution time: 40542.583 mseffective_io_concurrency=1 Execution time: 119996.892 mseffective_io_concurrency=2 Execution time: 51137.998 mseffective_io_concurrency=4 Execution time: 42301.922 mseffective_io_concurrency=8 Execution time: 42081.877 mseffective_io_concurrency=16 Execution time: 42253.782 mseffective_io_concurrency=32 Execution time: 42087.216 mseffective_io_concurrency=64 Execution time: 42112.105 mseffective_io_concurrency=128 Execution time: 42271.850 mseffective_io_concurrency=256 Execution time: 42213.074 msRegards,Vitaliy

Re: effective_io_concurrency on EBS/gp2

[Jeff Janes]

On Wed, Jan 31, 2018 at 4:03 AM, Vitaliy Garnashevich <
vgarnashev...@gmail.com> wrote:

>
> The results look really confusing to me in two ways. The first one is that
> I've seen recommendations to set effective_io_concurrency=256 (or more) on
> EBS.


I would not expect this to make much of a difference on a table which is
perfectly correlated with the index.  You would have to create an accounts
table which is randomly ordered to have a meaningful benchmark of the eic
parameter.

I don't know why the default for eic is 1.  It seems like that just turns
on the eic mechanism, without any hope of benefiting from it.

Cheers,

Jeff

Re: effective_io_concurrency on EBS/gp2

[Vitaliy Garnashevich]

I've done some more tests. Here they are all:

io1, 100 GB SSD, 1000 IOPS
effective_io_concurrency=0 Execution time: 40333.626 ms
effective_io_concurrency=1 Execution time: 163840.500 ms
effective_io_concurrency=2 Execution time: 162606.330 ms
effective_io_concurrency=4 Execution time: 163670.405 ms
effective_io_concurrency=8 Execution time: 161800.478 ms
effective_io_concurrency=16 Execution time: 161962.319 ms
effective_io_concurrency=32 Execution time: 160451.435 ms
effective_io_concurrency=64 Execution time: 161763.632 ms
effective_io_concurrency=128 Execution time: 161687.398 ms
effective_io_concurrency=256 Execution time: 160945.066 ms
effective_io_concurrency=256 Execution time: 161226.440 ms
effective_io_concurrency=128 Execution time: 161977.954 ms
effective_io_concurrency=64 Execution time: 159122.006 ms
effective_io_concurrency=32 Execution time: 154923.569 ms
effective_io_concurrency=16 Execution time: 160922.819 ms
effective_io_concurrency=8 Execution time: 160577.122 ms
effective_io_concurrency=4 Execution time: 157509.481 ms
effective_io_concurrency=2 Execution time: 161806.713 ms
effective_io_concurrency=1 Execution time: 164026.708 ms
effective_io_concurrency=0 Execution time: 40196.182 ms

gp2, 100 GB SSD
effective_io_concurrency=0 Execution time: 40262.781 ms
effective_io_concurrency=1 Execution time: 98125.987 ms
effective_io_concurrency=2 Execution time: 55343.776 ms
effective_io_concurrency=4 Execution time: 52505.638 ms
effective_io_concurrency=8 Execution time: 54954.024 ms
effective_io_concurrency=16 Execution time: 54346.455 ms
effective_io_concurrency=32 Execution time: 55196.626 ms
effective_io_concurrency=64 Execution time: 55057.956 ms
effective_io_concurrency=128 Execution time: 54963.510 ms
effective_io_concurrency=256 Execution time: 54339.258 ms

io1, 1 TB SSD, 3000 IOPS
effective_io_concurrency=0 Execution time: 40691.396 ms
effective_io_concurrency=1 Execution time: 87524.939 ms
effective_io_concurrency=2 Execution time: 54197.982 ms
effective_io_concurrency=4 Execution time: 55082.740 ms
effective_io_concurrency=8 Execution time: 54838.161 ms
effective_io_concurrency=16 Execution time: 52561.553 ms
effective_io_concurrency=32 Execution time: 54266.847 ms
effective_io_concurrency=64 Execution time: 54683.102 ms
effective_io_concurrency=128 Execution time: 54643.874 ms
effective_io_concurrency=256 Execution time: 42944.938 ms

gp2, 1 TB SSD
effective_io_concurrency=0 Execution time: 40072.880 ms
effective_io_concurrency=1 Execution time: 83528.679 ms
effective_io_concurrency=2 Execution time: 55706.941 ms
effective_io_concurrency=4 Execution time: 55664.646 ms
effective_io_concurrency=8 Execution time: 54699.658 ms
effective_io_concurrency=16 Execution time: 54632.291 ms
effective_io_concurrency=32 Execution time: 54793.305 ms
effective_io_concurrency=64 Execution time: 55227.875 ms
effective_io_concurrency=128 Execution time: 54638.744 ms
effective_io_concurrency=256 Execution time: 54869.761 ms

st1, 500 GB HDD
effective_io_concurrency=0 Execution time: 40542.583 ms
effective_io_concurrency=1 Execution time: 119996.892 ms
effective_io_concurrency=2 Execution time: 51137.998 ms
effective_io_concurrency=4 Execution time: 42301.922 ms
effective_io_concurrency=8 Execution time: 42081.877 ms
effective_io_concurrency=16 Execution time: 42253.782 ms
effective_io_concurrency=32 Execution time: 42087.216 ms
effective_io_concurrency=64 Execution time: 42112.105 ms
effective_io_concurrency=128 Execution time: 42271.850 ms
effective_io_concurrency=256 Execution time: 42213.074 ms

Regards,
Vitaliy

Re: effective_io_concurrency on EBS/gp2

[Claudio Freire]

On Wed, Jan 31, 2018 at 1:57 PM, Vitaliy Garnashevich
 wrote:
> More tests:
>
> io1, 100 GB:
>
> effective_io_concurrency=0
>  Execution time: 40333.626 ms
> effective_io_concurrency=1
>  Execution time: 163840.500 ms

In my experience playing with prefetch, e_i_c>0 interferes with kernel
read-ahead. What you've got there would make sense if what postgres
thinks will be random I/O ends up being sequential. With e_i_c=0, the
kernel will optimize the hell out of it, because it's a predictable
pattern. But with e_i_c=1, the kernel's optimization gets disabled but
postgres isn't reading much ahead, so you get the worst possible case.

Re: effective_io_concurrency on EBS/gp2

[Vitaliy Garnashevich]

More tests:

io1, 100 GB:

effective_io_concurrency=0
 Execution time: 40333.626 ms
effective_io_concurrency=1
 Execution time: 163840.500 ms
effective_io_concurrency=2
 Execution time: 162606.330 ms
effective_io_concurrency=4
 Execution time: 163670.405 ms
effective_io_concurrency=8
 Execution time: 161800.478 ms
effective_io_concurrency=16
 Execution time: 161962.319 ms
effective_io_concurrency=32
 Execution time: 160451.435 ms
effective_io_concurrency=64
 Execution time: 161763.632 ms
effective_io_concurrency=128
 Execution time: 161687.398 ms
effective_io_concurrency=256
 Execution time: 160945.066 ms

effective_io_concurrency=256
 Execution time: 161226.440 ms
effective_io_concurrency=128
 Execution time: 161977.954 ms
effective_io_concurrency=64
 Execution time: 159122.006 ms
effective_io_concurrency=32
 Execution time: 154923.569 ms
effective_io_concurrency=16
 Execution time: 160922.819 ms
effective_io_concurrency=8
 Execution time: 160577.122 ms
effective_io_concurrency=4
 Execution time: 157509.481 ms
effective_io_concurrency=2
 Execution time: 161806.713 ms
effective_io_concurrency=1
 Execution time: 164026.708 ms
effective_io_concurrency=0
 Execution time: 40196.182 ms


st1, 500 GB:

effective_io_concurrency=0
 Execution time: 40542.583 ms
effective_io_concurrency=1
 Execution time: 119996.892 ms
effective_io_concurrency=2
 Execution time: 51137.998 ms
effective_io_concurrency=4
 Execution time: 42301.922 ms
effective_io_concurrency=8
 Execution time: 42081.877 ms
effective_io_concurrency=16
 Execution time: 42253.782 ms
effective_io_concurrency=32
 Execution time: 42087.216 ms
effective_io_concurrency=64
 Execution time: 42112.105 ms
effective_io_concurrency=128
 Execution time: 42271.850 ms
effective_io_concurrency=256
 Execution time: 42213.074 ms

effective_io_concurrency=256
 Execution time: 42255.568 ms
effective_io_concurrency=128
 Execution time: 42030.515 ms
effective_io_concurrency=64
 Execution time: 41713.753 ms
effective_io_concurrency=32
 Execution time: 42035.436 ms
effective_io_concurrency=16
 Execution time: 42221.581 ms
effective_io_concurrency=8
 Execution time: 42203.730 ms
effective_io_concurrency=4
 Execution time: 42236.082 ms
effective_io_concurrency=2
 Execution time: 49531.558 ms
effective_io_concurrency=1
 Execution time: 117160.222 ms
effective_io_concurrency=0
 Execution time: 40059.259 ms

Regards,
Vitaliy

On 31/01/2018 15:46, Gary Doades wrote:


> I've tried to re-run the test for some specific values of 
effective_io_concurrency. The results were the same.


 > That's why I don't think the order of tests or variability in 
"hardware" performance affected the results.


We run many MS SQL server VMs in AWS with more than adequate performance.

AWS EBS performance is variable and depends on various factors, mainly 
the size of the volume and the size of the VM it is attached to. The 
bigger the VM, the more EBS “bandwidth” is available, especially if 
the VM is EBS Optimised.


The size of the disk determines the IOPS available, with smaller disks 
naturally getting less. However, even a small disk with (say) 300 IOPS 
is allowed to burst up to 3000 IOPS for a while and then gets 
clobbered. If you want predictable performance then get a bigger disk! 
If you really want maximum, predictable performance get an EBS 
Optimised VM and use Provisioned IOPS EBS volumes…. At a price!


Cheers,

Gary.

On 31/01/2018 15:01, Rick Otten wrote:

We moved our stuff out of AWS a little over a year ago because the
performance was crazy inconsistent and unpredictable.  I think
they do a lot of oversubscribing so you get strange sawtooth
performance patterns depending on who else is sharing your
infrastructure and what they are doing at the time.

The same unit of work would take 20 minutes each for several
hours, and then take 2 1/2 hours each for a day, and then back to
20 minutes, and sometimes anywhere in between for hours or days at
a stretch.  I could never tell the business when the processing
would be done, which made it hard for them to set expectations
with customers, promise deliverables, or manage the business. 
Smaller nodes seemed to be worse than larger nodes, I only have
theories as to why.  I never got good support from AWS to help me
figure out what was happening.

My first thought is to run the same test on different days of the
week and different times of day to see if the numbers change
radically.  Maybe spin up a node in another data center and
availability zone and try the test there too.

My real suggestion is to move to Google Cloud or Rackspace or
Digital Ocean or somewhere other than AWS.   (We moved to Google
Cloud and have been very happy there.  The performance is much
more consistent, the management UI is more intuitive, AND the cost
for equivalent infrastructure is lower too.)

On Wed, Jan 31, 2018 at 7:03 AM, Vitaliy Garnashevich

RE: effective_io_concurrency on EBS/gp2

[Gary Doades]

 

 

>  I've tried to re-run the test for some specific values of 
> effective_io_concurrency. The results were the same. 

 > That's why I don't think the order of tests or variability in "hardware" 
 > performance affected the results.



We run many MS SQL server VMs in AWS with more than adequate performance.

 

AWS EBS performance is variable and depends on various factors, mainly the size 
of the volume and the size of the VM it is attached to. The bigger the VM, the 
more EBS “bandwidth” is available, especially if the VM is EBS Optimised.

 

The size of the disk determines the IOPS available, with smaller disks 
naturally getting less. However, even a small disk with (say) 300 IOPS is 
allowed to burst up to 3000 IOPS for a while and then gets clobbered. If you 
want predictable performance then get a bigger disk! If you really want 
maximum, predictable performance get an EBS Optimised VM and use Provisioned 
IOPS EBS volumes…. At a price!

 

Cheers,

Gary.

On 31/01/2018 15:01, Rick Otten wrote:

We moved our stuff out of AWS a little over a year ago because the performance 
was crazy inconsistent and unpredictable.  I think they do a lot of 
oversubscribing so you get strange sawtooth performance patterns depending on 
who else is sharing your infrastructure and what they are doing at the time. 

 

The same unit of work would take 20 minutes each for several hours, and then 
take 2 1/2 hours each for a day, and then back to 20 minutes, and sometimes 
anywhere in between for hours or days at a stretch.  I could never tell the 
business when the processing would be done, which made it hard for them to set 
expectations with customers, promise deliverables, or manage the business.  
Smaller nodes seemed to be worse than larger nodes, I only have theories as to 
why.  I never got good support from AWS to help me figure out what was 
happening.

 

My first thought is to run the same test on different days of the week and 
different times of day to see if the numbers change radically.  Maybe spin up a 
node in another data center and availability zone and try the test there too.

 

My real suggestion is to move to Google Cloud or Rackspace or Digital Ocean or 
somewhere other than AWS.   (We moved to Google Cloud and have been very happy 
there.  The performance is much more consistent, the management UI is more 
intuitive, AND the cost for equivalent infrastructure is lower too.)

 

 

On Wed, Jan 31, 2018 at 7:03 AM, Vitaliy Garnashevich  > wrote:

Hi,

I've tried to run a benchmark, similar to this one:

https://www.postgresql.org/message-id/flat/CAHyXU0yiVvfQAnR9cyH%3DHWh1WbLRsioe%3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com#CAHyXU0yiVvfQAnR9cyH=HWh1WbLRsioe=mzRJTHwtr=2azs...@mail.gmail.com

CREATE TABLESPACE test OWNER postgres LOCATION '/path/to/ebs';

pgbench -i -s 1000 --tablespace=test pgbench

echo "" >test.txt
for i in 0 1 2 4 8 16 32 64 128 256 ; do
  sync; echo 3 > /proc/sys/vm/drop_caches; service postgresql restart
  echo "effective_io_concurrency=$i" >>test.txt
  psql pgbench -c "set effective_io_concurrency=$i; set enable_indexscan=off; 
explain (analyze, buffers)  select * from pgbench_accounts where aid between 
1000 and 1000 and abalance != 0;" >>test.txt
done

I get the following results:

effective_io_concurrency=0
 Execution time: 40262.781 ms
effective_io_concurrency=1
 Execution time: 98125.987 ms
effective_io_concurrency=2
 Execution time: 55343.776 ms
effective_io_concurrency=4
 Execution time: 52505.638 ms
effective_io_concurrency=8
 Execution time: 54954.024 ms
effective_io_concurrency=16
 Execution time: 54346.455 ms
effective_io_concurrency=32
 Execution time: 55196.626 ms
effective_io_concurrency=64
 Execution time: 55057.956 ms
effective_io_concurrency=128
 Execution time: 54963.510 ms
effective_io_concurrency=256
 Execution time: 54339.258 ms

The test was using 100 GB gp2 SSD EBS. More detailed query plans are attached.

PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit

The results look really confusing to me in two ways. The first one is that I've 
seen recommendations to set effective_io_concurrency=256 (or more) on EBS. The 
other one is that effective_io_concurrency=1 (the worst case) is actually the 
default for PostgreSQL on Linux.

Thoughts?

Regards,
Vitaliy

 

 

Re: effective_io_concurrency on EBS/gp2

[Pavel Stehule]

2018-01-31 14:15 GMT+01:00 Vitaliy Garnashevich :

> I've tried to re-run the test for some specific values of
> effective_io_concurrency. The results were the same.
>
> That's why I don't think the order of tests or variability in "hardware"
> performance affected the results.
>

AWS uses some intelligent throttling, so it can be related to hardware.


> Regards,
> Vitaliy
>
>
> On 31/01/2018 15:01, Rick Otten wrote:
>
> We moved our stuff out of AWS a little over a year ago because the
> performance was crazy inconsistent and unpredictable.  I think they do a
> lot of oversubscribing so you get strange sawtooth performance patterns
> depending on who else is sharing your infrastructure and what they are
> doing at the time.
>
> The same unit of work would take 20 minutes each for several hours, and
> then take 2 1/2 hours each for a day, and then back to 20 minutes, and
> sometimes anywhere in between for hours or days at a stretch.  I could
> never tell the business when the processing would be done, which made it
> hard for them to set expectations with customers, promise deliverables, or
> manage the business.  Smaller nodes seemed to be worse than larger nodes, I
> only have theories as to why.  I never got good support from AWS to help me
> figure out what was happening.
>
> My first thought is to run the same test on different days of the week and
> different times of day to see if the numbers change radically.  Maybe spin
> up a node in another data center and availability zone and try the test
> there too.
>
> My real suggestion is to move to Google Cloud or Rackspace or Digital
> Ocean or somewhere other than AWS.   (We moved to Google Cloud and have
> been very happy there.  The performance is much more consistent, the
> management UI is more intuitive, AND the cost for equivalent infrastructure
> is lower too.)
>
>
> On Wed, Jan 31, 2018 at 7:03 AM, Vitaliy Garnashevich <
> vgarnashev...@gmail.com> wrote:
>
>> Hi,
>>
>> I've tried to run a benchmark, similar to this one:
>>
>> https://www.postgresql.org/message-id/flat/CAHyXU0yiVvfQAnR9
>> cyH%3DHWh1WbLRsioe%3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com#
>> CAHyXU0yiVvfQAnR9cyH=HWh1WbLRsioe=mzRJTHwtr=2azs...@mail.gmail.com
>>
>> CREATE TABLESPACE test OWNER postgres LOCATION '/path/to/ebs';
>>
>> pgbench -i -s 1000 --tablespace=test pgbench
>>
>> echo "" >test.txt
>> for i in 0 1 2 4 8 16 32 64 128 256 ; do
>>   sync; echo 3 > /proc/sys/vm/drop_caches; service postgresql restart
>>   echo "effective_io_concurrency=$i" >>test.txt
>>   psql pgbench -c "set effective_io_concurrency=$i; set
>> enable_indexscan=off; explain (analyze, buffers)  select * from
>> pgbench_accounts where aid between 1000 and 1000 and abalance != 0;"
>> >>test.txt
>> done
>>
>> I get the following results:
>>
>> effective_io_concurrency=0
>>  Execution time: 40262.781 ms
>> effective_io_concurrency=1
>>  Execution time: 98125.987 ms
>> effective_io_concurrency=2
>>  Execution time: 55343.776 ms
>> effective_io_concurrency=4
>>  Execution time: 52505.638 ms
>> effective_io_concurrency=8
>>  Execution time: 54954.024 ms
>> effective_io_concurrency=16
>>  Execution time: 54346.455 ms
>> effective_io_concurrency=32
>>  Execution time: 55196.626 ms
>> effective_io_concurrency=64
>>  Execution time: 55057.956 ms
>> effective_io_concurrency=128
>>  Execution time: 54963.510 ms
>> effective_io_concurrency=256
>>  Execution time: 54339.258 ms
>>
>> The test was using 100 GB gp2 SSD EBS. More detailed query plans are
>> attached.
>>
>> PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
>> 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
>>
>> The results look really confusing to me in two ways. The first one is
>> that I've seen recommendations to set effective_io_concurrency=256 (or
>> more) on EBS. The other one is that effective_io_concurrency=1 (the worst
>> case) is actually the default for PostgreSQL on Linux.
>>
>> Thoughts?
>>
>> Regards,
>> Vitaliy
>>
>>
>
>

Re: effective_io_concurrency on EBS/gp2

[Vitaliy Garnashevich]

I've tried to re-run the test for some specific values of 
effective_io_concurrency. The results were the same.


That's why I don't think the order of tests or variability in "hardware" 
performance affected the results.


Regards,
Vitaliy

On 31/01/2018 15:01, Rick Otten wrote:
We moved our stuff out of AWS a little over a year ago because the 
performance was crazy inconsistent and unpredictable.  I think they do 
a lot of oversubscribing so you get strange sawtooth performance 
patterns depending on who else is sharing your infrastructure and what 
they are doing at the time.


The same unit of work would take 20 minutes each for several hours, 
and then take 2 1/2 hours each for a day, and then back to 20 minutes, 
and sometimes anywhere in between for hours or days at a stretch.  I 
could never tell the business when the processing would be done, which 
made it hard for them to set expectations with customers, promise 
deliverables, or manage the business.  Smaller nodes seemed to be 
worse than larger nodes, I only have theories as to why.  I never got 
good support from AWS to help me figure out what was happening.


My first thought is to run the same test on different days of the week 
and different times of day to see if the numbers change radically.  
Maybe spin up a node in another data center and availability zone and 
try the test there too.


My real suggestion is to move to Google Cloud or Rackspace or Digital 
Ocean or somewhere other than AWS.   (We moved to Google Cloud and 
have been very happy there.  The performance is much more consistent, 
the management UI is more intuitive, AND the cost for equivalent 
infrastructure is lower too.)



On Wed, Jan 31, 2018 at 7:03 AM, Vitaliy Garnashevich 
> wrote:


Hi,

I've tried to run a benchmark, similar to this one:


https://www.postgresql.org/message-id/flat/CAHyXU0yiVvfQAnR9cyH%3DHWh1WbLRsioe%3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com#CAHyXU0yiVvfQAnR9cyH=HWh1WbLRsioe=mzRJTHwtr=2azs...@mail.gmail.com



CREATE TABLESPACE test OWNER postgres LOCATION '/path/to/ebs';

pgbench -i -s 1000 --tablespace=test pgbench

echo "" >test.txt
for i in 0 1 2 4 8 16 32 64 128 256 ; do
  sync; echo 3 > /proc/sys/vm/drop_caches; service postgresql restart
  echo "effective_io_concurrency=$i" >>test.txt
  psql pgbench -c "set effective_io_concurrency=$i; set
enable_indexscan=off; explain (analyze, buffers)  select * from
pgbench_accounts where aid between 1000 and 1000 and abalance
!= 0;" >>test.txt
done

I get the following results:

effective_io_concurrency=0
 Execution time: 40262.781 ms
effective_io_concurrency=1
 Execution time: 98125.987 ms
effective_io_concurrency=2
 Execution time: 55343.776 ms
effective_io_concurrency=4
 Execution time: 52505.638 ms
effective_io_concurrency=8
 Execution time: 54954.024 ms
effective_io_concurrency=16
 Execution time: 54346.455 ms
effective_io_concurrency=32
 Execution time: 55196.626 ms
effective_io_concurrency=64
 Execution time: 55057.956 ms
effective_io_concurrency=128
 Execution time: 54963.510 ms
effective_io_concurrency=256
 Execution time: 54339.258 ms

The test was using 100 GB gp2 SSD EBS. More detailed query plans
are attached.

PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit

The results look really confusing to me in two ways. The first one
is that I've seen recommendations to set
effective_io_concurrency=256 (or more) on EBS. The other one is
that effective_io_concurrency=1 (the worst case) is actually the
default for PostgreSQL on Linux.

Thoughts?

Regards,
Vitaliy

effective_io_concurrency on EBS/gp2

[Vitaliy Garnashevich]

Hi,

I've tried to run a benchmark, similar to this one:

https://www.postgresql.org/message-id/flat/CAHyXU0yiVvfQAnR9cyH%3DHWh1WbLRsioe%3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com#CAHyXU0yiVvfQAnR9cyH=HWh1WbLRsioe=mzRJTHwtr=2azs...@mail.gmail.com

CREATE TABLESPACE test OWNER postgres LOCATION '/path/to/ebs';

pgbench -i -s 1000 --tablespace=test pgbench

echo "" >test.txt
for i in 0 1 2 4 8 16 32 64 128 256 ; do
  sync; echo 3 > /proc/sys/vm/drop_caches; service postgresql restart
  echo "effective_io_concurrency=$i" >>test.txt
  psql pgbench -c "set effective_io_concurrency=$i; set 
enable_indexscan=off; explain (analyze, buffers)  select * from 
pgbench_accounts where aid between 1000 and 1000 and abalance != 0;" 
>>test.txt

done

I get the following results:

effective_io_concurrency=0
 Execution time: 40262.781 ms
effective_io_concurrency=1
 Execution time: 98125.987 ms
effective_io_concurrency=2
 Execution time: 55343.776 ms
effective_io_concurrency=4
 Execution time: 52505.638 ms
effective_io_concurrency=8
 Execution time: 54954.024 ms
effective_io_concurrency=16
 Execution time: 54346.455 ms
effective_io_concurrency=32
 Execution time: 55196.626 ms
effective_io_concurrency=64
 Execution time: 55057.956 ms
effective_io_concurrency=128
 Execution time: 54963.510 ms
effective_io_concurrency=256
 Execution time: 54339.258 ms

The test was using 100 GB gp2 SSD EBS. More detailed query plans are 
attached.


PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit


The results look really confusing to me in two ways. The first one is 
that I've seen recommendations to set effective_io_concurrency=256 (or 
more) on EBS. The other one is that effective_io_concurrency=1 (the 
worst case) is actually the default for PostgreSQL on Linux.


Thoughts?

Regards,
Vitaliy


effective_io_concurrency=0
  QUERY 
PLAN  
--
 Bitmap Heap Scan on pgbench_accounts  (cost=137192.84..1960989.89 rows=1 
width=97) (actual time=40261.322..40261.322 rows=0 loops=1)
   Recheck Cond: ((aid >= 1000) AND (aid <= 1000))
   Rows Removed by Index Recheck: 23
   Filter: (abalance <> 0)
   Rows Removed by Filter: 001
   Heap Blocks: exact=97869 lossy=66050
   Buffers: shared hit=3 read=191240
   ->  Bitmap Index Scan on pgbench_accounts_pkey  (cost=0.00..137192.84 
rows=10540117 width=0) (actual time=3366.623..3366.623 rows=001 loops=1)
 Index Cond: ((aid >= 1000) AND (aid <= 1000))
 Buffers: shared hit=3 read=27321
 Planning time: 17.285 ms
 Execution time: 40262.781 ms
(12 rows)

effective_io_concurrency=1
  QUERY 
PLAN  
--
 Bitmap Heap Scan on pgbench_accounts  (cost=137192.84..1960989.89 rows=1 
width=97) (actual time=98124.607..98124.607 rows=0 loops=1)
   Recheck Cond: ((aid >= 1000) AND (aid <= 1000))
   Rows Removed by Index Recheck: 23
   Filter: (abalance <> 0)
   Rows Removed by Filter: 001
   Heap Blocks: exact=97869 lossy=66050
   Buffers: shared hit=3 read=191240
   ->  Bitmap Index Scan on pgbench_accounts_pkey  (cost=0.00..137192.84 
rows=10540117 width=0) (actual time=3373.380..3373.380 rows=001 loops=1)
 Index Cond: ((aid >= 1000) AND (aid <= 1000))
 Buffers: shared hit=3 read=27321
 Planning time: 18.110 ms
 Execution time: 98125.987 ms
(12 rows)

effective_io_concurrency=2
  QUERY 
PLAN  
--
 Bitmap Heap Scan on pgbench_accounts  (cost=137192.84..1960989.89 rows=1 
width=97) (actual time=55340.663..55340.663 rows=0 loops=1)
   Recheck Cond: ((aid >= 1000) AND (aid <= 1000))
   Rows Removed by Index Recheck: 23
   Filter: (abalance <> 0)
   Rows Removed by Filter: 001
   Heap Blocks: exact=97869 lossy=66050
   Buffers: shared hit=3 read=191240
   ->  Bitmap Index Scan on pgbench_accounts_pkey  (cost=0.00..137192.84 
rows=10540117 width=0) (actual time=3306.896..3306.896 rows=001 loops=1)
 Index Cond: ((aid >= 1000) AND (aid <= 1000))
 Buffers: shared hit=3 read=27321
 Planning time: 30.986 ms
 Execution time: 55343.776 ms
(12 rows)

effective_io_concurrency=4
  

Re: Nested Loops

[Laurenz Albe]

Kumar, Virendra wrote:
> Can somebody help me avoid nested loops in below query:
> --
> ap_poc_db=# explain (analyze,buffers)
> ap_poc_db-# select site_id, account_id FROM ap.site_exposure se
> ap_poc_db-# WHERE se.portfolio_id=-1191836
> ap_poc_db-# AND EXISTS (select 1 from ap.catevent_flood_sc_split 
> sp where sp.migration_sourcename= 'KatRisk_SC_Flood_2015_v9' AND 
> ST_Intersects(se.shape, sp.shape))
> ap_poc_db-# group by site_id, account_id;
>   
> QUERY PLAN
> --
[...]
>Buffers: shared hit=172041
>->  Gather Merge  (cost=23479854.04..23479879.04 rows=205 width=16) 
> (actual time=1387.823..1388.676 rows=1532 loops=1)
>  Workers Planned: 5
>  Workers Launched: 5
>  Buffers: shared hit=172041
[...]
>  ->  Nested Loop Semi Join  (cost=4.53..23478852.87 
> rows=41 width=16) (actual time=34.772..1345.489 rows=255 loops=6)
>Buffers: shared hit=864235
>->  Append  (cost=0.00..156424.56 rows=123645 
> width=48) (actual time=1.011..204.748 rows=102990 loops=6)
>  Buffers: shared hit=154879
>  ->  Parallel Seq Scan on 
> site_exposure_1191836 se  (cost=0.00..156424.56 rows=123645 width=48) (actual 
> time=1.004..187.702 rows=102990 loops=6)
>Filter: (portfolio_id = 
> '-1191836'::integer)
>Buffers: shared hit=154879
>->  Bitmap Heap Scan on catevent_flood_sc_split sp 
>  (cost=4.53..188.54 rows=15 width=492) (actual time=0.007..0.007 rows=0 
> loops=617937)
>  Recheck Cond: (se.shape && shape)
>  Filter: ((migration_sourcename = 
> 'KatRisk_SC_Flood_2015_v9'::bpchar) AND _st_intersects(se.shape, shape))
>  Rows Removed by Filter: 0
>  Heap Blocks: exact=1060
>  Buffers: shared hit=709356
>  ->  Bitmap Index Scan on 
> catevent_flood_sc_split_shape_mig_src_gix  (cost=0.00..4.52 rows=45 width=0) 
> (actual time=0.005..0.005 rows=0 loops=617937)
>Index Cond: (se.shape && shape)
>Buffers: shared hit=691115
> Planning time: 116.141 ms
> Execution time: 1391.785 ms

With a join condition like that (using on a function result),
only a nested loop join is possible.

I don't know how selective sp.migration_sourcename= 'KatRisk_SC_Flood_2015_v9'
is; perhaps an index on the column can help a little.

But you won't get around the 617937 loops, which is the cause of the
long query duration.  I don't think there is a lot of potential for 
optimization.

Yours,
Laurenz Albe