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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
