Not sure if anyone is interested in this info, but thought I'd post as I find pmacct and bwstat both useful tools. My queries are now completing in under .05 seconds. I'll describe what I did and if anyone would like more details let me know.
Currently with 3 million+ rows in my table, bwstats queries doing full table scans did not suffice. I tried Vladimir's patches and seen minor improvements although queries per IP were still taking 10-20+ seconds with the table cached in memory (much much longer from disk). The cause of this is that the LIKE method used in the queries on the 'stamp_inserted' column being datetime. Even with MySQL utilizing it's merge function with indexes this did not suffice. In addition alot of the indexes added to the table by that patch were not required either, consuming buffer blocks in memory, delayed inserts by pmacct on a busy system as well as consuming table space. Having already altered the tables with the keys in Vlad's patch, I dropped all indexes, as well all all primary keys on the table. I then created two multi column indexes on ip_src,stamp_inserted and ip_dst,stamp_inserted. This alone improved the standard bwstat queries 40-50% in execution time. Next I modified the queries to use between datetime data which allows MySQL to properly utilize the created indexes on selects from the pmacct table. This improved execution time from an average of 10-20 seconds depends on location of the data in the buffer cache to < 0.05 seconds per query. This has opened up the option of letting me graph data on the fly utilizing the already implemented functions in bwstat for pmacct, as opposed to running offline reporting which was consuming considerable resources where not required. I'd like to thank Vladimir (not sure if he still follows this list) for his efforts which motivated me to improve on his work. I can elaborate on the specifics if anyone feels this could benefit improving performance on large tables, let me know. Regards, -- Matthew Will
_______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
