Обсуждение: Memory usage after upgrade to 9.2.4
Hi all,
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
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
クルズ クリスチアン ダニエル
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.68Tasks: 417 total, 1 running, 416 sleeping, 0 stopped, 0 zombieCpu(s): 0.5%us, 0.4%sy, 0.0%ni, 94.8%id, 4.3%wa, 0.0%hi, 0.0%si, 0.0%stMem: 98923768k total, 53750228k used, 45173540k free, 46192k buffersSwap: 49150856k total, 24147924k used, 25002932k free, 32706740k cachedPID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND10522 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) idle9367 pg92 17 0 16.6g 6.7g 517m S 0.0 7.1 5:05.98 postgres: sgn2 senai 10.1.3.1(44277) idle13336 pg92 17 0 16.5g 6.6g 511m S 0.0 7.0 0:14.01 postgres: sgn2 senai 10.1.3.1(51436) idle26864 pg92 18 0 6487m 6.0g 6.0g S 0.0 6.3 3:20.21 postgres: pg92 senai [local] idle14360 pg92 15 0 6461m 1.2g 1.2g S 0.0 1.3 0:02.46 postgres: integracao senai 10.1.3.200(59197) idle14678 pg92 15 0 6461m 1.2g 1.2g S 0.0 1.3 0:01.34 postgres: integracao senai 10.1.3.200(59201) idle10597 pg92 17 0 6513m 878m 830m S 0.0 0.9 0:08.81 postgres: integracao senai 10.1.3.200(38519) idle9301 pg92 15 0 6456m 188m 187m S 0.0 0.2 0:08.98 /home/pg92/bin/postgres9408 pg92 17 0 6483m 115m 91m S 0.0 0.1 3:02.13 postgres: sgn2 senai 10.1.3.1(44310) idle9306 pg92 15 0 6459m 72m 70m S 0.0 0.1 0:00.93 postgres: writer process9305 pg92 16 0 6459m 60m 59m S 0.0 0.1 0:00.13 postgres: checkpointer process15028 pg92 16 0 6462m 54m 51m S 0.0 0.1 0:00.07 postgres: integracao senai 10.1.3.200(59203) idle17779 pg92 15 0 6464m 23m 19m S 0.0 0.0 0:00.30 postgres: sgn2 senai 10.1.3.1(34656) idle9307 pg92 15 0 6459m 6020 5392 S 0.0 0.0 0:00.01 postgres: wal writer process9308 pg92 15 0 6462m 4584 1740 S 0.0 0.0 0:04.94 postgres: autovacuum launcher process17766 pg92 16 0 6460m 3968 2340 S 0.0 0.0 0:00.00 postgres: sgn2 senai 10.1.3.1(34642) idle17767 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
2013/4/20 Adrian Klaver <adrian.klaver@gmail.com>
On 04/20/2013 04:08 PM, Daniel Cristian Cruz wrote:Before any one can help I would think more information is needed;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.
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.
Adrian Klaver
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
--
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
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
2013/4/21 Tom Lane <tgl@sss.pgh.pa.us>
Tomas Vondra <tv@fuzzy.cz> writes:The only thing that seems rather bloated is the CacheMemoryContext,
> I do have a log with the memory context info printed after the OOM
> killed the session - see it attached.
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.
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
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
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
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
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
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>><mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>>
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: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...
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
---------- 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>
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:
Thanks Tomas, at least I'm not so alone now...
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
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.
It's in production for a long time and so far it was running fine, until
> 3) Is it still in test mode or in production?
the upgrade to 9.2.4.
Same here.
It was working fine in the production (exactly the same workload) for a
> 4) You seem to imply that in test mode everything worked alright, is
> that the case?
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)
Simple selects, mostly index scans, nothing complex or time consuming.
> 5) In either case, test/production, what is being done in the session(s)?
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.
I do have a log with the memory context info printed after the OOM
> 6) Is there anything in the Postgres logs that might shed light?
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.
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
---------- 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>
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
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>>
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
So what difference is there between the test and production servers?
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
A real server, two VMs with very less memory and CPUs
Any chance to see an EXPLAIN ANALYZE for query on test machine vs production?
5) In either case, test/production, what is being done in the
session(s)?
Some complex queries. Some very complex queries...
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.
adrian.klaver@gmail.com <mailto:adrian.klaver@gmail.com>
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
--
Adrian Klaver
--
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
I'm running pgBadger over the log, and will get some queries to explain analyze them.
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
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>>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.
For a lack of anything else pick one and try it on the various servers to see if something stands out.
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
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:
server 9.2:
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:
server 9.2:
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:
server 9.2:
100MB more in RES memory after this one.
Thanks,
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
2013/4/22 Daniel Cristian Cruz <danielcristian@gmail.com>
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
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:server 9.2: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
2013/4/23 Adrian Klaver <adrian.klaver@gmail.com>
--
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 used to read a explain and find something, but this one is huge. Unfortunately I'm still working on data migration from the 9.2 to 9.1 and didn't get time to read it in detail...
Also noticed that in your 9.2 production conf: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?
(no constraint_exclusion set)
Does this mean the default of 'partition' was left as is or that the setting was set to 'off'?
No, default:
senai=# show constraint_exclusion ;
constraint_exclusion
----------------------
partition
(1 row)
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
I've done an explain analyze under the test environment, and there is no aggressive memory usage.
So I dropped the database in the new cluster and restored a fresh dump from production (in theory, that's the difference between the two environments).
Some minutes after I got an answer: after a dump / restore, there is no problem. The same cluster just a dump/restore.
Since I had no idea on what is the problem, and the structure from the dump of the bad database is equal to the dump from the production (it differs only in check constraints where "((turma.situacao)::text = ANY ((ARRAY['Aguardando Atualização'::character varying, 'Em Andamento'::character varying])::text[])))" became "((turma.situacao)::text = ANY (ARRAY[('Aguardando Atualização'::character varying)::text, ('Em Andamento'::character varying)::text])))"), how can I report a issue in pg_upgrade?
Thanks, Adrian, Tomas and Tom. It's a specific issue, it's on backup until May, 1st. Until there, if someone would like to know something about it, just ask me, I would like to help removing an issue.
2013/4/23 Daniel Cristian Cruz <danielcristian@gmail.com>
2013/4/23 Adrian Klaver <adrian.klaver@gmail.com>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 used to read a explain and find something, but this one is huge. Unfortunately I'm still working on data migration from the 9.2 to 9.1 and didn't get time to read it in detail...Also noticed that in your 9.2 production conf: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?
(no constraint_exclusion set)
Does this mean the default of 'partition' was left as is or that the setting was set to 'off'?No, default:senai=# show constraint_exclusion ;constraint_exclusion----------------------partition(1 row)--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
On 04/24/2013 11:20 AM, Daniel Cristian Cruz wrote:
> I've done an explain analyze under the test environment, and there is no
> aggressive memory usage.
>
> So I dropped the database in the new cluster and restored a fresh dump
> from production (in theory, that's the difference between the two
> environments).
>
> Some minutes after I got an answer: after a dump / restore, there is no
> problem. The same cluster just a dump/restore.
>
> Since I had no idea on what is the problem, and the structure from the
> dump of the bad database is equal to the dump from the production (it
> differs only in check constraints where "((turma.situacao)::text = ANY
> ((ARRAY['Aguardando Atualização'::character varying, 'Em
> Andamento'::character varying])::text[])))" became
> "((turma.situacao)::text = ANY (ARRAY[('Aguardando
> Atualização'::character varying)::text, ('Em Andamento'::character
> varying)::text])))"), how can I report a issue in pg_upgrade?
The bug reporting page is here:
http://www.postgresql.org/support/submitbug/
>
> Thanks, Adrian, Tomas and Tom. It's a specific issue, it's on backup
> until May, 1st. Until there, if someone would like to know something
> about it, just ask me, I would like to help removing an issue.
>
>
>
> --
> Daniel Cristian Cruz
> クルズ クリスチアン ダニエル
--
Adrian Klaver
adrian.klaver@gmail.com
I found where the problem is:
It could be nice if something is added to the 9.2 release notes to warn the admins.
2013/4/24 Adrian Klaver <adrian.klaver@gmail.com>
On 04/24/2013 11:20 AM, Daniel Cristian Cruz wrote:The bug reporting page is here:I've done an explain analyze under the test environment, and there is no
aggressive memory usage.
So I dropped the database in the new cluster and restored a fresh dump
from production (in theory, that's the difference between the two
environments).
Some minutes after I got an answer: after a dump / restore, there is no
problem. The same cluster just a dump/restore.
Since I had no idea on what is the problem, and the structure from the
dump of the bad database is equal to the dump from the production (it
differs only in check constraints where "((turma.situacao)::text = ANY
((ARRAY['Aguardando Atualização'::character varying, 'Em
Andamento'::character varying])::text[])))" became
"((turma.situacao)::text = ANY (ARRAY[('Aguardando
Atualização'::character varying)::text, ('Em Andamento'::character
varying)::text])))"), how can I report a issue in pg_upgrade?
http://www.postgresql.org/support/submitbug/
Thanks, Adrian, Tomas and Tom. It's a specific issue, it's on backup
until May, 1st. Until there, if someone would like to know something
about it, just ask me, I would like to help removing an issue.--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
--
Adrian Klaver
adrian.klaver@gmail.com
Daniel Cristian Cruz
クルズ クリスチアン ダニエル