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, 11 years and 25 days ago. Viewed 1,701 times. #1
[edit] [rdf]
labels
attachments

WHOO

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

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

Notes:

  • 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:


snipsnap.org | Copyright 2000-2002 Matthias L. Jugel and Stephan J. Schmidt