Re: [GENERAL] reindex table deadlock

El vie, 07-11-2014 a las 10:02 -0500, Dan H escribió: > Hi, > > I encountered a deadlock while running 'reindex table TABLE1' in > postgresql version 9.2.4 > The postgresql logs shows the two offending processes. > > 1st process was running reindex table TABLE1 > waiting for AccessExclusiveLock o

Re: [GENERAL] Reindex taking forever, and 99% CPU

Phoenix Kiula wrote: > We spent some time to do some massive cleaning of the data from > this table. Brought it down to around 630 million rows. Overall > size of the table including indexes is about 120GB anyway. Deleting rows that you don't need is good, and once a vacuum has a chance to run (

Re: [GENERAL] Reindex taking forever, and 99% CPU

Thank you for the very specific idea of pg_stat_user. This is what I see (the output is also included in email below, but this is easier to read) -- https://gist.github.com/anonymous/53f748a8c6c454b804b3 The output here (might become a jumbled mess)-- =# SELECT * from pg_stat_user_tables where

Re: [GENERAL] Reindex taking forever, and 99% CPU

On Sun, Aug 3, 2014 at 3:20 AM, Phoenix Kiula wrote: > Hi. I've been patient. PG is 9.0.17, updated via Yum yesterday. > > One of my large tables (101 GB on disk, about 1.1 billion rows) used > to take too long to vacuum. Not sure if it's an index corruption > issue. But I tried VACUUM FULL ANALY

Re: [GENERAL] Reindex taking forever, and 99% CPU

On 08/03/2014 08:55 PM, Jeff Janes wrote: Does RAID 1 mean you only have 2 disks in your RAID? If so, that is woefully inadequate to your apparent workload. The amount of RAM doesn't inspire confidence, either. Phoenix, I agree that this is probably the core of the problem you're having. a 1

Re: [GENERAL] Reindex taking forever, and 99% CPU

On Saturday, August 2, 2014, Phoenix Kiula wrote: > Hi. I've been patient. PG is 9.0.17, updated via Yum yesterday. > > One of my large tables (101 GB on disk, about 1.1 billion rows) used > to take too long to vacuum. Too long for what? Rome wasn't build in a day, it might not get vacuumed in

Re: [GENERAL] Reindex taking forever, and 99% CPU

On 08/02/2014 07:37 PM, Phoenix Kiula wrote: In your original post you said it was stopping on pg_class so now I am confused. No need to be confused. The vacuum thing is a bit tricky for laymen like myself. The "pg_class" seemed to be associated to this table. Anyway, even before the upgrade,

Re: [GENERAL] Reindex taking forever, and 99% CPU

On 08/02/2014 07:37 PM, Phoenix Kiula wrote: In your original post you said it was stopping on pg_class so now I am confused. No need to be confused. The vacuum thing is a bit tricky for laymen like myself. The "pg_class" seemed to be associated to this table. Anyway, even before the upgrade,

Re: [GENERAL] Reindex taking forever, and 99% CPU

> In your original post you said it was stopping on pg_class so now I am > confused. No need to be confused. The vacuum thing is a bit tricky for laymen like myself. The "pg_class" seemed to be associated to this table. Anyway, even before the upgrade, the vacuum was stopping at this table and t

Re: [GENERAL] Reindex taking forever, and 99% CPU

On 08/02/2014 07:02 PM, Phoenix Kiula wrote: Thanks John. So what're the right settings? Anyway, right now Postgresql is servicing only one main connection, which is the REINDEX. All other stuff is switched off, no one else is connecting to the DB. My issue with this table was the vaccum proces

Re: [GENERAL] Reindex taking forever, and 99% CPU

Thanks John. So what're the right settings? Anyway, right now Postgresql is servicing only one main connection, which is the REINDEX. All other stuff is switched off, no one else is connecting to the DB. My issue with this table was the vaccum process would stop at this table, and take hours. So

Re: [GENERAL] Reindex taking forever, and 99% CPU

