Mailbox-Line: From marcos-p-garcia@ptinovacao.pt  Thu Jun 27 13:23:07 2002
Subject: Re: Slow SELECT -> Growing Database
From: Marcos Garcia <marcos-p-garcia@ptinovacao.pt>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
In-Reply-To: <20020624143954.K59728-100000@megazone23.bigpanda.com>
References: <20020624143954.K59728-100000@megazone23.bigpanda.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-HqX9eT7/gmUW2GMk1pYp"
Date: 27 Jun 2002 16:24:04 +0100
Message-Id: <1025191444.5705.80.camel@sargao>
Mime-Version: 1.0

--=-HqX9eT7/gmUW2GMk1pYp
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Mon, 2002-06-24 at 22:43, Stephan Szabo wrote:
> On 24 Jun 2002, Marcos Garcia wrote:
>=20
> 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.
>=20
> 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.
>=20


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
=20
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
- =3D~ 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 =3D 0;
eval {
	select id from table where state=3D1 for update limit 10;
	update table set locked =3D true where id in (?);
};
if (ERROR){
	ROLLBACK;
}else{
	COMMIT;
}
Autocommit =3D 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=20
---------------------------------+----------+-----------
 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


--=20
M.P.Garcia
PT Inova=E7=E3o, SA
Servi=E7os e Redes M=F3veis=20
Rua Jos=E9 Ferreira Pinto Basto - 3810 Aveiro
Tel: 234 403 253  -  Fax: 234 424 160
E-mail: marcos-p-garcia@ptinovacao.pt

--=-HqX9eT7/gmUW2GMk1pYp
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQA9Gy4SGRIrLqlAVg0RAn/MAJ9LH8d7AZQBwOoBbTpuMHj/YYa/vACfRs6Q
/H3zkXfbhdSUemgu7ubdVOE=
=YN4h
-----END PGP SIGNATURE-----

--=-HqX9eT7/gmUW2GMk1pYp--