pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Table UPDATE is too slow
16+ messages / 9 participants
[nested] [flat]

* Table UPDATE is too slow
@ 2004-08-31 18:11 Ron St-Pierre <rstpierre@syscor.com>
  2004-08-31 18:16 ` Re: Table UPDATE is too slow Thomas F.O'Connell <tfo@sitening.com>
  2004-08-31 18:18 ` Re: Table UPDATE is too slow Steinar H. Gunderson <sgunderson@bigfoot.com>
  2004-08-31 18:46 ` Re: Table UPDATE is too slow Frank Wiles <frank@wiles.org>
  2004-09-01 00:18 ` Re: Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
  2004-09-01 05:32 ` Re: Table UPDATE is too slow Tom Lane <tgl@sss.pgh.pa.us>
  2004-09-05 23:28 ` Re: Table UPDATE is too slow Marinos J. Yannikos <mjy@geizhals.at>
  2004-09-07 16:20 ` Re: [PERFORM] Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
  0 siblings, 7 replies; 16+ messages in thread

From: Ron St-Pierre @ 2004-08-31 18:11 UTC (permalink / raw)
  To: pgsql-performance

 We have a web based application with data that is updated daily. The 
biggest bottleneck occurs when we try to update
 one of the tables. This table contains 58,000 rows and 62 columns, and 
EVERY column is indexed. Every column is
 queryable (?) by the users through the web interface so we are 
reluctant to remove the indexes (recreating them would
 be time consuming too). The primary key is an INT and the rest of the 
columns are a mix of NUMERIC, TEXT, and DATEs.
 A typical update is:
   UPDATE dataTable SET field01=44.5, field02=44.5, field03='Bob',
     field04='foo', ... , field60='2004-08-30', field61='2004-08-29'
     WHERE id = 1234;

 Also of note is that the update is run about 10 times per day; we get 
blocks of data from 10 different sources, so we pre-process the
 data and then update the table. We also run VACUUM FULL ANALYZE on a 
nightly basis.
 
 Does anyone have some idea on how we can increase speed, either by 
changing the updates, designing the database
 differently, etc, etc? This is currently a big problem for us.
 
 Other notables:
   The UPDATE is run from a within a function: FOR rec IN SELECT ...LOOP 
RETURN NEXT rec; UPDATE dataTable.....
   Postgres 7.4.3
   debian stable
   2 GB RAM
   80 DB IDE drive (we can't change it)
 
   shared_buffers = 2048
   sort_mem = 1024  
   max_fsm_pages = 40000
   checkpoint_segments = 5
   random_page_cost = 3
 
 
 Thanks
 
 Ron





^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
@ 2004-08-31 18:16 ` Thomas F.O'Connell <tfo@sitening.com>
  2004-08-31 18:23   ` Re: Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
  6 siblings, 1 reply; 16+ messages in thread

From: Thomas F.O'Connell @ 2004-08-31 18:16 UTC (permalink / raw)
  To: Ron St-Pierre <rstpierre@syscor.com>; +Cc: pgsql-performance

What is the datatype of the id column?

-tfo

On Aug 31, 2004, at 1:11 PM, Ron St-Pierre wrote:

> We have a web based application with data that is updated daily. The 
> biggest bottleneck occurs when we try to update
> one of the tables. This table contains 58,000 rows and 62 columns, and 
> EVERY column is indexed. Every column is
> queryable (?) by the users through the web interface so we are 
> reluctant to remove the indexes (recreating them would
> be time consuming too). The primary key is an INT and the rest of the 
> columns are a mix of NUMERIC, TEXT, and DATEs.
> A typical update is:
>   UPDATE dataTable SET field01=44.5, field02=44.5, field03='Bob',
>     field04='foo', ... , field60='2004-08-30', field61='2004-08-29'
>     WHERE id = 1234;
>
> Also of note is that the update is run about 10 times per day; we get 
> blocks of data from 10 different sources, so we pre-process the
> data and then update the table. We also run VACUUM FULL ANALYZE on a 
> nightly basis.
> Does anyone have some idea on how we can increase speed, either by 
> changing the updates, designing the database
> differently, etc, etc? This is currently a big problem for us.
> Other notables:
>   The UPDATE is run from a within a function: FOR rec IN SELECT 
> ...LOOP RETURN NEXT rec; UPDATE dataTable.....
>   Postgres 7.4.3
>   debian stable
>   2 GB RAM
>   80 DB IDE drive (we can't change it)
>   shared_buffers = 2048
>   sort_mem = 1024    max_fsm_pages = 40000
>   checkpoint_segments = 5
>   random_page_cost = 3
> Thanks
> Ron




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
  2004-08-31 18:16 ` Re: Table UPDATE is too slow Thomas F.O'Connell <tfo@sitening.com>
