pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Slow SELECT -> Growing Database
4+ messages / 4 participants
[nested] [flat]
* Re: Slow SELECT -> Growing Database
@ 2002-06-27 15:24 Marcos Garcia <marcos-p-garcia@ptinovacao.pt>
2002-06-27 17:11 ` Re: Slow SELECT -> Growing Database Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-06-27 19:01 ` Re: Slow SELECT -> Growing Database Marc Spitzer <marc@oscar.eng.cv.net>
2002-06-28 01:46 ` Re: Slow SELECT -> Growing Database Tatsuo Ishii <t-ishii@sra.co.jp>
0 siblings, 3 replies; 4+ messages in thread
From: Marcos Garcia @ 2002-06-27 15:24 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
On Mon, 2002-06-24 at 22:43, Stephan Szabo wrote:
> On 24 Jun 2002, Marcos Garcia wrote:
>
> Hmm, that should only happen if you're doing alot of updates or deletes
> I would guess (updates would cause it to slow down as well since it's
> similar to a delete and insert under MVCC). And frequent normal vacuums
> should do that as well unless the table has high turnover.
>
> Well, if you haven't yet, you might try upping the sort_mem and
> shared_buffers amounts, although I think the former would only
> potentially cut down the difference between 32s and 59s and the
> latter would probably only help on a later use of the call if the
> buffer is big enough to hold a significant portion of the pages.
>
The problem isn't in the select.
I realize that my database is growing and growing.
I've two tables that have, lets say, 120.000 records each, and:
- delete about 30.000 records a day from each table
- insert about 30.000 records a day on each table
- update each record at least 4 four times
I've two other ones, that were mentioned in my previous emails, that
have 12.000 records each, and:
- insert 48 records a day in each table
- =~ 120.000 updates in the last inserted records.
Track the problem:
# df -h /var/lib/pgsql -> 7.8 GB (I create this database 4 month's ago)
# pg_dump dbnane > dbname.dump
# dropdb dbname
# createdb dbname
# psql dbaname < dbname.dump
# df -h /var/lib/pgsql -> 140 M
I don't understand why the database is growing????
And is still growing.
I make a vacuum -z -d dbname everyday (I checked if it really runs).
The only reason, for the growing of the database space, that i can see
for now, is described in the following lines.
I've some perl programs that are concurrent in the access to the
database, so i've have to make "SELECT FOR UPDATE". The algorithm of the
program is:
Autocommit = 0;
eval {
select id from table where state=1 for update limit 10;
update table set locked = true where id in (?);
};
if (ERROR){
ROLLBACK;
}else{
COMMIT;
}
Autocommit = 1;
What are the major reasons for the growing of the database disk space?
Maybe the following information is important:
dbname> select relname, relpages,reltuples from pg_class order by relpages desc limit 10;
relname | relpages | reltuples
---------------------------------+----------+-----------
sms_recv | 30911 | 46801
sms_send | 7026 | 49119
sms_recv_unique_idclimsgidclien | 4561 | 46801
sms_recv_pkey | 3647 | 46801
sms_recv_msgidclient_idx | 3615 | 46801
recv_total | 1864 | 8120
send_total | 1378 | 12315
sms_send_pkey | 991 | 49119
sms_send_idclient_idx | 913 | 49119
recv_total_idclismsdate | 686 | 8120
(10 rows)
I really appreciate your help,
thanks,
M.P.Garcia
--
M.P.Garcia
PT Inovação, SA
Serviços e Redes Móveis
Rua José Ferreira Pinto Basto - 3810 Aveiro
Tel: 234 403 253 - Fax: 234 424 160
E-mail: marcos-p-garcia@ptinovacao.pt
Attachments:
signature.asc (application/pgp-signature, 231B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow SELECT -> Growing Database
2002-06-27 15:24 Re: Slow SELECT -> Growing Database Marcos Garcia <marcos-p-garcia@ptinovacao.pt>
@ 2002-06-27 17:11 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2002-06-27 17:11 UTC (permalink / raw)
To: Marcos Garcia <marcos-p-garcia@ptinovacao.pt>; +Cc: pgsql-sql
On 27 Jun 2002, Marcos Garcia wrote:
> On Mon, 2002-06-24 at 22:43, Stephan Szabo wrote:
> > On 24 Jun 2002, Marcos Garcia wrote:
> >
> > Hmm, that should only happen if you're doing alot of updates or deletes
> > I would guess (updates would cause it to slow down as well since it's
> > similar to a delete and insert under MVCC). And frequent normal vacuums
> > should do that as well unless the table has high turnover.
> >
> > Well, if you haven't yet, you might try upping the sort_mem and
> > shared_buffers amounts, although I think the former would only
> > potentially cut down the difference between 32s and 59s and the
> > latter would probably only help on a later use of the call if the
> > buffer is big enough to hold a significant portion of the pages.
> >
>
>
> The problem isn't in the select.
>
> I realize that my database is growing and growing.
>
> I've two tables that have, lets say, 120.000 records each, and:
> - delete about 30.000 records a day from each table
> - insert about 30.000 records a day on each table
> - update each record at least 4 four times
>
> I've two other ones, that were mentioned in my previous emails, that
> have 12.000 records each, and:
> - insert 48 records a day in each table
> - =~ 120.000 updates in the last inserted records.
>
> Track the problem:
>
> # df -h /var/lib/pgsql -> 7.8 GB (I create this database 4 month's ago)
>
> # pg_dump dbnane > dbname.dump
>
> # dropdb dbname
>
> # createdb dbname
>
> # psql dbaname < dbname.dump
>
> # df -h /var/lib/pgsql -> 140 M
>
> I don't understand why the database is growing????
Well, I'd suggest starting with doing some reindex (or drop/create)
indexes on big indexes (their space doesn't get reclaimed by vacuum
currently). Another possibility is that you've got more pages having
open space than the free space map has space for so you might want
to increase max_fsm_pages and see if that helps. And, if you're
effectively updating the table entirely 4 times in the day, you might want
to run a vacuum more often (you probably don't need the analyze)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow SELECT -> Growing Database
2002-06-27 15:24 Re: Slow SELECT -> Growing Database Marcos Garcia <marcos-p-garcia@ptinovacao.pt>
@ 2002-06-27 19:01 ` Marc Spitzer <marc@oscar.eng.cv.net>
2 siblings, 0 replies; 4+ messages in thread
From: Marc Spitzer @ 2002-06-27 19:01 UTC (permalink / raw)
To: pgsql-sql
On Thu, Jun 27, 2002 at 04:24:04PM +0100, Marcos Garcia wrote:
> On Mon, 2002-06-24 at 22:43, Stephan Szabo wrote:
> > On 24 Jun 2002, Marcos Garcia wrote:
> >
> > Hmm, that should only happen if you're doing alot of updates or deletes
> > I would guess (updates would cause it to slow down as well since it's
> > similar to a delete and insert under MVCC). And frequent normal vacuums
> > should do that as well unless the table has high turnover.
> >
> > Well, if you haven't yet, you might try upping the sort_mem and
> > shared_buffers amounts, although I think the former would only
> > potentially cut down the difference between 32s and 59s and the
> > latter would probably only help on a later use of the call if the
> > buffer is big enough to hold a significant portion of the pages.
> >
>
>
> The problem isn't in the select.
>
> I realize that my database is growing and growing.
>
> I've two tables that have, lets say, 120.000 records each, and:
> - delete about 30.000 records a day from each table
> - insert about 30.000 records a day on each table
> - update each record at least 4 four times
>
if you have a lot of change going on in your db do not forget
to reindex your tables every so often. Index space is not
reclamed by vacuum, the first time can take a long time. Also
in 7.2+ vacuum got a new keyword 'full'. "vacuum full' reclaims
disk space like in 7.1, the devault vacuum just marks rows that
were deleted as reusable.
lookin at your numbers you have 60,000 definate index entries
created each day, per index. And you have 120,000 x 4(min)=
480,000 endex entries created per index, if I remember correctly
update is handled by inserting a new row and deleteing the old
row. So it looks like 540,000 index entries changed per day.
good luck
marc
> I've two other ones, that were mentioned in my previous emails, that
> have 12.000 records each, and:
> - insert 48 records a day in each table
> - =~ 120.000 updates in the last inserted records.
>
> Track the problem:
>
> # df -h /var/lib/pgsql -> 7.8 GB (I create this database 4 month's ago)
>
> # pg_dump dbnane > dbname.dump
>
> # dropdb dbname
>
> # createdb dbname
>
> # psql dbaname < dbname.dump
>
> # df -h /var/lib/pgsql -> 140 M
>
> I don't understand why the database is growing????
> And is still growing.
> I make a vacuum -z -d dbname everyday (I checked if it really runs).
>
> The only reason, for the growing of the database space, that i can see
> for now, is described in the following lines.
>
> I've some perl programs that are concurrent in the access to the
> database, so i've have to make "SELECT FOR UPDATE". The algorithm of the
> program is:
>
> Autocommit = 0;
> eval {
> select id from table where state=1 for update limit 10;
> update table set locked = true where id in (?);
> };
> if (ERROR){
> ROLLBACK;
> }else{
> COMMIT;
> }
> Autocommit = 1;
>
>
> What are the major reasons for the growing of the database disk space?
>
> Maybe the following information is important:
>
> dbname> select relname, relpages,reltuples from pg_class order by relpages desc limit 10;
>
> relname | relpages | reltuples
> ---------------------------------+----------+-----------
> sms_recv | 30911 | 46801
> sms_send | 7026 | 49119
> sms_recv_unique_idclimsgidclien | 4561 | 46801
> sms_recv_pkey | 3647 | 46801
> sms_recv_msgidclient_idx | 3615 | 46801
> recv_total | 1864 | 8120
> send_total | 1378 | 12315
> sms_send_pkey | 991 | 49119
> sms_send_idclient_idx | 913 | 49119
> recv_total_idclismsdate | 686 | 8120
> (10 rows)
>
>
>
> I really appreciate your help,
>
> thanks,
>
> M.P.Garcia
>
>
> --
> M.P.Garcia
> PT Inovação, SA
> Serviços e Redes Móveis
> Rua José Ferreira Pinto Basto - 3810 Aveiro
> Tel: 234 403 253 - Fax: 234 424 160
> E-mail: marcos-p-garcia@ptinovacao.pt
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow SELECT -> Growing Database
2002-06-27 15:24 Re: Slow SELECT -> Growing Database Marcos Garcia <marcos-p-garcia@ptinovacao.pt>
@ 2002-06-28 01:46 ` Tatsuo Ishii <t-ishii@sra.co.jp>
2 siblings, 0 replies; 4+ messages in thread
From: Tatsuo Ishii @ 2002-06-28 01:46 UTC (permalink / raw)
To: marcos-p-garcia@ptinovacao.pt; +Cc: sszabo@megazone23.bigpanda.com; pgsql-sql
> The problem isn't in the select.
>
> I realize that my database is growing and growing.
>
> I've two tables that have, lets say, 120.000 records each, and:
> - delete about 30.000 records a day from each table
> - insert about 30.000 records a day on each table
> - update each record at least 4 four times
>
> I've two other ones, that were mentioned in my previous emails, that
> have 12.000 records each, and:
> - insert 48 records a day in each table
> - =~ 120.000 updates in the last inserted records.
>
> Track the problem:
>
> # df -h /var/lib/pgsql -> 7.8 GB (I create this database 4 month's ago)
>
> # pg_dump dbnane > dbname.dump
>
> # dropdb dbname
>
> # createdb dbname
>
> # psql dbaname < dbname.dump
>
> # df -h /var/lib/pgsql -> 140 M
>
> I don't understand why the database is growing????
> And is still growing.
> I make a vacuum -z -d dbname everyday (I checked if it really runs).
You have never mentioned PostgreSQL verison you are using, so...
(1) if that's 7.2.x, you need to slightly increase FSM parameters in
postgresql.conf. As you have ~8GB database, probably you need to
set max_fsm_pages to: 8*1024*1024*1024/8192 = 1048576. It will
need about 7MB more shmem, but it would not be too much for modern
PC.
(2) if that's 7.1.x, or earlier, you need to vacuum and reindex more
frequently.
--
Tatsuo Ishii
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-06-28 01:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-27 15:24 Re: Slow SELECT -> Growing Database Marcos Garcia <marcos-p-garcia@ptinovacao.pt>
2002-06-27 17:11 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-06-27 19:01 ` Marc Spitzer <marc@oscar.eng.cv.net>
2002-06-28 01:46 ` Tatsuo Ishii <t-ishii@sra.co.jp>
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