Data indexing is key element for fast content retrieval from a RDBMS. Default SQL tables come with a rather large primary key, ie. which includes all aggregation primitives, but without any secondary index. This combination protects from duplicated data, allows to quickly test SQL capabilities of pmacct and can work fine in small environments. But it will reveal insufficient as soon the data-set increases.

This is how a default table looks like. Examples will be based on this table but concepts similarly apply to any other default and custom table schemas. Same goes for the backend: examples are tailored on MySQL but they can be easily adapted to other RDBMS software of choice.

CREATE TABLE acct (
    mac_src CHAR(17) NOT NULL,
    mac_dst CHAR(17) NOT NULL,
    ip_src CHAR(15) NOT NULL,
    ip_dst CHAR(15) NOT NULL,
    src_port INT(2) UNSIGNED NOT NULL,
    dst_port INT(2) UNSIGNED NOT NULL,
    ip_proto CHAR(6) NOT NULL,
    packets INT UNSIGNED NOT NULL,
    bytes BIGINT UNSIGNED NOT NULL,
    stamp_inserted DATETIME NOT NULL,
    stamp_updated DATETIME,
    PRIMARY KEY (mac_src, mac_dst, ip_src, ip_dst, src_port, dst_port, ip_proto, stamp_inserted)
) TYPE=MyISAM;

In a primary key every record must be unique. An option is to select a small synthetic primary key. The method allows for "duplicate" rows but is savy in terms of space and consists in adding an auto-increasing field (ignored by pmacct) which in turn becomes the primary key. Duplicate rows need to be dealt with as part of some popular scenarios like a) disabling update queries (sql_dont_try_update: true) for performance purposes in conjunction with b) maintaining original NetFlow flow timestamps (nfacctd_time_new: false).

create table acct (
    id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (id)
) TYPE=MyISAM AUTO_INCREMENT=1;

An auto-increment ID field can very well wrap-up; its size should be selected basing on the planned data-set (ie. 4 bytes ID allows for a 4 billion rows data-set) and, expecially if performing periodic (daily, weekly or monthly) DELETE queries, it should be regularly checked and reset, if required.

Then, secondary indexes. These are not required to contain only unique records. Two strategies and their combination are the most popular and are typically driven from the context scenario:

• Ad-hoc indexes. A frontend or some scripts will run some pre-determined queries against the data-set with the aim of producing reports. In such a case the set of queries not only drives the aggregation method but also the database indexing. Depending on the selection of indexes, queries different from the intended ones can perform horribly due to sequential scanning of the data-set.

• Analytical selection of indexes. The underlying idea is to get a good coverage for most situations, although not optimal for any of them. This can easily result in creating a separate index for each selected aggregation primitive:

    CREATE TABLE acct (
        ...
        INDEX a (mac_src),
        INDEX b (mac_dst),
        INDEX c (ip_src),
        INDEX d (ip_dst),
        INDEX e (src_port),
        INDEX f (dst_port),
        INDEX g (ip_proto),
        INDEX h (stamp_inserted)
     );
  

• Any combination of the two methods above. For example aimed at getting good coverage of most situations and optimal coverage of some popular queries.

Indexes give benefits but don't come without a cost: they consume disk-space and slow down both INSERT and DELETE queries. Bottom line, using a SQL backend is rewarding on the long run but requires some good preparations. It is suggested to arrange a test-bed in order to reproduce a scaled-down environment representative enough of the production one. Then, analyze how queries would make use of the existing indexes via the EXPLAIN statement:

EXPLAIN SELECT ... FROM ... [ WHERE ] ... [ GROUP BY ] ... [ ORDER BY ] ... ; 

If a better or different indexing scheme reveals necessary, re-indexing can be a long process if existing data is of value and the data-set is large enough. The best approach might reveal a full export (dump) of the data-set, a drop of the existing tables followed-up by a restore once the indexing scheme has been changed. The whole process can be best executed on a shadow table with pmacct inserting fresh data on an empty table with the revised indexing scheme.

RENAME TABLE acct TO acct_old;
CREATE TABLE acct ( ... );
< export data from "acct_old" >
DROP TABLE acct_old;
< restore data to "acct" with lock > 

While restoring data, pmacct can work in parallel if configured not to perform locking (sql_locking_style: row); otherwise it can be safely locked out during the process for up to sql_max_writers (default: 10) times the sql_refresh_time interval (ie. if sql_refresh_time = 300 and sql_max_writers = 10, pmacct can be locked out for some 45 minutes out of the database; after that it will start dropping new SQL writer processes instead of queuing them up).

To conclude good tuning of any RDBMS software, requires good underlying knowledge of it, its way of working and the options offered. Some examples include engine selection (ie. MyISAM vs InnoDB in MySQL), index types (ie. hash, B-tree in PostgreSQL), buffers and temporary storage (ie. sort_buffer_size, tmp_table_size, tmpdir which severly impact queries with GROUP BY statements in MySQL), disk and memory requirements.