* Paolo Lucente <[EMAIL PROTECTED]> [06.05.2007 19:45]: > 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.
As a general note: LIKE expression with leading '%' will not use index by that field for sure. LIKE expression on field of type other than string will not use index on that field in MySQL because it will need to convert field to string before evaluation (and MySQL doesn't support indexes on expressions as of now). So using LIKE expression to match on DATETIME field is a very bad idea, IMHO. > * 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 wbr&w, dmitry. -- Dmitry Frolov <[EMAIL PROTECTED]> RISS-Telecom Network, Novosibirsk, Russia [EMAIL PROTECTED], +7 383 2278800, DVF-RIPE _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
