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: 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. > -- Sergio Roberto Charpinel Jr.
_______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
