Re: [HACKERS] top for postgresql (ptop?)
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 [EMAIL PROTECTED] Phone: +81-50-5546-2496 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] top for postgresql (ptop?)
On 9/25/07, Luke Lonergan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] top for postgresql (ptop?)
On 9/25/07, Euler Taveira de Oliveira [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] top for postgresql (ptop?)
On 9/25/07, Satoshi Nagayasu [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] top for postgresql (ptop?)
Mark Wong wrote: On 9/25/07, Satoshi Nagayasu [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] top for postgresql (ptop?)
On 9/26/07, Zdenek Kotala [EMAIL PROTECTED] wrote: Mark Wong wrote: On 9/25/07, Satoshi Nagayasu [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] top for postgresql (ptop?)
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] top for postgresql (ptop?)
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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] top for postgresql (ptop?)
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. [EMAIL PROTECTED]:~/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 0x0001 in ?? () #12 0x in ?? () (gdb) -- Euler Taveira de Oliveira http://www.timbira.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] top for postgresql (ptop?)
Hi Greg, On 9/25/07 9:15 PM, Greg Smith [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
