[syslog-ng] [RFC]: afsql improvement plans

[Gergely Nagy]

Hi!

I'm about to blow up^W^Wrefactor the afsql destination to increase both
performance and functionality. To do this properly, and to satisfy most
needs, I'd like to ask the greater community (especially those of you
who have more experience with logging to SQL than I do!)  about the
plans laid out on the paragraphs below.

Those of you who do log into SQL one way or the other, but aren't
interested in technical mumbo-jumbo, please skip to the end of this
e-mail, I'd have a few questions, for which all responses would be most
appreciated!

Where, when, how??
==================

There are multiple goals to accomplish, which I believe are not
exclusive: we want to keep the sql() statement in the configuration
file, and maintain backwards compatibility aswell. On the other hand, we
want to improve performance too, and that is best accomplished by using
the native database libraries. To this end, I propose we introduce new
database types: "mysql-native", "postgresql-native", "sqlite-native",
and so on and so forth.

The first step will be to rearrange the current afsql driver, so that it
has a generic configuration interface, but can have multiple backends
(dbi - the only existing backend right now; and one for each native db
client). I'd change the AFSqlDestDriver structure to only contain the
configurable data and nothing else. The backends would subclass this
type (much like how AFSqlDestDriver subclasses LogDriver) and add their
own.

The reason I want to do this, making the AFSqlDestDriver a config-only
interface instead of keeping larger chunks of code there (for example,
the threading code) is because I do not want to limit my possibilities:
I can imagine an SQL driver utilizing multiple writer threads
easily. However, neither model works for all databases (concurrent
writes to SQLite are usually not a good idea, for example).

And that's about the generic interface! Lets dive into the more
interesting parts: the native drivers. Since MySQL is the most used
server out there, that will be the first I tackle.

My goal with that is to implement a driver that is not only faster than
our current destinations, but one that is also faster than the perl
scripts people have been using until now, when they wanted top
performance. All that while maintaining reasonable flexibility.

An ambitious plan, but hopefully not impossible.

I've been reading the MySQL documentation for the past few hours to
explore my options, and found a few ways to increase our insert speed:

mysql_list_tables()
-------------------

Instead of doing a full select to verify that a table exists, we can
just try to list the table. This is probably not a significant increase,
but every bit counts.

Prepared statements
-------------------

We'll use prepared statements wherever possible - I expect this alone
will result in a measurable performance increase, since the server will
not have to parse each and every insert - only once, we'll bind
variables thereafter.

LOCK TABLES / transactions
--------------------------

Currently, syslog-ng can do bulk-inserts, wrapped in a transaction. But
that only works if the MySQL table we insert to is on a storage engine
that supports transactions (InnoDB). However, MyISAM has been the
default for quite a while, and that does not support transactions to the
best of my knowledge.

In this case, insert performance can be increased by locking the tables
for a few hundred inserts at a time, and unlocking after: poor man's
transactions, to some extent.

This, however, is more of a workaround than a real improvement.

Insertion plans
===============

There's a couple of alternative ways to improve insert speed by changing
how we actually do them. All have their pros and cons, which I will try
to enumerate below.

LOAD DATA INFILE
----------------

The fastest way by far is LOAD DATA INFILE, which is what people use in
the various perl scripts, as far as I remember. The downside is that -
as far as I saw so far - this does need a temporary file, which has its
downsides, like writing to disk (unless put on tmpfs or similar). An
option which I haven't tried would be to use a pipe(), so that the data
never hits the disk.

But nevertheless, the mysql-native db type will have an option to use
LOAD DATA INFILE.

 * Pros:
   + The fastest way to insert
 * Cons:
   - Needs a temporary file, with all its drawbacks, some of which can
   be worked around.
   - Serialisation is costlier than simple INSERTs.
   - No fine-grained error checking: if something goes wrong, the whole
   LOAD will have to be dropped (unless we add code that splits bulks up
   into smaller chunks, and retries - but that's something I want to
   avoid if possible)

For LOAD DATA to work, we will have to format the data to insert into a
suitable format, and if there's an error in the serialisation somewhere,
we'll have a hard time to figure out where the problem is, and syslog-ng
will just give up.

Also, beating our data into a suitable format isn't exactly cheap,
either (think escaping: processing large amounts of string data,
possibly changing strings - there's both processing and memory
ramifications involved).

The need for a file is also a (minor) issue. I suppose named pipes would
work, that way data never hits the disk between syslog-ng and
mysql. However, that still involves copying data around. We'll see if
these affect LOAD DATA's performance badly enough (they probably don't).
   
INSERT INTO ... VALUES (...), (...), ...
----------------------------------------

This is considerably slower than LOAD DATA, but still faster than using
INSERTs (even if we use transactions / locking for those).

The downside is that - like with LOAD DATA - serialisation is still
costlier than INSERTs.

 * Pros:
   + Reasonably fast
   + Does not require neither a temporary file, nor gross hacks
 * Cons:
   - Slower than LOAD DATA
   - Serialisation can be costly.
   - No fine-grained error checking

Serialisation is a little less costly than in LOAD DATA's case, but this
can be worked around a little with prepared statements (with a fallback
to parsed ones, in case we have to insert less data than originally
planned).

The same issues with error handling persist, though.
   
INSERT ... DELAYED
------------------

The DELAYED strategy works best if we have multiple writer threads, as
in this case, INSERT becomes a fire & forget call, and we can return to
our business far sooner. This does not work all that well with bulk
inserts, though.

 * Pros:
   + Reasonably fast with multiple writers
   + Simple & fast serialisation
 * Cons:
   - No error checking at all (except for connection and syntax
   errors)
   - Probably still slower than LOAD DATA

The advantage of this is that we can get the data from syslog-ng to the
mysql server faster than any of the other methods, and then the work is
off-loaded to the server. This has the potential of being faster than
any of the above, but with the cost of hardly any possibilities to
recover from an error, as we get no feedback after inserting: did it
succeed? did it not? We'll have no idea.
   
The Plan
========

The plan is to implement all the strategies above, and measure them
against each other and external scripts. This will take a little time,
because I want to get the architecture right, and figure out whether we
want a single or multiple writer threads (per-destination, of course),
or if I want to make that a configurable option aswell.

Once done, and people are happy with it, I'll move on to the next
database (PostgreSQL, to scratch my own itch too).

The Questions
=============

What I'd like to know most, is, that would YOU expect from a native
mysql destination?

Apart from speed, what would you want from it, that the current driver
does not support, but which is possible via external scripts?

If you happened to read through my wall of text above, did I miss
anything important?

Cheers,
-- 
|8]