Re: Scaling concerns

Tsuraan,

"Select count(*) from bigtable" is testing your disk drive speed up till
about 300MB/s, after which it is CPU limited in Postgres.

My guess is that your system has a very slow I/O configuration, either due
to faulty driver/hardware or the configuration.

The first thing you should do is run a simple I/O test on your data
directory - write a file twice the size of memory using dd like this:

time bash -c "dd if=/dev/zero of=data_directory/bigfile bs=8k count=(2 *
memory_size / 8192) && sync"

time dd if=data_directory/bigfile of=/dev/null bs=8k

Then report the times here.

- Luke

On 12/16/06 9:26 AM, "tsuraan" <tsuraan(at)gmail(dot)com> wrote:

> I'm writing a webmail-type application that is meant to be used in a
> corporate environment. The core of my system is a Postgres database
> that is used as a message header cache. The two (relevant) tables
> being used are pasted into the end of this message. My problem is
> that, as the messages table increases to tens of millions of rows,
> pgsql slows down considerably. Even an operation like "select
> count(*) from messages" can take minutes, with a totally idle system.
> Postgres seems to be the most scalable Free database out there, so I
> must be doing something wrong.
>
> As for the most common strategy of having a slower (more rows)
> "archival" database and a smaller, faster "live" database, all the
> clients in the company are using their normal corporate email server
> for day-to-day email handling. The webmail is used for access email
> that's no longer on the corporate server, so it's not really simple to
> say which emails should be considered live and which are really
> out-of-date.
>
> My postgres settings are entirely default with the exception of
> shared_buffers being set to 40,000 and max_connections set to 400.
> I'm not sure what the meaning of most of the other settings are, so I
> haven't touched them. The machines running the database servers are
> my home desktop (a dual-core athlon 3200+ with 2GB RAM and a 120GB
> SATA II drive), and a production server with two dual-core Intel
> chips, 4 GB RAM, and a RAID 5 array of SATA II drives on a 3Ware 9550
> controller. Both machines are running Gentoo Linux with a 2.6.1x
> kernel, and both exhibit significant performance degradation when I
> start getting tens of millions of records.
>
> Any advice would be most appreciated. Thanks in advance!
>
> Tables:
>
> CREATE TABLE EmailAddresses (
> emailid SERIAL PRIMARY KEY, -- The unique identifier of this address
> name TEXT NOT NULL, -- The friendly name in the address
> addrspec TEXT NOT NULL, -- The user(at)domain part of the address
> UNIQUE(name, addrspec)
> );
>
> and
>
> CREATE TABLE Messages (
> -- Store info:
> msgkey BIGSERIAL PRIMARY KEY, -- Unique identifier for a message
> path TEXT NOT NULL, -- Where the message is on the file
> system
> inserted TIMESTAMP DEFAULT now(),-- When the message was fetched
> -- Message Info:
> msgid TEXT UNIQUE NOT NULL, -- Message's Message-Id field
> mfrom INTEGER -- Who sent the message
> REFERENCES EmailAddresses
> DEFAULT NULL,
> mdate TIMESTAMP DEFAULT NULL, -- Message "date" header field
> replyto TEXT DEFAULT NULL, -- Message-ID of replied-to message
> subject TEXT DEFAULT NULL, -- Message "subject" header field
> numatch INTEGER DEFAULT NULL, -- Number of attachments
> UNIQUE(path)
> );
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>