Обсуждение: top for postgresql (ptop?)
Hi everyone, I was playing with converting unixtop (the version of top used in FreeBSD) to only show PostgreSQL processes pulled from the pg_stat_activity table. I have a version that kind of works here: http://pgfoundry.org/frs/download.php/1468/ptop-3.6.1-pre6.tar.gz I've tried it on FreeBSD and Linux, not sure about other platforms though. So it looks a lot like top and can currently do a few simple things like display the current_query from pg_stat_activity for a given process, show the locks held by a process and on which tables, and show the query plan for the current query. It is a ways from polished (not really documented, etc.) but I wanted to see what people thought of a text/curses sort of monitoring tool like this. Maybe something to distribute with PostgreSQL? :) Forgive me if I didn't try out pgtop (the CPAN module.) Regards, Mark
Hi Mark, I haven't yet looked at what you've done, but I'm an enthusiastic supporter of this idea. We're looking to do something that will view running queries and allow drill down into those executing at any given time, showing their plans and some notion of what operators are being executed. The idea of a ptop that shows running queries using a curses interface seems like a great start. Our needs for data warehousing workloads are going to be different from the OLTP users - our queries hang around long enough to warrant a drill-down. How far can you take the drill-down piece? Have you thought about how to acquire the status in the executor yet? One strategy we've considered is to use the same approach as "pstack" on Solaris - it takes a pid and dumps the stack of a backend, which clearly shows which executor node is being worked on currently. I think pstack uses dtrace underneath the hood... - Luke On 9/25/07 3:00 AM, "Mark Wong" <markwkm@gmail.com> wrote: > Hi everyone, > > I was playing with converting unixtop (the version of top used in > FreeBSD) to only show PostgreSQL processes pulled from the > pg_stat_activity table. I have a version that kind of works here: > > http://pgfoundry.org/frs/download.php/1468/ptop-3.6.1-pre6.tar.gz > > I've tried it on FreeBSD and Linux, not sure about other platforms > though. So it looks a lot like top and can currently do a few simple > things like display the current_query from pg_stat_activity for a > given process, show the locks held by a process and on which tables, > and show the query plan for the current query. It is a ways from > polished (not really documented, etc.) but I wanted to see what people > thought of a text/curses sort of monitoring tool like this. Maybe > something to distribute with PostgreSQL? :) > > Forgive me if I didn't try out pgtop (the CPAN module.) > > Regards, > Mark > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
On Tue, 25 Sep 2007, Luke Lonergan wrote: > One strategy we've considered is to use the same approach as "pstack" on > Solaris - it takes a pid and dumps the stack of a backend, which clearly > shows which executor node is being worked on currently. I think pstack > uses dtrace underneath the hood... pstack has been around since the SunOS days, long before dtrace was in Solaris, so it at least used to operate some other way. I know they added some features to Solaris 10 that let pstack look into Java librarites that may leverage dtrace, but I don't believe the internals of the main pstack tool rely on it when looking at regular processes. It's also worth noting that there's a similar Linux utility called gstack. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Mark Wong wrote: > Hi everyone, > > I was playing with converting unixtop (the version of top used in > FreeBSD) to only show PostgreSQL processes pulled from the > pg_stat_activity table. I have a version that kind of works here: > Nice idea. But I got a segfault trying to execute it. My SO is a Slackware 12.0, glibc 2.5, gcc 4.1.2. I didn't have time to look through the code, sorry. euler@harman:~/Desktop/ptop-3.6.1-pre6$ gdb ./ptop GNU gdb 6.6 Copyright (C) 2006 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "i486-slackware-linux"... Using host libthread_db library "/lib/libthread_db.so.1". (gdb) r Starting program: /home/euler/Desktop/ptop-3.6.1-pre6/ptop [Thread debugging using libthread_db enabled] [New Thread -1212918080 (LWP 9600)] Program received signal SIGSEGV, Segmentation fault. [Switching to Thread -1212918080 (LWP 9600)] 0xb7e8e8c0 in __find_specmb () from /lib/libc.so.6 (gdb) bt #0 0xb7e8e8c0 in __find_specmb () from /lib/libc.so.6 #1 0xb7e757ff in vfprintf () from /lib/libc.so.6 #2 0xb7e97d91 in vsnprintf () from /lib/libc.so.6 #3 0x0804cd8c in new_message_v (type=3, msgfmt=0x96c73ac3 <Address 0x96c73ac3 out of bounds>, ap=0xbfbae324 "Èt\a\b\003»¿6]ñ·¼ý·t£ý·H㺿t£ý·\224\003»¿") at display.c:1339 #4 0x0804ced2 in error_message ( msgfmt=0x96c73ac3 <Address 0x96c73ac3 out of bounds>) at display.c:1392 #5 0xb7fc2c79 in ?? () from /usr/lib/libpq.so.5 #6 0x96c73ac3 in ?? () #7 0x080774c8 in ?? () #8 0xbfbb03a0 in ?? () #9 0xb7f15d36 in pthread_mutex_lock () from /lib/libc.so.6 #10 0xb7fc2d49 in ?? () from /usr/lib/libpq.so.5 #11 0x00000001 in ?? () #12 0x00000000 in ?? () (gdb) -- Euler Taveira de Oliveira http://www.timbira.com/
Hi Greg, On 9/25/07 9:15 PM, "Greg Smith" <gsmith@gregsmith.com> wrote: > It's also worth noting that there's a similar Linux utility called gstack. Cool! So - the basic idea is that we could uncover the current run context in a very lightweight manner by just dumping the stack and interpreting it. This is pretty messy on the client side because of the ugly reconstruction, but is very unobtrusive to the running query. An alternative might be to take the plan tree, augment it with stats and store it in a table, maybe augment the backend to catch a certain signal (SIGUSR maybe?) and that would cause an update to the table? That way we'd have the desired feature that the tracking isn't continuous, it's based on a "peek" approach, which is only as obtrusive as needed. - Luke
Mark, Very interesting. I'm looking for such tool. Unfortunately, I can't compile it on my Solaris right now, but I hope it will be shipped with PostgreSQL distribution. Mark Wong wrote: > Hi everyone, > > I was playing with converting unixtop (the version of top used in > FreeBSD) to only show PostgreSQL processes pulled from the > pg_stat_activity table. I have a version that kind of works here: > > http://pgfoundry.org/frs/download.php/1468/ptop-3.6.1-pre6.tar.gz > > I've tried it on FreeBSD and Linux, not sure about other platforms > though. So it looks a lot like top and can currently do a few simple > things like display the current_query from pg_stat_activity for a > given process, show the locks held by a process and on which tables, > and show the query plan for the current query. It is a ways from > polished (not really documented, etc.) but I wanted to see what people > thought of a text/curses sort of monitoring tool like this. Maybe > something to distribute with PostgreSQL? :) > > Forgive me if I didn't try out pgtop (the CPAN module.) > > Regards, > Mark > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- NAGAYASU Satoshi <nagayasus@nttdata.co.jp> Phone: +81-50-5546-2496
Hi All, I am trying to run Regression test on postgreSQL-7.2.8, it got installed successfully, but the regression test is not goingthrough, it is giving the following errors... ==================================================78 of 79 tests passed, 1 failed test(s) ignored. ================================================== The differences that caused some tests to fail can be viewed in the file `./regression.diffs'. A copy of the test summary that you see above is saved in the file `./regression.out'. I am also pasting whatever is listed in the regression.diffs file, it is as follows.... *** ./expected/geometry.out Fri Nov 30 00:27:31 2001 --- ./results/geometry.out Wed Sep 26 16:13:00 2007 *************** *** 150,160 **** six | box -----+---------------------------------------------------------------------------- | (2.12132034355964,2.12132034355964),(-2.12132034355964,-2.12132034355964) ! | (71.7106781186548,72.7106781186548),(-69.7106781186548,-68.7106781186548) ! | (4.53553390593274,6.53553390593274),(-2.53553390593274,-0.535533905932738) ! | (3.12132034355964,4.12132034355964),(-1.12132034355964,-0.121320343559643) | (107.071067811865,207.071067811865),(92.9289321881345,192.928932188135) ! | (170.710678118655,70.7106781186548),(29.2893218813452,-70.7106781186548) (6 rows) -- translation --- 150,160 ---- six | box -----+---------------------------------------------------------------------------- | (2.12132034355964,2.12132034355964),(-2.12132034355964,-2.12132034355964) ! | (71.7106781186547,72.7106781186547),(-69.7106781186547,-68.7106781186547) ! | (4.53553390593274,6.53553390593274),(-2.53553390593274,-0.535533905932737) ! | (3.12132034355964,4.12132034355964),(-1.12132034355964,-0.121320343559642) | (107.071067811865,207.071067811865),(92.9289321881345,192.928932188135) ! | (170.710678118655,70.7106781186547),(29.2893218813453,-70.7106781186547) (6 rows) -- translation ====================================================================== Please tell me what shall I do to resolve this issue. Thanks and Regards Cinu Kuriakose
"Kuriakose, Cinu Cheriyamoozhiyil" <Cinu.Kuriakose@in.unisys.com> writes: > Hi All, > > I am trying to run Regression test on postgreSQL-7.2.8, it got installed > successfully, but the regression test is not going through, it is giving the > following errors... What architecture is this? And why would you be building 7.2.8, an unsupported release over two years and 5 major releases old? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
"Kuriakose, Cinu Cheriyamoozhiyil" <Cinu.Kuriakose@in.unisys.com> writes:
> 78 of 79 tests passed, 1 failed test(s) ignored.
> Please tell me what shall I do to resolve this issue.
Nothing --- the reason it's ignored is it's not significant.
I concur though with Greg's question: why aren't you building something
more modern? 7.2.x was desupported more than two years ago, and contains
many known unfixed (some unfixable) bugs.
regards, tom lane
On 9/25/07, Luke Lonergan <llonergan@greenplum.com> wrote: > Hi Mark, > > I haven't yet looked at what you've done, but I'm an enthusiastic supporter > of this idea. We're looking to do something that will view running queries > and allow drill down into those executing at any given time, showing their > plans and some notion of what operators are being executed. > > The idea of a ptop that shows running queries using a curses interface seems > like a great start. > > Our needs for data warehousing workloads are going to be different from the > OLTP users - our queries hang around long enough to warrant a drill-down. > > How far can you take the drill-down piece? Have you thought about how to > acquire the status in the executor yet? One strategy we've considered is to > use the same approach as "pstack" on Solaris - it takes a pid and dumps the > stack of a backend, which clearly shows which executor node is being worked > on currently. I think pstack uses dtrace underneath the hood... Hi Luke, Thanks. Honestly I haven't thought much past what I've thrown together thus far, so I haven't considered getting status in the executor. First thought would be to use popen() to call pstack and dump the output to the screen. Of course other suggestions are welcome. :) Regards, Mark
On 9/25/07, Euler Taveira de Oliveira <euler@timbira.com> wrote: > Mark Wong wrote: > > Hi everyone, > > > > I was playing with converting unixtop (the version of top used in > > FreeBSD) to only show PostgreSQL processes pulled from the > > pg_stat_activity table. I have a version that kind of works here: > > > > Nice idea. But I got a segfault trying to execute it. My SO is a > Slackware 12.0, glibc 2.5, gcc 4.1.2. I didn't have time to look through > the code, sorry. [snip] Thanks for that stack trace. I'm not too familiar with that part of the unixtop code, but I'll let you know if I can figure it out... Regards, Mark
On 9/25/07, Satoshi Nagayasu <nagayasus@nttdata.co.jp> wrote: > Mark, > > Very interesting. I'm looking for such tool. > > Unfortunately, I can't compile it on my Solaris right now, > but I hope it will be shipped with PostgreSQL distribution. I haven't tried it on Solaris but I'm not surprised. If I can get my hands on a Solaris system I can probably get it to work. :) The code for getting the process information is platform specific and I know I've broken it for all the platforms I haven't tried it on... Regards, Mark
Mark Wong wrote:
> On 9/25/07, Satoshi Nagayasu <nagayasus@nttdata.co.jp> wrote:
>> Mark,
>>
>> Very interesting. I'm looking for such tool.
>>
>> Unfortunately, I can't compile it on my Solaris right now,
>> but I hope it will be shipped with PostgreSQL distribution.
>
> I haven't tried it on Solaris but I'm not surprised. If I can get my
> hands on a Solaris system I can probably get it to work. :) The code
> for getting the process information is platform specific and I know
> I've broken it for all the platforms I haven't tried it on...
Mark,
I tested it on Solaris and I found two problems there. One is with
configure. It does not correctly handled CPPFLAGS. Generated makefile
ignore path to the include files. This line is wrong:
# explicit dependency for the module appropriate to this machine
m_sunos5.o: $(srcdir)/machine/m_sunos5.c$(COMPILE) -o $@ -c $(srcdir)/machine/m_sunos5.c
Second issue is with new interface of get_process_info function.
Currently it has 4 arguments but in m_sunos5.c is defined only with 3
arguments.
Last issue is with -m64 switch. Solaris pg installation does not have
64bit libpg (will be soon) and linker is not able put everything together.
Zdenek
On 9/26/07, Zdenek Kotala <Zdenek.Kotala@sun.com> wrote: > Mark Wong wrote: > > On 9/25/07, Satoshi Nagayasu <nagayasus@nttdata.co.jp> wrote: > >> Mark, > >> > >> Very interesting. I'm looking for such tool. > >> > >> Unfortunately, I can't compile it on my Solaris right now, > >> but I hope it will be shipped with PostgreSQL distribution. > > > > I haven't tried it on Solaris but I'm not surprised. If I can get my > > hands on a Solaris system I can probably get it to work. :) The code > > for getting the process information is platform specific and I know > > I've broken it for all the platforms I haven't tried it on... > > Mark, > > I tested it on Solaris and I found two problems there. One is with > configure. It does not correctly handled CPPFLAGS. Generated makefile > ignore path to the include files. This line is wrong: > > # explicit dependency for the module appropriate to this machine > m_sunos5.o: $(srcdir)/machine/m_sunos5.c > $(COMPILE) -o $@ -c $(srcdir)/machine/m_sunos5.c > > Second issue is with new interface of get_process_info function. > Currently it has 4 arguments but in m_sunos5.c is defined only with 3 > arguments. > > Last issue is with -m64 switch. Solaris pg installation does not have > 64bit libpg (will be soon) and linker is not able put everything together. Hi Zdenek, I don't have a Solaris system to work on. Would you be able to send a patch? I have a mercurial repository, info here: http://ptop.projects.postgresql.org/ I think I've already caught the CPPFLAGS problem too. Regards, Mark