Re: [ADMIN] Can schemas be ordered regarding their creation time ?
Amador Alvarez aalva...@d2.com writes: Any idea on doing (COMMENT ON SCHEMA x IS 'y') as 'y' variable? You could use PL/pgSQL's EXECUTE for that: DO $$BEGIN EXECUTE 'COMMENT ON SCHEMA myschema IS ''Created ' || current_timestamp || ; END$$; -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] pg_basebackup blocking all queries with horrible performance
Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. http://wiki.postgresql.org/wiki/Lock_Monitoring I'm finding that I cannot runpg_basebackup at all, or it slows down all SQL queries from running until pg_basebackup has completed (and the load on the box just takes off to over 30.00). By blocks I mean that any query that is submitted just hangs and does not return for seconds or sometimes even minutes until pg_basebackup has stopped. I'm assuming that this isn't expected behavior, so I'm rather confused on what is going on. The command that I'm issuing is: pg_basebackup -v -D /mnt/backups/backups/tmp0 -x -Ft -U postgres Can someone provide some guidance on how to debug this? Or is there some way to reduce the performance/priority of pg_basebackup so that it has much less impact on overall performance? thanks! -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_basebackup blocking all queries with horrible performance
On Thu, Jun 7, 2012 at 10:41 AM, Lonni J Friedman netll...@gmail.com wrote: Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. http://wiki.postgresql.org/wiki/Lock_Monitoring err, i included that URL but neglected to explain why. On a different list someone suggested that I verify that there were no locks that were blocking things, and I did so, and found no locks. So I'm still at a loss why pg_basebackup is killing perf, and would appreciate pointers on how to debug it or at least reduce its impact on performance if that is possible. tahnks -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] could not rename temporary statistics file pg_stat_tmp/pgstat.tmp to pg_stat_tmp/pgstat.stat: No such file or directory
Hi. I have this problem: I have PostgreSQL 9.1.3 and the last night crash it. This was the first error after an autovacuum (the night before last): 2012-06-06 00:59:07 MDT814 4fceffbb.32e LOG: autovacuum: found orphan temp table (null).tmpmuestadistica in database dbRX 2012-06-06 01:05:26 MDT1854 4fc7d1eb.73e LOG: could not rename temporary statistics file pg_stat_tmp/pgstat.tmp to pg_stat_tmp/pgstat.stat: No such file or directory 2012-06-06 01:05:28 MDT1383 4fcf0136.567 ERROR: tuple concurrently updated 2012-06-06 01:05:28 MDT1383 4fcf0136.567 CONTEXT: automatic vacuum of table global.pg_catalog.pg_attrdef 2012-06-06 01:06:09 MDT1851 4fc7d1eb.73b ERROR: xlog flush request 4/E29EE490 is not satisfied --- flushed only to 3/13527A10 2012-06-06 01:06:09 MDT1851 4fc7d1eb.73b CONTEXT: writing block 0 of relation base/311360/12244_vm 2012-06-06 01:06:10 MDT1851 4fc7d1eb.73b ERROR: xlog flush request 4/E29EE490 is not satisfied --- flushed only to 3/13527A10 2012-06-06 01:06:10 MDT1851 4fc7d1eb.73b CONTEXT: writing block 0 of relation base/311360/12244_vm 2012-06-06 01:06:10 MDT1851 4fc7d1eb.73b WARNING: could not write block 0 of base/311360/12244_vm 2012-06-06 01:06:10 MDT1851 4fc7d1eb.73b DETAIL: Multiple failures --- write error might be permanent. Last night it was terminated by signal 6. 2012-06-07 01:36:44 MDT2509 4fd05a0c.9cd LOG: startup process (PID 2525) was terminated by signal 6: Aborted 2012-06-07 01:36:44 MDT2509 4fd05a0c.9cd LOG: aborting startup due to startup process failure 2012-06-07 01:37:37 MDT2680 4fd05a41.a78 LOG: database system shutdown was interrupted; last known up at 2012-06-07 01:29:40 MDT 2012-06-07 01:37:37 MDT2680 4fd05a41.a78 LOG: could not open file pg_xlog/000100030013 (log file 3, segment 19): No such file or directory 2012-06-07 01:37:37 MDT2680 4fd05a41.a78 LOG: invalid primary checkpoint record And the only option was pg_resetxlog. After this a lot of querys showed me this error: 2012-06-07 09:24:22 MDT1306 4fd0c7a6.51a ERROR: missing chunk number 0 for toast value 393330 in pg_toast_2619 2012-06-07 09:24:31 MDT1306 4fd0c7a6.51a ERROR: missing chunk number 0 for toast value 393332 in pg_toast_2619 I lost some databases. I restarted the cluster again with initdb and then I restored the databases that I could backup (for the other I restored an old backup) no space or permissions problem. No filesystem or disk error. Can you help me to know what happened? Thanks and regards...
Re: [ADMIN] Can schemas be ordered regarding their creation time ?
Thanks a lot Hari, very resourceful, you have been very helpful. cheers, A.A. On 06/07/2012 12:47 AM, hari.fu...@gmail.com wrote: Amador Alvarezaalva...@d2.com writes: Any idea on doing (COMMENT ON SCHEMA x IS 'y') as 'y' variable? You could use PL/pgSQL's EXECUTE for that: DO $$BEGIN EXECUTE 'COMMENT ON SCHEMA myschema IS ''Created ' || current_timestamp || ; END$$; -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_basebackup blocking all queries with horrible performance
On Thu, Jun 7, 2012 at 8:04 PM, Lonni J Friedman netll...@gmail.com wrote: On Thu, Jun 7, 2012 at 10:41 AM, Lonni J Friedman netll...@gmail.com wrote: Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. http://wiki.postgresql.org/wiki/Lock_Monitoring err, i included that URL but neglected to explain why. On a different list someone suggested that I verify that there were no locks that were blocking things, and I did so, and found no locks. So I'm still at a loss why pg_basebackup is killing perf, and would appreciate pointers on how to debug it or at least reduce its impact on performance if that is possible. My guess would be that you are overloading your I/O system. You should look at values from iostat and vmstat from when the system works fine and when you run pg_basebackup, that should give you a hint in the right direction. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_basebackup blocking all queries with horrible performance
On Thu, Jun 7, 2012 at 12:40 PM, Magnus Hagander mag...@hagander.net wrote: On Thu, Jun 7, 2012 at 8:04 PM, Lonni J Friedman netll...@gmail.com wrote: On Thu, Jun 7, 2012 at 10:41 AM, Lonni J Friedman netll...@gmail.com wrote: Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. http://wiki.postgresql.org/wiki/Lock_Monitoring err, i included that URL but neglected to explain why. On a different list someone suggested that I verify that there were no locks that were blocking things, and I did so, and found no locks. So I'm still at a loss why pg_basebackup is killing perf, and would appreciate pointers on how to debug it or at least reduce its impact on performance if that is possible. My guess would be that you are overloading your I/O system. You should look at values from iostat and vmstat from when the system works fine and when you run pg_basebackup, that should give you a hint in the right direction. ok, thanks. i'll take a look at that. If this turns out to be the issue, is there some way to get pg_basebackup to run more slowly, so that it has less impact? Or could I do this with ionice on the pg_basebackup process? -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_basebackup blocking all queries with horrible performance
Lonni J Friedman netll...@gmail.com writes: On Thu, Jun 7, 2012 at 12:40 PM, Magnus Hagander mag...@hagander.net wrote: On Thu, Jun 7, 2012 at 8:04 PM, Lonni J Friedman netll...@gmail.com wrote: On Thu, Jun 7, 2012 at 10:41 AM, Lonni J Friedman netll...@gmail.com wrote: Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). Â All of them are running Fedora-16-x86_64. http://wiki.postgresql.org/wiki/Lock_Monitoring err, i included that URL but neglected to explain why. Â On a different list someone suggested that I verify that there were no locks that were blocking things, and I did so, and found no locks. So I'm still at a loss why pg_basebackup is killing perf, and would appreciate pointers on how to debug it or at least reduce its impact on performance if that is possible. My guess would be that you are overloading your I/O system. You should look at values from iostat and vmstat from when the system works fine and when you run pg_basebackup, that should give you a hint in the right direction. ok, thanks. i'll take a look at that. If this turns out to be the issue, is there some way to get pg_basebackup to run more slowly, so that it has less impact? Or could I do this with ionice on the pg_basebackup process? You might try stopping pg_basebackup in place with SIGSTOP and check if problem goes away. SIGCONT and you should start having sluggishness again. If verified, then any sort of throttling mechanism should work. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 732.216.7255 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_basebackup blocking all queries with horrible performance
On Thu, Jun 7, 2012 at 5:07 PM, Jerry Sievers gsiever...@comcast.net wrote: Lonni J Friedman netll...@gmail.com writes: On Thu, Jun 7, 2012 at 12:40 PM, Magnus Hagander mag...@hagander.net wrote: On Thu, Jun 7, 2012 at 8:04 PM, Lonni J Friedman netll...@gmail.com wrote: On Thu, Jun 7, 2012 at 10:41 AM, Lonni J Friedman netll...@gmail.com wrote: Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. http://wiki.postgresql.org/wiki/Lock_Monitoring err, i included that URL but neglected to explain why. On a different list someone suggested that I verify that there were no locks that were blocking things, and I did so, and found no locks. So I'm still at a loss why pg_basebackup is killing perf, and would appreciate pointers on how to debug it or at least reduce its impact on performance if that is possible. My guess would be that you are overloading your I/O system. You should look at values from iostat and vmstat from when the system works fine and when you run pg_basebackup, that should give you a hint in the right direction. ok, thanks. i'll take a look at that. If this turns out to be the issue, is there some way to get pg_basebackup to run more slowly, so that it has less impact? Or could I do this with ionice on the pg_basebackup process? You might try stopping pg_basebackup in place with SIGSTOP and check if problem goes away. SIGCONT and you should start having sluggishness again. If verified, then any sort of throttling mechanism should work. I'm certain that the problem is triggered only when pg_basebackup is running. Its very predictable, and goes away as soon as pg_basebackup finishes running. What do you mean by a throttling mechanism? -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] (new thread) could not rename temporary statistics file pg_stat_tmp/pgstat.tmp to pg_stat_tmp/pgstat.stat: No such file or directory
Hi. I have this problem: I have PostgreSQL 9.1.3 and the last night crash it. This was the first error after an autovacuum (the night before last): 2012-06-06 00:59:07 MDT814 4fceffbb.32e LOG: autovacuum: found orphan temp table (null).tmpmuestadistica in database dbRX 2012-06-06 01:05:26 MDT1854 4fc7d1eb.73e LOG: could not rename temporary statistics file pg_stat_tmp/pgstat.tmp to pg_stat_tmp/pgstat.stat: No such file or directory 2012-06-06 01:05:28 MDT1383 4fcf0136.567 ERROR: tuple concurrently updated 2012-06-06 01:05:28 MDT1383 4fcf0136.567 CONTEXT: automatic vacuum of table global.pg_catalog.pg_attrdef 2012-06-06 01:06:09 MDT1851 4fc7d1eb.73b ERROR: xlog flush request 4/E29EE490 is not satisfied --- flushed only to 3/13527A10 2012-06-06 01:06:09 MDT1851 4fc7d1eb.73b CONTEXT: writing block 0 of relation base/311360/12244_vm 2012-06-06 01:06:10 MDT1851 4fc7d1eb.73b ERROR: xlog flush request 4/E29EE490 is not satisfied --- flushed only to 3/13527A10 2012-06-06 01:06:10 MDT1851 4fc7d1eb.73b CONTEXT: writing block 0 of relation base/311360/12244_vm 2012-06-06 01:06:10 MDT1851 4fc7d1eb.73b WARNING: could not write block 0 of base/311360/12244_vm 2012-06-06 01:06:10 MDT1851 4fc7d1eb.73b DETAIL: Multiple failures --- write error might be permanent. Last night it was terminated by signal 6. 2012-06-07 01:36:44 MDT2509 4fd05a0c.9cd LOG: startup process (PID 2525) was terminated by signal 6: Aborted 2012-06-07 01:36:44 MDT2509 4fd05a0c.9cd LOG: aborting startup due to startup process failure 2012-06-07 01:37:37 MDT2680 4fd05a41.a78 LOG: database system shutdown was interrupted; last known up at 2012-06-07 01:29:40 MDT 2012-06-07 01:37:37 MDT2680 4fd05a41.a78 LOG: could not open file pg_xlog/000100030013 (log file 3, segment 19): No such file or directory 2012-06-07 01:37:37 MDT2680 4fd05a41.a78 LOG: invalid primary checkpoint record And the only option was pg_resetxlog. After this a lot of querys showed me this error: 2012-06-07 09:24:22 MDT 1306 4fd0c7a6.51a ERROR: missing chunk number 0 for toast value 393330 in pg_toast_2619 2012-06-07 09:24:31 MDT 1306 4fd0c7a6.51a ERROR: missing chunk number 0 for toast value 393332 in pg_toast_2619 I lost some databases. I restarted the cluster again with initdb and then I restored the databases that I could backup (for the other I restored an old backup) no space or permissions problem. No filesystem or disk error. Can you help me to know what happened? Did you take a copy of the PostgreSQL data directory and error logs before you tried to fix the problem, as per the advice here: http://wiki.postgresql.org/wiki/Corruption If you did, it might be possible to tell what happened. If you didn't then you've probably destroyed the evidence needed to determine what went wrong (and maybe recover some lost data). -- Craig Ringer -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