On 8/2/2014 6:20 PM, Phoenix Kiula wrote: PS: CentOS 6 64 bit, 4 GB of RAM, Raid 1 Raptor disks. Postgresql.conf and TOP output during the running of the REINDEX are below.. POSTGRESQL.CONF- max_connections = 180 superuser_reserved_connections = 5 shared_buffers

Re: [GENERAL] Reindex taking forever, and 99% CPU

On 08/02/2014 06:20 PM, Phoenix Kiula wrote: Hi. I've been patient. PG is 9.0.17, updated via Yum yesterday. One of my large tables (101 GB on disk, about 1.1 billion rows) used to take too long to vacuum. Not sure if it's an index corruption issue. But I tried VACUUM FULL ANALYZE as recommended

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

On 2/10/2013 10:25 PM, Anoop K wrote: Yes, we do that. well, you need to figure out which connection isn't doing that, as one of them is leaving a long running transaction pending. as I said, join pg_stat_activity.pid with pg_locks and whatever to find out what tables its locking on. try

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

On Sun, Feb 10, 2013 at 9:55 PM, Anoop K wrote: > We analyzed the application side. It doesn't seem to be create a transaction > and keep it open. StackTraces indicate that it is BLOCKED in JDBC > openConnection. > > Any JDBC driver issue or other scenarios which can result in transaction> ? The

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

Yes, we do that. On Mon, Feb 11, 2013 at 11:53 AM, John R Pierce wrote: > On 2/10/2013 9:55 PM, Anoop K wrote: > > We analyzed the application side. It doesn't seem to be create a > transaction and keep it open. StackTraces indicate that it is BLOCKED in > JDBC openConnection. > > Any JDBC dri

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

On 2/10/2013 9:55 PM, Anoop K wrote: We analyzed the application side. It doesn't seem to be create a transaction and keep it open. StackTraces indicate that it is BLOCKED in JDBC openConnection. Any JDBC driver issue or other scenarios which can result in <*idle in transaction*> ? JDBC has

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

We analyzed the application side. It doesn't seem to be create a transaction and keep it open. StackTraces indicate that it is BLOCKED in JDBC openConnection. Any JDBC driver issue or other scenarios which can result in <*idle in transaction*> ? Anoop On Mon, Feb 11, 2013 at 11:16 AM, Sergey Kon

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

On Wed, Feb 6, 2013 at 1:28 AM, Anoop K wrote: > We are hitting a situation where REINDEX is resulting in postgresql to go to > dead lock state for ever. On debugging the issue we found that > 3 connections are going in to some dead lock state. > > idle in transaction > REINDEX waiting > SELECT wa

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

