Memory usage after upgrade to 9.2.4

I think I didn't make it clear: the session memory usage is growing up too
fast, until all server memory got used and swap occurs.

Never saw something like that. The version is under a test enviroment for a
long time...

Thanks if someone could help me.

2013/4/20 Daniel Cristian Cruz <danielcristian@gmail.com>

Hi all,

I've upgrade from 9.1.4 to 9.2.4, and got some very weird issue.

My server got connections with RES (column from top utility) with too much
memory:

top - 19:50:58 up 384 days, 23:55, 2 users, load average: 4.28, 6.51,
7.68
Tasks: 417 total, 1 running, 416 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.5%us, 0.4%sy, 0.0%ni, 94.8%id, 4.3%wa, 0.0%hi, 0.0%si,
0.0%st
Mem: 98923768k total, 53750228k used, 45173540k free, 46192k buffers
Swap: 49150856k total, 24147924k used, 25002932k free, 32706740k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

10522 pg92 17 0 16.7g 7.9g 1.7g S 0.0 8.4 2:44.69 postgres:
sgn2 senai 10.1.3.1(24675) idle

9367 pg92 17 0 16.6g 6.7g 517m S 0.0 7.1 5:05.98 postgres:
sgn2 senai 10.1.3.1(44277) idle

13336 pg92 17 0 16.5g 6.6g 511m S 0.0 7.0 0:14.01 postgres:
sgn2 senai 10.1.3.1(51436) idle

26864 pg92 18 0 6487m 6.0g 6.0g S 0.0 6.3 3:20.21 postgres:
pg92 senai [local] idle

14360 pg92 15 0 6461m 1.2g 1.2g S 0.0 1.3 0:02.46 postgres:
integracao senai 10.1.3.200(59197) idle

14678 pg92 15 0 6461m 1.2g 1.2g S 0.0 1.3 0:01.34 postgres:
integracao senai 10.1.3.200(59201) idle

10597 pg92 17 0 6513m 878m 830m S 0.0 0.9 0:08.81 postgres:
integracao senai 10.1.3.200(38519) idle

9301 pg92 15 0 6456m 188m 187m S 0.0 0.2 0:08.98
/home/pg92/bin/postgres

9408 pg92 17 0 6483m 115m 91m S 0.0 0.1 3:02.13 postgres:
sgn2 senai 10.1.3.1(44310) idle

9306 pg92 15 0 6459m 72m 70m S 0.0 0.1 0:00.93 postgres:
writer process

9305 pg92 16 0 6459m 60m 59m S 0.0 0.1 0:00.13 postgres:
checkpointer process

15028 pg92 16 0 6462m 54m 51m S 0.0 0.1 0:00.07 postgres:
integracao senai 10.1.3.200(59203) idle

17779 pg92 15 0 6464m 23m 19m S 0.0 0.0 0:00.30 postgres:
sgn2 senai 10.1.3.1(34656) idle

9307 pg92 15 0 6459m 6020 5392 S 0.0 0.0 0:00.01 postgres: wal
writer process

9308 pg92 15 0 6462m 4584 1740 S 0.0 0.0 0:04.94 postgres:
autovacuum launcher process

17766 pg92 16 0 6460m 3968 2340 S 0.0 0.0 0:00.00 postgres:
sgn2 senai 10.1.3.1(34642) idle

17767 pg92 16 0 6460m 3968 2340 S 0.0 0.0 0:00.00 postgres:
sgn2 senai 10.1.3.1(62906) idle

(many other conns)

I don't know why this is happening. I'm stuck with all the same parameters
from 9.1.4.

The only solution right now is to rollback to the older version.

Does someone got something like this before?

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

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

On 04/20/2013 04:08 PM, Daniel Cristian Cruz wrote:

I think I didn't make it clear: the session memory usage is growing up
too fast, until all server memory got used and swap occurs.

Never saw something like that. The version is under a test enviroment
for a long time...

Thanks if someone could help me.

Before any one can help I would think more information is needed;

1) Is it on the same machine/OS as the old version?

2) What are the hardware specs for the machine?

3) Is it still in test mode or in production?

4) You seem to imply that in test mode everything worked alright, is
that the case?

5) In either case, test/production, what is being done in the session(s)?

6) Is there anything in the Postgres logs that might shed light?

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

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/4/20 Adrian Klaver <adrian.klaver@gmail.com>

On 04/20/2013 04:08 PM, Daniel Cristian Cruz wrote:

I think I didn't make it clear: the session memory usage is growing up
too fast, until all server memory got used and swap occurs.

