pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance issue
8+ messages / 8 participants
[nested] [flat]
* Performance issue
@ 2004-04-27 09:12 Michael L. Hostbaek <mich@the-lab.org>
2004-04-27 11:42 ` Re: Performance issue Paul Thomas <paul@tmsl.demon.co.uk>
2004-05-01 04:37 ` Re: Performance issue Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Michael L. Hostbaek @ 2004-04-27 09:12 UTC (permalink / raw)
To: pgsql-sql
Howdy,
I am expiriencing some performance issues, on a table in my postgres db.
I cron script is being run every night (while very low db activity),
that deletes all rows from the table, and injects a bunch of new data...
Every day between 60.000 and 100.000 rows.
Info:
ppdb=> select version();
version
------------------------------------------------------------------------------------------------
PostgreSQL 7.4.1 on i386-portbld-freebsd5.2, compiled by GCC cc (GCC)
3.3.3 [FreeBSD] 20031106
(1 row)
ppdb=> \d itxhas
Table "public.itxhas"
Column | Type |
Modifiers
-------------+-----------------------------+----------------------------------------------------
hasid | integer | not null default
nextval('itxhas_hasid_seq'::text)
partno | character varying(60) |
mfg | character varying(25) |
qty | character varying(20) |
condition | character varying(20) |
gescode | character varying(10) |
cmup | numeric(14,2) |
create_date | timestamp without time zone |
posted_date | timestamp without time zone |
status | character varying(15) |
company_id | integer |
die_date | timestamp without time zone |
Indexes:
"itx_create_date_idx" btree (create_date
Every time I need to select something from this table, I feel it takes
way too long..
I have run vacuum analyze without improvement.
ppdb=> explain select count(*) from itxhas;
QUERY PLAN
--------------------------------------------------------------------
Aggregate (cost=78472.86..78472.86 rows=1 width=0)
-> Seq Scan on itxhas (cost=0.00..78253.09 rows=87909 width=0)
(2 rows)
ppdb=> explain analyze select count(*) from itxhas;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=78472.86..78472.86 rows=1 width=0) (actual
time=24242.717..24242.719 rows=1 loops=1)
-> Seq Scan on itxhas (cost=0.00..78253.09 rows=87909 width=0)
(actual time=23763.883..24122.221 rows=87909 loops=1)
Total runtime: 24242.844 ms
(3 rows)
Is this normal ? If I run the same select on another table in the same
database with ~40.000 rows, it takes approx 820.00ms...
Any ideas ?
/mich
--
Best Regards,
Michael L. Hostbaek
*/ PGP-key available upon request /*
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance issue
2004-04-27 09:12 Performance issue Michael L. Hostbaek <mich@the-lab.org>
@ 2004-04-27 11:42 ` Paul Thomas <paul@tmsl.demon.co.uk>
1 sibling, 0 replies; 8+ messages in thread
From: Paul Thomas @ 2004-04-27 11:42 UTC (permalink / raw)
To: Michael L . Hostbaek <mich@the-lab.org>; +Cc: pgsql-sql
On 27/04/2004 10:12 Michael L. Hostbaek wrote:
> [snip]
> Is this normal ? If I run the same select on another table in the same
> database with ~40.000 rows, it takes approx 820.00ms...
You would probably get better answers on the correct list but my guess is
that your fsm setting might be too low for the number of dead tuples you
are trying to reclaim so the vacuum is not clearing all the dead stuff
out. There's plenty of stuff about this in the archives and the docs.
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance issue
2004-04-27 09:12 Performance issue Michael L. Hostbaek <mich@the-lab.org>
@ 2004-05-01 04:37 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2004-05-01 04:37 UTC (permalink / raw)
To: Michael L. Hostbaek <mich@the-lab.org>; +Cc: pgsql-sql
"Michael L. Hostbaek" <mich@the-lab.org> writes:
> I cron script is being run every night (while very low db activity),
> that deletes all rows from the table, and injects a bunch of new data...
You should vacuum in between ... or even better, do the deletion with
TRUNCATE.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Performance issue
@ 2005-08-30 08:38 Ricky Sutanto <ricky@connexiasolutions.com>
2005-09-06 18:32 ` Re: Performance issue Tim Goodaire <tgoodair@ca.afilias.info>
0 siblings, 1 reply; 8+ messages in thread
From: Ricky Sutanto @ 2005-08-30 08:38 UTC (permalink / raw)
To: pgsql-sql
I use Apache Web Server and PostgreSQL 7.3 to collect data everyday. Now it
has been 5 month since I install that server.
I wonder why now my web very slow to retrieve and display data?
When I check the memory, I found that postgreSQL client seem not release
after allocate. I try to find bug on my script, but everything look clean to
me.
Anyone have experience like me.. please share info with me
Thanks anyway,
Ricky Sutanto
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance issue
2005-08-30 08:38 Performance issue Ricky Sutanto <ricky@connexiasolutions.com>
@ 2005-09-06 18:32 ` Tim Goodaire <tgoodair@ca.afilias.info>
0 siblings, 0 replies; 8+ messages in thread
From: tgoodair@ca.afilias.info @ 2005-09-06 18:32 UTC (permalink / raw)
To: pgsql-sql
On Tue, Aug 30, 2005 at 03:38:52PM +0700, Ricky Sutanto wrote:
> I use Apache Web Server and PostgreSQL 7.3 to collect data everyday. Now it
> has been 5 month since I install that server.
>
> I wonder why now my web very slow to retrieve and display data?
> When I check the memory, I found that postgreSQL client seem not release
> after allocate. I try to find bug on my script, but everything look clean to
> me.
>
> Anyone have experience like me.. please share info with me
You haven't really provided much information on your problem. Are you
vacuuming regularly?
Tim
>
> Thanks anyway,
>
>
> Ricky Sutanto
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--
Tim Goodaire 416-673-4126 tgoodair@ca.afilias.info
Database Administrator, Afilias Canada Corp.
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Performance issue
@ 2005-08-30 08:42 Ricky Sutanto <ricky.sutanto@gmail.com>
2005-08-30 12:34 ` Re: Performance issue Michael Fuhr <mike@fuhr.org>
2005-08-30 12:42 ` Re: Performance issue Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 8+ messages in thread
From: Ricky Sutanto @ 2005-08-30 08:42 UTC (permalink / raw)
To: pgsql-sql
I use Apache Web Server and PostgreSQL 7.3 to collect data everyday. Now it
has been 5 month since I install that server.
I wonder why now my web very slow to retrieve and display data?
When I check the memory, I found that postgreSQL client seem not release
after allocate. I try to find bug on my script, but everything look clean to
me.
Anyone have experience like me.. please share info with me
Thanks anyway,
Ricky Sutanto
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance issue
2005-08-30 08:42 Performance issue Ricky Sutanto <ricky.sutanto@gmail.com>
@ 2005-08-30 12:34 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 8+ messages in thread
From: Michael Fuhr @ 2005-08-30 12:34 UTC (permalink / raw)
To: Ricky Sutanto <ricky.sutanto@gmail.com>; +Cc: pgsql-sql
On Tue, Aug 30, 2005 at 03:42:06PM +0700, Ricky Sutanto wrote:
> I use Apache Web Server and PostgreSQL 7.3 to collect data everyday. Now it
> has been 5 month since I install that server.
Which release of PostgreSQL 7.3? What operating system and version?
> I wonder why now my web very slow to retrieve and display data?
Are only web-based applications slow, or are the same queries issued
from another client (e.g., psql) also slow? Are you running VACUUM
ANALYZE on a regular basis? If not then you might have a lot of
dead tuples in your database. In 7.3 you might also want to REINDEX
periodically (this isn't as necessary in later versions). For more
information, see "Routine Database Maintenance Tasks" in the
documentation:
http://www.postgresql.org/docs/7.3/static/maintenance.html
If queries are still slow after database maintenance then please
post the EXPLAIN ANALYZE output of an example query. Additional
info like table descriptions and sizes would also be helpful.
> When I check the memory, I found that postgreSQL client seem not release
> after allocate. I try to find bug on my script, but everything look clean to
> me.
What client, and what do you mean by "seem not release after
allocate"? What are you doing and how are you checking it?
What language are you using?
BTW, the pgsql-performance list would be more appropriate for
discussing performance issues. Pgsql-sql is supposed to be for
SQL-related matters (how to write queries, etc.).
--
Michael Fuhr
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance issue
2005-08-30 08:42 Performance issue Ricky Sutanto <ricky.sutanto@gmail.com>
@ 2005-08-30 12:42 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 8+ messages in thread
From: Bruno Wolff III @ 2005-08-30 12:42 UTC (permalink / raw)
To: Ricky Sutanto <ricky.sutanto@gmail.com>; +Cc: pgsql-sql
On Tue, Aug 30, 2005 at 15:42:06 +0700,
Ricky Sutanto <ricky.sutanto@gmail.com> wrote:
>
> I use Apache Web Server and PostgreSQL 7.3 to collect data everyday. Now it
> has been 5 month since I install that server.
>
> I wonder why now my web very slow to retrieve and display data?
> When I check the memory, I found that postgreSQL client seem not release
> after allocate. I try to find bug on my script, but everything look clean to
> me.
>
> Anyone have experience like me.. please share info with me
You haven't given us much to go on. You might have a problem with not properly
vacuuming or analyzing or perhaps an issue with index bloat. There are also
a number of performance benefits to using more recent versions of postgres.
If you want some more specific help you should show us explain analyse
resulta for some of your slow queries.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-09-06 18:32 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-27 09:12 Performance issue Michael L. Hostbaek <mich@the-lab.org>
2004-04-27 11:42 ` Paul Thomas <paul@tmsl.demon.co.uk>
2004-05-01 04:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-30 08:38 Performance issue Ricky Sutanto <ricky@connexiasolutions.com>
2005-09-06 18:32 ` Tim Goodaire <tgoodair@ca.afilias.info>
2005-08-30 08:42 Performance issue Ricky Sutanto <ricky.sutanto@gmail.com>
2005-08-30 12:34 ` Michael Fuhr <mike@fuhr.org>
2005-08-30 12:42 ` Bruno Wolff III <bruno@wolff.to>
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