Routing Python Logs to Databases Efficiently

user_9542989 0
Viewed 31

I want to route some logs from my application to a database. Now, I know that this isn't exactly the ideal way to store logs, but my use case requires it.

I have also seen how one can write their own database logger as explained here,
python logging to database

This looks great, but given that a large number of logs are generated from an application, I feel like sending as many requests to the database could maybe overwhelm it? It may not be the most efficient solution?

Given that this argument is correct, what are some efficient methods for achieving this?

Some ideas that come to mind are,

  • Write the logs out to a log file during application run time and develop a script that will parse the file and make bulk inserts to a database.
  • Build some kind of queue architecture that the logs will be routed to, where each record will be inserted to the database in sequence.
  • Develop a type of reactive program, that will run in the background and route logs to the database.
  • etc.

What are some other possibilities that can be explored? Are there any best practices?

logging python database
1 Answers

The rule of thumb is that DB throughput will be greater if you can batch N row inserts into a single commit, rather than doing N separate commits.

Have your app append to a structured log file, such as a .CSV or an easily parsed logfile format. Be sure to .flush() before sleeping for a while, so recent output will be visible to other processes. Consider making a call to .fsync() every now and again if durability following power fail matters to the app.

Now you have timestamped structured logs that are safely stored in the filesystem. Clearly there are other ways, such as 0mq or Kafka, but FS is simplest and plays nicely with unit tests. During interactive debugging you can tail -f the file.

Now write a daemon that tail -f's the file and copies new records to the database. Upon reboot it will .seek() to end after perhaps copying any trailing lines that are missing from DB. Use kqueue -style events, or poll every K seconds and then sleep. You can .stat() the file to learn its current length. Beware of partial lines, where last character in file is not newline. Consume all unseen lines, BEGIN a transaction, INSERT each line, COMMIT the DB transaction, resume the loop.

When you do log rolling, avoid renaming logs. Prefer log filenames that contain ISO8601 timestamps. Perhaps you settle on daily logs. Writer won't append lines past midnight, and will move on to the next filename. Daemon will notice the newly created file and will .close() the old one, with optional delete of ancient logs more than a week old.

Log writers might choose to prepend a hashed checksum to each message, so the reader can verify it receieved the whole message intact.

A durable queue like Kafka certainly holds some attraction, but has more moving pieces. Maybe implement FS logging, with unit tests, and then use what you've already learned about the application, when you refactor to employ a more sophisticated message queueing API.

user_84311110
Related