Thread: 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
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
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
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 ...
>
>
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
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
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?
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
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
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.
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.
Tonight database has been vacumm full and reindex (all nights database do it) Now its working fine. Speed is as spected. I ll be watching that sql ... Maybe the problem exists when database is busy, or maybe its solved ...
On 13.06.2006, at 8:44 Uhr, Ruben Rubio Rey wrote: > Tonight database has been vacumm full and reindex (all nights > database do it) > > Now its working fine. Speed is as spected. I ll be watching that > sql ... > Maybe the problem exists when database is busy, or maybe its > solved ... Depending on the usage pattern the nightly re-index / vacuum analyse is suboptimal. If you have high insert/update traffic your performance will decrease over the day and will only be good in the morning hours and I hope this is not what you intend to have. Autovacuum is the way to go, if you have "changing content". Perhaps combined with vacuum analyse in a nightly or weekly schedule. We do this weekly. cug
Guido Neitzer wrote: > On 13.06.2006, at 8:44 Uhr, Ruben Rubio Rey wrote: > >> Tonight database has been vacumm full and reindex (all nights >> database do it) >> >> Now its working fine. Speed is as spected. I ll be watching that sql >> ... >> Maybe the problem exists when database is busy, or maybe its solved ... > > > Depending on the usage pattern the nightly re-index / vacuum analyse > is suboptimal. If you have high insert/update traffic your > performance will decrease over the day and will only be good in the > morning hours and I hope this is not what you intend to have. > > Autovacuum is the way to go, if you have "changing content". Perhaps > combined with vacuum analyse in a nightly or weekly schedule. We do > this weekly. > > cug > > I ll configure autovacum. I ll write if problem is solved.
Seems autovacumm is working fine. Logs are reporting that is being useful. But server load is high. Is out there any way to stop "autovacumm" if server load is very high? Thanks everyone!!!
On 13.06.2006, at 12:33 Uhr, Ruben Rubio Rey wrote: > Seems autovacumm is working fine. Logs are reporting that is being > useful. > > But server load is high. Is out there any way to stop "autovacumm" > if server load is very high? Look at the cost settings for vacuum and autovacuum. From the manual: "During the execution of VACUUM and ANALYZE commands, the system maintains an internal counter that keeps track of the estimated cost of the various I/O operations that are performed. When the accumulated cost reaches a limit (specified by vacuum_cost_limit), the process performing the operation will sleep for a while (specified by vacuum_cost_delay). Then it will reset the counter and continue execution. The intent of this feature is to allow administrators to reduce the I/ O impact of these commands on concurrent database activity. There are many situations in which it is not very important that mainte- nance commands like VACUUM and ANALYZE finish quickly; however, it is usually very important that these commands do not significantly interfere with the ability of the system to perform other database operations. Cost-based vacuum delay provides a way for administrators to achieve this." cug