Customizing the SQL database

A popular misconception is that pmacct comes with some pre-ordered SQL schemas and that's all about it. This is not true in two senses:

  1. A given SQL table can be optimized to your needs; unused fields can be freely shaved off the schema; primary key and indexes can be rearranged as per your needs.
  2. Fully customized SQL tables can be created from the scratch by freely mixing and matching the defined primitives. Fields ignored by pmacct can be also part of the table.

The official documentation already extensively explains the need for providing some default SQL schemas paired with a versioning system. The documentation also explains the plus side of the mechanism: letting everything work out-of-the-box for small installation and to people with minimal or no knowledge of SQL. Here it's explained the some of the main limitations of the default tables:

  1. They might not entirely match your needs. This can generate a waste of disk space, for example in case the schema is left with unused fields.
  2. Sub-optimal indexing. The default primary key is rather expensive in terms of size; some of the queries you run might not hit the primary key giving an overral feeling of slowness.
  3. Some SQL database offer multiple engines (ie. MySQL); default tables use one of them, which might not be the ideal choice for your configuration. MyISAM, the SQL engine used by default MySQL tables, is fast but lacks of transactions. If multiple plugins are writing to the same SQL table, InnoDB might result in a better choice.

So if you have any SQL ablities, you are recommended to customize the SQL table. As a starting point, two pointers are provided below. First off, a configuration fragment which flags pmacct the SQL table is customized:

sql_optimize_clauses: true
sql_table: <table name>
aggregate: <aggregation primitives list>
! sql_table_version: <whatever>

As you can notice, specifying a SQL table version doesn't apply while customizing the schema. Below there is an example, which doesn't include the indexes, of how a table can be customized for increased efficiency (ie. small primary key):

create table acct_bgp_5mins (
        agent_id INT(4) UNSIGNED NOT NULL,
        mac_src CHAR(17) UNSIGNED NOT NULL,
        mac_dst CHAR(17) UNSIGNED NOT NULL,
        vlan INT(2) UNSIGNED NOT NULL,
        as_src INT(4) UNSIGNED NOT NULL,
        as_dst INT(4) UNSIGNED NOT NULL,
        packets INT UNSIGNED NOT NULL,
        stamp_inserted DATETIME NOT NULL,
        stamp_updated DATETIME,
        PRIMARY KEY (id),
        INDEX ...

This is MySQL specific and tailored to a L2-switched scenario but the underlying concept can easily be extended to, say, whatever L3 scenario and works similarly for PostgreSQL and SQLite. Interestingly enough, the source and destination AS fields can be populated either by pmacct or looked up by a 3rd party application or script.

pmacct Wiki: CustomizingTheSqlDb (last edited 2009-08-01 18:09:43 by paolo)