From: Dan Langille <dan@langille.org>
To: Sven Clement <sven@dsign.lu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems with large telemetric datasets on 7.4.2
Date: Fri, 03 Aug 2007 10:17:58 -0400
Message-ID: <46B300D6.23687.147A9B8B@dan.langille.org> (raw)
In-Reply-To: <881035ea0708030652r37b25a86g61e88e1f5e95eab2@mail.gmail.com>
References: <881035ea0708030652r37b25a86g61e88e1f5e95eab2@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dan@langille.org, sven@dsign.lu
Subject: Re: Performance problems with large telemetric datasets on 7.4.2
In-Reply-To: <46B300D6.23687.147A9B8B@dan.langille.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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