@ 2004-08-31 18:23   ` Ron St-Pierre <rstpierre@syscor.com>
  0 siblings, 0 replies; 16+ messages in thread

From: Ron St-Pierre @ 2004-08-31 18:23 UTC (permalink / raw)
  To: Thomas F. O'Connell <tfo@sitening.com>; pgsql-performance

Thomas F. O'Connell wrote:

> What is the datatype of the id column?
>
The id column is INTEGER.

Ron




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
@ 2004-08-31 18:18 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
  2004-08-31 18:35   ` Re: Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
  6 siblings, 1 reply; 16+ messages in thread

From: Steinar H. Gunderson @ 2004-08-31 18:18 UTC (permalink / raw)
  To: pgsql-performance

On Tue, Aug 31, 2004 at 11:11:02AM -0700, Ron St-Pierre wrote:
> We have a web based application with data that is updated daily. The 
> biggest bottleneck occurs when we try to update
> one of the tables. This table contains 58,000 rows and 62 columns, and 
> EVERY column is indexed.

That is usually a very bad idea; for every update, PostgreSQL has to update
62 indexes. Do you really do queries on all those 62 columns?

> A typical update is:
>   UPDATE dataTable SET field01=44.5, field02=44.5, field03='Bob',
>     field04='foo', ... , field60='2004-08-30', field61='2004-08-29'
>     WHERE id = 1234;

That looks like poor database normalization, really. Are you sure you don't
want to split this into multiple tables instead of having 62 columns?

