Message-ID: <881035ea0708030652r37b25a86g61e88e1f5e95eab2@mail.gmail.com>
Date: Fri, 3 Aug 2007 06:52:38 -0700
From: "Sven Clement" <sven@dsign.lu>
To: pgsql-performance@postgresql.org
Subject: Performance problems with large telemetric datasets on 7.4.2
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_77282_4225123.1186149158700"

------=_Part_77282_4225123.1186149158700
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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.

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.

Thank you for your answers,
Sven Clement

------=_Part_77282_4225123.1186149158700
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hello everybody,<br><br>as I&#39;m new to this list I hope that it is the right place to post this and also the right format, so if I&#39;m committing an error, I apologize in advance.<br><br>First the background of my request:
<br><br>I&#39;m currently employed by an enterprise which has approx. 250 systems distributed worldwide which are sending telemetric data to the main PostgreSQL.<br>The remote systems are generating about 10 events per second per system which accumulates to about 2500/tps.
<br>The data is stored for about a month before it is exported and finally deleted from the database.<br>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).
<br>We already found that the VACUUM process takes excessively long and as consequence the database is Vacuumed permanently.<br><br>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.
<br>OS is Debian 3.1 Sarge with PostgreSQL 7.4.7 (7.4.7-6sarge1) with the libpq frontend library.<br><br>Now the problem:<br><br>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 (&lt; or &gt;) 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.
<br><br>So I&#39;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.<br><br>Thank you for your answers,<br>Sven Clement<br>

------=_Part_77282_4225123.1186149158700--