Thread: tuning

Hi,

Sorry for my last empty message.

I have a pgsql database containing DHCP lease informtation,one table for
each month.
As we get more and more customers there will be more rows in the tables. The
table for august
now contains about 2.7 million rows. There is about 5 inserts per second.

Table definition:

start | boolean
ts    | timestamp
mac   | macaddr
port  | character varying(50)
ip    | inet

There are indexes on ts,mac,port and ip.
Database is running under Linux 2.2 on a Compaq with a P3 800 and 256MB
memory.

Now for the problem :) I get what I think is very poor response times,about
90-140s to get a result when searching on ip.

The only options I have set in postgresql.conf is:
    wal_files = 8
    wal_sync_method = fdatasync

What can I do to tune this database besides memory upgrade and adding a
additional CPU?

Kind regards,
Daniel Lundqvist

On Thu, 30 Aug 2001, Daniel Lundqvist wrote:

> I have a pgsql database containing DHCP lease informtation,one table for
> each month.
> As we get more and more customers there will be more rows in the tables. The
> table for august
> now contains about 2.7 million rows. There is about 5 inserts per second.
>
> Table definition:
>
> start | boolean
> ts    | timestamp
> mac   | macaddr
> port  | character varying(50)
> ip    | inet
>
> There are indexes on ts,mac,port and ip.
> Database is running under Linux 2.2 on a Compaq with a P3 800 and 256MB
> memory.
>
> Now for the problem :) I get what I think is very poor response times,about
> 90-140s to get a result when searching on ip.
>
> The only options I have set in postgresql.conf is:
>     wal_files = 8
>     wal_sync_method = fdatasync
>
> What can I do to tune this database besides memory upgrade and adding a
> additional CPU?

Have you been running vacuum analyze?  What are the queries you're running
and what does explain show for them?