Performance difference between Slon master and slave

Поиск
Список
Период
Сортировка
От Matthew Lunnon
Тема Performance difference between Slon master and slave
Дата
Msg-id 566EF984.5070404@rwa-net.co.uk
обсуждение исходный текст
Ответы Re: Performance difference between Slon master and slave  (Jim Nasby <Jim.Nasby@BlueTreble.com>)
Список pgsql-performance
Hello all, I hope someone can help me with this.

Postgres 9.4.4
Slon 2.2.4
Linux

I am using slony-i to replicate a production database which is in the order of 70GB. I have a reasonably complex select query that runs in 40 seconds on the master but takes in the region of 30-40 minutes on the slave. The postgres configurations are identical and the machines are a similar specifications (12 core hyper threaded HP server and the slave has slightly less RAM: 132GB vs 148GB) The server running the slave database has a higher load than the one running the master though the load average on the slave machine was low (1-2) when running the test and the postgres process on the slave machine runs at 100% of a CPU with very little iowait on the server.

Inspecting the execution plan shows that there are some differences, for example, the slave is using a HashAggregate when the master is simply grouping. There also seems to be a difference with the ordering of the sub plans. Armed with this knowledge I have set enable_hashagg to off and run the query again and it now takes 53 seconds on the slave which is a more acceptable difference and the execution plans now look very similar (one difference being that there is another HashAggregate in the master which is now missing on the slave and may account for the 13 seconds). I have isolated a much simpler query which I have detailed below with their execution plans which shows the difference on line 4. I would rather not disable hash aggregation on the slave as this might have other consequences so this raises a number of questions. Firstly Is there anything that I can do to stop this feature? Why is the slave behaving differently to the master?

Thanks in advance for any help.

Cheers
Matthew

explain
with my_view_booking_pax_breakdown as (
SELECT bev.booking_id,
    ( SELECT count(*) AS count
           FROM passenger_version
          WHERE passenger_version.current_version = 'T'::bpchar AND passenger_version.deleted = 'F'::bpchar AND passenger_version.indicative_pax_type = 'A'::bpchar AND passenger_version.booking_id = bev.booking_id) AS adult_count,
    ( SELECT count(*) AS count
           FROM passenger_version
          WHERE passenger_version.current_version = 'T'::bpchar AND passenger_version.deleted = 'F'::bpchar AND passenger_version.indicative_pax_type = 'C'::bpchar AND passenger_version.booking_id = bev.booking_id) AS child_count,
    ( SELECT count(*) AS count
           FROM passenger_version
          WHERE passenger_version.current_version = 'T'::bpchar AND passenger_version.deleted = 'F'::bpchar AND passenger_version.indicative_pax_type = 'I'::bpchar AND passenger_version.booking_id = bev.booking_id) AS infant_count
   FROM booking_expanded_version bev
  GROUP BY bev.booking_id
)
select * from "my_view_booking_pax_breakdown" "view_booking_pax_breakdown"
    INNER JOIN "booking"."booking_expanded_version" "booking_expanded_version" ON "view_booking_pax_breakdown"."booking_id"="booking_expanded_version"."booking_id"
    
Master

"Merge Join  (cost=5569138.32..6158794.12 rows=2461265 width=1375)"
"  Merge Cond: (booking_expanded_version.booking_id = view_booking_pax_breakdown.booking_id)"
"  CTE my_view_booking_pax_breakdown"
"    ->  Group  (cost=0.43..5545692.19 rows=215891 width=4)"
"          Group Key: bev.booking_id"
"          ->  Index Only Scan using booking_expanded_version_booking_idx on booking_expanded_version bev  (cost=0.43..64607.40 rows=2461265 width=4)"
"          SubPlan 1"
"            ->  Aggregate  (cost=8.57..8.58 rows=1 width=0)"
"                  ->  Index Scan using passenger_version_idx_4 on passenger_version  (cost=0.43..8.55 rows=5 width=0)"
"                        Index Cond: (booking_id = bev.booking_id)"
"          SubPlan 2"
"            ->  Aggregate  (cost=8.45..8.46 rows=1 width=0)"
"                  ->  Index Scan using passenger_version_idx_3 on passenger_version passenger_version_1  (cost=0.42..8.45 rows=1 width=0)"
"                        Index Cond: (booking_id = bev.booking_id)"
"          SubPlan 3"
"            ->  Aggregate  (cost=8.31..8.32 rows=1 width=0)"
"                  ->  Index Scan using passenger_version_idx_2 on passenger_version passenger_version_2  (cost=0.29..8.31 rows=1 width=0)"
"                        Index Cond: (booking_id = bev.booking_id)"
"  ->  Index Scan using booking_expanded_version_booking_idx on booking_expanded_version  (cost=0.43..546584.09 rows=2461265 width=1347)"
"  ->  Sort  (cost=23445.70..23985.43 rows=215891 width=28)"
"        Sort Key: view_booking_pax_breakdown.booking_id"
"        ->  CTE Scan on my_view_booking_pax_breakdown view_booking_pax_breakdown  (cost=0.00..4317.82 rows=215891 width=28)"

Slave

"Merge Join  (cost=6168518.91..6764756.86 rows=2505042 width=1299)"
"  Merge Cond: (booking_expanded_version.booking_id = view_booking_pax_breakdown.booking_id)"
"  CTE my_view_booking_pax_breakdown"
"    ->  HashAggregate  (cost=212185.03..6142965.53 rows=234040 width=4)"
"          Group Key: bev.booking_id"
"          ->  Seq Scan on booking_expanded_version bev  (cost=0.00..205922.42 rows=2505042 width=4)"
"          SubPlan 1"
"            ->  Aggregate  (cost=8.54..8.55 rows=1 width=0)"
"                  ->  Index Scan using passenger_version_idx_4 on passenger_version  (cost=0.43..8.53 rows=4 width=0)"
"                        Index Cond: (booking_id = bev.booking_id)"
"          SubPlan 2"
"            ->  Aggregate  (cost=8.45..8.46 rows=1 width=0)"
"                  ->  Index Scan using passenger_version_idx_3 on passenger_version passenger_version_1  (cost=0.42..8.45 rows=1 width=0)"
"                        Index Cond: (booking_id = bev.booking_id)"
"          SubPlan 3"
"            ->  Aggregate  (cost=8.31..8.32 rows=1 width=0)"
"                  ->  Index Scan using passenger_version_idx_2 on passenger_version passenger_version_2  (cost=0.29..8.31 rows=1 width=0)"
"                        Index Cond: (booking_id = bev.booking_id)"
"  ->  Index Scan using booking_expanded_version_booking_idx on booking_expanded_version  (cost=0.43..552400.15 rows=2505042 width=1271)"
"  ->  Sort  (cost=25552.95..26138.05 rows=234040 width=28)"
"        Sort Key: view_booking_pax_breakdown.booking_id"
"        ->  CTE Scan on my_view_booking_pax_breakdown view_booking_pax_breakdown  (cost=0.00..4680.80 rows=234040 width=28)"



This message has been scanned for malware by Websense. www.websense.com

В списке pgsql-performance по дате отправления:

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: Advise needed for a join query with a where conditional
Следующее
От: Jim Nasby
Дата:
Сообщение: Re: Performance difference between Slon master and slave