Never saw something like that. The version is under a test enviroment
for a long time...

Thanks if someone could help me.

Before any one can help I would think more information is needed;

1) Is it on the same machine/OS as the old version?

Yes same machine, CentOS 5.5, just upgraded PostgreSQL only

2) What are the hardware specs for the machine?

CISCO Blade, 96GB RAM, 24 cores

3) Is it still in test mode or in production?

production.... sadly

4) You seem to imply that in test mode everything worked alright, is that
the case?

Yes, got two servers, never got the same issue

5) In either case, test/production, what is being done in the session(s)?

Some complex queries. Some very complex queries...

6) Is there anything in the Postgres logs that might shed light?

Unfortunelly, not at all. Just the same of previous version, some (more)
queries taking longer than 10 seconds.

Old config allowed to use 256MB on work_mem. I reduced to 24MB on new
version, but it still grew up strongly and fast.

Not a clue on why this is happening.

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

--
Adrian Klaver
adrian.klaver@gmail.com

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

On 04/20/2013 04:30 PM, Daniel Cristian Cruz wrote:

2013/4/20 Adrian Klaver <adrian.klaver@gmail.com
<mailto:adrian.klaver@gmail.com>>

On 04/20/2013 04:08 PM, Daniel Cristian Cruz wrote:

I think I didn't make it clear: the session memory usage is
growing up
too fast, until all server memory got used and swap occurs.

Never saw something like that. The version is under a test
enviroment
for a long time...

Thanks if someone could help me.

Before any one can help I would think more information is needed;

1) Is it on the same machine/OS as the old version?

Yes same machine, CentOS 5.5, just upgraded PostgreSQL only

How did you upgrade?
pg_upgrade
dump/restore

4) You seem to imply that in test mode everything worked alright, is
that the case?

Yes, got two servers, never got the same issue

So what difference is there between the test and production servers?

5) In either case, test/production, what is being done in the
session(s)?

Some complex queries. Some very complex queries...

Any chance to see an EXPLAIN ANALYZE for query on test machine vs
production?

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

--
Adrian Klaver
adrian.klaver@gmail.com <mailto:adrian.klaver@gmail.com>

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

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

we got a report of (probably) the same issue on a local mailing list.
Maybe it'll help in finding the root cause, so I'm resending the info
here too.

On 21.4.2013 01:19, Adrian Klaver wrote:

On 04/20/2013 04:08 PM, Daniel Cristian Cruz wrote:

I think I didn't make it clear: the session memory usage is growing up
too fast, until all server memory got used and swap occurs.

Never saw something like that. The version is under a test enviroment
for a long time...

Thanks if someone could help me.

Before any one can help I would think more information is needed;

1) Is it on the same machine/OS as the old version?

Yes. This was an upgrade from 9.2.3 to 9.2.4, so this was the usual
minor upgrade procedure - stop, install 9.2.4 package, start.

AFAIK there was no other change (e.g. update of kernel).

2) What are the hardware specs for the machine?

32GB of RAM, 6 cores. I don't know which linux distribution they run.

The interesting part is they have a lot of tables due to a partitioned
schema. In total there's ~9500 tables.

3) Is it still in test mode or in production?

It's in production for a long time and so far it was running fine, until
the upgrade to 9.2.4.

4) You seem to imply that in test mode everything worked alright, is
that the case?

It was working fine in the production (exactly the same workload) for a
long time (few months at least).

5) In either case, test/production, what is being done in the session(s)?

Simple selects, mostly index scans, nothing complex or time consuming.

There's not a particular query that crashes, it's rather about a
combination of queries.

6) Is there anything in the Postgres logs that might shed light?

I do have a log with the memory context info printed after the OOM
killed the session - see it attached.

Tomas

Tomas Vondra <tv@fuzzy.cz> writes:

I do have a log with the memory context info printed after the OOM
killed the session - see it attached.

The only thing that seems rather bloated is the CacheMemoryContext,
which seems to be because the backend has cached info about several
thousand tables and indexes. Given that you say there's 9500 relations
in their schema, it's hard to believe that 9.2.4 is suddenly doing that
where 9.2.3 didn't. I'm wondering if they've done something else that
restricted the amount of memory available to a backend.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/4/21 Tom Lane <tgl@sss.pgh.pa.us>

Tomas Vondra <tv@fuzzy.cz> writes:

I do have a log with the memory context info printed after the OOM
killed the session - see it attached.

