[GENERAL] Innotop for postgresl
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Hello, Is there any software for postgresql like innotop for mysql ? I would like to monitor postgresql with Cacti, but i didn't find any module for that in cacti, do you know a module for that ? ++ Alexis - -- Alexis MICHON CNRS, France IBCP, Institut de Biologie et Chimie des Proteines Mail : alexis.mic...@ibcp.fr Tel : 04.72.72.26.46 Empreinte : 8FDA 1594 2C18 EEDA 681E 8A6D 56EF F0A0 6F06 892A -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.12 (Darwin) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iF4EAREIAAYFAktAiR4ACgkQ1SPVZdGGxBlMIQEAntoGgS53ci4ZGLBmcoYJ4mxe dCH9GqZMV43dQXj/7CoA/AoInQfqk0RCRKJGjcX0wsS4GN8AZ799q0umKQnPD/+P =MPum -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Innotop for postgresl
Is there any software for postgresql like innotop for mysql ? I would like to monitor postgresql with Cacti, but i didn't find any module for that in cacti, do you know a module for that ? You have pgstats from Kenny Gorman. you can download my modified version with some enhacements (cosmetic at all) here: http://desarrollos.siu.edu.ar/trac/postgresql/attachment/wiki/Desarrollos/pgstat -- Emanuel Calvo Franco DBA at: www.siu.edu.ar www.emanuelcalvofranco.com.ar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] set-level update fails with unique constraint violation
# scott.marl...@gmail.com / 2010-01-02 11:23:24 -0700: On Sat, Jan 2, 2010 at 1:40 AM, Roman Neuhauser neuhau...@sigpipe.cz wrote: # da...@fetter.org / 2009-12-31 08:04:58 -0800: On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql@sigpipe.cz wrote: Hello, this fails with duplicate key value: CREATE TABLE x ( i INT NOT NULL UNIQUE ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; are there any plans to make this work? This will work in 8.5: CREATE TABLE x ( i int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; thanks, this might be a bearable workaround in some cases provided there's also SET CONSTRAINTS ... DEFERRED / IMMEDIATE. what I really want is a mode that fires the constraint check at the end of the statement. What advantage would there be to a constraint that fires right after to one that fires at the end of the transaction? What? I didn't say that. I'm saying that I want IMMEDIATE constraint that is atomic with regard to the statement. It's obvious that UPDATE x SET i = i + 1 cannot break a UNIQUE constraint on x.i lest the constraint checking is not atomic. I can see how such non-atomic checking can be good performance-wise, but I'm more interested in logical correctness. -- Roman Neuhauser -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlocks On Demand
On Sun, Jan 3, 2010 at 5:43 AM, David Fetter da...@fetter.org wrote: Folks, I'm working on some SQL intended to expose lock conditions (deadlock, etc.), but to do this, I need to be able to create such conditions at will. Rather than build an entire infrastructure from scratch, I'd like to know if there are any frameworks people are using to create such conditions, and if so, what they are. One possibility I've come up with is to finish up the concurrent psql proposal that's been floating around for some time. http://archives.postgresql.org/pgsql-patches/2007-05/msg00112.php What say? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general I am trying to come up with a probable use case scenario for such implementation. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] set-level update fails with unique constraint violation
On Sun, Jan 03, 2010 at 10:16:10AM +0100, Roman Neuhauser wrote: # scott.marl...@gmail.com / 2010-01-02 11:23:24 -0700: On Sat, Jan 2, 2010 at 1:40 AM, Roman Neuhauser neuhau...@sigpipe.cz wrote: # da...@fetter.org / 2009-12-31 08:04:58 -0800: On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql@sigpipe.cz wrote: Hello, this fails with duplicate key value: CREATE TABLE x ( i INT NOT NULL UNIQUE ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; are there any plans to make this work? This will work in 8.5: CREATE TABLE x ( i int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; thanks, this might be a bearable workaround in some cases provided there's also SET CONSTRAINTS ... DEFERRED / IMMEDIATE. what I really want is a mode that fires the constraint check at the end of the statement. What advantage would there be to a constraint that fires right after to one that fires at the end of the transaction? What? I didn't say that. I'm saying that I want IMMEDIATE constraint that is atomic with regard to the statement. It's obvious that UPDATE x SET i = i + 1 cannot break a UNIQUE constraint on x.i lest the constraint checking is not atomic. I can see how such non-atomic checking can be good performance-wise, but I'm more interested in logical correctness. At least one of us hasn't understood the situation. :) There is a problem in all released versions of PostgreSQL where, when you issue that UPDATE, it is checked at each row. If at any given row, the UDPATE causes a conflict, the statement fails, even though the whole UPDATE would have succeeded if it had completed. The DEFERRED uniqueness constraints in 8.5alpha3 fix this problem. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] WEIRD! postmaster: segfault with sub select??!
Hi everybody, I'm facing a strange problem with a relatively simple sub select whereas everything else runs perfect on this machine (PG 8.4.2 @ Fedora 12, Core2 E4600, 4GB, 2 x 320GB). # SELECT DISTINCT name FROM bbr_parts WHERE id IN (SELECT part_id FROM bbr_v_office_to_parts WHERE office_id=39 AND office_type=9); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. The sub select has no problems and returns 2 rows correctly: # SELECT part_id FROM bbr_v_office_to_parts WHERE office_id=39 AND office_type=9; part_id - 412 394 Replacing the sub select with these hard-coded numbers works as well: SELECT DISTINCT name FROM bbr_parts WHERE id IN (412, 394); -- okay What I tried so far to circle the problem: * REINDEXing all related tables didn't solve the problem (a tip from Tom Lange to a similar problem I've found in another posting). * Dropped RI Constraint. * Decreased shared_buffer down to 1MB (the minimum postmaster starts). * Dropped the complete database and re-created it. Then, the SELECT worked about 10 times until the problem comes back! When nothing else helps, I will downgrade from PostgreSQL 8.4.2 to 8.3.7. Thank you for your comments. --Reto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WEIRD! postmaster: segfault with sub select??!
On 3 Jan 2010, at 9:31, Reto wrote: Hi everybody, I'm facing a strange problem with a relatively simple sub select whereas everything else runs perfect on this machine (PG 8.4.2 @ Fedora 12, Core2 E4600, 4GB, 2 x 320GB). # SELECT DISTINCT name FROM bbr_parts WHERE id IN (SELECT part_id FROM bbr_v_office_to_parts WHERE office_id=39 AND office_type=9); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. Did you verify the problem is indeed a segmentation fault? There are other reasons backend processes terminate, for example the OS doing silly things. I'd check that your hardware is working properly and that you don't have things like memory overcommit (I believe that's what Linux calls it) turned on. When nothing else helps, I will downgrade from PostgreSQL 8.4.2 to 8.3.7. Thank you for your comments. If that helps, then that's a strong pointer to a bug in Postgres or in a stored procedure in your application. There are tons of people running sub-queries on 8.4 though, so I strongly doubt Postgres is to blame here. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b40e6e69954031810539! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WEIRD! postmaster: segfault with sub select??!
On Sun, Jan 3, 2010 at 8:31 AM, Reto primz...@gmail.com wrote: Hi everybody, I'm facing a strange problem with a relatively simple sub select whereas everything else runs perfect on this machine (PG 8.4.2 @ Fedora 12, Core2 E4600, 4GB, 2 x 320GB). # SELECT DISTINCT name FROM bbr_parts WHERE id IN (SELECT part_id FROM bbr_v_office_to_parts WHERE office_id=39 AND office_type=9); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. Look at the postmaster logs for the error which caused the crash. There should be something there even if it seg faulted. Also, can you get a backtrace? Even with your current build if you attach to the backend that you're connected to and get a backtrace that could be useful. (run select pg_backend_pid() and then attach to that pid in gdb, type c and then after the crash run backtrace). The most useful would be to get a coredump from a debugging build but the two things above would be a good start. -- greg -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Curious index selection when using a date range
I encountered a curious thing today. Simple select queries against a fairly large, ~60M rows, and active, both in reading and writing, suddenly were aweful slow, from milliseconds into 10th of seconds. Looking a bit closer revealed that on a date condition having a between 2010-01-01 00:00:00 and 2010-01-31 23:59:59 a simple datetime index was choosen while if the year was switched to 2009 a composed index making use of the other condition parameters as well was choosen. After this we ran vacuum analyze on the table which solved the issue with the composed index getting used for the current year as well. Does anyone of you guys have a good explanation for this behaviour? I'm only guessing about why the planner made this choice. I'm well aware of not supplying enough details about our scenario but I'm disconnected at the moment and I'm asking just out of curiousity (and hope I wont meet the same faith as the cat did). Sincerly, roppert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curious index selection when using a date range
On Sun, Jan 3, 2010 at 1:10 PM, Robert Gravsjö rob...@blogg.se wrote: I encountered a curious thing today. Simple select queries against a fairly large, ~60M rows, and active, both in reading and writing, suddenly were aweful slow, from milliseconds into 10th of seconds. Looking a bit closer revealed that on a date condition having a between 2010-01-01 00:00:00 and 2010-01-31 23:59:59 a simple datetime index was choosen while if the year was switched to 2009 a composed index making use of the other condition parameters as well was choosen. After this we ran vacuum analyze on the table which solved the issue with the composed index getting used for the current year as well. Assuming the analyze part is what fixed this, then the problem is you're analyzing often enough. Got autovac on? What version of pgsql are you running? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curious index selection when using a date range
Scott Marlowe skrev 2010-01-03 22.03: On Sun, Jan 3, 2010 at 1:10 PM, Robert Gravsjörob...@blogg.se wrote: I encountered a curious thing today. Simple select queries against a fairly large, ~60M rows, and active, both in reading and writing, suddenly were aweful slow, from milliseconds into 10th of seconds. Looking a bit closer revealed that on a date condition having a between 2010-01-01 00:00:00 and 2010-01-31 23:59:59 a simple datetime index was choosen while if the year was switched to 2009 a composed index making use of the other condition parameters as well was choosen. After this we ran vacuum analyze on the table which solved the issue with the composed index getting used for the current year as well. Assuming the analyze part is what fixed this, then the problem is you're analyzing often enough. Got autovac on? What version of pgsql are you running? We're using autovaccum and running PostgreSQL 8.4.1, compiled with GCC 4.3.4, on Linux kernel 2.6.31 on x86_64 arch. /r -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curious index selection when using a date range
On Sun, Jan 3, 2010 at 2:16 PM, Robert Gravsjö rob...@blogg.se wrote: Scott Marlowe skrev 2010-01-03 22.03: On Sun, Jan 3, 2010 at 1:10 PM, Robert Gravsjörob...@blogg.se wrote: I encountered a curious thing today. Simple select queries against a fairly large, ~60M rows, and active, both in reading and writing, suddenly were aweful slow, from milliseconds into 10th of seconds. Looking a bit closer revealed that on a date condition having a between 2010-01-01 00:00:00 and 2010-01-31 23:59:59 a simple datetime index was choosen while if the year was switched to 2009 a composed index making use of the other condition parameters as well was choosen. After this we ran vacuum analyze on the table which solved the issue with the composed index getting used for the current year as well. Assuming the analyze part is what fixed this, then the problem is you're analyzing often enough. Got autovac on? What version of pgsql are you running? We're using autovaccum and running PostgreSQL 8.4.1, compiled with GCC 4.3.4, on Linux kernel 2.6.31 on x86_64 arch. You might need to crank up the aggresiveness of auto-analyze, at least on that one table if not for the whole db. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curious index selection when using a date range
Scott Marlowe scott.marl...@gmail.com writes: You might need to crank up the aggresiveness of auto-analyze, at least on that one table if not for the whole db. This might be the same type of scenario Josh was complaining about: http://archives.postgresql.org/pgsql-hackers/2009-12/msg02308.php Inequality conditions near the end of a moving data range may not get estimated very accurately, unless you analyze very often. I'm fooling with a patch for that right now. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Innotop for postgresl
Hi, Is there any software for postgresql like innotop for mysql ? I am not sure. A while ago I saw a demo of pgtop, but I haven't actually used it. I forget exactly who created it, but I think that at least Selena Deckelmann had contributed to it. I would like to monitor postgresql with Cacti, but i didn't find any module for that in cacti, do you know a module for that ? No, but perhaps it would be a good thing to add to http://code.google.com/p/mysql-cacti-templates/ (which despite the name has high-quality templates for quite a few different things). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Innotop for postgresl
Alexis Michon wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Hello, Is there any software for postgresql like innotop for mysql ? I would like to monitor postgresql with Cacti, but i didn't find any module for that in cacti, do you know a module for that ? There is 'ptop' for watching things in progress: http://pgfoundry.org/projects/ptop/ This looks promising for monitoring postgres in cacti: http://forums.cacti.net/about23300.html I don't use cacti myself so not sure how well it works if at all. -- Postgresql php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Innotop for postgresl
Alexis Michon wrote: Is there any software for postgresql like innotop for mysql ? I would like to monitor postgresql with Cacti, but i didn't find any module for that in cacti, do you know a module for that ? See pg_top http://ptop.projects.postgresql.org/ Not sure about Cacti, but some people use Munin and Zabbix successfully. You can probably find scripts/modules by searching around. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] set-level update fails with unique constraint violation
Hi, This seems to work.. UPDATE x set i=i+1 from (select i as m from x order by m desc) y where x.i = y.m Jayadevan From: neuhauser+pgsql-general#postgresql@sigpipe.cz To: pgsql-general@postgresql.org Date: 12/31/2009 09:15 PM Subject:[GENERAL] set-level update fails with unique constraint violation Sent by:pgsql-general-ow...@postgresql.org Hello, this fails with duplicate key value: CREATE TABLE x ( i INT NOT NULL UNIQUE ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; are there any plans to make this work? -- Roman Neuhauser -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general DISCLAIMER: The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect.
