After setting up and using the previously described solution to store syslogs in a MySQL-database, we quickly ran in some issues:

  • the MySQL table did consume a lot of disk space
  • “expiring” entries (read: deleting all entries older than 7 days) did also consume a lot of time and did not reset the auto_increment counter
  • a full text search could take so long that Nginx resettet the connection to the PHP5-FPM process, yielding in a fatal error

To solve this issues, we switched from MySQL to MongoDB. Syslog-NG supports this backend natively as well, so the switch was without any major interruption.

MongoDB supports a special kind of “table”: the capped collections. These collections are realized with a ring buffer, where the size may be specified at creation.

Some people argument that MongoDB does not provide true data consistency and may fail from time to time resulting in data loss. As we rely on MongoDB only for easier search and the actual logs are still stored on hard disk, this is not an issue at all.

  1. Install the MongoDB Server:
    aptitude install mongodb-server
  2. connect to the MongoDB server on localhost (connections from the network are per default not possible). A username or password are initially not required, so the setup should be secured afterwards:
    mongo syslog
  3. The database “syslog” is created automatically when required.
  4. To create the capped collection for Syslog-NG, issue the following command:
    db.createCollection( "messages", { capped: true, size: 100000000 } )

    The size may be changed, depending on the available disk space, performance requirements etc.

  5. Now, Syslog-NG has to be advised to use our new MongoDB. Installation of the new database driver is as easy as expected:
    aptitude install syslog-ng-mod-mongodb
  6. Afterwards, we alter our previously created /etc/syslog-ng/conf.d/05loghost.conf with the new configuration directives for MongoDB:
    filter f_no_debug { not level(debug); };
    destination d_db { mongodb(); };
    log {source(s_net); source(s_src); filter(f_no_debug); destination(d_db); };

    Further configuration is not required. Per default, Syslog-NG uses the database “syslog” and the table “messages” – which we already created beforehand.
    For reference, the complete file should look like this (except the full paths and – of course – the listener addresses!):

    source s_net { udp( ip("") port(514));
    udp( ip6("2a01:138:a008::xxx") port(514)); 
    destination hosts_syslog { file("/var/logs/$HOST/$YEAR/$MONTH/$DAY/syslog" owner(root) group(root) perm(0644) dir_perm(0700) create_dirs(yes)); };
    log {source(s_net); source(s_src); destination(hosts_syslog); };
    filter f_no_debug { not level(debug); };
    destination d_db { mongodb(); };
    log {source(s_net); source(s_src); filter(f_no_debug); destination(d_db); };
  7. Restart Syslog-NG, so the changed configuration is activated.
  8. To verify, connect to the MongoDB-Server (see 2.) and send the following command:
    > db.messages.find().pretty()

    If MongoDB shows results, the configuration was successful.

  9. As we created a “capped collection”, MongoDB will automatically remove the oldest entries when the size limit is reached. This limit may be monitored by the “status()” command:
    # mongo syslog
    MongoDB shell version: 2.4.8
    connecting to: syslog
    > db.messages.stats()
            "ns" : "syslog.messages",
            "count" : 2537201,
            "size" : 959405776,
            "avgObjSize" : 378.1355028631945,
            "storageSize" : 1000001536,
            "numExtents" : 1,
            "nindexes" : 1,
            "lastExtentSize" : 1000001536,
            "paddingFactor" : 1,
            "systemFlags" : 1,
            "userFlags" : 0,
            "totalIndexSize" : 82332320,
            "indexSizes" : {
                    "_id_" : 82332320
            "capped" : true,
            "max" : NumberLong("9223372036854775807"),
            "ok" : 1
  10. When searching the database, all MongoDB Query operations may be used. Especially “$regex” should be very useful 🙂

To display the log entires, we developed a small PHP application with php-mongo and Twitter Bootstrap. This needs some cleanup and additional improvements regarding the search and will probably afterwards be released on Github.

Next Post Previous Post