Date: Thu, 7 Oct 2010 15:00:06 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Aaron Turner <synfinatic@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: large dataset with write vs read clients
Message-ID: <20101007190006.GA26232@tamriel.snowman.net>
References: <AANLkTimNJc=0ffgRO_gO6Vsq06=aqBYXZouhbv0kBe-i@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="6YFfOQ1DNaZEJxLS"
Content-Disposition: inline
In-Reply-To: <AANLkTimNJc=0ffgRO_gO6Vsq06=aqBYXZouhbv0kBe-i@mail.gmail.com>
User-Agent: Mutt/1.5.18 (2008-05-17)


--6YFfOQ1DNaZEJxLS
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Aaron Turner (synfinatic@gmail.com) wrote:
> The graphing front end CGI is all SELECT.  There's 12k tables today,
> and new tables are created each month. =20

That's a heck of alot of tables..  Probably more than you really need.
Not sure if reducing that number would help query times though.

> The number of rows per table
> is 100-700k, with most in the 600-700K range.  190GB of data so far.
> Good news is that queries have no joins and are limited to only a few
> tables at a time.

Have you got indexes and whatnot on these tables?

> Basically, each connection is taking about 100MB resident.  As we need
> to increase the number of threads to be able to query all the devices
> in the 5 minute window, we're running out of memory.  There aren't
> that many CGI connections at anyone one time, but obviously query
> performance isn't great, but honestly is surprisingly good all things
> considered.

I'm kind of suprised at each connection taking 100MB, especially ones
which are just doing simple inserts.

	Thanks,

		Stephen

--6YFfOQ1DNaZEJxLS
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkyuGLYACgkQrzgMPqB3kijd1wCfajSEegvQrrHk1l0B7JqABwqW
W7cAoJIWrXa1bY8egsaXSpAdpCJOCKfd
=Zhan
-----END PGP SIGNATURE-----

--6YFfOQ1DNaZEJxLS--