The only thing that seems rather bloated is the CacheMemoryContext,
which seems to be because the backend has cached info about several
thousand tables and indexes. Given that you say there's 9500 relations
in their schema, it's hard to believe that 9.2.4 is suddenly doing that
where 9.2.3 didn't. I'm wondering if they've done something else that
restricted the amount of memory available to a backend.

Maybe, since I'm running the same server and top shows a RES size a bit
large for idle sessions. Not so large than 9.2. Bellow is the actual server
top.

top - 10:30:35 up 385 days, 14:35, 1 user, load average: 1.48, 1.32, 1.28
Tasks: 668 total, 5 running, 663 sleeping, 0 stopped, 0 zombie
Cpu(s): 10.3%us, 0.7%sy, 0.0%ni, 87.6%id, 1.3%wa, 0.0%hi, 0.1%si,
0.0%st
Mem: 98923768k total, 95618640k used, 3305128k free, 232888k buffers
Swap: 49150856k total, 264284k used, 48886572k free, 91567048k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

32497 pg91 15 0 6462m 3.5g 3.5g S 0.0 3.7 0:13.13 postgres:
writer process

10988 pg91 15 0 6475m 1.7g 1.7g S 0.0 1.9 0:40.74 postgres:
integracao senai 10.1.3.200(57290) idle

18518 pg91 18 0 6475m 1.7g 1.7g S 0.0 1.9 0:36.38 postgres:
integracao senai 10.1.3.200(51766) idle

23965 pg91 25 0 6528m 1.3g 1.2g S 0.0 1.3 1:09.19 postgres: sgn2
senai 10.1.3.1(8353) idle

30504 pg91 15 0 6700m 1.2g 1.0g S 0.0 1.3 0:17.64 postgres: sgn2
senai 10.1.3.1(20575) idle

1399 pg91 17 0 6515m 1.2g 1.2g S 0.0 1.3 0:56.62 postgres: sgn2
senai 10.1.3.1(52594) idle

5732 pg91 15 0 6521m 1.1g 1.1g S 0.0 1.2 0:33.35 postgres: sgn2
senai 10.1.3.1(57789) idle

8223 pg91 15 0 6520m 1.1g 1.1g S 0.0 1.2 0:22.02 postgres: sgn2
senai 10.1.3.1(39002) idle

7244 pg91 16 0 6527m 1.1g 1.0g R 6.1 1.2 0:14.65 postgres: sgn2
senai 10.1.3.1(58921) SELECT

7916 pg91 15 0 6527m 1.1g 1.0g S 0.0 1.1 0:32.47 postgres: sgn2
senai 10.1.3.1(38869) idle

29701 pg91 15 0 6517m 1.0g 1.0g S 0.0 1.1 0:08.02 postgres: sgn2
senai 10.1.3.1(47910) idle

17445 pg91 17 0 6519m 1.0g 1.0g S 0.0 1.1 0:06.75 postgres: sgn2
senai 10.1.3.1(10035) idle

1398 pg91 17 0 6513m 1.0g 951m S 0.0 1.0 1:46.55 postgres: sgn2
senai 10.1.3.1(26616) idle

30155 pg91 15 0 6496m 825m 792m S 0.0 0.9 0:16.80 postgres: sgn2
senai 10.1.3.1(20472) idle

8225 pg91 15 0 6511m 743m 696m S 0.0 0.8 0:17.39 postgres: sgn2
senai 10.1.3.1(59397) idle

30156 pg91 15 0 6492m 712m 683m S 0.0 0.7 0:18.27 postgres: sgn2
senai 10.1.3.1(48402) idle

29471 pg91 15 0 6514m 559m 508m S 0.0 0.6 0:11.30 postgres: sgn2
senai 10.1.3.1(47784) idle

8314 pg91 15 0 6695m 447m 225m S 0.0 0.5 0:14.44 postgres: sgn2
senai 10.1.3.1(59469) idle

13807 pg91 15 0 6492m 399m 369m S 0.0 0.4 2:00.32 postgres: sgn2
senai 10.1.3.1(31021) idle

18621 pg91 17 0 6554m 370m 294m R 44.4 0.4 0:01.17 postgres: sgn2
senai 10.1.3.1(61676) SELECT

15904 pg91 17 0 6507m 316m 273m R 66.4 0.3 0:04.10 postgres: sgn2
senai 10.1.3.1(7043) SELECT

28055 pg91 18 0 6493m 236m 203m S 0.0 0.2 0:02.72 postgres: sgn2
senai 10.1.3.1(14902) idle

