Hi all, I just noticed a factor that may be affecting the performance of pmacct when using a MySQL database in the default configuration.
MySQL's InnoDB storage engine clusters records by the primary key, and it's recommended to make it a monotonically-increasing value: > As alluded to above, the primary key should be sequential. Because the > structure of the data on the disk is dictated by the primary key, it > should be sequential, to avoid extremely expensive I/O on writes (when > the B-Tree has to be reorganized), and to take advantage of the native > clustering. [http://blog.johnjosephbachir.org/2006/10/22/everything-you-need-to-know-about-designing-mysql-innodb-primary-keys/] The MyISAM storage engine says: > When rows are inserted in sorted order (as when you are using an > AUTO_INCREMENT column), the index tree is split so that the high node > only contains one key. This improves space utilization in the index > tree. [http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html] These two are probably the most popular MySQL storage engines overall, and both benefit from rows being inserted more or less "in order". However, the default primary key is: (agent_id, class_id, mac_src, mac_dst, vlan, as_src, as_dst, ip_src, ip_dst, src_port, dst_port, ip_proto, tos, stamp_inserted) Which puts stamp_inserted right at the back of the primary key, and makes it far less likely to be approximately monotonic and far more likely to be approximately random as far as the database is concerned. I think it would make no semantic difference, but would increase MySQL performance with these table types, if the primary key listed stamp_inserted first instead of last. I imagine that more database queries use stamp_inserted than use agent_id, so it might also increase performance on common queries, as MySQL can then perform a prefix search on the index for any queries including exact or range matches on stamp_inserted. Cheers, Chris. -- Aptivate | http://www.aptivate.org | Phone: +44 1223 760887 The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES Aptivate is a not-for-profit company registered in England and Wales with company number 04980791. _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
