Hi Vladimir, i'm not a bwstat expert - so i might be wrong. As far as i can see, bwstat involves the following couples in the WHERE part of its SQL statements - refer to the "include/functions-pmacct.php" file in bwstat distribution:
* ip_src/stamp_inserted * ip_dst/stamp_inserted Basing on this observation, this is what i would suggest: * as a general rule, to debug the performances of your SQL queries, re-phrase the SQL query by prepending the EXPLAIN, ie. in bwstat's case: EXPLAIN SELECT SUM(bytes) FROM acct WHERE ip_dst='$ip' AND \ stamp_inserted LIKE '%$year-$month-$day %'; The outcome will confirm you whether any indexes are used or not. In this case you are deadly right - the default index is not used. * In this case i would suggest you to add the following indexes to your table: CREATE INDEX tmp ON acct (ip_src,stamp_inserted) CREATE INDEX tmp ON acct (ip_dst,stamp_inserted) The EXPLAIN should now confirm that the SQL query is using one of the above. The flip side of adding indexes is that you waste some more space on the disk and there is little more efforts (CPU time) in keeping them up-to-date when inserting new data - which is the pmacct bit. It depends on the specific scenario whether the pros outperform the cons. Generally, if your pmacct has no problem inserting and updating your table (ie. you don't see a massive stack of pmacct processes lying in your process list), you can easily assume everything is grand. Let me know whether things get any better by applying the indexes. Cheers, Paolo On Sun, May 06, 2007 at 12:17:38PM +0300, Vladimir Vitkov wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hi, > > Ive setup nfacctd collector collecting data from 2 linux routers. The data is > exported with fprobe. I am keeping history and nfacctd is exporting the > collected > data to mysql. > > The problem is as follows: > Mysql is becoming quite slow and that's with a low number of rows ~400K. It is > especially bad when exploring the data with bwstat. I tend to think that the > problem > is the mysql indexes. > > Any suggestions on which and what indexes to set will be greatly appreciated. > Also a > suggestion for another frontend for data exploration will be nice. > - -- > If everything fails, use a hammer > > Vladimir Vitkov <vvitkov [at] gmail [dot] com > pgp KeyID: 0x9215F7F7 > Fingerprint: DDC0 DC86 AE97 D9EE F654 7EDC 5F03 309C 9215 F7F7 > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.6 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iD8DBQFGPZ0yXwMwnJIV9/cRAuQOAJ9SUxC+AlKoqy7VqzxEg6GE6chAMwCfQ4Qj > NEAsD3LdA8rV9R3oV8mZjA8= > =7/oB > -----END PGP SIGNATURE----- > > _______________________________________________ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
