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]
You've laid everything out nicely, but you're missing a huge one: HandlerSocket! I would forget about all of the INSERT variations and focus on a HanderSocket implementation. Check out the MySQL Performance Blog articles on it, and you'll see that according to their numbers, you can get LOAD DATA speed with normal inserts using it. The only catch with it is you can't use auto-increment columns, so $SEQNUM or something would have to be used for an id. On Fri, Apr 1, 2011 at 8:05 AM, Gergely Nagy <algernon@balabit.hu> wrote:
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] ______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
Martin Holste <mcholste@gmail.com> writes:
You've laid everything out nicely, but you're missing a huge one: HandlerSocket! I would forget about all of the INSERT variations and focus on a HanderSocket implementation. Check out the MySQL Performance Blog articles on it, and you'll see that according to their numbers, you can get LOAD DATA speed with normal inserts using it. The only catch with it is you can't use auto-increment columns, so $SEQNUM or something would have to be used for an id.
Right, I knew I forgot something, thank you! The catch with HandlerSocket is that it's an additional plugin for MySQL, so there needs to be an alternative way to log to MySQL at decent speed even without it. It also lacks a C API, but that's not a big deal, either. I'll read up on it, and probably go with supporting both LOAD DATA and HandlerSocket (after thinking a bit more about the INSERT variations, the few things I didn't like about LOAD DATA aren't that severe that would make it worth exploring the INSERT options). -- |8]
I'll read up on it, and probably go with supporting both LOAD DATA and HandlerSocket (after thinking a bit more about the INSERT variations, the few things I didn't like about LOAD DATA aren't that severe that would make it worth exploring the INSERT options).
Good points. LOAD DATA really is the holy grail of INSERT for both speed and universality. As I wrote earlier, just writing to /dev/shm means you don't need tmpfs on Linux. You might want to look at the source code for the mysqlimport binary to see how they implement the various low-level functions for LOAD DATA. Surely there's a way to bypass the ridiculousness of a buffer file if you can load straight from in-program memory in syslog-ng. When looking at an implementation, don't forget the functionality that comes with "CONCURRENT" so that it doesn't need to lock the table or block. The manual says there is a performance hit for doing this, but I can assure you it is unnoticeable.
Martin Holste <mcholste@gmail.com> writes:
I'll read up on it, and probably go with supporting both LOAD DATA and HandlerSocket (after thinking a bit more about the INSERT variations, the few things I didn't like about LOAD DATA aren't that severe that would make it worth exploring the INSERT options).
Good points. LOAD DATA really is the holy grail of INSERT for both speed and universality. As I wrote earlier, just writing to /dev/shm means you don't need tmpfs on Linux.
I'll look into that, thanks, along with mysqlimport (already looking at it :). I can think of a number of ways to bypass the buffer file, but the fact that I need to find a workaround is what I didn't like at first. However, exploring the other options further... this is still the best. Also had a look at HandlerSocket in the meantime... that's an entirely different protocol, so it would probably be best to have a mysql-native (that uses libmysqlclient, and thus, LOAD DATA & whatever else the library supports), and a mysql-handlersocket type (and then we still have the original libdbi-using mysql type - fun times! I don't envy the documentation team now).
When looking at an implementation, don't forget the functionality that comes with "CONCURRENT" so that it doesn't need to lock the table or block. The manual says there is a performance hit for doing this, but I can assure you it is unnoticeable.
Oh, good to know, thanks for the hint! -- |8]
The only catch with it is you can't use auto-increment columns, so $SEQNUM or something would have to be used for an id.
Update, according to http://www.mysqlperformanceblog.com/2011/03/28/whats-up-with-handlersocket/ , HandlerSocket now supports auto-increment, which makes implementation a lot more straightforward.
On Fri, Apr 1, 2011 at 3:05 PM, Gergely Nagy <algernon@balabit.hu> wrote:
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.
This is also a performance increase for Postgresql/etc. so this should (IMHO) be an universal method for all the SQL drivers, as it is expected give a global rather than specific gain. Postgresql's COPY appears to be equivalent to the MySQL's LOAD, so my advice will be to keep these two methods in a way to easily replace/substitute for Postgresql ;) Hendrik
Hendrik Visage <hvjunk@gmail.com> writes:
On Fri, Apr 1, 2011 at 3:05 PM, Gergely Nagy <algernon@balabit.hu> wrote:
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.
This is also a performance increase for Postgresql/etc. so this should (IMHO) be an universal method for all the SQL drivers, as it is expected give a global rather than specific gain.
Indeed, prepared statements will be implemented for all native drivers that support it, postgres included. I can't make it a global feature, since libdbi does not support it as far as I know, so I have to bring it out to the native-driver level (and since those use the respective C libraries instead of a libdbi bridge, they all implement prepared statements a bit differently).
Postgresql's COPY appears to be equivalent to the MySQL's LOAD, so my advice will be to keep these two methods in a way to easily replace/substitute for Postgresql ;)
Well, COPY is a lot friendlier than LOAD, from my side for sure, but it's syntax is different enough that there's not much code that could be shared. On the other hand, the last step of The Plan, which I forgot to list, is that once we have drivers for the most used sql database types (mysql, postgres & sqlite; I'll leave oracle to someone who dares to touch it), I'll see if there's enough common code that could be shared between these. Basically, we'll have a single sql() statement, but the afsql driver itself will be nothing more than a configuration interface for - say - afsql-dbi, afsql-mysql, afsql-mysql-handlesocket, afsql-postgres, afsql-sqlite, and so on. These all will use the same configuration, give or take a few options here and there, but the underlying module will be almost entirely separate in the beginning. -- |8]
It sounds like a good plan, and the community will really benefit. One last one to consider--bcp for MSSQL. FreeTDS provides a Linux version in addition to the native Windows binary. On Fri, Apr 1, 2011 at 10:35 AM, Gergely Nagy <algernon@balabit.hu> wrote:
Hendrik Visage <hvjunk@gmail.com> writes:
On Fri, Apr 1, 2011 at 3:05 PM, Gergely Nagy <algernon@balabit.hu> wrote:
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.
This is also a performance increase for Postgresql/etc. so this should (IMHO) be an universal method for all the SQL drivers, as it is expected give a global rather than specific gain.
Indeed, prepared statements will be implemented for all native drivers that support it, postgres included.
I can't make it a global feature, since libdbi does not support it as far as I know, so I have to bring it out to the native-driver level (and since those use the respective C libraries instead of a libdbi bridge, they all implement prepared statements a bit differently).
Postgresql's COPY appears to be equivalent to the MySQL's LOAD, so my advice will be to keep these two methods in a way to easily replace/substitute for Postgresql ;)
Well, COPY is a lot friendlier than LOAD, from my side for sure, but it's syntax is different enough that there's not much code that could be shared.
On the other hand, the last step of The Plan, which I forgot to list, is that once we have drivers for the most used sql database types (mysql, postgres & sqlite; I'll leave oracle to someone who dares to touch it), I'll see if there's enough common code that could be shared between these.
Basically, we'll have a single sql() statement, but the afsql driver itself will be nothing more than a configuration interface for - say - afsql-dbi, afsql-mysql, afsql-mysql-handlesocket, afsql-postgres, afsql-sqlite, and so on. These all will use the same configuration, give or take a few options here and there, but the underlying module will be almost entirely separate in the beginning.
-- |8] ______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
Hi, On Fri, 2011-04-01 at 15:05 +0200, Gergely Nagy wrote:
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.
I would probably not create separate types for these, is there a point in specifying which API to use from a user's point of view? I would guess, we have the responsibility to decide which one to use, and if there's a notable, real difference between methods (except performance), make some knobs to adjust those, but only those.
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.
I'm not sure this is worth the effort, a table is only checked if it exists when we first one to insert a message and when syslog-ng is restarted or an error occurs. I'd recommend to make it possible to reuse RDBMS independent code blocks. (like checking if a table exists), because if we do everything in an RDBMS specific way, it could become a maintenance hell.
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.
well, at least on Linux this could be worked around by using POSIX shared memory, which has an underlying file on Linux (under /dev/shm), but also on Solaris (/tmp/.SHMD<shmname>). Possibly other platforms too.
- Serialisation is costlier than simple INSERTs.
I wouldn't say that. Even INSERTs need escaping in order to avoid SQL injection, and the escaping needed for the LOAD format is not really different (if at all).
- 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)
What we do with transactions currently, is that we put items being added to the transactions to the "backlog" queue, which is then dropped when the transaction succeeds, or rewound when it doesn't. I think the same can work with LOAD DATA.
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).
I was considering this option, earlier when I was thinking about impementing bulk loads. There's a "LOAD DATA LOCAL INFILE" option, can't that be used to generate the file without having to write to disk? Or is that completely hidden by the mysql client lib?
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.
I would recommend not to implement all loading options in parallel, but rather choose the one which offers the best pros/cons ratio and stick with it. Completely independent methods for writing databases can again become a maintenance burden. I'd really doubt if multiple feeding threads would indeed increase the loading speed. I'd think this is really disk bound, and/or internal locking would inhibit scaling to multiple threads anyway. Although it'd probably make sense to try it :)
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?
I'd say that the scripts that I saw either did nothing but load the files, or did things that could be performed by patterndb and/or other specific parsers.
If you happened to read through my wall of text above, did I miss anything important?
-- Bazsi
Balazs Scheidler <bazsi@balabit.hu> writes:
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.
I would probably not create separate types for these, is there a point in specifying which API to use from a user's point of view? I would guess, we have the responsibility to decide which one to use, and if there's a notable, real difference between methods (except performance), make some knobs to adjust those, but only those.
Good point. So if syslog-ng was compiled with libmysqlclient present, then sql(type(mysql)) would automatically use the native driver instead of libdbi? HandleSocket would/will be a little different, as it's not really SQL: it just happens to use MySQL for storage, but that's about it. I'm still pondering how (if it is possible at all) to use that as part of afsql.
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.
I'm not sure this is worth the effort, a table is only checked if it exists when we first one to insert a message and when syslog-ng is restarted or an error occurs.
Indeed, in that case, it's not worth it.
I'd recommend to make it possible to reuse RDBMS independent code blocks. (like checking if a table exists), because if we do everything in an RDBMS specific way, it could become a maintenance hell.
Yep, reusing as much code as possible is part of my plans. But, I'd rather do the native drivers first, possibly reimplementing a lot of stuff, and then - in a next iteration - move the RDBMS independent code into a common place.
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.
well, at least on Linux this could be worked around by using POSIX shared memory, which has an underlying file on Linux (under /dev/shm), but also on Solaris (/tmp/.SHMD<shmname>). Possibly other platforms too.
Yeah. And on other platforms, there's still named pipes. I'll probably start with named pipes, and add platform hacks later.
- Serialisation is costlier than simple INSERTs.
I wouldn't say that. Even INSERTs need escaping in order to avoid SQL injection, and the escaping needed for the LOAD format is not really different (if at all).
There's a difference though: with inserts, assuming that we're using prepared statements, there's not much extra processing than resolving the templates, the rest is handled by the native client library. With prepared statements, we don't need to do construct INSERT commands: we already did that, we just bind our new values to it. For example, assuming we have a statement template like this: INSERT INTO syslog (seqid, host, message) VALUES (?, ?, ?) Then whenever we want to insert a message, we do something along these lines: rdbms_prep_statement_bind (stmnt_handle, 0, seqid); rdbms_prep_statement_bind (stmnt_handle, 1, host); rdbms_prep_statement_bind (stmnt_handle, 2, message); rdbms_prep_statement_execute (stmnt_handle); There's no extra escaping needed, since the server does not need to parse the whole statement, we already prepared it ages ago. We just send two parts: the template and the variables, the server does the rest. However, in LOAD DATA INFILE's case, we'd need to do escaping ourselves (which is not needed when using INSERT with prepared statements) and format the data into a suitable format. On the other hand, the extra overhead of formatting is far smaller than the overhead of using INSERTs instead of LOAD DATA INFILE, so as it turns out, this is a non-issue aswell :)
- 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)
What we do with transactions currently, is that we put items being added to the transactions to the "backlog" queue, which is then dropped when the transaction succeeds, or rewound when it doesn't.
I think the same can work with LOAD DATA.
Yep, but that still doesn't tell us which of the 1000 messages triggered the error, only that one of them did. Nevertheless, this is a very very minor inconvenience, and if there's no bugs in our code, it doesn't even matter.
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).
I was considering this option, earlier when I was thinking about impementing bulk loads.
There's a "LOAD DATA LOCAL INFILE" option, can't that be used to generate the file without having to write to disk? Or is that completely hidden by the mysql client lib?
LOAD DATA LOCAL INFILE is what the driver will use, and it still needs a temporary file. The difference between LOAD DATA INFILE and LOAD DATA LOCAL INFILE is that in the latter case, the file is on client-side, while in the former, it's on server side. All forms of LOAD DATA need an input file (it's really LOAD DATA INFILE, but that's just too long to write every time O:).
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.
I would recommend not to implement all loading options in parallel, but rather choose the one which offers the best pros/cons ratio and stick with it.
After thinking about it more, and based on your and Martin's suggestion, I'll stick with LOAD DATA INFILE & HandlerSocket for mysql. For Postgres, I'll go with COPY, and we'll see about the rest when I get there.
Completely independent methods for writing databases can again become a maintenance burden.
Yep, but at least for me, it makes initial prototyping easier. Once that's done, I will collect the common stuff into a shared core, and leave only those things in the database-specific drivers that do need to be there (basically, the calls into the native client libraries in most cases). At least, that's how I see it now. Once I sit down and start coding, I'll see if this idea is flawed or not. The end result will be the same in both cases, though.
I'd really doubt if multiple feeding threads would indeed increase the loading speed. I'd think this is really disk bound, and/or internal locking would inhibit scaling to multiple threads anyway. Although it'd probably make sense to try it :)
In the meantime, I tried a few experiments, and concluded that multiple feeders are not worth the hassle. -- |8]
participants (4)
-
Balazs Scheidler
-
Gergely Nagy
-
Hendrik Visage
-
Martin Holste