On Mon, 10 Dec 2018 16:10:23 +0000
Paolo Lucente <pa...@pmacct.net> wrote:

> 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.

Looks ok to me.  I can't speak to the performance
implications of fixed v.s. variable length string
columns in MySQL. And it probably depends on the
back-end storage engine anyway.  What you have
leaves everything to do with performance up to
the end-user, which is fine.

You might consider altering sql/pmacct-create-db.pgsql
so that the create database is:


This should work from Postgres 8.4, released 2009-07-01
and out of support July 24, 2014.  RHEL 6 includes
Postgres 8.4 (or so I'm told here
).  RHEL 5 is no longer supported.

It uses template0 because in theory template1 (the default) could
have been modified by the local db admin to contain
data which is not compatible with the C collation.
In practice template1 is probably never different
from template0.

Most likely, the OS is using UTF8.  And the PG cluster
will be created using the OS default.  So the db will
be able to contain UTF8 characters, which I presume
is what you want, but sorting/indexing will be in UTF8 codepoint
order.  Much faster than a dictionary sort.

While you're at it, a note in pmacct-create-db.pgsql
to increase "shared_buffers"
in postgresql.conf might also be useful.  The default
is 128M, which is a very small amount of memory these

Something like:

  -- NOTE: Database performance is _much_ improved with minimal
  --       tuning.  Start by allocating more RAM to the database.
  --       Increase the "shared_buffers" setting in
  --       postgresql.conf to as much as 25% of RAM.
  --       Consider also setting "effective_cache_size".  It should
  --       be larger than shared_buffers.  "checkpoint_segments"
  --       should probably be at least 32, much more if write
  --       performance is an issue.  For details see:
  --    https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

> 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.


Karl <k...@meme.com>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

pmacct-discussion mailing list

Reply via email to