8313 pg91 15 0 6684m 228m 85m S 0.0 0.2 0:00.59 postgres: sgn2
senai 10.1.3.1(59466) idle

30503 pg91 15 0 6682m 212m 71m S 0.0 0.2 0:00.43 postgres: sgn2
senai 10.1.3.1(48499) idle

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

On 04/20/2013 05:19 PM, Daniel Cristian Cruz wrote:

Copying to list to fill in blanks.

2013/4/20 Adrian Klaver <adrian.klaver@gmail.com
<mailto:adrian.klaver@gmail.com>>

On 04/20/2013 04:30 PM, Daniel Cristian Cruz wrote:

2013/4/20 Adrian Klaver <adrian.klaver@gmail.com
<mailto:adrian.klaver@gmail.com>
<mailto:adrian.klaver@gmail.__com <mailto:adrian.klaver@gmail.com>>>

On 04/20/2013 04:08 PM, Daniel Cristian Cruz wrote:

I think I didn't make it clear: the session memory usage is
growing up
too fast, until all server memory got used and swap occurs.

Never saw something like that. The version is under a test
enviroment
for a long time...

Thanks if someone could help me.

Before any one can help I would think more information is
needed;

1) Is it on the same machine/OS as the old version?

Yes same machine, CentOS 5.5, just upgraded PostgreSQL only

How did you upgrade?
pg_upgrade
dump/restore

pg_upgrade

4) You seem to imply that in test mode everything worked
alright, is
that the case?

Yes, got two servers, never got the same issue

So what difference is there between the test and production servers?

A real server, two VMs with very less memory and CPUs

5) In either case, test/production, what is being done in the
session(s)?

Some complex queries. Some very complex queries...

Any chance to see an EXPLAIN ANALYZE for query on test machine vs
production?

Right now I'm building a tool to import the loose records from the new
version to the old, since I'm doing a rollback over the upgrade. As soon
I finish it, I could debug what is happening in the cluster.

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

--
Adrian Klaver
adrian.klaver@gmail.com <mailto:adrian.klaver@gmail.com>
<mailto:adrian.klaver@gmail.__com <mailto:adrian.klaver@gmail.com>>

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

--
Adrian Klaver
adrian.klaver@gmail.com <mailto:adrian.klaver@gmail.com>

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

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/21/2013 06:37 AM, Daniel Cristian Cruz wrote:

2013/4/21 Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>

Tomas Vondra <tv@fuzzy.cz <mailto:tv@fuzzy.cz>> writes:

I do have a log with the memory context info printed after the OOM
killed the session - see it attached.

The only thing that seems rather bloated is the CacheMemoryContext,
which seems to be because the backend has cached info about several
thousand tables and indexes. Given that you say there's 9500 relations
in their schema, it's hard to believe that 9.2.4 is suddenly doing that
where 9.2.3 didn't. I'm wondering if they've done something else that
restricted the amount of memory available to a backend.

Maybe, since I'm running the same server and top shows a RES size a bit
large for idle sessions. Not so large than 9.2. Bellow is the actual
server top.

Just to be clear the below is for the 9.1.4 server you rolled backed to?

