Re: Performance problems with large telemetric datasets on 7.4.2

From: "Dan Langille" <dan(at)langille(dot)org>
To: "Sven Clement" <sven(at)dsign(dot)lu>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance problems with large telemetric datasets on 7.4.2
Date: 2007-08-03 14:17:58
Message-ID: 46B300D6.23687.147A9B8B@dan.langille.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Mark Lewis 2007-08-03 14:20:36 Re: Performance problems with large telemetric datasets on 7.4.2
Previous Message Sven Clement 2007-08-03 13:52:38 Performance problems with large telemetric datasets on 7.4.2