Hi Aaron and all,
thank you for your reply.
Aaron Glenn, 08.04.2006 01:50:
> It could be a myriad of issues. Have you changed any of the default
> PostgreSQL settings? PostgreSQL, by default, is not configured for
> anything but pure compatibility - meaning it will certainly work, but
> not at it's best. For what it's worth, I had PostgreSQL and pmacct on
> an OpenBSD 3.8 box and experienced no problems past the 1.2million row
> mark (I shutdown that installation for unrelated reasons). If you are
> not familiar with PostgreSQL - definitely start reading here[1]
I changed the values of the kernel and PostgreSQL itself as proposed in
README.OpenBSD and INSTALL. But most of the stuff seems to relate to the
number of concurrent connections, which are not the problem. Now I removed
the primary key constraint of acct_v5:
CONSTRAINT acct_uni_pk PRIMARY KEY (agent_id, class_id, mac_src, mac_dst,
ip_src, ip_dst, port_src, port_dst, ip_proto, tos, stamp_inserted)
and replaced it with two indexes:
"acct_v5_idx" btree (ip_src, ip_dst, stamp_inserted)
"stamp_inserted_idx" hash (stamp_inserted)
Now I've got over the weekend 2.6 million rows, and it works ok so far. So
the maintaining of the complex index of the primary key seems to make a
big difference.
How long did it take for the 1.2 million rows in your setup? If the rows
are collected slow enough, then the updating of the table is no problem of
course. My machine is quite weak, with 900MHz PIII CPU, 320MB RAM and a
normal IDE hard disk. But with MySQL it was no problem, so I'm just comparing.
Cheers,
Sven
--
Sven Anderson
Institute for Informatics - http://www.ifi.informatik.uni-goettingen.de
Georg-August-Universitaet Goettingen
Lotzestr. 16-18, 37083 Goettingen, Germany