> Other notables:
>   The UPDATE is run from a within a function: FOR rec IN SELECT ...LOOP 
> RETURN NEXT rec; UPDATE dataTable.....
>   Postgres 7.4.3
>   debian stable
>   2 GB RAM
>   80 DB IDE drive (we can't change it)

Are you doing all this in multiple transactions, or in a sngle one? Wrapping
the FOR loop in a transaction might help speed.

/* Steinar */
-- 
Homepage: http://www.sesse.net/



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
  2004-08-31 18:18 ` Re: Table UPDATE is too slow Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2004-08-31 18:35   ` Ron St-Pierre <rstpierre@syscor.com>
  2004-08-31 18:48     ` Re: Table UPDATE is too slow Steinar H. Gunderson <sgunderson@bigfoot.com>
  2004-08-31 18:59     ` Re: Table UPDATE is too slow Matt Clark <matt@ymogen.net>
  2004-08-31 19:15     ` Re: Table UPDATE is too slow William Yu <wyu@talisys.com>
  0 siblings, 3 replies; 16+ messages in thread

From: Ron St-Pierre @ 2004-08-31 18:35 UTC (permalink / raw)
  To: Steinar H. Gunderson <sgunderson@bigfoot.com>; pgsql-performance

Steinar H. Gunderson wrote:

>On Tue, Aug 31, 2004 at 11:11:02AM -0700, Ron St-Pierre wrote:
>  
>
>>We have a web based application with data that is updated daily. The 
>>biggest bottleneck occurs when we try to update
>>one of the tables. This table contains 58,000 rows and 62 columns, and 
>>EVERY column is indexed.
>>    
>>
>
>That is usually a very bad idea; for every update, PostgreSQL has to update
>62 indexes. Do you really do queries on all those 62 columns?
>  
>
Yes, I know that it's not a very good idea, however queries are allowed 
against all of those columns. One option is to disable some or all of the
indexes when we update, run the update, and recreate the indexes, 
however it may slow down user queries. Because there are so many indexes,
it is time consuming to recreate them after the update.

>  
>
>>A typical update is:
>>  UPDATE dataTable SET field01=44.5, field02=44.5, field03='Bob',
>>    field04='foo', ... , field60='2004-08-30', field61='2004-08-29'
>>    WHERE id = 1234;
>>    
>>
>
>That looks like poor database normalization, really. Are you sure you don't
>want to split this into multiple tables instead of having 62 columns?
>
No, it is properly normalized. The data in this table is stock 
fundamentals, stuff like 52 week high, ex-dividend date, etc, etc.

>
>  
>
>>Other notables:
>>  The UPDATE is run from a within a function: FOR rec IN SELECT ...LOOP 
>>RETURN NEXT rec; UPDATE dataTable.....
>>  Postgres 7.4.3
>>  debian stable
>>  2 GB RAM
>>  80 DB IDE drive (we can't change it)
>>    
>>
>
>Are you doing all this in multiple transactions, or in a sngle one? Wrapping
>the FOR loop in a transaction might help speed.
>
We're doing it in multiple transactions within the function. Could we do 
something like this?:

....
BEGIN
  FOR rec IN SELECT field01, field02, ... FROM otherTable LOOP 
  RETURN NEXT rec;
  UPDATE dataTable SET field01=rec.field01, field02=rec.field02, rec.field03=field03, ...
    WHERE id = rec.id;
COMMIT;
....


If we can do it this way, are there any other gotcha's we should be 
aware of?


Ron




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
  2004-08-31 18:18 ` Re: Table UPDATE is too slow Steinar H. Gunderson <sgunderson@bigfoot.com>
  2004-08-31 18:35   ` Re: Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
@ 2004-08-31 18:48     ` Steinar H. Gunderson <sgunderson@bigfoot.com>
  2 siblings, 0 replies; 16+ messages in thread

From: Steinar H. Gunderson @ 2004-08-31 18:48 UTC (permalink / raw)
  To: pgsql-performance

On Tue, Aug 31, 2004 at 11:35:38AM -0700, Ron St-Pierre wrote:
> We're doing it in multiple transactions within the function. Could we do 
> something like this?:
> 
> ....
> BEGIN
>  FOR rec IN SELECT field01, field02, ... FROM otherTable LOOP 
>  RETURN NEXT rec;
>  UPDATE dataTable SET field01=rec.field01, field02=rec.field02, 
>  rec.field03=field03, ...
>    WHERE id = rec.id;
> COMMIT;
> ....
> 
> 
> If we can do it this way, are there any other gotcha's we should be 
> aware of?

AFAIK you should be able to do this, yes (although I have no experience with
PL/SQL); I'm not sure how much it buys you, but you might want to test it, at
least.

/* Steinar */
-- 
Homepage: http://www.sesse.net/



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
  2004-08-31 18:18 ` Re: Table UPDATE is too slow Steinar H. Gunderson <sgunderson@bigfoot.com>
  2004-08-31 18:35   ` Re: Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
@ 2004-08-31 18:59     ` Matt Clark <matt@ymogen.net>
  2 siblings, 0 replies; 16+ messages in thread

From: Matt Clark @ 2004-08-31 18:59 UTC (permalink / raw)
  To: 'Ron St-Pierre' <rstpierre@syscor.com>; 'Steinar H. Gunderson' <sgunderson@bigfoot.com>; pgsql-performance

> >That looks like poor database normalization, really. Are you 
> sure you 
> >don't want to split this into multiple tables instead of having 62 
> >columns?
> >
> No, it is properly normalized. The data in this table is stock 
> fundamentals, stuff like 52 week high, ex-dividend date, etc, etc.

Hmm, the two examples you gave there are actually ripe for breaking out into
another table.  It's not quite 'normalisation', but if you have data that
changes very rarely, why not group it into a separate table?  You could have
the highly volatile data in one table, the semi-volatile stuff in another,
and the pretty static stuff in a third.  Looked at another way, if you have
sets of fields that tend to change together, group them into tables
together.  That way you will radically reduce the number of indexes that are
affected by each update.

But as someone else pointed out, you should at the very least wrap your
updates in a big transaction.

M




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
  2004-08-31 18:18 ` Re: Table UPDATE is too slow Steinar H. Gunderson <sgunderson@bigfoot.com>
  2004-08-31 18:35   ` Re: Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
@ 2004-08-31 19:15     ` William Yu <wyu@talisys.com>
  2 siblings, 0 replies; 16+ messages in thread

From: William Yu @ 2004-08-31 19:15 UTC (permalink / raw)
  To: pgsql-performance

Ron St-Pierre wrote:
> Yes, I know that it's not a very good idea, however queries are allowed 
> against all of those columns. One option is to disable some or all of the
> indexes when we update, run the update, and recreate the indexes, 
> however it may slow down user queries. Because there are so many indexes,
> it is time consuming to recreate them after the update.

Just because a query can run against any column does not mean all 
columns should be indexed. Take a good look at the column types and 
their value distribution.

Let's say I have a table of addresses but every address I collect is in 
the 94116 zip code. That would mean indexes on city, state and zip are 
not only useless but could decrease performance.

Also, if a search always includes a unique key (or a column with highly 
unique values), eliminating the other indexes would force the planner to 
always use that index first.



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
@ 2004-08-31 18:46 ` Frank Wiles <frank@wiles.org>
  6 siblings, 0 replies; 16+ messages in thread

From: Frank Wiles @ 2004-08-31 18:46 UTC (permalink / raw)
  To: Ron St-Pierre <rstpierre@syscor.com>; +Cc: pgsql-performance

On Tue, 31 Aug 2004 11:11:02 -0700
Ron St-Pierre <rstpierre@syscor.com> wrote:


>    Postgres 7.4.3
>    debian stable
>    2 GB RAM
>    80 DB IDE drive (we can't change it)
>  
>    shared_buffers = 2048
>    sort_mem = 1024  
>    max_fsm_pages = 40000
>    checkpoint_segments = 5
>    random_page_cost = 3

  I agree with all of the follow ups that having indexes on every 
  column is a bad idea.  I would remove the indexes from the
  least searched upon 10-20 columns, as I'm sure this will help
  your performance.

  You mention that not having indexes on some of the columns because it
  "may slow down user queries".  I think you should investigate this and
  make sure they are necessary.  I've seen many an application, with far
  more rows than you're dealing with, only need 1 or 2 indexes, even
  when all (or most) columns could be searched. 

  Also, you should consider increasing your shared_buffers and probably
  your sort memory a touch as well. This will help your overall
  performance. 

 ---------------------------------
   Frank Wiles <frank@wiles.org>
   http://www.wiles.org
 ---------------------------------




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
@ 2004-09-01 00:18 ` Ron St-Pierre <rstpierre@syscor.com>
  6 siblings, 0 replies; 16+ messages in thread

From: Ron St-Pierre @ 2004-09-01 00:18 UTC (permalink / raw)
  To: pgsql-performance

Thanks for everyone's comments (Thomas, Steinar, Frank, Matt, William). 
Right now I'm bench-marking the time it takes for each step
in the end of day update process and then I am going to test a few things:
- dropping most indexes, and check the full processing time and see if 
there is any noticeable performance degradation on the web-end
- wrapping the updates in a transaction, and check the timing
- combining the two
- reviewing my shared_buffers and sort_memory settings

Thanks
Ron




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
@ 2004-09-01 05:32 ` Tom Lane <tgl@sss.pgh.pa.us>
  6 siblings, 0 replies; 16+ messages in thread

From: Tom Lane @ 2004-09-01 05:32 UTC (permalink / raw)
  To: Ron St-Pierre <rstpierre@syscor.com>; +Cc: pgsql-performance

Ron St-Pierre <rstpierre@syscor.com> writes:
>  Does anyone have some idea on how we can increase speed, either by 
> changing the updates, designing the database
>  differently, etc, etc? This is currently a big problem for us.
 
>  Other notables:
>    The UPDATE is run from a within a function: FOR rec IN SELECT ...LOOP 
> RETURN NEXT rec; UPDATE dataTable.....

One point that I don't think was made before: by doing a collection of
updates in this serial one-at-a-time fashion, you are precluding any
possibility of query optimization over the collection of updates.  It
might win to dump the update data into a temp table and do a single
UPDATE command joining to the temp table.  Or not --- quite possibly not
--- but I think it's something to think about.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
@ 2004-09-05 23:28 ` Marinos J. Yannikos <mjy@geizhals.at>
  2004-09-06 01:19   ` Re: Table UPDATE is too slow Kevin Barnard <kevin.barnard@gmail.com>
  6 siblings, 1 reply; 16+ messages in thread

From: Marinos J. Yannikos @ 2004-09-05 23:28 UTC (permalink / raw)
  To: Ron St-Pierre <rstpierre@syscor.com>; +Cc: pgsql-performance

Ron St-Pierre wrote:

> We have a web based application with data that is updated daily. The 
> biggest bottleneck occurs when we try to update
> one of the tables. This table contains 58,000 rows and 62 columns, and 
> EVERY column is indexed.

Have you thought of / tried using 2 separate databases or tables and 
switching between them? Since you seem to be updating all the values, it 
might be a lot faster to re-create the table from scratch without 
indexes and add those later (maybe followed by a VACUUM ANALYZE) ...

That said, I'm not entirely sure how well postgres' client libraries can 
deal with tables being renamed while in use, perhaps someone can shed 
some light on this.

Regards,
  Marinos
-- 
Dipl.-Ing. Marinos Yannikos, CEO
Preisvergleich Internet Services AG
Obere Donaustraße 63/2, A-1020 Wien
Tel./Fax: (+431) 5811609-52/-55



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
  2004-09-05 23:28 ` Re: Table UPDATE is too slow Marinos J. Yannikos <mjy@geizhals.at>
@ 2004-09-06 01:19   ` Kevin Barnard <kevin.barnard@gmail.com>
  0 siblings, 0 replies; 16+ messages in thread

From: Kevin Barnard @ 2004-09-06 01:19 UTC (permalink / raw)
  To: pgsql-performance

Do all of the commands to swap tables in a transaction.  The table
gets locked briefly but should have a lot less impact then the update
command.


On Mon, 06 Sep 2004 01:28:04 +0200, Marinos J. Yannikos <mjy@geizhals.at> wrote:
> 
> That said, I'm not entirely sure how well postgres' client libraries can
> deal with tables being renamed while in use, perhaps someone can shed
> some light on this.
>



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: [PERFORM] Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
@ 2004-09-07 16:20 ` Ron St-Pierre <rstpierre@syscor.com>
  2004-09-07 18:00   ` Re: [PERFORM] Table UPDATE is too slow Tom Lane <tgl@sss.pgh.pa.us>
  2004-09-07 18:42   ` Re: [PERFORM] Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
  6 siblings, 2 replies; 16+ messages in thread

From: Ron St-Pierre @ 2004-09-07 16:20 UTC (permalink / raw)
  To: pgsql-general <pgsql-general@postgresql.org>

Ron St-Pierre wrote:

> We have a web based application with data that is updated daily. The 
> biggest bottleneck occurs when we try to update
> one of the tables. This table contains 58,000 rows and 62 columns, and 
> EVERY column is indexed. Every column is
> queryable (?) by the users through the web interface so we are 
> reluctant to remove the indexes (recreating them would
> be time consuming too). The primary key is an INT and the rest of the 
> columns are a mix of NUMERIC, TEXT, and DATEs.
> A typical update is:
>   UPDATE dataTable SET field01=44.5, field02=44.5, field03='Bob',
>     field04='foo', ... , field60='2004-08-30', field61='2004-08-29'
>     WHERE id = 1234;
>
> Also of note is that the update is run about 10 times per day; we get 
> blocks of data from 10 different sources, so we pre-process the
> data and then update the table. We also run VACUUM FULL ANALYZE on a 
> nightly basis. 

It now appears that VACUUM wasn't running properly. A manual VACUUM FULL 
ANALYZE VEBOSE told us that
approximately 275000  total pages were needed. I increased the 
max_fsm_pages to 300000, VACUUMED, renamed the
database and re-created it from backup, vacuumed numerous times, and the 
total fsm_pages needed continued to remain in
the 235000 -> 270000 range. This morning I deleted the original 
(renamed) database, and a VACUUM FULL ANALYZE
VEBOSE now says that only about 9400 pages are needed.

One question about redirecting VACUUMs output to file though. When I run:
   psql -d imperial -c "vacuum full verbose analyze;" > vac.info
vac.info contains only the following line:
   VACUUM
I've been unable to capture the VERBOSE output to file. Any suggestions?

<snip>

>
Also, thanks for everyone's input about my original posting, I am 
investigating some of the options mentioned to further increase
performance.

Ron




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: [PERFORM] Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
  2004-09-07 16:20 ` Re: [PERFORM] Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
@ 2004-09-07 18:00   ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 16+ messages in thread

From: Tom Lane @ 2004-09-07 18:00 UTC (permalink / raw)
  To: Ron St-Pierre <rstpierre@syscor.com>; +Cc: pgsql-general <pgsql-general@postgresql.org>

Ron St-Pierre <rstpierre@syscor.com> writes:
> One question about redirecting VACUUMs output to file though. When I run:
>    psql -d imperial -c "vacuum full verbose analyze;" > vac.info
> vac.info contains only the following line:
>    VACUUM
> I've been unable to capture the VERBOSE output to file. Any suggestions?

You need to catch stderr not only stdout.

(I'd be less vague if I knew which shell you were running, but sh- and
csh-derived shells do it differently.)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: [PERFORM] Table UPDATE is too slow
  2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
  2004-09-07 16:20 ` Re: [PERFORM] Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
@ 2004-09-07 18:42   ` Ron St-Pierre <rstpierre@syscor.com>
  1 sibling, 0 replies; 16+ messages in thread

From: Ron St-Pierre @ 2004-09-07 18:42 UTC (permalink / raw)
  To: pgsql-general <pgsql-general@postgresql.org>

Tom Lane wrote:

>Ron St-Pierre <rstpierre@syscor.com> writes:
>  
>
>>One question about redirecting VACUUMs output to file though. When I run:
>>   psql -d imperial -c "vacuum full verbose analyze;" > vac.info
>>vac.info contains only the following line:
>>   VACUUM
>>I've been unable to capture the VERBOSE output to file. Any suggestions?
>>    
>>
>
>You need to catch stderr not only stdout.
>
>(I'd be less vague if I knew which shell you were running, but sh- and
>csh-derived shells do it differently.)
>
>	
>
Oops, I'm running bash. I just redirected stderr to the file
    psql -d imperial -c "vacuum full verbose analyze;" 2> 
/usr/local/pgsql/vac.info
which gives me exactly what I want.

Thanks again Tom

Ron




^ permalink  raw  reply  [nested|flat] 16+ messages in thread


end of thread, other threads:[~2004-09-07 18:42 UTC | newest]

Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-31 18:11 Table UPDATE is too slow Ron St-Pierre <rstpierre@syscor.com>
2004-08-31 18:16 ` Thomas F.O'Connell <tfo@sitening.com>
2004-08-31 18:23   ` Ron St-Pierre <rstpierre@syscor.com>
2004-08-31 18:18 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2004-08-31 18:35   ` Ron St-Pierre <rstpierre@syscor.com>
2004-08-31 18:48     ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2004-08-31 18:59     ` Matt Clark <matt@ymogen.net>
2004-08-31 19:15     ` William Yu <wyu@talisys.com>
2004-08-31 18:46 ` Frank Wiles <frank@wiles.org>
2004-09-01 00:18 ` Ron St-Pierre <rstpierre@syscor.com>
2004-09-01 05:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-05 23:28 ` Marinos J. Yannikos <mjy@geizhals.at>
2004-09-06 01:19   ` Kevin Barnard <kevin.barnard@gmail.com>
2004-09-07 16:20 ` Ron St-Pierre <rstpierre@syscor.com>
2004-09-07 18:00   ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-07 18:42   ` Ron St-Pierre <rstpierre@syscor.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox