agora inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedRe: [SQL] 7.3 analyze & vacuum analyze problem
4+ messages / 4 participants
[nested] [flat]
* Re: [SQL] 7.3 analyze & vacuum analyze problem
@ 2003-04-30 16:03 Josh Berkus <josh@agliodbs.com>
2003-04-30 21:13 ` Re: [SQL] 7.3 analyze & vacuum analyze problem Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2003-04-30 16:03 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; pgsql-sql@postgresql.org; +Cc: pgsql-performance
Achilleus,
> i think i have an issue regarding the statistics that
> a) (plain) ANALYZE status and
> b) VACUUM ANALYZE status
> produce.
It's perfectly normal for a query to run faster after a VACUUM ANALYZE than
after an ANALYZE ... after all, you just vacuumed it, didn't you?
If you're demonstrating some other kind of behavioural difference, then please
post the results of EXPLAIN ANALYZE for the two examples.
Oh, and we should probably shift this discussion to the PGSQL-PERFORMANCE
list.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] 7.3 analyze & vacuum analyze problem
2003-04-30 16:03 Re: [SQL] 7.3 analyze & vacuum analyze problem Josh Berkus <josh@agliodbs.com>
@ 2003-04-30 21:13 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Achilleus Mantzios @ 2003-04-30 21:13 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql@postgresql.org; pgsql-performance
On Wed, 30 Apr 2003, Josh Berkus wrote:
> Achilleus,
>
> > i think i have an issue regarding the statistics that
> > a) (plain) ANALYZE status and
> > b) VACUUM ANALYZE status
> > produce.
>
> It's perfectly normal for a query to run faster after a VACUUM ANALYZE than
> after an ANALYZE ... after all, you just vacuumed it, didn't you?
I am afraid it is not so simple.
What i (unsuccessfully) implied is that
dynacom=# VACUUM ANALYZE status ;
VACUUM
dynacom=# ANALYZE status ;
ANALYZE
dynacom=#
is enuf to damage the performance.
>
> If you're demonstrating some other kind of behavioural difference, then please
> post the results of EXPLAIN ANALYZE for the two examples.
>
dynacom=# ANALYZE status ;
ANALYZE
dynacom=# EXPLAIN ANALYZE select count(*) from status where
assettable='vessels' and appname='ISM PMS' and apptblname='items' and
status='warn' and isvalid and assetidval=49;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4309.53..4309.53 rows=1 width=0) (actual
time=242.60..242.60 rows=1 loops=1)
-> Seq Scan on status (cost=0.00..4306.08 rows=1378 width=0) (actual
time=15.75..242.51 rows=50 loops=1)
Filter: ((assettable = 'vessels'::character varying) AND (appname
= 'ISM PMS'::character varying) AND (apptblname = 'items'::character
varying) AND (status = 'warn'::character varying) AND isvalid AND
(assetidval = 49))
Total runtime: 242.74 msec
(4 rows)
dynacom=#
dynacom=# VACUUM ANALYZE status ;
VACUUM
dynacom=# EXPLAIN ANALYZE select count(*) from status where
assettable='vessels' and appname='ISM PMS' and apptblname='items' and
status='warn' and isvalid and assetidval=49;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2274.90..2274.90 rows=1 width=0) (actual time=8.89..8.89
rows=1 loops=1) -> Index Scan using status_all on status
(cost=0.00..2274.34 rows=223 width=0) (actual time=8.31..8.83 rows=50
loops=1)
Index Cond: ((assettable = 'vessels'::character varying) AND
(assetidval = 49) AND (appname = 'ISM PMS'::character varying) AND
(apptblname = 'items'::character varying) AND (status = 'warn'::character
varying))
Filter: isvalid
Total runtime: 8.98 msec
(5 rows)
dynacom=#
> Oh, and we should probably shift this discussion to the PGSQL-PERFORMANCE
> list.
>
OK.
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 4+ messages in thread
* DB slowness after upgrade from Postgres 9.1 to 9.4
@ 2017-11-10 10:58 p kirti <kirtip92@gmail.com>
2017-11-12 19:37 ` Re: DB slowness after upgrade from Postgres 9.1 to 9.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: p kirti @ 2017-11-10 10:58 UTC (permalink / raw)
To: pgsql-performance
Hi all,
We have recently upgraded our project with a huge DB from Postgres v9.1 to
v9.4. The whole system performance has degraded alarmingly after the
upgrade. Simple operations that were taking only a few seconds in Postgres
9.1 are now taking minutes of time.
The problem is not specific to one query orany particular kind of query.
Its been generic and overall system has become very slow.
We tried running 'VACUUM ANALYZE' on the DB and that seemed to be helpful
too. But the improvement after this is nowhere close to the performance we
had in 9.1.
We tried changing some of the performance parameters in the
postgres.confirm as follows (our Postgres server has an 8GB RAM) -
shared_buffers = 200MB
maintenance_work_mem = 1000MB
default_statistics_target = 1000
effective_cache_size = 4000MB
And these made absolutely no difference to the query execution time.
The strangest part of the problem is when I EXPLAIN ANALYZE the same query
multiple times in the same Postgres server, it gives me different execution
times every time ranging from 45 ms to 181 ms.
We are absolutely clueless on how to proceed. Any help would be greatly
appreciated.
Thanks in advance.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DB slowness after upgrade from Postgres 9.1 to 9.4
2017-11-10 10:58 DB slowness after upgrade from Postgres 9.1 to 9.4 p kirti <kirtip92@gmail.com>
@ 2017-11-12 19:37 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2017-11-12 19:37 UTC (permalink / raw)
To: p kirti <kirtip92@gmail.com>; +Cc: pgsql-performance
p kirti <kirtip92@gmail.com> writes:
> We have recently upgraded our project with a huge DB from Postgres v9.1 to
> v9.4. The whole system performance has degraded alarmingly after the
> upgrade. Simple operations that were taking only a few seconds in Postgres
> 9.1 are now taking minutes of time.
Are you certain nothing else changed? Same hardware, same OS, same
database configuration settings?
Once you've eliminated issues like that, you'd need to drill down deeper.
There's useful advice to help crystallize the situation at
https://wiki.postgresql.org/wiki/Slow_Query_Questions
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-11-12 19:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-30 16:03 Re: [SQL] 7.3 analyze & vacuum analyze problem Josh Berkus <josh@agliodbs.com>
2003-04-30 21:13 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2017-11-10 10:58 DB slowness after upgrade from Postgres 9.1 to 9.4 p kirti <kirtip92@gmail.com>
2017-11-12 19:37 ` Re: DB slowness after upgrade from Postgres 9.1 to 9.4 Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox