FIX logs in a database?

, ,
1

Imported from previous forum

2

I am curious how many folks out there configure their FIX engines to save their logs to a relational database. Is this fairly common? I can see advantages in terms of availability, but seems like it would be a minus from a performance perspective.

3

I am curious how many folks out there configure their FIX engines to
save their logs to a relational database. Is this fairly common? I can
see advantages in terms of availability, but seems like it would be a
minus from a performance perspective.

Relational database logging does offer a lot in terms of availability, failover/redundancy and reporting, but I wouldn’t use it as a primary message store for the reason you mentioned, the performance hit. This does mean that you have to work out another scheme for persisting messages in such a way as to provide seamless failover, but this can be done in many ways that I would expect to be more efficient than using a relational database. Also, if you’re logging to a database and it becomes inaccessible for some reason, you have no logging. One solution would be to do a hybrid implementation. For example, you could write messages immediately to a file or some in-memory cache, then lazily write them to a database.

4

Thanks Paul,

I posted the same question in the LinkedIn group and got a good number of responses, though it seems like most people wanted to argue whether it was a good design choice or not, whereas I just wanted a survey of how many folks out there actually did logging to a DB.

One thing that is not clear to me is why you could not use a shared filesystem/SAN type of design to be able to have the best of both worlds. A flatfile logging mechanism, yet the ability to failover should one of the boxes connected to the filesystem goes down.

Relational database logging does offer a lot in terms of availability,
failover/redundancy and reporting, but I wouldn’t use it as a primary
message store for the reason you mentioned, the performance hit. This
does mean that you have to work out another scheme for persisting
messages in such a way as to provide seamless failover, but this can be
done in many ways that I would expect to be more efficient than using a
relational database. Also, if you’re logging to a database and it
becomes inaccessible for some reason, you have no logging. One solution
would be to do a hybrid implementation. For example, you could write
messages immediately to a file or some in-memory cache, then lazily
write them to a database.

5

I bet there was quite a debate in that FIX group. I agree with you that a filesystem/SAN scheme would probably work quite well. I alluded to using a flatfile immediately and then writing to some other centralized storage (e.g. a database, or as you said, some shared directory on a SAN) either as convenient (for best performance) or immediately (for best recoverability). I could also see a system with immediate, local file-based logging, and a separate service to sniff for FIX messages on the NIC that sends and receives the FIX traffic which would immediately persist any messages it detected to some other location(s) to help facilitate complete and swift failover.

Thanks Paul,

I posted the same question in the LinkedIn group and got a good number
of responses, though it seems like most people wanted to argue whether
it was a good design choice or not, whereas I just wanted a survey of
how many folks out there actually did logging to a DB.

One thing that is not clear to me is why you could not use a shared
filesystem/SAN type of design to be able to have the best of both
worlds. A flatfile logging mechanism, yet the ability to failover should
one of the boxes connected to the filesystem goes down.

Relational database logging does offer a lot in terms of availability,
failover/redundancy and reporting, but I wouldn’t use it as a primary
message store for the reason you mentioned, the performance hit. This
does mean that you have to work out another scheme for persisting
messages in such a way as to provide seamless failover, but this can
be done in many ways that I would expect to be more efficient than
using a relational database. Also, if you’re logging to a database and
it becomes inaccessible for some reason, you have no logging. One
solution would be to do a hybrid implementation. For example, you
could write messages immediately to a file or some in-memory cache,
then lazily write them to a database.

6

I think I should have been a bit more precise. When I said SAN, what I really meant was a FibreChannel attached disk with a shared(not distributed) filesystem on top of it (there are quite a few options on Linux, not sure whats out there for Windows). A SAN will probably lose out in terms of I/O latency. That way you don’t need to have any local logs and a process for transferring them elsewhere. You just write to the shared disk and it’s available to all attached machines.

Here’s the link to the LinkedIn discussion in case you’re curious - http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers&discussionID=10625621&gid=61789&commentID=9122112&trk=view_disc

I bet there was quite a debate in that FIX group. I agree with you that
a filesystem/SAN scheme would probably work quite well. I alluded to
using a flatfile immediately and then writing to some other centralized
storage (e.g. a database, or as you said, some shared directory on a
SAN) either as convenient (for best performance) or immediately (for
best recoverability). I could also see a system with immediate, local
file-based logging, and a separate service to sniff for FIX messages on
the NIC that sends and receives the FIX traffic which would immediately
persist any messages it detected to some other location(s) to help
facilitate complete and swift failover.

Thanks Paul,

I posted the same question in the LinkedIn group and got a good number
of responses, though it seems like most people wanted to argue whether
it was a good design choice or not, whereas I just wanted a survey of
how many folks out there actually did logging to a DB.

One thing that is not clear to me is why you could not use a shared
filesystem/SAN type of design to be able to have the best of both
worlds. A flatfile logging mechanism, yet the ability to failover
should one of the boxes connected to the filesystem goes down.

Relational database logging does offer a lot in terms of
availability, failover/redundancy and reporting, but I wouldn’t use
it as a primary message store for the reason you mentioned, the
performance hit. This does mean that you have to work out another
scheme for persisting messages in such a way as to provide seamless
failover, but this can be done in many ways that I would expect to
be more efficient than using a relational database. Also, if you’re
logging to a database and it becomes inaccessible for some reason,
you have no logging. One solution would be to do a hybrid
implementation. For example, you could write messages immediately to
a file or some in-memory cache, then lazily write them to a
database.