Skip site navigation (1) Skip section navigation (2)

CPU and RAM

From: Harry Jackson <harryjackson(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: CPU and RAM
Date: 2005-12-22 01:20:16
Message-ID: 45b42ce40512211720t164ea389wa5bfd0cbae6c80e2@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
I am currently using a dual Opteron (248) single core system (RAM
PC3200) and for a change I am finding that the bottleneck is not disk
I/O but CPU/RAM (not sure which). The reason for this is that the most
frequently accessed tables/indexes are all held in RAM and when
querying the database there is almost no disk activity which is great,
most of the time. However, the database is growing and this database
is supporting an OLTP system where the retrieval of the data is an
order of magnitude more important than the insertion and general
upkeep of the data. It supports a search engine[0] and contains a
reverse index, lexicon and the actual data table (currently just under
2Gb for the three tables and associated indexes).

At the moment everything is working OK but I am noticing an almost
linear increase in time to retrieve data from the database as the data
set increases in size. Clustering knocks the access times down by 25%
but it also knocks users off the website and can take up to 30 minutes
which is hardly an ideal scenario. I have also considered partitioning
the tables up using extendible hashing and tries to allocate the terms
in the index to the correct table but after some testing I noticed no
noticeable gain using this method which surprised me a bit.

The actual size of the database is not that big (4Gb) but I am
expecting this to increase to at least 20Gb over the next year or so.
This means that search times are going to jump dramatically which also
means the site becomes completely unusable. This also means that
although disk access is currently low I am eventually going to run out
of RAM and require a decent disk subsystem.

Do people have any recommendations as to what hardware would alleviate
my current CPU/RAM problem but with a mind to the future would still
be able to cope with heavy disk access. My budget is about £2300/$4000
which is not a lot of money when talking databases so suggestions of a
Sun Fire T2000 or similar systems will be treated with the utmost
disdain ;) unless you are about to give me one to keep.

--
Harry
http://www.hjackson.org
http://www.uklug.co.uk


Before anyone asks I have considered using tsearch2.

Responses

pgsql-performance by date

Next:From: Madison KellyDate: 2005-12-22 02:03:18
Subject: MySQL is faster than PgSQL but a large margin in my program... any ideas why?
Previous:From: Steinar H. GundersonDate: 2005-12-22 01:08:23
Subject: Re: Speed of different procedural language

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group