Hi Good work. Would it be possible for you to send create table "your table". Im right now looking if its possible to use partitions with memory map tables. The reason for this is that most of my queries are done on SRC,DST IP and a timeframe for my reports. What im looking at is doing parttions on date and subpartions on IP. There are several things that i need to change for this to work. IP need to be INT and DATE must be of type DATETIME. The type timestamp is not supported by partitions. I think its possible to use disk based tables with this and still keep queries very short.
//Regards Jonas 2010/3/21 Matthew Will <[email protected]> > 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 > -- /Mvh Jonas nylund
_______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