top - 10:30:35 up 385 days, 14:35, 1 user, load average: 1.48, 1.32, 1.28
Tasks: 668 total, 5 running, 663 sleeping, 0 stopped, 0 zombie
Cpu(s): 10.3%us, 0.7%sy, 0.0%ni, 87.6%id, 1.3%wa, 0.0%hi, 0.1%si,
0.0%st
Mem: 98923768k total, 95618640k used, 3305128k free, 232888k buffers
Swap: 49150856k total, 264284k used, 48886572k free, 91567048k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
32497 pg91 15 0 6462m 3.5g 3.5g S 0.0 3.7 0:13.13 postgres:
writer process
10988 pg91 15 0 6475m 1.7g 1.7g S 0.0 1.9 0:40.74 postgres:
integracao senai 10.1.3.200(57290) idle
18518 pg91 18 0 6475m 1.7g 1.7g S 0.0 1.9 0:36.38 postgres:
integracao senai 10.1.3.200(51766) idle
23965 pg91 25 0 6528m 1.3g 1.2g S 0.0 1.3 1:09.19 postgres:
sgn2 senai 10.1.3.1(8353) idle
30504 pg91 15 0 6700m 1.2g 1.0g S 0.0 1.3 0:17.64 postgres:
sgn2 senai 10.1.3.1(20575) idle
1399 pg91 17 0 6515m 1.2g 1.2g S 0.0 1.3 0:56.62 postgres:
sgn2 senai 10.1.3.1(52594) idle
5732 pg91 15 0 6521m 1.1g 1.1g S 0.0 1.2 0:33.35 postgres:
sgn2 senai 10.1.3.1(57789) idle
8223 pg91 15 0 6520m 1.1g 1.1g S 0.0 1.2 0:22.02 postgres:
sgn2 senai 10.1.3.1(39002) idle
7244 pg91 16 0 6527m 1.1g 1.0g R 6.1 1.2 0:14.65 postgres:
sgn2 senai 10.1.3.1(58921) SELECT
7916 pg91 15 0 6527m 1.1g 1.0g S 0.0 1.1 0:32.47 postgres:
sgn2 senai 10.1.3.1(38869) idle
29701 pg91 15 0 6517m 1.0g 1.0g S 0.0 1.1 0:08.02 postgres:
sgn2 senai 10.1.3.1(47910) idle
17445 pg91 17 0 6519m 1.0g 1.0g S 0.0 1.1 0:06.75 postgres:
sgn2 senai 10.1.3.1(10035) idle
1398 pg91 17 0 6513m 1.0g 951m S 0.0 1.0 1:46.55 postgres:
sgn2 senai 10.1.3.1(26616) idle
30155 pg91 15 0 6496m 825m 792m S 0.0 0.9 0:16.80 postgres:
sgn2 senai 10.1.3.1(20472) idle
8225 pg91 15 0 6511m 743m 696m S 0.0 0.8 0:17.39 postgres:
sgn2 senai 10.1.3.1(59397) idle
30156 pg91 15 0 6492m 712m 683m S 0.0 0.7 0:18.27 postgres:
sgn2 senai 10.1.3.1(48402) idle
29471 pg91 15 0 6514m 559m 508m S 0.0 0.6 0:11.30 postgres:
sgn2 senai 10.1.3.1(47784) idle
8314 pg91 15 0 6695m 447m 225m S 0.0 0.5 0:14.44 postgres:
sgn2 senai 10.1.3.1(59469) idle
13807 pg91 15 0 6492m 399m 369m S 0.0 0.4 2:00.32 postgres:
sgn2 senai 10.1.3.1(31021) idle
18621 pg91 17 0 6554m 370m 294m R 44.4 0.4 0:01.17 postgres:
sgn2 senai 10.1.3.1(61676) SELECT
15904 pg91 17 0 6507m 316m 273m R 66.4 0.3 0:04.10 postgres:
sgn2 senai 10.1.3.1(7043) SELECT
28055 pg91 18 0 6493m 236m 203m S 0.0 0.2 0:02.72 postgres:
sgn2 senai 10.1.3.1(14902) idle
8313 pg91 15 0 6684m 228m 85m S 0.0 0.2 0:00.59 postgres:
sgn2 senai 10.1.3.1(59466) idle
30503 pg91 15 0 6682m 212m 71m S 0.0 0.2 0:00.43 postgres:
sgn2 senai 10.1.3.1(48499) idle

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

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 21.4.2013 15:14, Tom Lane wrote:

Tomas Vondra <tv@fuzzy.cz> writes:

I do have a log with the memory context info printed after the OOM
killed the session - see it attached.

The only thing that seems rather bloated is the CacheMemoryContext,
which seems to be because the backend has cached info about several
thousand tables and indexes. Given that you say there's 9500 relations
in their schema, it's hard to believe that 9.2.4 is suddenly doing that
where 9.2.3 didn't. I'm wondering if they've done something else that
restricted the amount of memory available to a backend.

My thoughts, exactly. I can't really compare the CacheMemoryContext to
the 9.1.3, as I have no data from that version. So maybe it really did
not change, but something else obviously did.

I'm not aware of any other changes, but I'll verify that.

Tomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/4/21 Adrian Klaver <adrian.klaver@gmail.com>

On 04/21/2013 06:37 AM, Daniel Cristian Cruz wrote:

2013/4/21 Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>

Tomas Vondra <tv@fuzzy.cz <mailto:tv@fuzzy.cz>> writes:

I do have a log with the memory context info printed after the OOM
killed the session - see it attached.

The only thing that seems rather bloated is the CacheMemoryContext,
which seems to be because the backend has cached info about several
thousand tables and indexes. Given that you say there's 9500
relations
in their schema, it's hard to believe that 9.2.4 is suddenly doing
that
where 9.2.3 didn't. I'm wondering if they've done something else that
restricted the amount of memory available to a backend.

