Hello Simo, As Paolo has properly suggested the main problem with a single table is you will kill performance once you get a certain size, no matter what database you use.
In our experience with our custom made flow tools based system (hope to be replaced soon by pmacct) the only ways to solve this are: 1) Apply RRD concepts to a database. This means having different tables for different times (1h, 1d, 1w, 1m, 1y or similar) with different precisions (aggregate data on each one with different precision). Do you really want to keep 5min precision in a value 6 months old? Of course, remove from those table rows older than the expected timeframe. The point here is to try to get each table with a fixed ammount (managable for your resources) or rows. I think in intelligent sampling this is called "under strict sourtage" or something like that. 2) Reduce the volume of data without affecting precision too much. You can apply sampling or even better intelligent sampling techniques in the collector too, not only in the probe. I think pmacct server has this capability. 3) Aggregate even further, for example we take all connections going from multiple ports to a single port (typicall websurfing behaviour) aggregate as comming from "Non provileged" to port 80. Of course this reduces a bit the precision, but you get 1 row instead of 5 for a very similar data Pmaact already has most of this and we hope to be able to help Paolo implement those that doesnt have but first we need to port our fron end to pmacct instead of our own, and you know, at the end other things are more urgent. Regards ----- Original Message ----- From: idrissi Yaghir <[EMAIL PROTECTED]> To: [email protected] Sent: jueves 12 de octubre de 2006 01H45 GMT+0100 Europe/Berlin Subject: [pmacct-discussion] MySQL ist too slow with Millions of Data-rows Hello, i´m using nfacctd with the MySQL-Plugin in my Project. I´ve wrote an analyser with php, that use this Database to list the Flow-Data and to generate Diagramms. I use the fprobe on Linux-routers, and I have successfully received the Flows with nfacctd and stored in a MySQL-database. This worked perfectly, but now and after one Month, I have Millions of rows in the database. The searchfunctions of the Database are now too slow and need Minutes to scan the entire Table. Is this a normal Problem, or should I use PostgreSQL to solve this??? How can I solve this Problem??? can u help me please?? Simo _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
