Re: Memory usage after upgrade to 9.2.4

From: Daniel Cristian Cruz <danielcristian(at)gmail(dot)com>
To: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Memory usage after upgrade to 9.2.4
Date: 2013-04-22 14:06:45
Message-ID: CACffM9EC3uPrwLH2PLfyrsyO5aE2ac+wv9yBwzQFFGqeHQtyEg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm running pgBadger over the log, and will get some queries to explain
analyze them.

The 9.2 cluster is running in the same server as the production, so I will
try to compare some critical explains and publish on explain.depesz.com.

2013/4/21 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>

> On 04/21/2013 09:29 AM, Daniel Cristian Cruz wrote:
>
>>
>>
>>
>> 2013/4/21 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com
>> <mailto:adrian(dot)klaver(at)gmail(dot)**com <adrian(dot)klaver(at)gmail(dot)com>>>
>>
>
>
>
>> 1)
>> Major upgrade from 9.1.4 to 9.2.4.
>> Used pg_upgrade
>> Tested on VM with 9.2.4 and no problems.
>> Same machine used for production server 9.1.4 and 9.2.4
>> When complex queries where run on production server under 9.2.4
>> memory usage climbed out of control.
>>
>> Unanswered questions:
>>
>> a) Data set sizes between test and production machines, how do they
>> differ?
>>
>>
>> It's the same on both; we do a dump/restore every day to the development
>> / issue team work.
>>
>
> Which begs the question, what is different about your test setup that
> makes it not act up?
>

test environment:

virtual server
8 cores
12 GB RAM
4GB SWAP

max_connections = 200
shared_buffers = 800MB
temp_buffers = 32MB
work_mem = 256MB
maintenance_work_mem = 768MB
(no max_stack_depth set)
shared_preload_libraries =
'$libdir/plpgsql,$libdir/plpython2,$libdir/pgxml,$libdir/pg_stat_statements'
wal_level = minimal
checkpoint_segments = 15
archive_mode = off
max_wal_senders = 0
(no effective_cache_size set)
constraint_exclusion = partition
log_min_duration_statement = 1000
(no log_temp_files set)
(no statement_timeout set)
max_locks_per_transaction = 1024

production:

true server
24 cores
96GB RAM
50GB SWAP

max_connections = 1000
shared_buffers = 6GB
temp_buffers = 24MB
work_mem = 24MB
maintenance_work_mem = 128MB
max_stack_depth = 8MB
(no shared_preload_libraries, disabled after problems because
pg_stat_statements was a new module)
wal_level = hot_standby
checkpoint_segments = 20
archive_mode = on
(archive_command set)
max_wal_senders = 1
effective_cache_size = 32GB
(no constraint_exclusion set)
log_min_duration_statement = 5000
log_temp_files = 0
statement_timeout = 300000
(no max_locks_per_transaction set)

> We know that the test servers are running on VMs with fewer resources than
> the production server.
>
> So:
>
> Are the VMs running the same OS and OS version as the production server?
>

test: Red Hat Enterprise Linux Server release 5.5, Linux
2.6.18-194.26.1.el5 #1 SMP Fri Oct 29 14:21:16 EDT 2010 x86_64 x86_64
x86_64 GNU/Linux
production: CentOS release 5.5, Linux 2.6.18-194.32.1.el5 #1 SMP Wed Jan 5
17:52:25 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

>
> What are 'hardware differences' between the test VMs and the physical
> server?
>

above.

>
> Are the Postgres configurations different for the test vs production
> servers?
>

Yes, some of them, shown above.

> I would guess the usage pattern is different, but in what way?
> Number of connections/sessions?
>

300 connections in production, 50 in test.

> INSERT/UPDATE/DELETE pattern?
>

test: just test cases, development cases and issue cases
production: for 1 minute (00:01:00.076343), 583 inserts, 306 updates and 13
deletes and 3300 transactions (xacts_commits)

> Client software using the database?
>
>
Sites using PHP with and without connection pool and with and without
Doctrine; a huge system with Java and Hibernate using the JBoss pooler,
Java aplication is the main user.

>
>
>
>>
>> b) What are the EXPLAIN/ANALYZE results for a query on 9.1.4, 9.2.4
>> test and 9.2.4 production?
>> Since there is no single query causing the problem, I don't know if it
>> could help
>>
>
> For a lack of anything else pick one and try it on the various servers to
> see if something stands out.

Yes , here it is:

query1:
EXPLAIN ANALYZE SELECT ced.id_evento, ced.inicio, ced.termino, ced.evento,
ced.id_eventos IS NOT NULL AS aula_dividida, ac.titulo, ced.id_tipo_evento,
ced.tipo_evento, ac.media_referencia, p.nome, ef.nome AS nomeEspacoFisico,
( SELECT count ( pre2.presente ) > 0 FROM turma.presenca pre2 WHERE
pre2.id_aula = ac.id_aula AND pre2.id_evento = ac.id_evento AND
pre2.id_diario = '64469' ) AS presenca, ced.id_aula FROM
recurso.consulta_evento_diario ced LEFT JOIN recurso.evento e USING (
id_evento ) LEFT JOIN recurso.espaco_fisico ef USING ( id_espaco_fisico )
LEFT JOIN turma.aula_calendario ac USING ( id_aula, id_evento ) LEFT JOIN
recurso.evento_participante ep USING ( id_evento ) LEFT JOIN senai.pessoa p
USING ( id_pessoa ) WHERE id_diario = '64469' AND ced.id_evento NOT IN (
SELECT ec.id_evento_sobreposto FROM recurso.evento_conflito ec WHERE
ec.id_evento_sobreposto = ced.id_evento AND ec.ignorado IS NULL ) AND
ced.inicio BETWEEN '2013-04-14 00:00:00' AND '2013-04-20 23:59:59.999999'
ORDER BY inicio;

