Re: Performance problems with large telemetric datasets on 7.4.2

On 3 Aug 2007 at 6:52, Sven Clement wrote:

> Hello everybody,
>
> as I'm new to this list I hope that it is the right place to post this
> and also the right format, so if I'm committing an error, I apologize
> in advance.
>
> First the background of my request:
>
> I'm currently employed by an enterprise which has approx. 250 systems
> distributed worldwide which are sending telemetric data to the main
> PostgreSQL. The remote systems are generating about 10 events per
> second per system which accumulates to about 2500/tps. The data is
> stored for about a month before it is exported and finally deleted
> from the database. On the PostgreSQL server are running to databases
> one with little traffic (about 750K per day) and the telemetric
> database with heavy write operations all around the day (over 20
> million per day). We already found that the VACUUM process takes
> excessively long and as consequence the database is Vacuumed
> permanently.
>
> The hardware is a IBM X306m Server, 3.2 GHz HT (Pentium IV), 1 GB RAM
> and 2x 250 GB HDD (SATA-II) with ext3 fs, one of the HDD is dedicated
> to database. OS is Debian 3.1 Sarge with PostgreSQL 7.4.7
> (7.4.7-6sarge1) with the libpq frontend library.
>
> Now the problem:
>
> The problem we are experiencing is that our queries are slowing down
> continuously even if we are performing queries on the index which is
> the timestamp of the event, a simple SELECT query with only a simple
> WHERE clause (< or >) takes very long to complete. So the database
> becomes unusable for production use as the data has to be retrieved
> very quickly if we want to act based on the telemetric data.

Have you confirmed via explain (or explain analyse) that the index is
being used?

> So I'm asking me if it is useful to update to the actual 8.2 version
> and if we could experience performance improvement only by updating.

There are other benefits from upgrading, but you may be able to solve
this problem without upgrading.

--
Dan Langille - http://www.langille.org/
Available for hire: http://www.freebsddiary.org/dan_langille.php