the current status is as follows * all stock indexes removed * added new indexes as follows: ** stamp_inserted, stamp_updated, ip_src, ip_dst, bytes, packets the indexes itself are on a single column and not combinations. * removed the leading "%' from the LIKE clauses * fixed 2 or 3 small bugs in bwstat (wrong column names)
The net outcome from this is speed increase in the range of 10^1. Before this optimizations a single page with 64 ip's opened for 240+ secs, now in 10 (700K rows) and the machine this is running on is fuckin' low-end one :) On 06/05/07, Dmitry Frolov <[EMAIL PROTECTED]> wrote: > * 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 > -- С уважение, Владимир Витков http://www.netsecad.com http://www.supportbg.com _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
