Hi Scott and Paolo, May I ask a question per your good experience?
What is the performance difference between using string ipv4 and integer ipv4? And potential impact? I have a PC running sfacctd that collects 2 giga-switches. And it collects around 5M records into mysql per day. I am satisfied with the default string ipv4 for its convenience of being readable. And I would like to know the performance drawback. Thank you. :) -----Original Message----- From: pmacct-discussion [mailto:[email protected]] On Behalf Of Paolo Lucente Sent: Wednesday, September 17, 2014 7:29 AM To: [email protected] Subject: Re: [pmacct-discussion] mySQL ATON/NTOA and pmacct performance Hi Scott, You are looking for "sql_num_hosts: true". This is tested to work with IPv4 addresses. I'm reasonably sure it won't work with IPv6: i seem to recall INET6_ATON was not available in 2011, the time of the original implementation of the feature. Should not be a biggie to introduce it. Cheers, Paolo On Mon, Sep 15, 2014 at 11:54:25PM +0000, Scott Pettit - Vorco wrote: > Hi, > > I've been running pmacct for a while with PostgreSQL and it works well with > lots of netflow data presumably due to inbuilt handling of CIDR. > > For various reasons we're migrating to a mySQL backend to integrate with our > billing/customer portal environment better and mySQL does not appear to deal > well with strings in complex reports. > > We generate around 10GB of data per hour so when I want to do something like > select all the flows over a week and then spit out the top 50 AS sources, > show the total data from each of the top AS sources, and then the top 50 > destination IP's in our network that those AS's are sending data to, mySQL > chokes for an hour trying to work that out whereas PostgreSQL takes a few > minutes but doesn't struggle. I've played with indexes and got some > improvement but nothing significant. > > I was looking into using ATON/NTOA functions in mySQL to store IP addresses > as integers instead which I expect should dramatically increase performance > especially when dealing with CIDR as I can query based on a range of integers. > > As far as I can see pmacct doesn't seem to have an option to tell it to do > its INSERTs of ip_src and ip_dst with INET_ATON and INET6_ATON. Is there such > an option or a patch that exists? If not is there a reason why this would be > a bad idea? > > -Scott > ________________________________ > The content of this message and any attachments may be privileged, > confidential or sensitive. Any unauthorised used is prohibited. Views > expressed in this message are those of the individual sender, except where > stated otherwise with appropriate authority. All pricing provided is valid at > the time of writing only and due to factors such as the exchange rate, may > change without notice. Sales are made subject to our Terms & Conditions, > available on our website or on request. > ________________________________ > _______________________________________________ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists ==================================================================== 本信件可能包含工研院機密資訊，非指定之收件者，請勿使用或揭露本信件內容，並請銷毀此信件。 This email may contain confidential information. Please do not use or disclose it in any way and delete it if you are not the intended recipient. _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
