Thread: Query Plans on Slaves
Hi all!
We're working on our architecture for our next set of systems, and we normally have a simple master/slave with wal shipping and hot_standby set up, with pgpool sitting in front of both to do load balancing.
However, one piece I'm very confused about is the query plans on the slave server.
Let's pretend we have a master server with:
(2) E5-2660 CPU's
(4) S3700 400GB SSD's
256GB of RAM
And multiple slave servers that looks like:
(1) E3-1290 CPU's
(2) Intel 520 SSD's
32GB of RAM
There's a significant difference between the master and slaves.
Does the ANALYZE command run on each system, and work differently on each system, or would the slave servers use the query plans from the master machine?
Thanks!
--
Anthony
> Hi all! > > We're working on our architecture for our next set of systems, and we > normally have a simple master/slave with wal shipping and hot_standby set > up, with pgpool sitting in front of both to do load balancing. > > However, one piece I'm very confused about is the query plans on the slave > server. > > Let's pretend we have a master server with: > (2) E5-2660 CPU's > (4) S3700 400GB SSD's > 256GB of RAM > > And multiple slave servers that looks like: > (1) E3-1290 CPU's > (2) Intel 520 SSD's > 32GB of RAM > > There's a significant difference between the master and slaves. Can you show us the explain analyze result on both master and slave? > Does the ANALYZE command run on each system, and work differently on each > system, or would the slave servers use the query plans from the master > machine? ANALYZE can only be executed on master. Slave creates its own plan. However since statistics should be same as master, I guess if you get different plan, then postgresql.conf maybe different among master and slave. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
We're still considering this architecture - so I don't have any plans.
My question is, does the ANALYZE command, which is only executed on the master, mean that the statistics / plans that are used on the master are ALSO used on the slaves? OR .... does the slave create it's own plan?
I would anticipate very different plans between the two machines, and different postgresql.conf settings as well.
I would *want* different query plans between the two. Is that possible?
On Sun, Nov 9, 2014 at 8:50 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:
> Hi all!
>
> We're working on our architecture for our next set of systems, and we
> normally have a simple master/slave with wal shipping and hot_standby set
> up, with pgpool sitting in front of both to do load balancing.
>
> However, one piece I'm very confused about is the query plans on the slave
> server.
>
> Let's pretend we have a master server with:
> (2) E5-2660 CPU's
> (4) S3700 400GB SSD's
> 256GB of RAM
>
> And multiple slave servers that looks like:
> (1) E3-1290 CPU's
> (2) Intel 520 SSD's
> 32GB of RAM
>
> There's a significant difference between the master and slaves.
Can you show us the explain analyze result on both master and slave?
> Does the ANALYZE command run on each system, and work differently on each
> system, or would the slave servers use the query plans from the master
> machine?
ANALYZE can only be executed on master. Slave creates its own
plan. However since statistics should be same as master, I guess if
you get different plan, then postgresql.conf maybe different among
master and slave.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
> We're still considering this architecture - so I don't have any plans. > > My question is, does the ANALYZE command, which is only executed on the > master, mean that the statistics / plans that are used on the master are > ALSO used on the slaves? OR .... does the slave create it's own plan? The statistics is same on master and slave. Plans are made on master and slave independently. > I would anticipate very different plans between the two machines, and > different postgresql.conf settings as well. > > I would *want* different query plans between the two. Is that possible? If you have different postgresql.conf settings on slave, you could get different plans on slave. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp > On Sun, Nov 9, 2014 at 8:50 PM, Tatsuo Ishii <ishii@postgresql.org> wrote: > >> > Hi all! >> > >> > We're working on our architecture for our next set of systems, and we >> > normally have a simple master/slave with wal shipping and hot_standby set >> > up, with pgpool sitting in front of both to do load balancing. >> > >> > However, one piece I'm very confused about is the query plans on the >> slave >> > server. >> > >> > Let's pretend we have a master server with: >> > (2) E5-2660 CPU's >> > (4) S3700 400GB SSD's >> > 256GB of RAM >> > >> > And multiple slave servers that looks like: >> > (1) E3-1290 CPU's >> > (2) Intel 520 SSD's >> > 32GB of RAM >> > >> > There's a significant difference between the master and slaves. >> >> Can you show us the explain analyze result on both master and slave? >> >> > Does the ANALYZE command run on each system, and work differently on each >> > system, or would the slave servers use the query plans from the master >> > machine? >> >> ANALYZE can only be executed on master. Slave creates its own >> plan. However since statistics should be same as master, I guess if >> you get different plan, then postgresql.conf maybe different among >> master and slave. >> >> Best regards, >> -- >> Tatsuo Ishii >> SRA OSS, Inc. Japan >> English: http://www.sraoss.co.jp/index_en.php >> Japanese:http://www.sraoss.co.jp >>