Maybe, since I'm running the same server and top shows a RES size a bit
large for idle sessions. Not so large than 9.2. Bellow is the actual
server top.

Just to be clear the below is for the 9.1.4 server you rolled backed to?

Yes.

top - 10:30:35 up 385 days, 14:35, 1 user, load average: 1.48, 1.32,
1.28
Tasks: 668 total, 5 running, 663 sleeping, 0 stopped, 0 zombie
Cpu(s): 10.3%us, 0.7%sy, 0.0%ni, 87.6%id, 1.3%wa, 0.0%hi, 0.1%si,
0.0%st
Mem: 98923768k total, 95618640k used, 3305128k free, 232888k buffers
Swap: 49150856k total, 264284k used, 48886572k free, 91567048k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
32497 pg91 15 0 6462m 3.5g 3.5g S 0.0 3.7 0:13.13 postgres:
writer process
10988 pg91 15 0 6475m 1.7g 1.7g S 0.0 1.9 0:40.74 postgres:
integracao senai 10.1.3.200(57290) idle
18518 pg91 18 0 6475m 1.7g 1.7g S 0.0 1.9 0:36.38 postgres:
integracao senai 10.1.3.200(51766) idle
23965 pg91 25 0 6528m 1.3g 1.2g S 0.0 1.3 1:09.19 postgres:
sgn2 senai 10.1.3.1(8353) idle
30504 pg91 15 0 6700m 1.2g 1.0g S 0.0 1.3 0:17.64 postgres:
sgn2 senai 10.1.3.1(20575) idle
1399 pg91 17 0 6515m 1.2g 1.2g S 0.0 1.3 0:56.62 postgres:
sgn2 senai 10.1.3.1(52594) idle
5732 pg91 15 0 6521m 1.1g 1.1g S 0.0 1.2 0:33.35 postgres:
sgn2 senai 10.1.3.1(57789) idle
8223 pg91 15 0 6520m 1.1g 1.1g S 0.0 1.2 0:22.02 postgres:
sgn2 senai 10.1.3.1(39002) idle
7244 pg91 16 0 6527m 1.1g 1.0g R 6.1 1.2 0:14.65 postgres:
sgn2 senai 10.1.3.1(58921) SELECT
7916 pg91 15 0 6527m 1.1g 1.0g S 0.0 1.1 0:32.47 postgres:
sgn2 senai 10.1.3.1(38869) idle
29701 pg91 15 0 6517m 1.0g 1.0g S 0.0 1.1 0:08.02 postgres:
sgn2 senai 10.1.3.1(47910) idle
17445 pg91 17 0 6519m 1.0g 1.0g S 0.0 1.1 0:06.75 postgres:
sgn2 senai 10.1.3.1(10035) idle
1398 pg91 17 0 6513m 1.0g 951m S 0.0 1.0 1:46.55 postgres:
sgn2 senai 10.1.3.1(26616) idle
30155 pg91 15 0 6496m 825m 792m S 0.0 0.9 0:16.80 postgres:
sgn2 senai 10.1.3.1(20472) idle
8225 pg91 15 0 6511m 743m 696m S 0.0 0.8 0:17.39 postgres:
sgn2 senai 10.1.3.1(59397) idle
30156 pg91 15 0 6492m 712m 683m S 0.0 0.7 0:18.27 postgres:
sgn2 senai 10.1.3.1(48402) idle
29471 pg91 15 0 6514m 559m 508m S 0.0 0.6 0:11.30 postgres:
sgn2 senai 10.1.3.1(47784) idle
8314 pg91 15 0 6695m 447m 225m S 0.0 0.5 0:14.44 postgres:
sgn2 senai 10.1.3.1(59469) idle
13807 pg91 15 0 6492m 399m 369m S 0.0 0.4 2:00.32 postgres:
sgn2 senai 10.1.3.1(31021) idle
18621 pg91 17 0 6554m 370m 294m R 44.4 0.4 0:01.17 postgres:
sgn2 senai 10.1.3.1(61676) SELECT
15904 pg91 17 0 6507m 316m 273m R 66.4 0.3 0:04.10 postgres:
sgn2 senai 10.1.3.1(7043) SELECT
28055 pg91 18 0 6493m 236m 203m S 0.0 0.2 0:02.72 postgres:
sgn2 senai 10.1.3.1(14902) idle
8313 pg91 15 0 6684m 228m 85m S 0.0 0.2 0:00.59 postgres:
sgn2 senai 10.1.3.1(59466) idle
30503 pg91 15 0 6682m 212m 71m S 0.0 0.2 0:00.43 postgres:
sgn2 senai 10.1.3.1(48499) idle

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

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-general&lt;http://www.postgresql.org/mailpref/pgsql-general&gt;

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

