pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedMemory leak (possibly connected to postgis) leading to server crash
16+ messages / 3 participants
[nested] [flat]
* Memory leak (possibly connected to postgis) leading to server crash
@ 2019-12-06 09:22 Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 1 reply; 16+ messages in thread
From: Roman Cervenak @ 2019-12-06 09:22 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hello,
this may be a serious issue and I am not very experienced with reporting
this kind of stuff, I will try my best. I believe there may be a memory
leak somewhere in postgres/postgis, which can eat up all the available
memory, leading to postgres crash (signal 9 kill of backend, postmaster
terminating others, recovery).
My setup and workload:
Postgres 12.1 (Ubuntu 12.1-1.pgdg18.04+1) installed via apt on Ubuntu
server 18.04 LTS, in VM in Azure cloud.
PostGIS version: 3.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
VM has 8cores, 56 GB RAM, 7 TB RAID from managed disks (azure network
storage)
shared_buffers = 12GB
work_mem = 256MB
maintenance_work_mem = 2GB
I was running 8 clients (on different VMs, over network) using the
database, batch processing geographic stuff. Each worker is running one
query at a time, so pretty much 8 parallel queries 100% of the time.
Queries themselves are fairly short (5-60 seconds), SELECTing rows by
geometry index (GIST) and doing stuff like ST_SimplifyPreserveTopology,
ST_Buffer, ST_Union on them, essentially using all 8 cores to 100%. There
is a lot of things to process, so this was going on for maybe 12 hours,
when I noticed (in "htop") that memory usage is unusually high, somewhere
about 50 GB. It was suspicious, because it is way higher than it should be
with 12 GB shared buffers and 256MB work_mem with 8 clients, and it
continued to grow slowly (I could see numbers in RES column in htop slowly
rise for all backends). Until eventually:
2019-12-06 00:04:24 UTC [21487-8] LOG: server process (PID 52059) was
terminated by signal 9: Killed
2019-12-06 00:04:24 UTC [21487-10] LOG: terminating any other active
server processes
2019-12-06 00:04:24 UTC [91091-1] WARNING: terminating connection because
of crash of another server process
2019-12-06 00:04:24 UTC [91091-2] DETAIL: The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
...
FATAL: the database system is in recovery mode
...
I realize, this is probably not enough for you. But I will be running this
workload again soon, so if this repeats, what should I do to help you
diagnose this?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
@ 2019-12-06 11:01 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
0 siblings, 1 reply; 16+ messages in thread
From: Tomas Vondra @ 2019-12-06 11:01 UTC (permalink / raw)
To: Roman Cervenak <roman@cervenak.info>; +Cc: pgsql-bugs@lists.postgresql.org
On Fri, Dec 06, 2019 at 10:22:42AM +0100, Roman Cervenak wrote:
>Hello,
>this may be a serious issue and I am not very experienced with reporting
>this kind of stuff, I will try my best. I believe there may be a memory
>leak somewhere in postgres/postgis, which can eat up all the available
>memory, leading to postgres crash (signal 9 kill of backend, postmaster
>terminating others, recovery).
>
>My setup and workload:
>Postgres 12.1 (Ubuntu 12.1-1.pgdg18.04+1) installed via apt on Ubuntu
>server 18.04 LTS, in VM in Azure cloud.
>PostGIS version: 3.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
>VM has 8cores, 56 GB RAM, 7 TB RAID from managed disks (azure network
>storage)
>
>shared_buffers = 12GB
>work_mem = 256MB
>maintenance_work_mem = 2GB
>
>I was running 8 clients (on different VMs, over network) using the
>database, batch processing geographic stuff. Each worker is running one
>query at a time, so pretty much 8 parallel queries 100% of the time.
>Queries themselves are fairly short (5-60 seconds), SELECTing rows by
>geometry index (GIST) and doing stuff like ST_SimplifyPreserveTopology,
>ST_Buffer, ST_Union on them, essentially using all 8 cores to 100%. There
>is a lot of things to process, so this was going on for maybe 12 hours,
>when I noticed (in "htop") that memory usage is unusually high, somewhere
>about 50 GB. It was suspicious, because it is way higher than it should be
>with 12 GB shared buffers and 256MB work_mem with 8 clients, and it
>continued to grow slowly (I could see numbers in RES column in htop slowly
>rise for all backends). Until eventually:
>
>2019-12-06 00:04:24 UTC [21487-8] LOG: server process (PID 52059) was
>terminated by signal 9: Killed
>2019-12-06 00:04:24 UTC [21487-10] LOG: terminating any other active
>server processes
>2019-12-06 00:04:24 UTC [91091-1] WARNING: terminating connection because
>of crash of another server process
>2019-12-06 00:04:24 UTC [91091-2] DETAIL: The postmaster has commanded
>this server process to roll back the current transaction and exit, because
>another server process exited abnormally and possibly corrupted shared
>memory.
>...
>FATAL: the database system is in recovery mode
>...
>
>I realize, this is probably not enough for you. But I will be running this
>workload again soon, so if this repeats, what should I do to help you
>diagnose this?
The kill was initiated by oom killer, I suppose? It might be interesting
to see the message written to dmesg by it, it usually includes info
about how much memory was used by the backend etc.
The other thing that might be useful is dump of memory contexts - you'll
have to wait until the memory usage gets excessive (i.e. shortly before
the oom killer would kick in), attach gdb to a backend and call
MemoryContextStats on TopMemoryContext. So, something like this:
$ gdb -p $PID
(gdb) p MemoryContextStats(TopMemoryContext)
(gdb) q
which writes a bunch of info about memory contexts into the server log.
But, I'm not sure this will actually help. Based on what you wrote, the
memory stays allocated across queries. So either it's allocated in one
of the long-lived contexts (which queries usually don't do), or it's
allocated directly through malloc() and not through our memory context
infrastructure (hence it'll be invisible in the context stats).
I'm not particularly familiar with PostGIS, but AFAIK it's using various
libraries, and those are naturally using malloc/free directly. So maybe
it's not freeing the memory in some cases.
What would really help is having some sort of reproducer, and/or running
the queries with valgrind, which can detect memory leaks.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2019-12-06 11:46 ` Roman Cervenak <roman@cervenak.info>
2019-12-06 12:41 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 1 reply; 16+ messages in thread
From: Roman Cervenak @ 2019-12-06 11:46 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: pgsql-bugs@lists.postgresql.org
Yes, it was killed by oom killer:
[2037990.376427]
oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.slice/system-postgresql.slice,task=postgres,pid=52059,uid=111
[2037990.376433] Out of memory: Kill process 52059 (postgres) score 294 or
sacrifice child
[2037990.384186] Killed process 52059 (postgres) total-vm:17508832kB,
anon-rss:4309296kB, file-rss:108kB, shmem-rss:12641580kB
[2037990.516504] oom_reaper: reaped process 52059 (postgres), now
anon-rss:0kB, file-rss:0kB, shmem-rss:12641580kB
(full dmesg.log attached, if it is interesting; there are more postgres
backends visible, but they were inactive at the time)
I can try the gdb dump next time I will see it. But I cannot imagine giving
you reproducible case - it is 500 GB proprietary database, and without it,
queries would be hardly useful, I presume? I can try to make "sample" with
generated data and find out if I can reproduce the issue with my queries
that way, but that will be quite time consuming.
Would it help to dump the memory of the backend process and deliver the
dump (by some private channel) to somebody to identify who is consuming all
that memory? (that is the usual drill in windows)
On Fri, Dec 6, 2019 at 12:02 PM Tomas Vondra <tomas.vondra@2ndquadrant.com>
wrote:
> On Fri, Dec 06, 2019 at 10:22:42AM +0100, Roman Cervenak wrote:
> >Hello,
> >this may be a serious issue and I am not very experienced with reporting
> >this kind of stuff, I will try my best. I believe there may be a memory
> >leak somewhere in postgres/postgis, which can eat up all the available
> >memory, leading to postgres crash (signal 9 kill of backend, postmaster
> >terminating others, recovery).
> >
> >My setup and workload:
> >Postgres 12.1 (Ubuntu 12.1-1.pgdg18.04+1) installed via apt on Ubuntu
> >server 18.04 LTS, in VM in Azure cloud.
> >PostGIS version: 3.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
> >VM has 8cores, 56 GB RAM, 7 TB RAID from managed disks (azure network
> >storage)
> >
> >shared_buffers = 12GB
> >work_mem = 256MB
> >maintenance_work_mem = 2GB
> >
> >I was running 8 clients (on different VMs, over network) using the
> >database, batch processing geographic stuff. Each worker is running one
> >query at a time, so pretty much 8 parallel queries 100% of the time.
> >Queries themselves are fairly short (5-60 seconds), SELECTing rows by
> >geometry index (GIST) and doing stuff like ST_SimplifyPreserveTopology,
> >ST_Buffer, ST_Union on them, essentially using all 8 cores to 100%. There
> >is a lot of things to process, so this was going on for maybe 12 hours,
> >when I noticed (in "htop") that memory usage is unusually high, somewhere
> >about 50 GB. It was suspicious, because it is way higher than it should be
> >with 12 GB shared buffers and 256MB work_mem with 8 clients, and it
> >continued to grow slowly (I could see numbers in RES column in htop slowly
> >rise for all backends). Until eventually:
> >
> >2019-12-06 00:04:24 UTC [21487-8] LOG: server process (PID 52059) was
> >terminated by signal 9: Killed
> >2019-12-06 00:04:24 UTC [21487-10] LOG: terminating any other active
> >server processes
> >2019-12-06 00:04:24 UTC [91091-1] WARNING: terminating connection
> because
> >of crash of another server process
> >2019-12-06 00:04:24 UTC [91091-2] DETAIL: The postmaster has commanded
> >this server process to roll back the current transaction and exit, because
> >another server process exited abnormally and possibly corrupted shared
> >memory.
> >...
> >FATAL: the database system is in recovery mode
> >...
> >
> >I realize, this is probably not enough for you. But I will be running this
> >workload again soon, so if this repeats, what should I do to help you
> >diagnose this?
>
> The kill was initiated by oom killer, I suppose? It might be interesting
> to see the message written to dmesg by it, it usually includes info
> about how much memory was used by the backend etc.
>
> The other thing that might be useful is dump of memory contexts - you'll
> have to wait until the memory usage gets excessive (i.e. shortly before
> the oom killer would kick in), attach gdb to a backend and call
> MemoryContextStats on TopMemoryContext. So, something like this:
>
> $ gdb -p $PID
> (gdb) p MemoryContextStats(TopMemoryContext)
> (gdb) q
>
> which writes a bunch of info about memory contexts into the server log.
>
> But, I'm not sure this will actually help. Based on what you wrote, the
> memory stays allocated across queries. So either it's allocated in one
> of the long-lived contexts (which queries usually don't do), or it's
> allocated directly through malloc() and not through our memory context
> infrastructure (hence it'll be invisible in the context stats).
>
> I'm not particularly familiar with PostGIS, but AFAIK it's using various
> libraries, and those are naturally using malloc/free directly. So maybe
> it's not freeing the memory in some cases.
>
> What would really help is having some sort of reproducer, and/or running
> the queries with valgrind, which can detect memory leaks.
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
Attachments:
dmesg.log (application/octet-stream, 75.5K) download
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
@ 2019-12-06 12:41 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 11:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
0 siblings, 1 reply; 16+ messages in thread
From: Tomas Vondra @ 2019-12-06 12:41 UTC (permalink / raw)
To: Roman Cervenak <roman@cervenak.info>; +Cc: pgsql-bugs@lists.postgresql.org
On Fri, Dec 06, 2019 at 12:46:44PM +0100, Roman Cervenak wrote:
>Yes, it was killed by oom killer:
>
>[2037990.376427]
>oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.slice/system-postgresql.slice,task=postgres,pid=52059,uid=111
>[2037990.376433] Out of memory: Kill process 52059 (postgres) score 294 or
>sacrifice child
>[2037990.384186] Killed process 52059 (postgres) total-vm:17508832kB,
>anon-rss:4309296kB, file-rss:108kB, shmem-rss:12641580kB
>[2037990.516504] oom_reaper: reaped process 52059 (postgres), now
>anon-rss:0kB, file-rss:0kB, shmem-rss:12641580kB
>
>(full dmesg.log attached, if it is interesting; there are more postgres
>backends visible, but they were inactive at the time)
>
OK, so it allocated extra ~4.3GB or so (plus shared buffers).
>I can try the gdb dump next time I will see it. But I cannot imagine giving
>you reproducible case - it is 500 GB proprietary database, and without it,
>queries would be hardly useful, I presume? I can try to make "sample" with
>generated data and find out if I can reproduce the issue with my queries
>that way, but that will be quite time consuming.
>
Yeah, I understand the data is proprietary. But we need to identify
where the issue is, somehow. And being able to reproduce it is likely
the best way to do that. One way to do that is to either generate
synthetic data with similar structure/features (geometries of similar
size etc.), and hope that it triggers the issue too. Or distill a subset
of data triggering the issue and anonymize it. Or something like that.
Yes, it's going to be quite time consuming, but I don't have better
ideas. Maybe running it under valgrind would help - you'd have to
rebuild PostgreSQL with valgrind support and run the workload on it. The
queries would be much slower (possibly by an order of magnitude or so),
so you'd have to run it on a different machine, but that's machine time,
not time wasted by a human.
Anyway, let's start with the easy stuff - try running it again and get
the memory context stats. Maybe it's a simple leak in PostgreSQL, in
which case it should be easier to investigate it. If that turns out to
be untrue, you can try this more complicated stuff (valgrind, ...).
>Would it help to dump the memory of the backend process and deliver the
>dump (by some private channel) to somebody to identify who is consuming all
>that memory? (that is the usual drill in windows)
>
I don't think that'd be very helpful - if this really is memory leak in
one of the libraries, I have no idea how to spot that in the memory
dump. Also, if you claim the data is sensitive/proprietary, you should
not really be sharing the dumps.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 12:41 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2019-12-11 11:09 ` Roman Cervenak <roman@cervenak.info>
2019-12-11 14:23 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 16:49 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 16+ messages in thread
From: Roman Cervenak @ 2019-12-11 11:09 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: pgsql-bugs@lists.postgresql.org
Hi,
I am trying to do
(gdb) p MemoryContextStats(TopMemoryContext)
on postgres backend process, but the response is: 'TopMemoryContext' has
unknown type; cast it to its declared type
I never debugged postgres (or anything under linux), and google is not
helpful. Can you please advise? Full output is attached.
Regards,
Roman
On Fri, Dec 6, 2019 at 1:41 PM Tomas Vondra <tomas.vondra@2ndquadrant.com>
wrote:
> On Fri, Dec 06, 2019 at 12:46:44PM +0100, Roman Cervenak wrote:
> >Yes, it was killed by oom killer:
> >
> >[2037990.376427]
>
> >oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.slice/system-postgresql.slice,task=postgres,pid=52059,uid=111
> >[2037990.376433] Out of memory: Kill process 52059 (postgres) score 294 or
> >sacrifice child
> >[2037990.384186] Killed process 52059 (postgres) total-vm:17508832kB,
> >anon-rss:4309296kB, file-rss:108kB, shmem-rss:12641580kB
> >[2037990.516504] oom_reaper: reaped process 52059 (postgres), now
> >anon-rss:0kB, file-rss:0kB, shmem-rss:12641580kB
> >
> >(full dmesg.log attached, if it is interesting; there are more postgres
> >backends visible, but they were inactive at the time)
> >
>
> OK, so it allocated extra ~4.3GB or so (plus shared buffers).
>
> >I can try the gdb dump next time I will see it. But I cannot imagine
> giving
> >you reproducible case - it is 500 GB proprietary database, and without it,
> >queries would be hardly useful, I presume? I can try to make "sample" with
> >generated data and find out if I can reproduce the issue with my queries
> >that way, but that will be quite time consuming.
> >
>
> Yeah, I understand the data is proprietary. But we need to identify
> where the issue is, somehow. And being able to reproduce it is likely
> the best way to do that. One way to do that is to either generate
> synthetic data with similar structure/features (geometries of similar
> size etc.), and hope that it triggers the issue too. Or distill a subset
> of data triggering the issue and anonymize it. Or something like that.
>
> Yes, it's going to be quite time consuming, but I don't have better
> ideas. Maybe running it under valgrind would help - you'd have to
> rebuild PostgreSQL with valgrind support and run the workload on it. The
> queries would be much slower (possibly by an order of magnitude or so),
> so you'd have to run it on a different machine, but that's machine time,
> not time wasted by a human.
>
> Anyway, let's start with the easy stuff - try running it again and get
> the memory context stats. Maybe it's a simple leak in PostgreSQL, in
> which case it should be easier to investigate it. If that turns out to
> be untrue, you can try this more complicated stuff (valgrind, ...).
>
> >Would it help to dump the memory of the backend process and deliver the
> >dump (by some private channel) to somebody to identify who is consuming
> all
> >that memory? (that is the usual drill in windows)
> >
>
> I don't think that'd be very helpful - if this really is memory leak in
> one of the libraries, I have no idea how to spot that in the memory
> dump. Also, if you claim the data is sensitive/proprietary, you should
> not really be sharing the dumps.
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
Attachments:
postgres_gdb.txt (text/plain, 9.0K) download
root@myvm:~# gdb -p 26850
GNU gdb (Ubuntu 8.1-0ubuntu3.2) 8.1.0.20180409-git
Copyright (C) 2018 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-linux-gnu".
Type "show configuration" for configuration details.
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Find the GDB manual and other documentation resources online at:
<http://www.gnu.org/software/gdb/documentation/>.
For help, type "help".
Type "apropos word" to search for commands related to "word".
Attaching to process 26850
Reading symbols from /usr/lib/postgresql/12/bin/postgres...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libpthread.so.0...Reading symbols from /usr/lib/debug/.build-id/28/c6aade70b2d40d1f0f3d0a1a0cad1ab816448f.debug...done.
done.
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1".
Reading symbols from /usr/lib/x86_64-linux-gnu/libxml2.so.2...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libpam.so.0...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libssl.so.1.1...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libcrypto.so.1.1...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libgssapi_krb5.so.2...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/librt.so.1...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/librt-2.27.so...done.
done.
Reading symbols from /lib/x86_64-linux-gnu/libdl.so.2...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libdl-2.27.so...done.
done.
Reading symbols from /lib/x86_64-linux-gnu/libm.so.6...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libm-2.27.so...done.
done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libldap_r-2.4.so.2...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libicui18n.so.60...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libicuuc.so.60...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libsystemd.so.0...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libc.so.6...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libc-2.27.so...done.
done.
Reading symbols from /lib64/ld-linux-x86-64.so.2...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/ld-2.27.so...done.
done.
Reading symbols from /lib/x86_64-linux-gnu/libz.so.1...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/liblzma.so.5...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libaudit.so.1...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libkrb5.so.3...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libk5crypto.so.3...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libcom_err.so.2...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libkrb5support.so.0...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/liblber-2.4.so.2...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libresolv.so.2...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libresolv-2.27.so...done.
done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libsasl2.so.2...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libgssapi.so.3...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libgnutls.so.30...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libstdc++.so.6...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libgcc_s.so.1...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libicudata.so.60...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/liblz4.so.1...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libgcrypt.so.20...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libcap-ng.so.0...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libkeyutils.so.1...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libheimntlm.so.0...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libkrb5.so.26...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libasn1.so.8...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libhcrypto.so.4...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libroken.so.18...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libp11-kit.so.0...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libidn2.so.0...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libunistring.so.2...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libtasn1.so.6...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libnettle.so.6...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libhogweed.so.4...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libgmp.so.10...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libgpg-error.so.0...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libwind.so.0...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libheimbase.so.1...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libhx509.so.5...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libsqlite3.so.0...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libcrypt.so.1...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libcrypt-2.27.so...done.
done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libffi.so.6...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libnss_files.so.2...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libnss_files-2.27.so...done.
done.
Reading symbols from /usr/lib/postgresql/12/lib/postgis-3.so...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libgeos_c.so.1...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libproj.so.12...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libjson-c.so.3...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libprotobuf-c.so.1...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libSFCGAL.so.1...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libgeos-3.7.1.so...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libCGAL_Core.so.13...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libboost_thread.so.1.65.1...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libboost_serialization.so.1.65.1...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libCGAL.so.13...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libgmpxx.so.4...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libmpfr.so.6...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libboost_system.so.1.65.1...(no debugging symbols found)...done.
Reading symbols from /usr/lib/postgresql/12/lib/plpgsql.so...(no debugging symbols found)...done.
Reading symbols from /usr/lib/postgresql/12/lib/llvmjit.so...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libLLVM-6.0.so.1...(no debugging symbols found)...done.
Reading symbols from /usr/lib/x86_64-linux-gnu/libedit.so.2...(no debugging symbols found)...done.
Reading symbols from /lib/x86_64-linux-gnu/libtinfo.so.5...(no debugging symbols found)...done.
Reading symbols from /usr/lib/postgresql/12/lib/utf8_and_iso8859.so...(no debugging symbols found)...done.
0x00007f4d3f073b77 in epoll_wait (epfd=5, events=0x55f735ff1260, maxevents=1, timeout=-1) at ../sysdeps/unix/sysv/linux/epoll_wait.c:30
30 ../sysdeps/unix/sysv/linux/epoll_wait.c: No such file or directory.
(gdb) p MemoryContextStats(TopMemoryContext)
'TopMemoryContext' has unknown type; cast it to its declared type
(gdb)
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 12:41 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 11:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
@ 2019-12-11 14:23 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
1 sibling, 0 replies; 16+ messages in thread
From: Tomas Vondra @ 2019-12-11 14:23 UTC (permalink / raw)
To: Roman Cervenak <roman@cervenak.info>; +Cc: pgsql-bugs@lists.postgresql.org
Hi,
On Wed, Dec 11, 2019 at 12:09:11PM +0100, Roman Cervenak wrote:
>Hi,
>I am trying to do
>
>(gdb) p MemoryContextStats(TopMemoryContext)
>
>on postgres backend process, but the response is: 'TopMemoryContext' has
>unknown type; cast it to its declared type
>
>I never debugged postgres (or anything under linux), and google is not
>helpful. Can you please advise? Full output is attached.
>
Hmmmm, I don't recall having this issue. Can you try installing the
package with debug symbols, it should be called -debug or so.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 12:41 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 11:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
@ 2019-12-11 16:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-12-11 17:23 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-13 10:51 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
1 sibling, 2 replies; 16+ messages in thread
From: Tom Lane @ 2019-12-11 16:49 UTC (permalink / raw)
To: Roman Cervenak <roman@cervenak.info>; +Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>; pgsql-bugs@lists.postgresql.org
Roman Cervenak <roman@cervenak.info> writes:
> I am trying to do
> (gdb) p MemoryContextStats(TopMemoryContext)
I think what you wanted to do is "call MemoryContextStats(TopMemoryContext)".
"p" means "print".
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 12:41 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 11:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-11 16:49 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tom Lane <tgl@sss.pgh.pa.us>
@ 2019-12-11 17:23 ` Roman Cervenak <roman@cervenak.info>
1 sibling, 0 replies; 16+ messages in thread
From: Roman Cervenak @ 2019-12-11 17:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>; pgsql-bugs@lists.postgresql.org
Same error with call.
On Wed, Dec 11, 2019, 17:49 Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Roman Cervenak <roman@cervenak.info> writes:
> > I am trying to do
> > (gdb) p MemoryContextStats(TopMemoryContext)
>
> I think what you wanted to do is "call
> MemoryContextStats(TopMemoryContext)".
> "p" means "print".
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 12:41 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 11:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-11 16:49 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tom Lane <tgl@sss.pgh.pa.us>
@ 2019-12-13 10:51 ` Roman Cervenak <roman@cervenak.info>
2019-12-13 13:20 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
1 sibling, 1 reply; 16+ messages in thread
From: Roman Cervenak @ 2019-12-13 10:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>; pgsql-bugs@lists.postgresql.org
With "postgresql-12-dbgsym" installed,
call MemoryContextStats(TopMemoryContext) succeeded on postgres backend
process with high memory consumption.
I cut it from server logs, see attached file (I have redacted queries and
table structures, I left SELECT parts where is visible that geometric
operations are involved).
Regards,
Roman Cervenak
On Wed, Dec 11, 2019 at 5:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Roman Cervenak <roman@cervenak.info> writes:
> > I am trying to do
> > (gdb) p MemoryContextStats(TopMemoryContext)
>
> I think what you wanted to do is "call
> MemoryContextStats(TopMemoryContext)".
> "p" means "print".
>
> regards, tom lane
>
Attachments:
pg_memory_context.txt (text/plain, 16.9K) download
TopMemoryContext: 135832 total in 7 blocks; 16424 free (23 chunks); 119408 used
TopTransactionContext: 8192 total in 1 blocks; 7424 free (0 chunks); 768 used
ExecutorState: 8192 total in 1 blocks; 304 free (0 chunks); 7888 used
SQL function: 49208 total in 4 blocks; 12608 free (4 chunks); 36600 used: z
ExprContext: 8192 total in 1 blocks; 7936 free (1 chunks); 256 used
SPI Exec: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
SPI Proc: 8192 total in 1 blocks; 6592 free (0 chunks); 1600 used
SPI TupTable: 8192 total in 1 blocks; 6288 free (0 chunks); 1904 used
PLpgSQL per-statement data: 8192 total in 1 blocks; 7648 free (0 chunks); 544 used
PLpgSQL per-statement data: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
expanded array: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pgstat TabStatusArray lookup hash table: 8192 total in 1 blocks; 416 free (0 chunks); 7776 used
PL/pgSQL function: 8192 total in 1 blocks; 1624 free (1 chunks); 6568 used: text_is_latin(text)
PL/pgSQL function: 65536 total in 4 blocks; 26952 free (3 chunks); 38584 used: [redacted]
PL/pgSQL function: 32768 total in 3 blocks; 9640 free (2 chunks); 23128 used: [redacted]
PL/pgSQL function: 65536 total in 4 blocks; 31784 free (1 chunks); 33752 used: [redacted]
PL/pgSQL function: 32768 total in 3 blocks; 18376 free (2 chunks); 14392 used: [redacted]
PL/pgSQL function: 32768 total in 3 blocks; 10648 free (2 chunks); 22120 used: [redacted]
PL/pgSQL function: 65536 total in 4 blocks; 9176 free (3 chunks); 56360 used: [redacted]
PL/pgSQL function: 65536 total in 4 blocks; 32264 free (2 chunks); 33272 used: [redacted]
PL/pgSQL function: 32768 total in 3 blocks; 2896 free (2 chunks); 29872 used: [redacted]
Type information cache: 24352 total in 2 blocks; 2624 free (0 chunks); 21728 used
PL/pgSQL function: 32768 total in 3 blocks; 3704 free (2 chunks); 29064 used: [redacted]
PL/pgSQL function: 65536 total in 4 blocks; 19816 free (2 chunks); 45720 used: [redacted]
Record information cache: 8192 total in 1 blocks; 1584 free (0 chunks); 6608 used
PL/pgSQL function: 32768 total in 3 blocks; 15496 free (5 chunks); 17272 used: [redacted]
TableSpace cache: 8192 total in 1 blocks; 2096 free (0 chunks); 6096 used
Operator lookup cache: 24576 total in 2 blocks; 10760 free (3 chunks); 13816 used
PLpgSQL cast info: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1584 free (0 chunks); 6608 used
PL/pgSQL function: 32768 total in 3 blocks; 10592 free (5 chunks); 22176 used: [redacted]
Rendezvous variable hash: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
PLpgSQL function hash: 24528 total in 2 blocks; 2624 free (0 chunks); 21904 used
CFuncHash: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
RowDescriptionContext: 8192 total in 1 blocks; 3824 free (2 chunks); 4368 used
MessageContext: 8192 total in 1 blocks; 6752 free (0 chunks); 1440 used
Operator class cache: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
smgr relation table: 32768 total in 3 blocks; 8608 free (8 chunks); 24160 used
TransactionAbortContext: 32768 total in 1 blocks; 32512 free (0 chunks); 256 used
Portal hash: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
TopPortalContext: 8192 total in 1 blocks; 7392 free (0 chunks); 800 used
PortalContext: 3072 total in 2 blocks; 920 free (1 chunks); 2152 used: <unnamed portal 124258>
ExecutorState: 65536 total in 4 blocks; 18856 free (0 chunks); 46680 used
TupleSort main: 32824 total in 2 blocks; 6752 free (0 chunks); 26072 used
Caller tuples: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
TupleSort main: 32824 total in 2 blocks; 4272 free (0 chunks); 28552 used
Caller tuples: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
PortalContext: 1024 total in 1 blocks; 72 free (0 chunks); 952 used:
ExecutorState: 32824 total in 3 blocks; 3576 free (0 chunks); 29248 used
printtup: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
Table function arguments: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
Relcache by OID: 16384 total in 2 blocks; 1432 free (2 chunks); 14952 used
CacheMemoryContext: 2442624 total in 25 blocks; 952656 free (1 chunks); 1489968 used
CachedPlan: 17408 total in 5 blocks; 1264 free (1 chunks); 16144 used: SELECT ST_SimplifyPreserveTopology(ST_LineMerge(ST_Collect(td.geom)),simp), [redacted]
CachedPlan: 8192 total in 4 blocks; 3544 free (0 chunks); 4648 used: [redacted]
CachedPlanSource: 8192 total in 4 blocks; 1816 free (0 chunks); 6376 used: SELECT [redacted]
unnamed prepared statement: 32768 total in 3 blocks; 13688 free (2 chunks); 19080 used
CachedPlan: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: SELECT false
CachedPlanSource: 2048 total in 2 blocks; 264 free (0 chunks); 1784 used: SELECT false
CachedPlanQuery: 2048 total in 2 blocks; 976 free (1 chunks); 1072 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: pg_toast_4874580_index
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: pg_toast_4874640_index
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: pg_toast_4874698_index
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: pg_toast_14283960_index
CachedPlan: 35840 total in 6 blocks; 4360 free (1 chunks); 31480 used: SELECT [redacted]
CachedPlan: 8192 total in 4 blocks; 2408 free (0 chunks); 5784 used: SELECT [redacted]
CachedPlan: 17408 total in 5 blocks; 1248 free (1 chunks); 16160 used: SELECT ST_SimplifyPreserveTopology(ST_LineMerge(ST_Collect(td.geom)),simp), [redacted]
CachedPlan: 8192 total in 4 blocks; 3992 free (0 chunks); 4200 used: SELECT [redacted]
CachedPlan: 8192 total in 4 blocks; 760 free (0 chunks); 7432 used: SELECT ST_SimplifyPreserveTopology(f.geometry, simp), [redacted]
CachedPlan: 265216 total in 9 blocks; 114000 free (0 chunks); 151216 used: SELECT ST_SimplifyPreserveTopology(ST_LineMerge(ST_Collect(t.geom)),simp) [redacted]
CachedPlan: 17408 total in 5 blocks; 5472 free (1 chunks); 11936 used: SELECT ST_SimplifyPreserveTopology(ST_LineMerge(ST_Collect(t.geometry)),simp), [redacted]
CachedPlan: 8192 total in 4 blocks; 3048 free (0 chunks); 5144 used: SELECT ST_SimplifyPreserveTopology(w.geometry,simp), [redacted]
CachedPlan: 8192 total in 4 blocks; 2664 free (0 chunks); 5528 used: SELECT ST_SimplifyPreserveTopology(w.geometry,simp), [redacted]
CachedPlan: 33792 total in 6 blocks; 9744 free (1 chunks); 24048 used: SELECT ST_SimplifyPreserveTopology(t.geometry, simp), [redacted]
CachedPlan: 8192 total in 4 blocks; 3040 free (0 chunks); 5152 used: SELECT ST_SimplifyPreserveTopology(a.geometry, simp) [redacted]
CachedPlan: 8192 total in 4 blocks; 4000 free (0 chunks); 4192 used: SELECT p.geom [redacted]
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: pg_toast_4874678_index
CachedPlan: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: SELECT true
CachedPlanSource: 2048 total in 2 blocks; 264 free (0 chunks); 1784 used: SELECT true
CachedPlanQuery: 2048 total in 2 blocks; 976 free (1 chunks); 1072 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 200 free (0 chunks); 1848 used: SELECT convert_to(input, 'iso-8859-15')
CachedPlanSource: 2048 total in 2 blocks; 48 free (0 chunks); 2000 used: SELECT convert_to(input, 'iso-8859-15')
CachedPlanQuery: 2048 total in 2 blocks; 648 free (1 chunks); 1400 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
index info: 2048 total in 2 blocks; 952 free (1 chunks); 1096 used: [redacted]
index info: 4096 total in 3 blocks; 1664 free (0 chunks); 2432 used: [redacted]
index info: 2048 total in 2 blocks; 744 free (2 chunks); 1304 used: [redacted]
index info: 2048 total in 2 blocks; 696 free (1 chunks); 1352 used: [redacted]
index info: 2048 total in 2 blocks; 376 free (0 chunks); 1672 used: [redacted]
index info: 4096 total in 3 blocks; 1632 free (1 chunks); 2464 used: [redacted]
index info: 2048 total in 2 blocks; 728 free (2 chunks); 1320 used: [redacted]
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: [redacted]
CachedPlanSource: 32768 total in 6 blocks; 1696 free (0 chunks); 31072 used: SELECT t.geometry, [redacted]
CachedPlanQuery: 32768 total in 6 blocks; 8240 free (1 chunks); 24528 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 72 free (0 chunks); 1976 used: SELECT ('SET LOCAL search_path to ' || sch)
CachedPlanSource: 2048 total in 2 blocks; 152 free (0 chunks); 1896 used: SELECT ('SET LOCAL search_path to ' || sch)
CachedPlanQuery: 2048 total in 2 blocks; 520 free (1 chunks); 1528 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 608 free (0 chunks); 1440 used: SELECT schemas
CachedPlanSource: 2048 total in 2 blocks; 512 free (0 chunks); 1536 used: SELECT schemas
CachedPlanQuery: 2048 total in 2 blocks; 1008 free (2 chunks); 1040 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 320 free (0 chunks); 1728 used: SELECT [redacted]
CachedPlanSource: 4096 total in 3 blocks; 1864 free (2 chunks); 2232 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 96 free (1 chunks); 1952 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 312 free (0 chunks); 1736 used: SELECT [redacted]
CachedPlanSource: 2048 total in 2 blocks; 232 free (0 chunks); 1816 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 312 free (0 chunks); 1736 used: SELECT [redacted]
CachedPlanSource: 2048 total in 2 blocks; 232 free (0 chunks); 1816 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 312 free (0 chunks); 1736 used: SELECT [redacted]
CachedPlanSource: 2048 total in 2 blocks; 232 free (0 chunks); 1816 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 312 free (0 chunks); 1736 used: SELECT [redacted]
CachedPlanSource: 2048 total in 2 blocks; 232 free (0 chunks); 1816 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 8 free (0 chunks); 2040 used: SELECT [redacted]
CachedPlanSource: 4096 total in 3 blocks; 1776 free (1 chunks); 2320 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 456 free (1 chunks); 1592 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
index info: 2048 total in 2 blocks; 728 free (2 chunks); 1320 used: [redacted]
CachedPlanSource: 8192 total in 4 blocks; 2088 free (0 chunks); 6104 used: SELECT t.geom, [redacted]
CachedPlanQuery: 8192 total in 4 blocks; 3328 free (1 chunks); 4864 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 72 free (0 chunks); 1976 used: SELECT ('SET LOCAL search_path to ' || sch)
CachedPlanSource: 2048 total in 2 blocks; 152 free (0 chunks); 1896 used: SELECT ('SET LOCAL search_path to ' || sch)
CachedPlanQuery: 2048 total in 2 blocks; 520 free (1 chunks); 1528 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 608 free (0 chunks); 1440 used: SELECT schemas
CachedPlanSource: 2048 total in 2 blocks; 512 free (0 chunks); 1536 used: SELECT schemas
CachedPlanQuery: 2048 total in 2 blocks; 1008 free (2 chunks); 1040 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 4096 total in 3 blocks; 1384 free (0 chunks); 2712 used: SELECT [redacted]
CachedPlanSource: 4096 total in 3 blocks; 1264 free (0 chunks); 2832 used: SELECT [redacted]
CachedPlanQuery: 4096 total in 3 blocks; 1896 free (1 chunks); 2200 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 8 free (0 chunks); 2040 used: SELECT [redacted]
CachedPlanSource: 4096 total in 3 blocks; 1776 free (1 chunks); 2320 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 456 free (1 chunks); 1592 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: pg_toast_14228756_index
CachedPlanSource: 16384 total in 5 blocks; 824 free (0 chunks); 15560 used: SELECT ST_SimplifyPreserveTopology(ST_LineMerge(ST_Collect(td.geom)),simp), [redacted]
CachedPlanQuery: 16384 total in 5 blocks; 4464 free (1 chunks); 11920 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 312 free (0 chunks); 1736 used: SELECT [redacted]
CachedPlanSource: 2048 total in 2 blocks; 232 free (0 chunks); 1816 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 72 free (0 chunks); 1976 used: SELECT ('SET search_path to ' || sch)
CachedPlanSource: 2048 total in 2 blocks; 152 free (0 chunks); 1896 used: SELECT ('SET search_path to ' || sch)
CachedPlanQuery: 2048 total in 2 blocks; 520 free (1 chunks); 1528 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 608 free (0 chunks); 1440 used: SELECT schemas
CachedPlanSource: 2048 total in 2 blocks; 512 free (0 chunks); 1536 used: SELECT schemas
CachedPlanQuery: 2048 total in 2 blocks; 1008 free (2 chunks); 1040 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 88 free (0 chunks); 1960 used: SELECT [redacted]
CachedPlanSource: 4096 total in 3 blocks; 1952 free (2 chunks); 2144 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 712 free (1 chunks); 1336 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 8 free (0 chunks); 2040 used: SELECT [redacted]
CachedPlanSource: 4096 total in 3 blocks; 1776 free (1 chunks); 2320 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 456 free (1 chunks); 1592 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
index info: 4096 total in 3 blocks; 1664 free (0 chunks); 2432 used: [redacted]
index info: 2048 total in 2 blocks; 728 free (2 chunks); 1320 used: [redacted]
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: [redacted]
CachedPlanSource: 4096 total in 3 blocks; 680 free (0 chunks); 3416 used: SELECT r.geometry, [redacted]
CachedPlanQuery: 4096 total in 3 blocks; 808 free (1 chunks); 3288 used
268 more child contexts containing 886784 total in 635 blocks; 282872 free (219 chunks); 603912 used
WAL record construction: 49768 total in 2 blocks; 6368 free (0 chunks); 43400 used
PrivateRefCount: 8192 total in 1 blocks; 2624 free (0 chunks); 5568 used
MdSmgr: 8192 total in 1 blocks; 5168 free (4 chunks); 3024 used
LOCALLOCK hash: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
Timezones: 104120 total in 2 blocks; 2624 free (0 chunks); 101496 used
ErrorContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
Grand total: 5683464 bytes in 1082 blocks; 2027536 free (362 chunks); 3655928 used
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 12:41 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 11:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-11 16:49 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tom Lane <tgl@sss.pgh.pa.us>
2019-12-13 10:51 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
@ 2019-12-13 13:20 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-17 11:51 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
0 siblings, 1 reply; 16+ messages in thread
From: Tomas Vondra @ 2019-12-13 13:20 UTC (permalink / raw)
To: Roman Cervenak <roman@cervenak.info>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
On Fri, Dec 13, 2019 at 11:51:55AM +0100, Roman Cervenak wrote:
>With "postgresql-12-dbgsym" installed,
>call MemoryContextStats(TopMemoryContext) succeeded on postgres backend
>process with high memory consumption.
>I cut it from server logs, see attached file (I have redacted queries and
>table structures, I left SELECT parts where is visible that geometric
>operations are involved).
>
The way I read the context stats, the memory leak has to be somewhere in
a library calling malloc directly, or something like that. Because per
the log, the PostgreSQL memory contexts are a whopping 5MB in total:
Grand total: 5683464 bytes in 1082 blocks; 2027536 free (362 chunks); 3655928
So this is likely a memory leak either somewhere in PostGIS (I see ~200
malloc calls in the current source code) or maybe in one of the
libraries it's using.
I think at this point we need to get more information about the queries
and data, or even better a reproducer. Or try running the queries under
valgrind, which does have a memory leak detector.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 12:41 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 11:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-11 16:49 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tom Lane <tgl@sss.pgh.pa.us>
2019-12-13 10:51 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-13 13:20 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2019-12-17 11:51 ` Roman Cervenak <roman@cervenak.info>
2019-12-17 12:50 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 1 reply; 16+ messages in thread
From: Roman Cervenak @ 2019-12-17 11:51 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
Hey guys,
I have reproducer for you.
Luckily, the issue is not specific for our data, and can be demonstrated
also on OpenStreepMap data. I have imported OSM planet with Imposm, which
creates tables with PostGIS geometry columns.
I have used roads for linestring testing and landuse for polygon testing,
with queries combining several geometric operations (similar to my
production workload).
The linesting test with 7 concurrent workers increases memory consumption
by about 6 GB per hour on my environment.
If you don't have OSM database with geometries, I have dumped the roads
table (about 25GB download):
https://sygicpublic.blob.core.windows.net/postgres-bug-data/osm_road.sql.gzip
Here is the tool running queries in parallel workers, written in .NET Core
(just fill in database connection info in Program.cs, build and run):
https://github.com/rouen-sk/PostgisMemoryLeakDemoCore
Let me know if I can help further.
Regards,
Roman Cervenak
On Fri, Dec 13, 2019 at 2:20 PM Tomas Vondra <tomas.vondra@2ndquadrant.com>
wrote:
> On Fri, Dec 13, 2019 at 11:51:55AM +0100, Roman Cervenak wrote:
> >With "postgresql-12-dbgsym" installed,
> >call MemoryContextStats(TopMemoryContext) succeeded on postgres backend
> >process with high memory consumption.
> >I cut it from server logs, see attached file (I have redacted queries and
> >table structures, I left SELECT parts where is visible that geometric
> >operations are involved).
> >
>
> The way I read the context stats, the memory leak has to be somewhere in
> a library calling malloc directly, or something like that. Because per
> the log, the PostgreSQL memory contexts are a whopping 5MB in total:
>
> Grand total: 5683464 bytes in 1082 blocks; 2027536 free (362 chunks);
> 3655928
>
> So this is likely a memory leak either somewhere in PostGIS (I see ~200
> malloc calls in the current source code) or maybe in one of the
> libraries it's using.
>
> I think at this point we need to get more information about the queries
> and data, or even better a reproducer. Or try running the queries under
> valgrind, which does have a memory leak detector.
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 12:41 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 11:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-11 16:49 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tom Lane <tgl@sss.pgh.pa.us>
2019-12-13 10:51 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-13 13:20 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-17 11:51 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
@ 2019-12-17 12:50 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-17 13:20 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
0 siblings, 1 reply; 16+ messages in thread
From: Tomas Vondra @ 2019-12-17 12:50 UTC (permalink / raw)
To: Roman Cervenak <roman@cervenak.info>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
On Tue, Dec 17, 2019 at 12:51:27PM +0100, Roman Cervenak wrote:
>Hey guys,
>I have reproducer for you.
>
>Luckily, the issue is not specific for our data, and can be demonstrated
>also on OpenStreepMap data. I have imported OSM planet with Imposm, which
>creates tables with PostGIS geometry columns.
>I have used roads for linestring testing and landuse for polygon testing,
>with queries combining several geometric operations (similar to my
>production workload).
>
>The linesting test with 7 concurrent workers increases memory consumption
>by about 6 GB per hour on my environment.
>
>If you don't have OSM database with geometries, I have dumped the roads
>table (about 25GB download):
>https://sygicpublic.blob.core.windows.net/postgres-bug-data/osm_road.sql.gzip
>Here is the tool running queries in parallel workers, written in .NET Core
>(just fill in database connection info in Program.cs, build and run):
>https://github.com/rouen-sk/PostgisMemoryLeakDemoCore
>
>Let me know if I can help further.
>
I'll try reproducing it, but I don't have any environment to run c#
stuff. Is it possible to reproduce a single query reproducing the issue,
with fixed parameters? Or is the randomization necessary?
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 12:41 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 11:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-11 16:49 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tom Lane <tgl@sss.pgh.pa.us>
2019-12-13 10:51 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-13 13:20 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-17 11:51 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-17 12:50 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2019-12-17 13:20 ` Roman Cervenak <roman@cervenak.info>
2019-12-17 18:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 1 reply; 16+ messages in thread
From: Roman Cervenak @ 2019-12-17 13:20 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
.NET core is cross platform, you can run it under windows or linux, just
install .net core runtime.
I did not try to run loop with fixed params, I tried to mimic my production
workload (which is not random of course, but highly variable by location).
You can certainly try.
On Tue, Dec 17, 2019, 13:50 Tomas Vondra <tomas.vondra@2ndquadrant.com>
wrote:
> On Tue, Dec 17, 2019 at 12:51:27PM +0100, Roman Cervenak wrote:
> >Hey guys,
> >I have reproducer for you.
> >
> >Luckily, the issue is not specific for our data, and can be demonstrated
> >also on OpenStreepMap data. I have imported OSM planet with Imposm, which
> >creates tables with PostGIS geometry columns.
> >I have used roads for linestring testing and landuse for polygon testing,
> >with queries combining several geometric operations (similar to my
> >production workload).
> >
> >The linesting test with 7 concurrent workers increases memory consumption
> >by about 6 GB per hour on my environment.
> >
> >If you don't have OSM database with geometries, I have dumped the roads
> >table (about 25GB download):
> >
> https://sygicpublic.blob.core.windows.net/postgres-bug-data/osm_road.sql.gzip
> >Here is the tool running queries in parallel workers, written in .NET Core
> >(just fill in database connection info in Program.cs, build and run):
> >https://github.com/rouen-sk/PostgisMemoryLeakDemoCore
> >
> >Let me know if I can help further.
> >
>
> I'll try reproducing it, but I don't have any environment to run c#
> stuff. Is it possible to reproduce a single query reproducing the issue,
> with fixed parameters? Or is the randomization necessary?
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 12:41 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 11:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-11 16:49 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tom Lane <tgl@sss.pgh.pa.us>
2019-12-13 10:51 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-13 13:20 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-17 11:51 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-17 12:50 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-17 13:20 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
@ 2019-12-17 18:09 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-18 08:49 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
0 siblings, 1 reply; 16+ messages in thread
From: Tomas Vondra @ 2019-12-17 18:09 UTC (permalink / raw)
To: Roman Cervenak <roman@cervenak.info>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
On Tue, Dec 17, 2019 at 02:20:41PM +0100, Roman Cervenak wrote:
>.NET core is cross platform, you can run it under windows or linux, just
>install .net core runtime.
That's possible, but I have zero experience with .net core, and
unfortunately I have no system with it installed. I've tried running it
under mono I just installed, but quickly ran into issue that I don't
know how to solve. I'm willing to spend time investigating the issue,
but this seems a bit too much ...
>I did not try to run loop with fixed params, I tried to mimic my production
>workload (which is not random of course, but highly variable by location).
>You can certainly try.
>
Can you capture a couple of queries, and share those? I'll run that
under valgrind and see if that produces something interesting.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 12:41 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 11:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-11 16:49 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tom Lane <tgl@sss.pgh.pa.us>
2019-12-13 10:51 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-13 13:20 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-17 11:51 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-17 12:50 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-17 13:20 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-17 18:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2019-12-18 08:49 ` Roman Cervenak <roman@cervenak.info>
2019-12-18 14:55 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 1 reply; 16+ messages in thread
From: Roman Cervenak @ 2019-12-18 08:49 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
Sure, here it is, 10k queries generated by this tool:
https://sygicpublic.blob.core.windows.net/postgres-bug-data/postgis_memory_leak_queries.txt
RC
On Tue, Dec 17, 2019 at 7:10 PM Tomas Vondra <tomas.vondra@2ndquadrant.com>
wrote:
> On Tue, Dec 17, 2019 at 02:20:41PM +0100, Roman Cervenak wrote:
> >.NET core is cross platform, you can run it under windows or linux, just
> >install .net core runtime.
>
> That's possible, but I have zero experience with .net core, and
> unfortunately I have no system with it installed. I've tried running it
> under mono I just installed, but quickly ran into issue that I don't
> know how to solve. I'm willing to spend time investigating the issue,
> but this seems a bit too much ...
>
> >I did not try to run loop with fixed params, I tried to mimic my
> production
> >workload (which is not random of course, but highly variable by location).
> >You can certainly try.
> >
>
> Can you capture a couple of queries, and share those? I'll run that
> under valgrind and see if that produces something interesting.
>
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory leak (possibly connected to postgis) leading to server crash
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 12:41 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 11:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-11 16:49 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tom Lane <tgl@sss.pgh.pa.us>
2019-12-13 10:51 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-13 13:20 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-17 11:51 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-17 12:50 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-17 13:20 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-17 18:09 ` Re: Memory leak (possibly connected to postgis) leading to server crash Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-18 08:49 ` Re: Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
@ 2019-12-18 14:55 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 0 replies; 16+ messages in thread
From: Tomas Vondra @ 2019-12-18 14:55 UTC (permalink / raw)
To: Roman Cervenak <roman@cervenak.info>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
On Wed, Dec 18, 2019 at 09:49:22AM +0100, Roman Cervenak wrote:
>Sure, here it is, 10k queries generated by this tool:
>https://sygicpublic.blob.core.windows.net/postgres-bug-data/postgis_memory_leak_queries.txt
>
Thanks. I've tried running this on current branches (so PostgreSQL 13dev
and PostGIS 3.1.0dev), but that does not seem to be leaking so far. I'll
leave running it for a bit longer and then I'll try running it on 12.1
and 3.0.0 which I think are the versions you reported. I don't have an
Ubuntu box at hand, so I'll have to build it locally, though.
BTW it's customary to reply in-line here, top-posting makes it harder to
follow the discussion etc.
regards
>RC
>
>On Tue, Dec 17, 2019 at 7:10 PM Tomas Vondra <tomas.vondra@2ndquadrant.com>
>wrote:
>
>> On Tue, Dec 17, 2019 at 02:20:41PM +0100, Roman Cervenak wrote:
>> >.NET core is cross platform, you can run it under windows or linux, just
>> >install .net core runtime.
>>
>> That's possible, but I have zero experience with .net core, and
>> unfortunately I have no system with it installed. I've tried running it
>> under mono I just installed, but quickly ran into issue that I don't
>> know how to solve. I'm willing to spend time investigating the issue,
>> but this seems a bit too much ...
>>
>> >I did not try to run loop with fixed params, I tried to mimic my
>> production
>> >workload (which is not random of course, but highly variable by location).
>> >You can certainly try.
>> >
>>
>> Can you capture a couple of queries, and share those? I'll run that
>> under valgrind and see if that produces something interesting.
>>
>>
>> regards
>>
>> --
>> Tomas Vondra http://www.2ndQuadrant.com
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2019-12-18 14:55 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-06 09:22 Memory leak (possibly connected to postgis) leading to server crash Roman Cervenak <roman@cervenak.info>
2019-12-06 11:01 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-06 11:46 ` Roman Cervenak <roman@cervenak.info>
2019-12-06 12:41 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 11:09 ` Roman Cervenak <roman@cervenak.info>
2019-12-11 14:23 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-11 16:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-12-11 17:23 ` Roman Cervenak <roman@cervenak.info>
2019-12-13 10:51 ` Roman Cervenak <roman@cervenak.info>
2019-12-13 13:20 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-17 11:51 ` Roman Cervenak <roman@cervenak.info>
2019-12-17 12:50 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-17 13:20 ` Roman Cervenak <roman@cervenak.info>
2019-12-17 18:09 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-12-18 08:49 ` Roman Cervenak <roman@cervenak.info>
2019-12-18 14:55 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox