Hi Karl,
Thanks for your input - always very interesting. I sketched a new note in the SQL docs basing on the thread so far: https://github.com/pmacct/pmacct/commit/e4c594a9f10040b53d4523f7edecf455a20a9151 Hope it looks right enough. Paolo On Sun, Dec 09, 2018 at 07:13:34PM -0600, Karl O. Pinc wrote: > On Fri, 7 Dec 2018 16:42:31 +0000 > Paolo Lucente <pa...@pmacct.net> wrote: > > > You could make the field variable-length - optimizing space and > > avoiding you the try & error of finding the sweet spot size for the > > as path field at the expense of more computing. > > I would not expect a Postgres TEXT column, which is variable > length, to be significantly more CPU intensive than a > fixed length CHAR column. > > From the PG docs regarding the various types which store strings: > > --- > There is no performance difference among these three types, apart from > increased storage space when using the blank-padded type, and a few extra CPU > cycles to check the length when storing into a length-constrained column. > While character(n) has performance advantages in some other database systems, > there is no such advantage in PostgreSQL; in fact character(n) is usually the > slowest of the three because of its additional storage costs. In most > situations text or character varying should be used instead. > --- > > So, when using Postgres, there's no particular reason to > limit string column lengths. > > What is important with Postgres is the collation sequence > used by the database. UTF-8 collation support slows down sorting > significantly, and so affects indexes and so forth. > If you need UTF-8 characters you can do that, but > sort them by byte-code values if you care about performance. > If necessary collation can even be set down to the column > level in the newest PG. What's easiest is to avoid UTF-8 > entirely and set the locale for the whole db to "C". > This gives you an entire byte to store each character > and characters sorted by byte-code value. > > https://www.postgresql.org/docs/11/charset.html > > > On Fri, Dec 07, 2018 at 10:46:32AM +0100, Fabien VINCENT wrote: > > > Dear List, > > > > > > I've an issue when nfacctd try to push to pgsql database : > > > > > > PGSQL log file : > > > > > > ERROR: value too long for type character(80) > > > CONTEXT: > > > > > > COPY flow _*****_, line 74771, column as_path_src: "14061 > > > {46652,4210010000,4210010200,4210010201,4210010202,4210010297,4210010400,4210010402,4210010499..." > > > > > > > > > In my nfacctd config file I've : > > > > > > bgp_aspath_radius: 10 > > > > > > because as_path_src is set to CHAR(80). But seems BGP aggregates > > > break the rules ? > > > > > > Is there anyway to limit / cut down BGP aggregates in column > > > as_path_src ? > > Karl <k...@meme.com> > Free Software: "You don't pay back, you pay forward." > -- Robert A. Heinlein _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