On 04/21/2013 07:50 AM, Daniel Cristian Cruz wrote:

2013/4/21 Adrian Klaver <adrian.klaver@gmail.com
<mailto:adrian.klaver@gmail.com>>

On 04/21/2013 06:37 AM, Daniel Cristian Cruz wrote:

2013/4/21 Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>
<mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>>

Tomas Vondra <tv@fuzzy.cz <mailto:tv@fuzzy.cz>
<mailto:tv@fuzzy.cz <mailto:tv@fuzzy.cz>>> writes:

I do have a log with the memory context info printed

after the OOM

killed the session - see it attached.

The only thing that seems rather bloated is the
CacheMemoryContext,
which seems to be because the backend has cached info about
several
thousand tables and indexes. Given that you say there's
9500 relations
in their schema, it's hard to believe that 9.2.4 is
suddenly doing that
where 9.2.3 didn't. I'm wondering if they've done
something else that
restricted the amount of memory available to a backend.

Maybe, since I'm running the same server and top shows a RES
size a bit
large for idle sessions. Not so large than 9.2. Bellow is the actual
server top.

Just to be clear the below is for the 9.1.4 server you rolled backed to?

Yes.

To recap for those following along, there are two different cases in
play here.

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?

b) What are the EXPLAIN/ANALYZE results for a query on 9.1.4, 9.2.4 test
and 9.2.4 production?

2)
Minor upgrade 9.2.3 to 9.2.4
No test server, went production to production.
Same machine.
When some combination of queries where run on 9.2.4 memory usage climbed
out of control.

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

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

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/4/21 Adrian Klaver <adrian.klaver@gmail.com>

On 04/21/2013 07:50 AM, Daniel Cristian Cruz wrote:

2013/4/21 Adrian Klaver <adrian.klaver@gmail.com
<mailto:adrian.klaver@gmail.**com <adrian.klaver@gmail.com>>>

On 04/21/2013 06:37 AM, Daniel Cristian Cruz wrote:

2013/4/21 Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>
<mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>>

Tomas Vondra <tv@fuzzy.cz <mailto:tv@fuzzy.cz>
<mailto:tv@fuzzy.cz <mailto:tv@fuzzy.cz>>> writes:

I do have a log with the memory context info printed

after the OOM

killed the session - see it attached.

The only thing that seems rather bloated is the
CacheMemoryContext,
which seems to be because the backend has cached info about
several
thousand tables and indexes. Given that you say there's
9500 relations
in their schema, it's hard to believe that 9.2.4 is
suddenly doing that
where 9.2.3 didn't. I'm wondering if they've done
something else that
restricted the amount of memory available to a backend.

Maybe, since I'm running the same server and top shows a RES
size a bit
large for idle sessions. Not so large than 9.2. Bellow is the
actual
server top.

Just to be clear the below is for the 9.1.4 server you rolled backed
to?

Yes.

To recap for those following along, there are two different cases in play
here.

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.

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.

One thing I didn't mention: I build a record table used to track every
transaction on database, and all tables are inherited from it. This way I
easily found the records to transfer from 9.2.4 to 9.1.x.

But it's something similar with the other case, where they are using for
partitioning purposes, and I'm using to simplify the model (the inherited
table is hidden in the model).

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

Sorry, I answered to Tomas only...

---------- Forwarded message ----------
From: Daniel Cristian Cruz <danielcristian@gmail.com>
Date: 2013/4/21
Subject: Re: [GENERAL] Memory usage after upgrade to 9.2.4
To: Tomas Vondra <tv@fuzzy.cz>

I had the same environment, almost:

2013/4/21 Tomas Vondra <tv@fuzzy.cz>

2) What are the hardware specs for the machine?

32GB of RAM, 6 cores. I don't know which linux distribution they run.

The interesting part is they have a lot of tables due to a partitioned
schema. In total there's ~9500 tables.

We had many tables, not that many, 743 right now.

3) Is it still in test mode or in production?

It's in production for a long time and so far it was running fine, until
the upgrade to 9.2.4.

Same here.

4) You seem to imply that in test mode everything worked alright, is
that the case?

It was working fine in the production (exactly the same workload) for a
long time (few months at least).

Production is working on 9.1.4; Test environments are on 9.2.4 for some
time (they use a dump from production, updated daily or at request)

