For When You Can't Have The Real Thing
[ start | index | login ]
start > dave > experiments > Net Flows > 2007-11-21 > 1

2007-11-21 #1

Created by dave. Last edited by dave, 16 years and 158 days ago. Viewed 3,121 times. #1
[edit] [rdf]


Thanks to IRC!
flowdb=# create index localip_timeslog on localip(timeslot);
flowdb=# create index localip_ip on localip(ip);

This speeds the postgres2rrd up by several orders of magnitude! Timeslot processing goes from 15-30 seconds per, to 3-7 timeslots per second!


  • the index will get updated as more data is inserted. Future inserts may be slower than they otherwise might have been (but now that the bulk of the data has been inserted, this isn't a huge penalty
  • after a lot of modifications, it will be worth occasionally worth doing vacuum analyze; to compact the database and update the planner's stats that tell it how to construct queries
  • indexes are part of the table structure and have to be recreated if the table is dropped and then recreated.
Also of note is that there is an IP datatype, this might be faster to work with than the varchar that we are using.
no comments | post comment
This is a collection of techical information, much of it learned the hard way. Consider it a lab book or a /info directory. I doubt much of it will be of use to anyone else.

Useful: | Copyright 2000-2002 Matthias L. Jugel and Stephan J. Schmidt