Обсуждение: Better Hardware, worst Results
Hi,
I have a very tricky situation here. A client bought a Dell dual-machine
to be used as Database Server, and we have a cheaper machine used in
development. With identical databases, configuration parameters and
running the same query, our machine is almost 3x faster.
I tried to increase the shared_buffers and other parameters, but the
result is still the same. I would like to know what can I do to check
what can be "holding" the Dell server (HD, memory, etc). Both machines
run Debian Linux.
I'll post configuration details below, so you'll can figure my scenario
better.
==> Dell PowerEdge:
HD: SCSI
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 9
cpu MHz : 2791.292
cache size : 512 KB
processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 9
cpu MHz : 2791.292
cache size : 512 KB
# free -m
total used free shared buffers
cached
Mem: 1010 996 14 0 98
506
==> Other machine:
HD: IDE
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Pentium(R) 4 CPU 2.26GHz
stepping : 5
cpu MHz : 2262.166
cache size : 512 KB
#free -m
total used free shared buffers
cached
Mem: 439 434 4 0 16
395
--
+---------------------------------------------------+
| Alvaro Nunes Melo Atua Sistemas de Informacao |
| al_nunes@atua.com.br www.atua.com.br |
| UIN - 42722678 (54) 327-1044 |
+---------------------------------------------------+
On Thu, 2004-11-04 at 16:06, Alvaro Nunes Melo wrote: > Hi, > > I have a very tricky situation here. A client bought a Dell dual-machine > to be used as Database Server, and we have a cheaper machine used in > development. With identical databases, configuration parameters and > running the same query, our machine is almost 3x faster. Please send an explain analyze from both.
Alvaro Nunes Melo <al_nunes@atua.com.br> writes:
> I have a very tricky situation here. A client bought a Dell dual-machine
> to be used as Database Server, and we have a cheaper machine used in
> development. With identical databases, configuration parameters and
> running the same query, our machine is almost 3x faster.
> ==> Dell PowerEdge:
> HD: SCSI
> ==> Other machine:
> HD: IDE
I'll bet a nickel that the IDE drive is lying about write completion,
thereby gaining a significant performance boost at the cost of probable
data corruption during a power failure. SCSI drives generally tell the
truth about this, but consumer-grade IDE gear is usually configured to
lie.
regards, tom lane
Citando Rod Taylor <pg@rbt.ca>:
> Please send an explain analyze from both.
I'm sendin three explains. In the first the Dell machine didn't use existing
indexes, so I turn enable_seqscan off (this is the second explain). The total
cost decreased, but the total time not. The third explain refers to the cheaper
(and faster) machine. The last thing is the query itself.
Nested Loop (cost=9008.68..13596.97 rows=1 width=317) (actual
time=9272.803..65287.304 rows=2604 loops=1)
-> Hash Join (cost=9008.68..13590.91 rows=1 width=319) (actual
time=9243.294..10560.330 rows=2604 loops=1)
Hash Cond: ("outer".cd_tipo_pagamento = "inner".cd_tipo_pagamento)
-> Hash Join (cost=9007.59..13589.81 rows=1 width=317) (actual
time=9243.149..10529.765 rows=2604 loops=1)
Hash Cond: ("outer".cd_condicao = "inner".cd_condicao)
-> Nested Loop (cost=9006.46..13588.62 rows=8 width=315)
(actual time=9243.083..10497.385 rows=2604 loops=1)
-> Merge Join (cost=9006.46..13540.44 rows=8 width=290)
(actual time=9242.962..10405.245 rows=2604 loops=1)
Merge Cond: ("outer".cd_pessoa = "inner".cd_pessoa)
-> Nested Loop Left Join (cost=4658.37..9183.72
rows=375 width=286) (actual time=9210.101..10327.003 rows=23392 loops=1)
-> Merge Left Join (cost=4658.37..6924.15
rows=375 width=274) (actual time=9209.952..9981.475 rows=23392 loops=1)
Merge Cond: ("outer".cd_pessoa =
"inner".cd_pessoa)
-> Merge Left Join
(cost=3366.00..5629.19 rows=375 width=255) (actual time=9158.705..9832.781
rows=23392 loops=1)
Merge Cond: ("outer".cd_pessoa =
"inner".cd_pessoa)
-> Nested Loop Left Join
(cost=2073.63..4334.24 rows=375 width=236) (actual time=8679.698..9152.213
rows=23392 loops=
1)
-> Merge Left Join
(cost=2073.63..2075.94 rows=375 width=44) (actual time=8679.557..8826.898
rows=23392 loops=1
)
Merge Cond:
("outer".cd_pessoa = "inner".cd_pessoa)
-> Sort
(cost=1727.15..1728.09 rows=375 width=40) (actual time=8580.391..8611.842
rows=23392 loops=1)
Sort Key:
p.cd_pessoa
-> Seq Scan on
pessoa p (cost=0.00..1711.12 rows=375 width=40) (actual time=0.371..8247.028
rows=50
412 loops=1)
Filter:
(cliente_liberado(cd_pessoa) = 1)
-> Sort
(cost=346.47..346.69 rows=85 width=8) (actual time=99.121..120.706 rows=16470
loops=1)
Sort Key:
e.cd_pessoa
-> Seq Scan on
endereco e (cost=0.00..343.75 rows=85 width=8) (actual time=0.070..30.558
rows=16858
loops=1)
Filter:
(id_tipo_endereco = 2)
-> Index Scan using
pk_pessoa_juridica on pessoa_juridica pj (cost=0.00..6.01 rows=1 width=196)
(actual time=0.
007..0.008 rows=1 loops=23392)
Index Cond:
(pj.cd_pessoa = "outer".cd_pessoa)
-> Sort (cost=1292.37..1293.18
rows=325 width=23) (actual time=478.963..522.701 rows=33659 loops=1)
Sort Key: t.cd_pessoa
-> Seq Scan on telefone t
(cost=0.00..1278.81 rows=325 width=23) (actual time=0.039..120.256 rows=59572
loops=1
)
Filter: (id_principal =
1::smallint)
-> Sort (cost=1292.37..1293.18 rows=325
width=23) (actual time=51.205..53.662 rows=3422 loops=1)
Sort Key: tf.cd_pessoa
-> Seq Scan on telefone tf
(cost=0.00..1278.81 rows=325 width=23) (actual time=0.024..43.192 rows=3885
loops=1)
Filter: (id_tipo =
4::smallint)
-> Index Scan using pk_cep on cep c
(cost=0.00..6.01 rows=1 width=20) (actual time=0.007..0.009 rows=1 loops=23392)
Index Cond: (c.cd_cep = "outer".cd_cep)
-> Sort (cost=4348.08..4351.89 rows=1524 width=4)
(actual time=13.182..18.069 rows=2619 loops=1)
Sort Key: cgv.cd_pessoa
-> Index Scan using
idx_cliente_grupo_vendedor_cd_vendedor on cliente_grupo_vendedor cgv
(cost=0.00..4267.51 rows=1524 width=4) (
actual time=0.114..8.986 rows=2619 loops=1)
Index Cond: (cd_vendedor = 577)
-> Index Scan using pk_cliente_financeiro on
cliente_financeiro cf (cost=0.00..6.01 rows=1 width=25) (actual
time=0.018..0.021 rows=1 loops=2
604)
Index Cond: ("outer".cd_pessoa = cf.cd_pessoa)
-> Hash (cost=1.11..1.11 rows=11 width=6) (actual
time=0.029..0.029 rows=0 loops=1)
-> Seq Scan on condicao_pagamento cp (cost=0.00..1.11
rows=11 width=6) (actual time=0.006..0.024 rows=11 loops=1)
-> Hash (cost=1.07..1.07 rows=7 width=6) (actual time=0.114..0.114
rows=0 loops=1)
-> Seq Scan on tipo_pagamento tp (cost=0.00..1.07 rows=7
width=6) (actual time=0.095..0.106 rows=7 loops=1)
-> Index Scan using pk_cliente on cliente cl (cost=0.00..6.01 rows=1
width=10) (actual time=0.013..0.017 rows=1 loops=2604)
Index Cond: ("outer".cd_pessoa = cl.cd_pessoa)
Total runtime: 65298.215 ms
(49 registros)
*************************
Nested Loop (cost=5155.51..19320.20 rows=1 width=317) (actual
time=480.311..62530.121 rows=2604 loops=1)
-> Nested Loop (cost=5155.51..19314.14 rows=1 width=319) (actual
time=445.146..7385.369 rows=2604 loops=1)
-> Hash Join (cost=5155.51..19309.45 rows=1 width=317) (actual
time=429.995..7307.799 rows=2604 loops=1)
Hash Cond: ("outer".cd_tipo_pagamento =
"inner".cd_tipo_pagamento)
-> Nested Loop (cost=5149.42..19303.31 rows=8 width=315)
(actual time=365.722..7208.785 rows=2604 loops=1)
-> Merge Join (cost=5149.42..19255.13 rows=8 width=290)
(actual time=365.551..7112.292 rows=2604 loops=1)
Merge Cond: ("outer".cd_pessoa = "inner".cd_pessoa)
-> Nested Loop Left Join (cost=801.33..14898.41
rows=375 width=286) (actual time=180.146..7026.597 rows=23392 loops=1)
-> Merge Left Join (cost=801.33..12638.83
rows=375 width=274) (actual time=180.087..6620.025 rows=23392 loops=1)
Merge Cond: ("outer".cd_pessoa =
"inner".cd_pessoa)
-> Merge Left Join
(cost=801.33..9709.38 rows=375 width=255) (actual time=179.964..6443.147
rows=23392 loops=1)
Merge Cond: ("outer".cd_pessoa =
"inner".cd_pessoa)
-> Nested Loop Left Join
(cost=801.33..6779.94 rows=375 width=236) (actual time=178.106..6131.000
rows=23392 loops=1)
-> Merge Left Join
(cost=801.33..4521.63 rows=375 width=44) (actual time=177.883..5737.847
rows=23392 loops=1)
Merge Cond:
("outer".cd_pessoa = "inner".cd_pessoa)
-> Index Scan using
pk_pessoa on pessoa p (cost=0.00..3718.93 rows=375 width=40) (actual
time=41.851..543
1.143 rows=23392 loops=1)
Filter:
(cliente_liberado(cd_pessoa) = 1)
-> Sort
(cost=801.33..801.55 rows=85 width=8) (actual time=135.988..166.175 rows=16470
loops=1)
Sort Key:
e.cd_pessoa
-> Index Scan
using idx_endereco_cd_cep on endereco e (cost=0.00..798.61 rows=85 width=8)
(actual t
ime=8.121..61.640 rows=16858 loops=1)
Filter:
(id_tipo_endereco = 2)
-> Index Scan using
pk_pessoa_juridica on pessoa_juridica pj (cost=0.00..6.01 rows=1 width=196)
(actual time=0.
009..0.010 rows=1 loops=23392)
Index Cond:
(pj.cd_pessoa = "outer".cd_pessoa)
-> Index Scan using
idx_telefone_cd_pessoa_id_principal on telefone t (cost=0.00..2927.68 rows=325
width=23) (actual
time=1.840..106.496 rows=33659 loops=1)
Filter: (id_principal =
1::smallint)
-> Index Scan using
idx_telefone_cd_pessoa_id_principal on telefone tf (cost=0.00..2927.68
rows=325 width=23) (actual time=
0.056..67.089 rows=3422 loops=1)
Filter: (id_tipo = 4::smallint)
-> Index Scan using pk_cep on cep c
(cost=0.00..6.01 rows=1 width=20) (actual time=0.010..0.011 rows=1 loops=23392)
Index Cond: (c.cd_cep = "outer".cd_cep)
-> Sort (cost=4348.08..4351.89 rows=1524 width=4)
(actual time=14.178..18.668 rows=2619 loops=1)
Sort Key: cgv.cd_pessoa
-> Index Scan using
idx_cliente_grupo_vendedor_cd_vendedor on cliente_grupo_vendedor cgv
(cost=0.00..4267.51 rows=1524 width=4) (
actual time=0.177..9.557 rows=2619 loops=1)
Index Cond: (cd_vendedor = 577)
-> Index Scan using pk_cliente_financeiro on
cliente_financeiro cf (cost=0.00..6.01 rows=1 width=25) (actual
time=0.019..0.022 rows=1 loops=2
604)
Index Cond: ("outer".cd_pessoa = cf.cd_pessoa)
-> Hash (cost=6.08..6.08 rows=7 width=6) (actual
time=64.025..64.025 rows=0 loops=1)
-> Index Scan using pk_tipo_pagamento on tipo_pagamento tp
(cost=0.00..6.08 rows=7 width=6) (actual time=63.991..64.007 rows=7 loops=1)
-> Index Scan using pk_condicao_pagamento on condicao_pagamento cp
(cost=0.00.. Index Cond: (cp.cd_condicao = "outer".cd_condicao)
-> Index Scan using pk_cliente on cliente cl (cost=0.00..6.01 rows=1
width=10) (actual time=0.013..0.017 rows=1 loops=2604)
Index Cond: ("outer".cd_pessoa = cl.cd_pessoa)
Total runtime: 62536.845 ms
(42 registros)
4.68 rows=1 width=6) (actual time=0.014..0.016 rows=1 loops=2604)
*************************
Hash Join (cost=2.23..11191.77 rows=9 width=134) (actual
time=341.708..21868.167 rows=2604 loops=1)
Hash Cond: ("outer".cd_condicao = "inner".cd_condicao)
-> Hash Join (cost=1.09..11190.16 rows=9 width=132) (actual
time=329.205..19758.764 rows=2604 loops=1)
Hash Cond: ("outer".cd_tipo_pagamento = "inner".cd_tipo_pagamento)
-> Nested Loop (cost=0.00..11188.94 rows=9 width=130) (actual
time=329.086..19727.477 rows=2604 loops=1)
-> Merge Join (cost=0.00..9190.52 rows=245 width=138) (actual
time=7.860..18543.354 rows=24380 loops=1)
Merge Cond: ("outer".cd_pessoa = "inner".cd_pessoa)
-> Merge Join (cost=0.00..11686.19 rows=245 width=128)
(actual time=7.692..17802.380 rows=24380 loops=1)
Merge Cond: ("outer".cd_pessoa = "inner".cd_pessoa)
-> Nested Loop Left Join (cost=0.00..14123.02
rows=375 width=106) (actual time=7.513..17071.221 rows=70931 loops=1)
-> Merge Left Join (cost=0.00..12973.12
rows=375 width=94) (actual time=7.297..16005.974 rows=70931 loops=1)
Merge Cond: ("outer".cd_pessoa =
"inner".cd_pessoa)
-> Merge Left Join (cost=0.00..10076.90
rows=375 width=82) (actual time=7.161..15391.752 rows=70931 loops=1)
Merge Cond: ("outer".cd_pessoa =
"inner".cd_pessoa)
-> Nested Loop Left Join
(cost=0.00..7040.30 rows=375 width=70) (actual time=6.990..14516.256 rows=47998
loops=1)
-> Nested Loop Left Join
(cost=0.00..5401.41 rows=375 width=37) (actual time=6.839..13504.771 rows=47998
loops=
1)
-> Index Scan using
pk_pessoa on pessoa p (cost=0.00..3398.09 rows=375 width=33) (actual
time=6.599..1234
7.532 rows=47998 loops=1)
Filter:
(cliente_liberado(cd_pessoa) = 1)
-> Index Scan using
un_endereco_id_tipo_endereco on endereco e (cost=0.00..5.33 rows=1 width=8)
(actual t
ime=0.015..0.016 rows=0 loops=47998)
Index Cond:
(e.cd_pessoa = "outer".cd_pessoa)
Filter:
(id_tipo_endereco = 2)
-> Index Scan using
pk_pessoa_juridica on pessoa_juridica pj (cost=0.00..4.36 rows=1 width=37)
(actual time=0.0
12..0.013 rows=0 loops=47998)
Index Cond:
(pj.cd_pessoa = "outer".cd_pessoa)
-> Index Scan using
idx_telefone_cd_pessoa_id_principal on telefone t (cost=0.00..2884.52
rows=59265 width=16) (actua
l time=0.146..260.008 rows=58128 loops=1)
Filter: (id_principal =
1::smallint)
-> Index Scan using
idx_telefone_cd_pessoa_id_principal on telefone tf (cost=0.00..2884.52
rows=4217 width=16) (actual time
=0.053..159.212 rows=3600 loops=1)
Filter: (id_tipo = 4::smallint)
-> Index Scan using pk_cep on cep c
(cost=0.00..3.05 rows=1 width=20) (actual time=0.006..0.007 rows=0 loops=70931)
Index Cond: (c.cd_cep = "outer".cd_cep)
-> Index Scan using pk_cliente_financeiro on
cliente_financeiro cf (cost=0.00..1806.88 rows=48765 width=22) (actual
time=0.146..175.468
rows=48765 loops=1)
-> Index Scan using pk_cliente on cliente cl
(cost=0.00..1387.01 rows=48805 width=10) (actual time=0.135..179.715 rows=48804
loops=1)
-> Index Scan using idx_cliente_grupo_vendedor_cd_pessoa on
cliente_grupo_vendedor cgv (cost=0.00..8.14 rows=1 width=4) (actual
time=0.042..0.043 r
ows=0 loops=24380)
Index Cond: (cgv.cd_pessoa = "outer".cd_pessoa)
Filter: (cd_vendedor = 577)
-> Hash (cost=1.07..1.07 rows=7 width=6) (actual time=0.059..0.059
rows=0 loops=1)
-> Seq Scan on tipo_pagamento tp (cost=0.00..1.07 rows=7
width=6) (actual time=0.033..0.047 rows=7 loops=1)
-> Hash (cost=1.11..1.11 rows=11 width=6) (actual time=0.096..0.096 rows=0
loops=1)
-> Seq Scan on condicao_pagamento cp (cost=0.00..1.11 rows=11
width=6) (actual time=0.054..0.079 rows=11 loops=1)
Total runtime: 21873.236 ms
(39 rows)
SELECT p.cd_pessoa,
obtem_cnpj_cpf(p.cd_pessoa) AS nr_cnpj_cpf, p.nm_pessoa,
COALESCE(pj.nm_fantasia, p.nm_pessoa),
obtem_endereco(obtem_endereco_comercial(p.cd_pessoa)) AS ds_endereco,
obtem_bairro(obtem_endereco_comercial(p.cd_pessoa)) AS ds_bairro,
c.cd_cidade, c.nr_cep, pj.nr_ie, '0' || t.nr_telefone, '0' ||
tf.nr_telefone,
cf.cd_tipo_pagamento, cf.cd_condicao, cp.nr_dias, cl.cd_atividade,
tp.nr_hierarquia,
'0', REPLACE(cf.pr_taxa_financeira, '.', ',') AS pr_taxa_financeira,
TO_CHAR(p.dt_nascimento, 'DDMMYYYY') AS dt_nascimento,
cl.nr_checkouts,
CASE WHEN cf.id_confianca = 1 THEN 'A'
WHEN cf.id_confianca = 2 THEN 'B'
WHEN cf.id_confianca = 3 THEN 'C'
WHEN cf.id_confianca = 4 THEN 'D'
END AS id_confianca,
'' AS id_cadastro
FROM pessoa p
LEFT OUTER JOIN endereco e ON e.cd_pessoa = p.cd_pessoa AND
e.id_tipo_endereco = 2
LEFT OUTER JOIN pessoa_juridica pj ON pj.cd_pessoa = p.cd_pessoa
LEFT OUTER JOIN telefone t ON t.cd_pessoa = p.cd_pessoa AND t.id_principal
= '1'
LEFT OUTER JOIN telefone tf ON tf.cd_pessoa = p.cd_pessoa AND tf.id_tipo =
'4'
LEFT OUTER JOIN cep c ON c.cd_cep = e.cd_cep
JOIN cliente cl ON cl.cd_pessoa = p.cd_pessoa
JOIN cliente_financeiro cf ON cf.cd_pessoa = cl.cd_pessoa
JOIN cliente_grupo_vendedor cgv ON cgv.cd_pessoa = p.cd_pessoa
JOIN condicao_pagamento cp ON cp.cd_condicao = cf.cd_condicao
JOIN tipo_pagamento tp ON tp.cd_tipo_pagamento = cf.cd_tipo_pagamento
WHERE cgv.cd_vendedor = '577'
AND cliente_liberado(p.cd_pessoa) = 1;
--
+---------------------------------------------------+
| Alvaro Nunes Melo Atua Sistemas de Informacao |
| al_nunes@atua.com.br www.atua.com.br |
| UIN - 42722678 (54) 327-1044 |
+---------------------------------------------------+
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
Atua Sistemas de Informação - http://www.atua.com.br
al_nunes@atua.com.br wrote:
Well the plan is completely different on the dev machine. Therefore either the PG version or the postgresql.conf is different. No other possible answer.Citando Rod Taylor <pg@rbt.ca>:Please send an explain analyze from both.I'm sendin three explains. In the first the Dell machine didn't use existing indexes, so I turn enable_seqscan off (this is the second explain). The total cost decreased, but the total time not. The third explain refers to the cheaper (and faster) machine. The last thing is the query itself. Nested Loop (cost=9008.68..13596.97 rows=1 width=317) (actual time=9272.803..65287.304 rows=2604 loops=1)Nested Loop (cost=5155.51..19320.20 rows=1 width=317) (actual time=480.311..62530.121 rows=2604 loops=1)Hash Join (cost=2.23..11191.77 rows=9 width=134) (actual time=341.708..21868.167 rows=2604 loops=1)
M
On Thu, 2004-11-04 at 17:42, al_nunes@atua.com.br wrote: > Citando Rod Taylor <pg@rbt.ca>: > > Please send an explain analyze from both. > I'm sendin three explains. In the first the Dell machine didn't use existing > indexes, so I turn enable_seqscan off (this is the second explain). The total > cost decreased, but the total time not. The third explain refers to the cheaper > (and faster) machine. The last thing is the query itself. All 3 plans have crappy estimates. Run ANALYZE in production, then send another explain analyze (as an attachment please, to avoid linewrap).
>All 3 plans have crappy estimates. > >Run ANALYZE in production, then send another explain analyze (as an >attachment please, to avoid linewrap). > > > Er, no other possible answer except Rod's :-)
Em Qui, 2004-11-04 às 20:58, Rod Taylor escreveu: > All 3 plans have crappy estimates. > > Run ANALYZE in production, then send another explain analyze (as an > attachment please, to avoid linewrap). First of all, I'd like to apoligize for taking so long to post a new position. After this, I apologize again because the problem was in my query. It used some functions that for some reason made the Dell machine have a greater cost than our house-made machine. After correcting this functions, the results were faster in the Dell machine. The last apologize is for the linewrapped explains. In our brazilian PostgreSQL mailing list, attachments are not allowed, so I send them as inline text. Thanks to everyone who spent some time to help me solving this problem. -- +---------------------------------------------------+ | Alvaro Nunes Melo Atua Sistemas de Informacao | | al_nunes@atua.com.br www.atua.com.br | | UIN - 42722678 (54) 327-1044 | +---------------------------------------------------+