Hi Paolo and all,
Paolo Lucente, 10.04.2006 18:42:
> digging through the SQL scripts i've just noticed something bad: primary
> keys of default PostgreSQL tables v2-v5 are just missing the 'vlan' field
> (while it correctly appears in equivalent MySQL/SQLite 3.x schemas).
> This may explain the slowness. To verify this, can you please modify the
> script, place the 'vlan' field inside the primary key (between mac_dst
> and ip_src), create the default table again and see whether everything
> now works correctly ? Surprised that we have not caught this before.
> Let me know.
I created a new table with this index:
"acct_pk" primary key, btree (agent_id, class_id, mac_src, mac_dst,
vlan, ip_src, ip_dst, port_src, port_dst, ip_proto, tos, stamp_inserted)
and I still have the problem. There is alway a long lasting postmaster
process:
8115 ?? D 0:02.91 postmaster: pmacct pmacct [local] UPDATE (postgres)
which blocks the other DB Writer processes.
My other table with these indexes:
"acct_v5_idx" btree (ip_src, ip_dst, stamp_inserted)
"stamp_inserted_idx" hash (stamp_inserted)
still works fine with 14731769 rows.
I think the problem is not the updating of the data itself, but updating
the complex primary key. An index of (ip_src, ip_dst, stamp_inserted) is
fast enough to find entries, and easy enough to maintain.
BTW.: What is the best indexing to make a select with "WHERE
stamp_inserted>=2006-04-21 20:17:55 AND stamp_inserted<2006-04-21
21:17:55" fast? I guess a btree index, but maybe there's something better?
Cheers,
Sven
--
Sven Anderson
Institute for Informatics - http://www.ifi.informatik.uni-goettingen.de
Georg-August-Universitaet Goettingen
Lotzestr. 16-18, 37083 Goettingen, Germany
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