On 2/6/2013 1:28 AM, Anoop K wrote: 3 connections are going in to some dead lock state. 1. *idle in transaction * 2. *REINDEX waiting * 3. *SELECT waiting * you need to track down what resources are being locked by those processes, by joining pg_stat_activity against pg_locks and (bee

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

You might want to consider adding a pooler like pgbouncer to the equation so that the pooler is what runs out of connections and not the database. Then you could at least get into it to fix things. On Thu, Feb 7, 2013 at 9:04 PM, Anoop K wrote: > REINDEX was for the whole database. It seems REIN

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

REINDEX was for the whole database. It seems REINDEX was blocked by the <*idle in transaction*> process. What we are not able to explain is how that connection went in to <*idle in transaction*> state. The app stacktrace confirms that app (JDBC) is trying to open a connection. We do close connecti

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

Pavan Deolasee writes: > Sorry, I was going to ask what REINDEX was really indexing ? System > tables ? The stack trace for the REINDEX process includes ReindexDatabase(), so if it was running as a superuser it would be trying to reindex system catalogs too. We don't actually know that the parti

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

On Thu, Feb 7, 2013 at 8:19 PM, Anoop K wrote: > In an attempt to get access, I ended up killing a postgres process and the > whole thing recovered from hang state. Now don't have more data points to > debug. > Sorry, I was going to ask what REINDEX was really indexing ? System tables ? ISTM that

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

In an attempt to get access, I ended up killing a postgres process and the whole thing *recovered from hang* state. Now don't have more data points to debug. I feel the trigger is the connection in <*idle in transaction>* state. On examining the application side(Java) stacktrace, I found that othe

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

Anoop K wrote: >I will try. Here are the gdb stacktraces of hung processes. > > > Have you tried `kill -SIGTERM` on the "idle in transaction" pid? -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/ma

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

On Wed, Feb 6, 2013 at 11:55 PM, Anoop K wrote: > We are hitting a situation where REINDEX is resulting in postgresql to go to > dead lock state for ever. On debugging the issue we found that > 3 connections are going in to some dead lock state. > > idle in transaction > REINDEX waiting > SELECT w

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

I will try. Here are the gdb stacktraces of hung processes. #0 0x7fbdfaceb3e2 in recv () from /lib64/libc.so.6 #1 0x0058bde6 in secure_read () #2 0x0059697b in ?? () #3 0x00596d7b in pq_getbyte () #4 0x006334af in PostgresMain () #5 0x005f4d69 in

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

Note that if those processes use persistent connections you'll need to restart them to free up the connections. In the meantime you can use the step of renaming your superuser account. Then cutting all superuser conns and turning off superuser of postgres user temporarily. I wouldn't do this all

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

Processes should always connect by some other role with suspendable superuser connections for situations like this. Do your processes really need superuser access all the time? If you could turn it off for a bit you could get into your database and troubleshoot from there first. Not being able t

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

Actually some of our processes connect as superuser. So even that is over and is in hung state. On Thu, Feb 7, 2013 at 4:29 PM, Scott Marlowe wrote: > So have you tried connecting as a superuser? > > On Thu, Feb 7, 2013 at 3:19 AM, Anoop K wrote: > > We did run out of conns as our processes whic

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

So have you tried connecting as a superuser? On Thu, Feb 7, 2013 at 3:19 AM, Anoop K wrote: > We did run out of conns as our processes which tried to connect (over few > days) got hung in 'startup waiting state'. Even superuser conns are also > over. > > Thanks > Anoop > > > On Thu, Feb 7, 2013 a

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

I dont have C triggers. I can attach gdb and get stacktrace. Wondering if it will take the processes out of problem state. Thanks Anoop On Thu, Feb 7, 2013 at 3:33 PM, Pavan Deolasee wrote: > On Thu, Feb 7, 2013 at 2:08 PM, Anoop K wrote: > > I have the setup in problem state. But I am not able

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

We did run out of conns as our processes which tried to connect (over few days) got hung in '*startup waiting state'. *Even superuser conns are also over. Thanks Anoop On Thu, Feb 7, 2013 at 3:37 PM, Scott Marlowe wrote: > It sounds like you're running out of connections. Have you tried > conne

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

It sounds like you're running out of connections. Have you tried connecting as postgres? It has 2 or 3 superuser connections reserved by default. On Thu, Feb 7, 2013 at 1:38 AM, Anoop K wrote: > I have the setup in problem state. But I am not able to make psql > connections to view the lock det

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

On Thu, Feb 7, 2013 at 2:08 PM, Anoop K wrote: > I have the setup in problem state. But I am not able to make psql > connections to view the lock details. > psql connections are hanging. Is there any other info which can be collected > in this state ? > Try attaching each process involved in the

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

I have the setup in problem state. But I am not able to make psql connections to view the lock details. psql connections are hanging. Is there any other info which can be collected in this state ? Also we don't know the steps to reproduce the issue. On Thu, Feb 7, 2013 at 1:23 PM, Albe Laurenz w

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

Anoop K wrote: > We are hitting a situation where REINDEX is resulting in postgresql to go to > dead lock state for ever. > On debugging the issue we found that > 3 connections are going in to some dead lock state. > > 1.idle in transaction > 2.REINDEX waiting > 3.SELECT waiting > >

Re: [GENERAL] REINDEX and COPY is wainting since Jun 21!

El 27/07/2012 12:26 p.m., Alan Hodgson escribió: On Friday, July 27, 2012 11:59:49 AM Ing.Edmundo.Robles.Lopez wrote: Hi, i have postgres 8.3 running in SCO Openserver 5.0.7 today, i had problems to start psql, the error mesage was: 'FATAL Memory out, Detail: Failed on resqueted size ...' ,

Re: [GENERAL] REINDEX and COPY is wainting since Jun 21!

thanks a lot for answer. El 27/07/2012 12:26 p.m., Alan Hodgson escribió: On Friday, July 27, 2012 11:59:49 AM Ing.Edmundo.Robles.Lopez wrote: Hi, i have postgres 8.3 running in SCO Openserver 5.0.7 today, i had problems to start psql, the error mesage was: 'FATAL Memory out, Detail: Failed

Re: [GENERAL] REINDEX and COPY is wainting since Jun 21!

On Friday, July 27, 2012 11:59:49 AM Ing.Edmundo.Robles.Lopez wrote: > Hi, i have postgres 8.3 running in SCO Openserver 5.0.7 > > today, i had problems to start psql, the error mesage was: 'FATAL > Memory out, Detail: Failed on resqueted size ...' , and after i checked > the process i notic

Re: [GENERAL] REINDEX requirement?

...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Robert Treat Sent: Mittwoch, 10. November 2010 22:40 To: Igor Neyman Cc: AI Rumman; pgsql-general General Subject: Re: [GENERAL] REINDEX requirement? On Tue, Nov 9, 2010 at 1:51 PM, Igor Neyman wrote

Re: [GENERAL] REINDEX requirement?

On Tue, Nov 9, 2010 at 1:51 PM, Igor Neyman wrote: > > -Original Message- > > From: AI Rumman [mailto:rumman...@gmail.com] > > Sent: Tuesday, November 09, 2010 3:26 AM > > To: pgsql-general General > > Subject: REINDEX requirement? > > > > How do I know that index require REINDEX? > > > >

Re: [GENERAL] REINDEX requirement?

> -Original Message- > From: AI Rumman [mailto:rumman...@gmail.com] > Sent: Tuesday, November 09, 2010 3:26 AM > To: pgsql-general General > Subject: REINDEX requirement? > > How do I know that index require REINDEX? > > Look at the results of pgstatindex(...) function for specific

Re: [GENERAL] REINDEX requirement?

On Tue, Nov 9, 2010 at 4:26 AM, AI Rumman wrote: > How do I know that index require REINDEX? Well, the REINDEX page: gives a few examples of why you might need to reindex. I think the most common reason would probably be due to i

Re: [GENERAL] reindex

Sim Zacks writes: > I have an aggregate table which is constantly being overwritten. Every > 10 minutes or so, the table is erased and populated with new data, most > of which is the same. > Basically a materialized view. > I have been going through some queries that use this table and noticed >

Re: [GENERAL] reindex

use truncate instead of delete ? -- 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] REINDEX "is not a btree"

