Re: [pmacct-discussion] Low performance query
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. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] Low performance query
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
[pmacct-discussion] Low performance query
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
Re: [pmacct-discussion] Low performance query
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
Re: [pmacct-discussion] Low performance query
Hi Paolo, 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. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
