Thanks Paolo,
Creating index decreased query time from 1min to 5 sec hehe.
The searchs are often by ip_src and ip_dst, between 2 dates
(stamp_inserted). I think that indexes just for ip_src and ip_dst are fine.
Cheers.
2010/4/28 Paolo Lucente pa...@pmacct.net
Hi Sergio,
The sql_history drives temporal aggregation by splitting traffic data
into time-bins. You configured 5 minutes time-bins. See the behaviour
of the 'stamp_inserted' field, ie. do SELECT DISTINCT(stamp_inserted)
FROM table ORDER BY stamp_inserted, to get this more clear. The
roundoff thing does some nice time alignment.
So, increasing the size of the time-bins will be certainly beneficial
but that's not the entire win: important thing is understanding what
you are trying to achieve by putting data into PostgreSQL and choose
a suitable spatial aggregation method (the aggregate directive) for
your task. Suitable here means: build a data-set fitting your project
requirements with the (nearly) least possible amount of records.
Then indexing. It gets useful speeding up SQL queries with conditions,
ie. WHERE ip_src = 'x.x.x.x'. If that's the kind of query you run the
most, try inserting in the SQL table schema a INDEX (ip_src), after
the PRIMARY KEY line. Perhaps you might want to do the same with the
ip_dst field.
Cheers,
Paolo
On Tue, Apr 27, 2010 at 10:37:28PM -0300, Sergio Charpinel Jr. wrote:
I'm getting low perfomance while selecting from psql and from FloX.
For example, selecting * WHERE ip_src = 'x.x.x.x'
How can I do a more compact aggregation? You mean by getting samples ?
Actually, I did not understand very well sql_history and roundoff
concepts,
but increasing history would do the trick?
Thanks for answering.
Cheers.
2010/4/27 Paolo Lucente pa...@pmacct.net
Hi Sergio,
I don't know FloX very well - hence would be good information to know
which specific SQL queries are performing bad. Maybe there is room to
improve indexing.
Is it also your goal to store every micro-flow into the SQL database?
Any chance a more compact aggregation method would fit the bill?
Cheers,
Paolo
On Tue, Apr 27, 2010 at 11:36:50AM -0300, Sergio Charpinel Jr. wrote:
Hi,
I'm using pmacct 0.12.1 with PostgreSQL 8.4 and FloX. i'm running
pmacctd
and nfacctd daemons (probe and collector).
After 1 day running, I'm getting 1+min to make a query.
Is there any config in pmacct and PostgreSQL that I can do improve
this?
Here is my config in pmacct:
pmacctd.conf:
daemonize: true
aggregate: src_host, src_port, dst_host, dst_port, proto, tcpflags
plugins: nfprobe
nfprobe_receiver: x.x.x.x
nfprobe_version: 9
interface: eth1
nfacctd.conf:
daemonize: true
pidfile: /var/run/nfacctd.pid
aggregate: src_host,dst_host, src_port, dst_port, proto, tcpflags
plugins: pgsql
sql_table_version: 7
sql_db: x
sql_host: x.x.x.x
sql_passwd: xxx
sql_user: x
sql_refresh_time: 300
sql_history: 5m
sql_history_roundoff: m
sql_dont_try_update: true
nfacctd_port:
nfacctd_ip:
In PostgreSQL, I did not change the default config in CentOS 5.4.
Thanks in advance!
--
Sergio Roberto Charpinel Jr.
___
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
--
Sergio Roberto Charpinel Jr.
--
Sergio Roberto Charpinel Jr.
___
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