On Jul 10, 2009, at 6:47 AM, Vanessa Lopez wrote: I discovered the table that was causing the error, delete it and create it again (I miss some data but at least everything else is working now) Yes, for the backup we copy everything we had under /data (the directory containing "base", "glo

Re: [GENERAL] REINDEX "is not a btree"

On Friday 10 July 2009, Vanessa Lopez wrote: > What do you mean by we can't simply take a filesystem copy of a > running database? :-O ... How should we then do the backups (so next > time I will not have the same problem again) ? There is extensive documentation on how to do backups. For filesys

Re: [GENERAL] REINDEX "is not a btree"

Vanessa Lopez writes: > What do you mean by we can't simply take a filesystem copy of a > running database? :-O ... How should we then do the backups (so next > time I will not have the same problem again) ? Read the fine manual ... http://www.postgresql.org/docs/8.3/static/backup.html Sectio

Re: [GENERAL] REINDEX "is not a btree"

Hello, Thanks for all your answers! I discovered the table that was causing the error, delete it and create it again (I miss some data but at least everything else is working now) Yes, for the backup we copy everything we had under /data (the directory containing "base", "global", and so

Re: [GENERAL] REINDEX "is not a btree"

On Jul 4, 2009, at 8:06 AM, Craig Ringer wrote: On Fri, 2009-07-03 at 15:00 +0100, Vanessa Lopez wrote: I don't know much about postgre, I have no clue what else I can do. Please, please any help is very very much appreciated I have lots of databases and months of work in postgre (also lots of b

Re: [GENERAL] REINDEX "is not a btree"

On Fri, 2009-07-03 at 15:00 +0100, Vanessa Lopez wrote: > I don't know much about postgre, I have no clue what else I can do. > Please, please any help is very very much appreciated I have lots of > databases and months of work in postgre (also lots of backups for the > data in /data) When you s

Re: [GENERAL] Reindex does not finish 8.2.6

2008/3/26, Alvaro Herrera <[EMAIL PROTECTED]>: > Clodoaldo escribió: > > > > The database performance slowly degrades and after two weeks i issue a > > reindex on the database and the performance gets back to normal. I > > have been doing this for ages and i don't remember in which version > >

Re: [GENERAL] Reindex does not finish 8.2.6

Clodoaldo escribió: > The database performance slowly degrades and after two weeks i issue a > reindex on the database and the performance gets back to normal. I > have been doing this for ages and i don't remember in which version > this degradation behavior appeared, perhaps 8.0 I'm not sure. W

Re: [GENERAL] Reindex does not finish 8.2.6

2008/3/13, Alvaro Herrera <[EMAIL PROTECTED]>: > Clodoaldo escribió: > > > 2008/3/13, Alvaro Herrera <[EMAIL PROTECTED]>: > > > Clodoaldo escribió: > > > > > > > > > > Now what is happening is that reindex does not finish even with a > > > > small 6,500 rows table and after a reboot. In top

Re: [GENERAL] Reindex does not finish 8.2.6

2008/3/14, Alvaro Herrera <[EMAIL PROTECTED]>: > Clodoaldo escribió: > > > 2008/3/14, Alvaro Herrera <[EMAIL PROTECTED]>: > > > > > A quick look into pg_locks should tell you if it's blocking. > > > > pg_prepared_xacts is empty and pg_locks has 288 rows: > > > > # select locktype, mode, count(

Re: [GENERAL] Reindex does not finish 8.2.6

Clodoaldo escribió: > 2008/3/14, Alvaro Herrera <[EMAIL PROTECTED]>: > > A quick look into pg_locks should tell you if it's blocking. > > pg_prepared_xacts is empty and pg_locks has 288 rows: > > # select locktype, mode, count(*) as total > from pg_locks group by locktype, mode; >locktype

Re: [GENERAL] Reindex does not finish 8.2.6

2008/3/14, Alvaro Herrera <[EMAIL PROTECTED]>: > Clodoaldo escribió: > > > > Postgresql was restarted twice, but yes, it is as if the crash left > > some kind of permanent lock somewhere. > > > A prepared transaction perhaps? SELECT * FROM pg_prepared_xacts; > > A quick look into pg_locks shoul

Re: [GENERAL] Reindex does not finish 8.2.6

Clodoaldo escribió: > Postgresql was restarted twice, but yes, it is as if the crash left > some kind of permanent lock somewhere. A prepared transaction perhaps? SELECT * FROM pg_prepared_xacts; A quick look into pg_locks should tell you if it's blocking. -- Alvaro Herrera

Re: [GENERAL] Reindex does not finish 8.2.6

2008/3/14, Pavan Deolasee <[EMAIL PROTECTED]>: > On Fri, Mar 14, 2008 at 5:19 AM, Clodoaldo > > <[EMAIL PROTECTED]> wrote: > > > > > > > Try vacuuming pg_class, pg_index, pg_attribute manually and see if that > > > makes the problem go away. > > > > It does not go away. > > > > > Can it b

Re: [GENERAL] Reindex does not finish 8.2.6

2008/3/14, Scott Marlowe <[EMAIL PROTECTED]>: > On Thu, Mar 13, 2008 at 5:49 PM, Clodoaldo > <[EMAIL PROTECTED]> wrote: > > 2008/3/13, Alvaro Herrera <[EMAIL PROTECTED]>: > > > Clodoaldo escribió: > > > > > > > 2008/3/13, Alvaro Herrera <[EMAIL PROTECTED]>: > > > > > Clodoaldo escribió:

Re: [GENERAL] Reindex does not finish 8.2.6

On Fri, Mar 14, 2008 at 5:19 AM, Clodoaldo <[EMAIL PROTECTED]> wrote: > > > Try vacuuming pg_class, pg_index, pg_attribute manually and see if that > > makes the problem go away. > > It does not go away. > Can it be a case where some other open transaction is holding a lock on the table ? No

Re: [GENERAL] Reindex does not finish 8.2.6

On Thu, Mar 13, 2008 at 5:49 PM, Clodoaldo <[EMAIL PROTECTED]> wrote: > 2008/3/13, Alvaro Herrera <[EMAIL PROTECTED]>: > > Clodoaldo escribió: > > > > > 2008/3/13, Alvaro Herrera <[EMAIL PROTECTED]>: > > > > Clodoaldo escribió: > > > > > > > > > > > > > Now what is happening is that rei

Re: [GENERAL] Reindex does not finish 8.2.6

2008/3/13, Alvaro Herrera <[EMAIL PROTECTED]>: > Clodoaldo escribió: > > > 2008/3/13, Alvaro Herrera <[EMAIL PROTECTED]>: > > > Clodoaldo escribió: > > > > > > > > > > Now what is happening is that reindex does not finish even with a > > > > small 6,500 rows table and after a reboot. In top

Re: [GENERAL] Reindex does not finish 8.2.6

2008/3/13, Greg Smith <[EMAIL PROTECTED]>: > On Thu, 13 Mar 2008, Clodoaldo wrote: > > > I recently had a crash during a bulk insert when i updated to the > > 2.6.24.3-12.fc8 kernel. I rebooted and Postgresql did its thing to > > recover and everything was working. I made the previous kernel, >

Re: [GENERAL] Reindex does not finish 8.2.6

Clodoaldo escribió: > 2008/3/13, Alvaro Herrera <[EMAIL PROTECTED]>: > > Clodoaldo escribió: > > > > > > > Now what is happening is that reindex does not finish even with a > > > small 6,500 rows table and after a reboot. In top there is no CPU or > > > memory usage by postmaster and vmstat show

Re: [GENERAL] Reindex does not finish 8.2.6

2008/3/13, Alvaro Herrera <[EMAIL PROTECTED]>: > Clodoaldo escribió: > > > > Now what is happening is that reindex does not finish even with a > > small 6,500 rows table and after a reboot. In top there is no CPU or > > memory usage by postmaster and vmstat shows no disk activity. > > > Hmm, are

Re: [GENERAL] Reindex does not finish 8.2.6

Clodoaldo escribió: > Now what is happening is that reindex does not finish even with a > small 6,500 rows table and after a reboot. In top there is no CPU or > memory usage by postmaster and vmstat shows no disk activity. Hmm, are you vacuuming the system catalogs appropriately? -- Alvaro Herr

Re: [GENERAL] Reindex does not finish 8.2.6

On Thu, 13 Mar 2008, Clodoaldo wrote: I recently had a crash during a bulk insert when i updated to the 2.6.24.3-12.fc8 kernel. I rebooted and Postgresql did its thing to recover and everything was working. I made the previous kernel, 2.6.23.15-137.fc8, the default in grub.conf and rebooted. H

Re: [GENERAL] REINDEX on large DB vs. DROP INDEX/CREATE INDEX

Wes <[EMAIL PROTECTED]> writes: > The reason for the huge change in the vacuum time is that the indexes are > scanned in index order instead of disk order. I understand that is fixed in > 8.2 or 8.3 (don't recall which I saw it in), but have never gotten > confirmation from anyone on that. Yeah,

Re: [GENERAL] REINDEX on large DB vs. DROP INDEX/CREATE INDEX

On 2/4/08 9:53 AM, "Vivek Khera" <[EMAIL PROTECTED]> wrote: > what you need to do is compare the relpages from the pg_class table > for that index before and after. > > if you didn't get much disk space back, make sure you have no long > running transactions that may have kept some older files op

Re: [GENERAL] REINDEX on large DB vs. DROP INDEX/CREATE INDEX

On Feb 4, 2008, at 10:00 AM, Wes wrote: Just a follow-up on this... The REINDEX took about 2 1/2 days. I didn't gain much disk space back - a full backup takes just as long as before, but the vacuum time dropped from 30 hours to 3 hours. what you need to do is compare the relpages from

Re: [GENERAL] REINDEX on large DB vs. DROP INDEX/CREATE INDEX

Just a follow-up on this... The REINDEX took about 2 1/2 days. I didn't gain much disk space back - a full backup takes just as long as before, but the vacuum time dropped from 30 hours to 3 hours. Wes >> 1. Is there any advantage to doing the DROP/CREATE over just doing a REINDEX >> DATABASE.

Re: [GENERAL] REINDEX on large DB vs. DROP INDEX/CREATE INDEX

On 1/25/08 5:40 AM, "Gregory Stark" <[EMAIL PROTECTED]> wrote: > It shouldn't make a big difference. fsync only happens at the end of a > transaction or at a checkpoint. > > Since you're concerned with very long operations the slowdown at the end of > the transaction won't make a big difference.

Re: [GENERAL] REINDEX on large DB vs. DROP INDEX/CREATE INDEX

"Wes" <[EMAIL PROTECTED]> writes: > I guess I should also turn off fsync for the duration. It shouldn't make a big difference. fsync only happens at the end of a transaction or at a checkpoint. Since you're concerned with very long operations the slowdown at the end of the transaction won't mak

Re: [GENERAL] REINDEX on large DB vs. DROP INDEX/CREATE INDEX

On 1/24/08 12:48 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote: > Wes <[EMAIL PROTECTED]> writes: >> I'm running 8.1.4. Assume I have exclusive access to the DB. > > You really ought to update to 8.1.something-newer, but I digress. I was planning on upgrading to 8.x at the same time as this reindex

Re: [GENERAL] REINDEX on large DB vs. DROP INDEX/CREATE INDEX

Wes <[EMAIL PROTECTED]> writes: > I'm running 8.1.4. Assume I have exclusive access to the DB. You really ought to update to 8.1.something-newer, but I digress. > 1. Is there any advantage to doing the DROP/CREATE over just doing a REINDEX > DATABASE. No, not if you don't mind exclusive locks.

Re: [GENERAL] REINDEX problem

Ulrich Wisser <[EMAIL PROTECTED]> writes: > -bash-2.05b$ postgres -D /var/lib/pgsql/data/ -O -P CLIX1 > ... > backend> REINDEX DATABASE CLIX1 > ERROR: REINDEX DATABASE: Can be executed only on the currently open > database. The database name is evidently all upper case, so you need double quotes

Re: [GENERAL] REINDEX slow?

Edmund Dengler <[EMAIL PROTECTED]> writes: > Alternatively, if I created a second index, and then dropped the first, > would this be faster (though I would suppose that an ANALYZE would need to > be done to recognize the utility of the new index, thereby negating any > speed improvements)? Curre