server 9.1:
http://explain.depesz.com/s/fmM

server 9.2:
http://explain.depesz.com/s/wXm

After run this one on server 9.2, RES memory reached 6.5GB, VIRT 15GB.

query 2:
EXPLAIN ANALYZE SELECT count ( consulta_diario.id_diario ) FROM
turma.consulta_diario LEFT JOIN turma.turma_cancelamento ON
consulta_diario.id_turma = turma_cancelamento.id_turma WHERE
turma_cancelamento.id_turma IS NULL AND
consulta_diario.id_unidade_curricular_tipo IN ( 1, 6, 7, 8 ) AND ( 8365 =
ANY ( consulta_diario.id_colaborador_coordenadores ) OR 2252 = ANY (
consulta_diario.id_docentes ) ) AND consulta_diario.id_unidade_execucao IN
( 33, 33, 46, 46, 53, 53 ) AND consulta_diario.situacao_diario LIKE 'Em
Andamento' LIMIT '2';

server 9.1:
http://explain.depesz.com/s/qfC

server 9.2:
http://explain.depesz.com/s/mh2

No change in RES memory after this one (stayed at 6.5GB).

query 3:
EXPLAIN ANALYZE WITH justificativas AS ( SELECT
justificativa_falta_aula.id_matricula, justificativa_falta_aula.id_diario,
justificativa_falta_aula.id_aula, justificativa_falta_aula.id_evento FROM
turma.presenca JOIN recurso.evento ON evento.id_evento = presenca.id_evento
LEFT JOIN matricula.justificativa_falta_aula JOIN
matricula.justificativa_falta ON justificativa_falta.id_justificativa_falta
= justificativa_falta_aula.id_justificativa_falta JOIN
matricula.justificativa_falta_tipo ON
justificativa_falta_tipo.id_justificativa_falta_tipo =
justificativa_falta.id_justificativa_falta_tipo LEFT JOIN matricula.parecer
ON parecer.id_parecer = justificativa_falta.id_parecer ON
justificativa_falta_tipo.tipo = 'Abono' AND
justificativa_falta_aula.id_matricula = presenca.id_matricula AND
justificativa_falta_aula.id_diario = presenca.id_diario AND
justificativa_falta_aula.id_aula = presenca.id_aula AND
justificativa_falta_aula.id_evento = presenca.id_evento AND ( NOT
justificativa_falta.encaminhar OR parecer.aceito ) WHERE
justificativa_falta_aula.id_matricula = 147124 ) SELECT id_diario, to_char
( ( contagem.carga_uc - COALESCE ( contagem.carga_ausencias, 0 ) ) /
contagem.carga_uc * 100, '990D99' ) AS frequencia FROM ( SELECT
estudante.id_matricula, estudante.id_diario, extract ( EPOCH FROM SUM (
evento.termino - evento.inicio ) ) AS carga_uc, extract ( EPOCH FROM SUM (
CASE WHEN aula_confirmacao.confirmada AND evento.inicio <= CURRENT_DATE AND
NOT presenca.presente AND justificativas.id_evento IS NULL THEN
evento.termino - evento.inicio END ) ) AS carga_ausencias FROM
turma.estudante JOIN turma.presenca ON presenca.id_diario =
estudante.id_diario AND presenca.id_matricula = estudante.id_matricula JOIN
recurso.evento ON evento.id_evento = presenca.id_evento LEFT JOIN
turma.aula_confirmacao ON aula_confirmacao.id_evento = presenca.id_evento
AND aula_confirmacao.id_aula = presenca.id_aula LEFT JOIN
matricula.matricula_cancelamento ON matricula_cancelamento.id_matricula =
estudante.id_matricula LEFT JOIN justificativas ON
justificativas.id_matricula = presenca.id_matricula AND
justificativas.id_diario = presenca.id_diario AND justificativas.id_aula =
presenca.id_aula AND justificativas.id_evento = presenca.id_evento WHERE
matricula_cancelamento.id_matricula IS NULL AND estudante.id_matricula =
147124 GROUP BY estudante.id_matricula, estudante.id_diario ) AS contagem;

server 9.1:
http://explain.depesz.com/s/jD4

server 9.2:
http://explain.depesz.com/s/hV9

100MB more in RES memory after this one.

Thanks,
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alfonso Afonso 2013-04-22 15:57:16 Re: using text search
Previous Message Merlin Moncure 2013-04-22 13:24:41 Re: Where in the source code does postgres write to disk?