5) In either case, test/production, what is being done in the session(s)?

Simple selects, mostly index scans, nothing complex or time consuming.

There's not a particular query that crashes, it's rather about a
combination of queries.

I can say that there is mostly simple queries, but there is more complex
queries showing in the log.

6) Is there anything in the Postgres logs that might shed light?

I do have a log with the memory context info printed after the OOM
killed the session - see it attached.

I didn't let the OOM killer works, since I was the session killer...

Thanks Tomas, at least I'm not so alone now...

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

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

And this one only to Adrian.

Sorry to all.

---------- Forwarded message ----------
From: Daniel Cristian Cruz <danielcristian@gmail.com>
Date: 2013/4/20
Subject: Re: [GENERAL] Memory usage after upgrade to 9.2.4
To: Adrian Klaver <adrian.klaver@gmail.com>

2013/4/20 Adrian Klaver <adrian.klaver@gmail.com>

On 04/20/2013 04:30 PM, Daniel Cristian Cruz wrote:

2013/4/20 Adrian Klaver <adrian.klaver@gmail.com
<mailto:adrian.klaver@gmail.**com <adrian.klaver@gmail.com>>>

On 04/20/2013 04:08 PM, Daniel Cristian Cruz wrote:

I think I didn't make it clear: the session memory usage is
growing up
too fast, until all server memory got used and swap occurs.

Never saw something like that. The version is under a test
enviroment
for a long time...

Thanks if someone could help me.

Before any one can help I would think more information is needed;

1) Is it on the same machine/OS as the old version?

Yes same machine, CentOS 5.5, just upgraded PostgreSQL only

How did you upgrade?
pg_upgrade
dump/restore

pg_upgrade

4) You seem to imply that in test mode everything worked alright, is
that the case?

Yes, got two servers, never got the same issue

So what difference is there between the test and production servers?

A real server, two VMs with very less memory and CPUs

5) In either case, test/production, what is being done in the
session(s)?

Some complex queries. Some very complex queries...

Any chance to see an EXPLAIN ANALYZE for query on test machine vs
production?

Right now I'm building a tool to import the loose records from the new
version to the old, since I'm doing a rollback over the upgrade. As soon I
finish it, I could debug what is happening in the cluster.

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

--
Adrian Klaver
adrian.klaver@gmail.com <mailto:adrian.klaver@gmail.**com<adrian.klaver@gmail.com>

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

--
Adrian Klaver
adrian.klaver@gmail.com

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

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

On 04/21/2013 09:29 AM, Daniel Cristian Cruz wrote:

2013/4/21 Adrian Klaver <adrian.klaver@gmail.com
<mailto:adrian.klaver@gmail.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?

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?

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

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

I would guess the usage pattern is different, but in what way?
Number of connections/sessions?
INSERT/UPDATE/DELETE pattern?
Client software using the database?

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.

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

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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.klaver@gmail.com>

On 04/21/2013 09:29 AM, Daniel Cristian Cruz wrote:

2013/4/21 Adrian Klaver <adrian.klaver@gmail.com
<mailto:adrian.klaver@gmail.**com <adrian.klaver@gmail.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
クルズ クリスチアン ダニエル

2013/4/22 Daniel Cristian Cruz <danielcristian@gmail.com>

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.

Since there is no response, is this memory usage normal? The same query on
version 9.1 doesn't use that much memory.

I'm concerned about this because there is just only one report like that.
Does someone else has the same pattern when using inherited tables?

Just for information, my schema uses one table that is inherited by all
others tables; it is an audit record: creator, creation time, creator
application, updater, update time, updater application, table name and
record id.

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

On 04/23/2013 04:23 AM, Daniel Cristian Cruz wrote:

2013/4/22 Daniel Cristian Cruz <danielcristian@gmail.com
<mailto:danielcristian@gmail.com>>

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.

Since there is no response, is this memory usage normal? The same query
on version 9.1 doesn't use that much memory.

Not sure how it applies but I noticed that a GroupAggregate in 9.1 that
took 1.22 secs became a a HashAggregate in the 9.2 query and took 12.54
secs.

I'm concerned about this because there is just only one report like
that. Does someone else has the same pattern when using inherited tables?

Also noticed that in your 9.2 production conf:

(no constraint_exclusion set)

Does this mean the default of 'partition' was left as is or that the
setting was set to 'off'?

Just for information, my schema uses one table that is inherited by all
others tables; it is an audit record: creator, creation time, creator
application, updater, update time, updater application, table name and
record id.

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

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general