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: xxxxx > > > sql_host: x.x.x.x > > > sql_passwd: xxxxxxx > > > sql_user: xxxxx > > > sql_refresh_time: 300 > > > sql_history: 5m > > > sql_history_roundoff: m > > > sql_dont_try_update: true > > > nfacctd_port: xxxx > > > nfacctd_ip: xxxx > > > > > > 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. _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
