database 将服务器日志文件写入数据库是个好主意吗？

Is writing server log files to a database a good idea?

提问by Thomas Owens

After reading an article about the subject from O'Reilly, I wanted to ask Stack Overflow for their thoughts on the matter.

在阅读O'Reilly 的一篇关于该主题的文章后，我想询问 Stack Overflow 对此事的看法。

回答by Jon Skeet

Write locally to disk, then batch insert to the database periodically (e.g. at log rollover time). Do that in a separate, low-priority process. More efficient and more robust...

本地写入磁盘，然后定期批量插入数据库（例如在日志翻转时）。在单独的、低优先级的过程中执行此操作。更高效、更稳健...

(Make sure that your database log table contains a column for "which machine the log event came from" by the way - very handy!)

（顺便确保您的数据库日志表包含“日志事件来自哪台机器”的列 - 非常方便！）

回答by MusiGenesis

I'd say no, given that a fairly large percentage of server errors involve problems communicating with databases. If the database were on another machine, network connectivity would be another source of errors that couldn't be logged.

我会说不，因为相当大比例的服务器错误涉及与数据库通信的问题。如果数据库在另一台机器上，网络连接将是另一个无法记录的错误来源。

If I were to log server errors to a database, it would be critical to have a backup logger that wrote locally (to an event log or file or something) in case the database was unreachable.

如果我要将服务器错误记录到数据库中，那么在数据库无法访问的情况下，拥有一个在本地写入（到事件日志或文件或其他内容）的备份记录器将是至关重要的。

回答by Piotr Perak

Log to DB if you can and it doesn't slow down your DB :)

如果可以，请登录到数据库并且它不会减慢您的数据库 :)

It's way way way faster to find anything in DB then in log files. Especially if you think ahead what you will need. Logging in db let's you query log table like this:

在数据库中查找任何内容比在日志文件中查找要快得多。特别是如果你提前考虑你需要什么。登录数据库让你查询日志表是这样的：

select * from logs
where log_name = 'wcf' and log_level = 'error'

then after you find error you can see the whole path that lead to this error

然后在发现错误后，您可以看到导致此错误的整个路径

select * from logs
where contextId = 'what you get from previous select' order by timestamp

How will you get this info if you log it in text files?

如果您将这些信息记录在文本文件中，您将如何获得这些信息？

Edit:As JonSkeet suggested this answer would be better if I stated that one should consider making logging to db asynchronous. So I state it :) I just didn't need it. For example how to do it you can check "Ultra Fast ASP.NET" by Richard Kiessig.

编辑：正如 JonSkeet 建议的那样，如果我说应该考虑将日志记录到 db 异步，这个答案会更好。所以我声明:) 我只是不需要它。例如如何做到这一点，您可以查看 Richard Kiessig 的“Ultra Fast ASP.NET”。

回答by Piotr Perak

If the database is production database, this is a horrible idea. You willhave issues with backups, replication, recovery. Like more storage for DB itself, replicas, if any, and backups. More time to setup and restore replication, more time to verify backups, more time to recover DB from backups.

如果数据库是生产数据库，这是一个可怕的想法。您将遇到备份、复制、恢复方面的问题。就像为数据库本身、副本（如果有）和备份提供更多存储空间一样。更多的时间来设置和恢复复制，更多的时间来验证备份，更多的时间从备份中恢复数据库。

回答by carson

It probably isn't a bad idea if you want the logs in a database but I would say not to follow the article's advice if you have a lot of log file entries. The main issue is that I've seen file systems have issues keeping up with logs coming from a busy site let alone a database. If you really want to do this I would look at loading the log files into the database after they are first written to disk.

如果您想要数据库中的日志，这可能不是一个坏主意，但如果您有很多日志文件条目，我会说不要遵循文章的建议。主要问题是我看到文件系统在跟上来自繁忙站点的日志方面存在问题，更不用说数据库了。如果你真的想这样做，我会考虑在日志文件第一次写入磁盘后将它们加载到数据库中。

回答by Andrew Carr

Think about a properly setup database that utilizes RAM for reads and writes? This is so much faster than writing to disk and would not present the disk IO bottleneck you see when serving large numbers of clients that occurs when threads start locking down due to the OS telling them to wait on currently executing threads that are using all available IO handles.

想想一个正确设置的数据库，它利用 RAM 进行读写？这比写入磁盘快得多，并且不会出现您在为大量客户端提供服务时看到的磁盘 IO 瓶颈，这种瓶颈是由于操作系统告诉它们等待当前正在执行的正在使用所有可用 IO 的线程而导致线程开始锁定时发生的处理。

I don't have any benchmarks to prove this data, although my latest application is rolling with database logging. This will have a failsafe as mentioned in one of these responses. If the database connection can not be created, create local database (h2 maybe?) and write to that. Then we can have a periodic check of database connectivity that will re-establish the connection, dump the local database, and push it to the remote database.

我没有任何基准来证明这些数据，尽管我的最新应用程序正在使用数据库日志记录。如这些响应之一中所述，这将具有故障保护功能。如果无法创建数据库连接，请创建本地数据库（可能是 h2？）并写入该数据库。然后我们可以定期检查数据库连接，重新建立连接，转储本地数据库，并将其推送到远程数据库。

This could be done during off hours if you do not have a H-A site.

如果您没有 HA 站点，这可以在下班时间完成。

Sometime in the future I hope to develop benchmarks to demonstrate my point.

在未来的某个时候，我希望开发基准来证明我的观点。

Good Luck!

祝你好运！