pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPosrgres speed problem
11+ messages / 5 participants
[nested] [flat]
* Posrgres speed problem
@ 2006-06-12 14:38 Ruben Rubio Rey <ruben@rentalia.com>
0 siblings, 5 replies; 11+ messages in thread
From: Ruben Rubio Rey @ 2006-06-12 14:38 UTC (permalink / raw)
To: pgsql-performance
Hi,
Im having a problem with postgres 8.1.3 on a Fedora Core 3 (kernel
2.6.9-1.667smp)
I have two similar servers, one in production and another for testing
purposes.
Databases are equal (with a difference of some hours)
In the testing server, an sql sentence takes arround 1 sec.
In production server (low server load) takes arround 50 secs, and uses
too much resources.
Explain analyze takes too much load, i had to cancel it!
Could it be a it a bug?
Any ideas?
Thanks in advance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Posrgres speed problem
@ 2006-06-12 14:45 Dave Dutcher <dave@tridecap.com>
parent: Ruben Rubio Rey <ruben@rentalia.com>
4 siblings, 0 replies; 11+ messages in thread
From: Dave Dutcher @ 2006-06-12 14:45 UTC (permalink / raw)
To: 'Ruben Rubio Rey' <ruben@rentalia.com>; pgsql-performance
Do you run analyze on the production server regularly?
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> Ruben Rubio Rey
> Sent: Monday, June 12, 2006 9:39 AM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Posrgres speed problem
>
>
>
> Hi,
>
> Im having a problem with postgres 8.1.3 on a Fedora Core 3 (kernel
> 2.6.9-1.667smp)
>
> I have two similar servers, one in production and another for testing
> purposes.
> Databases are equal (with a difference of some hours)
>
> In the testing server, an sql sentence takes arround 1 sec.
> In production server (low server load) takes arround 50 secs,
> and uses
> too much resources.
>
> Explain analyze takes too much load, i had to cancel it!
>
> Could it be a it a bug?
> Any ideas?
>
> Thanks in advance
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Posrgres speed problem
@ 2006-06-12 14:58 Ruben Rubio Rey <ruben@rentalia.com>
parent: Ruben Rubio Rey <ruben@rentalia.com>
4 siblings, 1 reply; 11+ messages in thread
From: Ruben Rubio Rey @ 2006-06-12 14:58 UTC (permalink / raw)
To: Gábriel Ákos <akos.gabriel@i-logic.hu>; +Cc: pgsql-performance
Gábriel Ákos wrote:
> Ruben Rubio Rey wrote:
>
>>
>> Hi,
>>
>> Im having a problem with postgres 8.1.3 on a Fedora Core 3 (kernel
>> 2.6.9-1.667smp)
>>
>> I have two similar servers, one in production and another for testing
>> purposes.
>> Databases are equal (with a difference of some hours)
>>
>> In the testing server, an sql sentence takes arround 1 sec.
>> In production server (low server load) takes arround 50 secs, and
>> uses too much resources.
>>
>> Explain analyze takes too much load, i had to cancel it!
>>
>> Could it be a it a bug?
>> Any ideas?
>
>
> vacuum full analyse the database.
>
>
I use to do it all nights
Its an script with content:
DIREC=/usr/local/pgsql/bin/
DIRLOGS=/var/log/rentalia
LOGBIN=/usr/sbin/cronolog
echo "vacuum vacadb..." | $LOGBIN $DIRLOGS/%Y-%m-%d_limpieza.log
date | $LOGBIN $DIRLOGS/%Y-%m-%d_limpieza.log
$DIREC/vacuumdb -f -v --analyze vacadb 2>&1 | $LOGBIN
$DIRLOGS/%Y-%m-%d_limpieza.log
echo "reindex database vacadb;" | $DIREC/psql vacadb 2>&1 | $LOGBIN
$DIRLOGS/%Y-%m-%d_limpieza.log
date | $LOGBIN $DIRLOGS/%Y-%m-%d_limpieza.log
No errors or warnings are reported. instead repeating it now, I preffer
to wait at tomorrow to check again the logs
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Posrgres speed problem
@ 2006-06-12 15:02 Ruben Rubio Rey <ruben@rentalia.com>
parent: Ruben Rubio Rey <ruben@rentalia.com>
4 siblings, 0 replies; 11+ messages in thread
From: Ruben Rubio Rey @ 2006-06-12 15:02 UTC (permalink / raw)
To: Jonah H. Harris <jonah.harris@gmail.com>; +Cc: pgsql-performance
Jonah H. Harris wrote:
> On 6/12/06, Ruben Rubio Rey <ruben@rentalia.com> wrote:
>
>> I have two similar servers, one in production and another
>> for testing purposes. In testing server ~1sec ... in
>> production ~50 secs
>
>
> What ver of PostgreSQL?
Version 8.1.3
> Same ver on both systems?
Yes
> Are there any
> locks currently held on the resources needed in your Production
> environment?
How to check it?
> Have you analyzed both databases?
I have restores testing server today. Full Analyce included.
Production server all nights is done. (i have posted the script in other
message to the mailing list)
> Any sequential scans
> running?
In the table, there is several scans.
vacadb=# \d grupoforo
Table "public.grupoforo"
Column | Type
| Modifiers
------------------+-----------------------------+---------------------------------------------------------------
idmensaje | integer | not null default
nextval('grupoforo_idmensaje_seq'::regclass)
idusuario | integer | not null
idgrupo | integer | not null
idmensajetema | integer | not null default -1
mensaje | character varying(4000) |
asunto | character varying(255) | not null
fechalocal | timestamp without time zone | default now()
webenabled | integer | not null default 1
por | character varying(255) |
estadocomentario | character(1) | default 'D'::bpchar
idlenguaje | character(2) | default 'ES'::bpchar
fechacreacion | timestamp without time zone | default now()
hijos | integer |
hijoreciente | timestamp without time zone |
valoracion | integer | default 0
codigo | character varying(100) |
Indexes:
"pk_grupoforo" PRIMARY KEY, btree (idmensaje)
"grupoforo_asunto_idx" btree (asunto)
"grupoforo_codigo_idx" btree (codigo)
"grupoforo_estadocomentario_idx" btree (estadocomentario)
"grupoforo_idgrupo_idx" btree (idgrupo)
"grupoforo_idlenguaje_idx" btree (idlenguaje)
"grupoforo_idmensajetema_idx" btree (idmensajetema)
"grupoforo_idusuario_idx" btree (idusuario)
"idx_grupoforo_webenabled" btree (webenabled)
> If so, have you vacuumed?
Yes.
>
> Send the explain analyze from your test database.
Tomorrow morning i ll send it ... now it could be a disaster ...
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Posrgres speed problem
@ 2006-06-12 15:05 Michael Fuhr <mike@fuhr.org>
parent: Ruben Rubio Rey <ruben@rentalia.com>
4 siblings, 1 reply; 11+ messages in thread
From: Michael Fuhr @ 2006-06-12 15:05 UTC (permalink / raw)
To: Ruben Rubio Rey <ruben@rentalia.com>; +Cc: pgsql-performance
On Mon, Jun 12, 2006 at 04:38:57PM +0200, Ruben Rubio Rey wrote:
> I have two similar servers, one in production and another for testing
> purposes.
> Databases are equal (with a difference of some hours)
>
> In the testing server, an sql sentence takes arround 1 sec.
> In production server (low server load) takes arround 50 secs, and uses
> too much resources.
>
> Explain analyze takes too much load, i had to cancel it!
The EXPLAIN ANALYZE output would be helpful, but if you don't want
to run it to completion then please post the output of EXPLAIN
ANALYZE for the fast system and EXPLAIN (without ANALYZE) for the
slow one.
As someone else asked, are you running ANALYZE regularly? What
about VACUUM?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Posrgres speed problem
@ 2006-06-12 15:17 Jim C. Nasby <jnasby@pervasive.com>
parent: Ruben Rubio Rey <ruben@rentalia.com>
0 siblings, 1 reply; 11+ messages in thread
From: Jim C. Nasby @ 2006-06-12 15:17 UTC (permalink / raw)
To: Ruben Rubio Rey <ruben@rentalia.com>; +Cc: G?briel ?kos <akos.gabriel@i-logic.hu>; pgsql-performance
On Mon, Jun 12, 2006 at 04:58:49PM +0200, Ruben Rubio Rey wrote:
> $DIREC/vacuumdb -f -v --analyze vacadb 2>&1 | $LOGBIN
> $DIRLOGS/%Y-%m-%d_limpieza.log
> echo "reindex database vacadb;" | $DIREC/psql vacadb 2>&1 | $LOGBIN
> $DIRLOGS/%Y-%m-%d_limpieza.log
> date | $LOGBIN $DIRLOGS/%Y-%m-%d_limpieza.log
Ugh. Is there some reason you're not using the built-in autovacuum? If
you enable it and cut the thresholds in half you'll most likely never
need to vacuum manually, let alone reindex.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Posrgres speed problem
@ 2006-06-12 15:22 Ruben Rubio Rey <ruben@rentalia.com>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 11+ messages in thread
From: Ruben Rubio Rey @ 2006-06-12 15:22 UTC (permalink / raw)
To: pgsql-performance
Jim C. Nasby wrote:
>On Mon, Jun 12, 2006 at 04:58:49PM +0200, Ruben Rubio Rey wrote:
>
>
>>$DIREC/vacuumdb -f -v --analyze vacadb 2>&1 | $LOGBIN
>>$DIRLOGS/%Y-%m-%d_limpieza.log
>>echo "reindex database vacadb;" | $DIREC/psql vacadb 2>&1 | $LOGBIN
>>$DIRLOGS/%Y-%m-%d_limpieza.log
>>date | $LOGBIN $DIRLOGS/%Y-%m-%d_limpieza.log
>>
>>
>
>Ugh. Is there some reason you're not using the built-in autovacuum?
>
How do I execute built-in autovacuum?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Posrgres speed problem
@ 2006-06-12 15:23 Jim C. Nasby <jnasby@pervasive.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 11+ messages in thread
From: Jim C. Nasby @ 2006-06-12 15:23 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Ruben Rubio Rey <ruben@rentalia.com>; pgsql-performance
On Mon, Jun 12, 2006 at 09:05:06AM -0600, Michael Fuhr wrote:
> On Mon, Jun 12, 2006 at 04:38:57PM +0200, Ruben Rubio Rey wrote:
> > I have two similar servers, one in production and another for testing
> > purposes.
> > Databases are equal (with a difference of some hours)
> >
> > In the testing server, an sql sentence takes arround 1 sec.
> > In production server (low server load) takes arround 50 secs, and uses
> > too much resources.
> >
> > Explain analyze takes too much load, i had to cancel it!
>
> The EXPLAIN ANALYZE output would be helpful, but if you don't want
> to run it to completion then please post the output of EXPLAIN
> ANALYZE for the fast system and EXPLAIN (without ANALYZE) for the
> slow one.
>
> As someone else asked, are you running ANALYZE regularly? What
> about VACUUM?
For the next vacuum, can you add the -v (verbose) switch and email the
last few lines of output?
INFO: free space map contains 39 pages in 56 relations
DETAIL: A total of 896 page slots are in use (including overhead).
896 page slots are required to track all free space.
Current limits are: 20000 page slots, 1000 relations, using 223 KB.
VACUUM
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Posrgres speed problem
@ 2006-06-12 15:25 Jim C. Nasby <jnasby@pervasive.com>
parent: Ruben Rubio Rey <ruben@rentalia.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jim C. Nasby @ 2006-06-12 15:25 UTC (permalink / raw)
To: Ruben Rubio Rey <ruben@rentalia.com>; +Cc: pgsql-performance
On Mon, Jun 12, 2006 at 05:22:05PM +0200, Ruben Rubio Rey wrote:
> Jim C. Nasby wrote:
>
> >On Mon, Jun 12, 2006 at 04:58:49PM +0200, Ruben Rubio Rey wrote:
> >
> >
> >>$DIREC/vacuumdb -f -v --analyze vacadb 2>&1 | $LOGBIN
> >>$DIRLOGS/%Y-%m-%d_limpieza.log
> >>echo "reindex database vacadb;" | $DIREC/psql vacadb 2>&1 | $LOGBIN
> >>$DIRLOGS/%Y-%m-%d_limpieza.log
> >>date | $LOGBIN $DIRLOGS/%Y-%m-%d_limpieza.log
> >>
> >>
> >
> >Ugh. Is there some reason you're not using the built-in autovacuum?
> >
> How do I execute built-in autovacuum?
Make the following changes to postgresql.conf:
autovacuum = on # enable autovacuum subprocess?
autovacuum_vacuum_threshold = 500 # min # of tuple updates before
# vacuum
autovacuum_analyze_threshold = 200 # min # of tuple updates before
autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
# vacuum
autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Posrgres speed problem
@ 2006-06-12 15:33 Sven Geisler <sgeisler@aeccom.com>
parent: Ruben Rubio Rey <ruben@rentalia.com>
4 siblings, 0 replies; 11+ messages in thread
From: Sven Geisler @ 2006-06-12 15:33 UTC (permalink / raw)
To: Ruben Rubio Rey <ruben@rentalia.com>; +Cc: pgsql-performance
Hi Ruben,
Ruben Rubio Rey schrieb:
>
> Hi,
>
> Im having a problem with postgres 8.1.3 on a Fedora Core 3 (kernel
> 2.6.9-1.667smp)
>
> I have two similar servers, one in production and another for testing
> purposes.
> Databases are equal (with a difference of some hours)
>
> In the testing server, an sql sentence takes arround 1 sec.
> In production server (low server load) takes arround 50 secs, and uses
> too much resources.
>
> Explain analyze takes too much load, i had to cancel it!
>
> Could it be a it a bug?
> Any ideas?
How do you load the data to the testing server? (Dump, Copy, etc)
As you wrote the difference are some hours. I think you copy something.
It is possible that you production database as too much deleted tuples.
Vacuum full does only rebuild the table an not the index. You may also
run reindex on certain tables. I guess, this may the issue if you use
dump/restore to get your production copy.
Is three a huge difference in the result of this queries:
select relname,relpages,reltuples from pg_class order by relpages desc;
and
select relname,relpages,reltuples from pg_class where relname like
'%index' order by relpages desc;
Cheers Sven.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Posrgres speed problem
@ 2006-06-13 06:37 Ruben Rubio Rey <ruben@rentalia.com>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 11+ messages in thread
From: Ruben Rubio Rey @ 2006-06-13 06:37 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; pgsql-performance
Jim C. Nasby wrote:
>On Mon, Jun 12, 2006 at 09:05:06AM -0600, Michael Fuhr wrote:
>
>
>>On Mon, Jun 12, 2006 at 04:38:57PM +0200, Ruben Rubio Rey wrote:
>>
>>
>>>I have two similar servers, one in production and another for testing
>>>purposes.
>>>Databases are equal (with a difference of some hours)
>>>
>>>In the testing server, an sql sentence takes arround 1 sec.
>>>In production server (low server load) takes arround 50 secs, and uses
>>>too much resources.
>>>
>>>Explain analyze takes too much load, i had to cancel it!
>>>
>>>
>>The EXPLAIN ANALYZE output would be helpful, but if you don't want
>>to run it to completion then please post the output of EXPLAIN
>>ANALYZE for the fast system and EXPLAIN (without ANALYZE) for the
>>slow one.
>>
>>As someone else asked, are you running ANALYZE regularly? What
>>about VACUUM?
>>
>>
>
>For the next vacuum, can you add the -v (verbose) switch and email the
>last few lines of output?
>
>INFO: free space map contains 39 pages in 56 relations
>DETAIL: A total of 896 page slots are in use (including overhead).
>896 page slots are required to track all free space.
>Current limits are: 20000 page slots, 1000 relations, using 223 KB.
>VACUUM
>
>
INFO: free space map contains 1624 pages in 137 relations
DETAIL: A total of 3200 page slots are in use (including overhead).
3200 page slots are required to track all free space.
Current limits are: 20000 page slots, 1000 relations, using 182 KB.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-06-13 06:37 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-12 14:38 Posrgres speed problem Ruben Rubio Rey <ruben@rentalia.com>
2006-06-12 14:45 ` Dave Dutcher <dave@tridecap.com>
2006-06-12 14:58 ` Ruben Rubio Rey <ruben@rentalia.com>
2006-06-12 15:17 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-12 15:22 ` Ruben Rubio Rey <ruben@rentalia.com>
2006-06-12 15:25 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-12 15:02 ` Ruben Rubio Rey <ruben@rentalia.com>
2006-06-12 15:05 ` Michael Fuhr <mike@fuhr.org>
2006-06-12 15:23 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-13 06:37 ` Ruben Rubio Rey <ruben@rentalia.com>
2006-06-12 15:33 ` Sven Geisler <sgeisler@aeccom.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