Re: Memory usage after upgrade to 9.2.4

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
クルズ クリスチアン ダニエル