5 ms·

Strongly disagree on logging to database being inferior to flat files or Loggly/Splunk. The first point is good: you shouldn't put them in the same place as you

Strongly disagree on logging to database being inferior to flat files or Loggly/Splunk. The first point is good: you shouldn't put them in the same place as your production data, agreed. Then he goes on to recommend not logging into a database at all, which is just silly. Map/reduce on log data is absolutely wonderful and opens new worlds of answers that you can only dream of. A Hive workflow is fantastic for parsing log data. You can answer any question, and even GRAPH any answer from your logs. The insight gained from capturing and processing logs can do wonders for your performance problems, ideas for new uses of your code, and so on. I hate any approach to engineering that says "you'll never need it, so don't do it"; do it, and discover the great things that some things unlock for you. Burying your head in the sand on this point just limits your ability to unlock unique insight and gain an edge. I particularly disagree with this because of the flourish at the end of article, where he says there are no exceptions to his advice. Flatly wrong, and if you tell me "don't do it, trust me," I lose a bit of respect for you as a communicator.

koko775 14y ago
Altering the logging table scheme once it reaches several million rows? Not fun. I think flat files are much easier to scale.
- sukuriant 14y ago
  You could create a new table called Logs2 and use your database's union functionality when querying it. Perhaps even a view to make things a whole lot easier.
  - Yeroc 14y ago
    If you're talking about Postgres most people would likely implement this using date/time-based partitioning which provides a more seamless solution to this problem.
    - chris_wot 14y ago
      Not just Postgres folks - SQL Server and Oracle also :-)
    - sukuriant 14y ago
      Hehe. Advanced SQL is unfortunately not my thing. I only dabble in it; and the solution seemed easy enough given my ... limited knowledge of views and UNION
- jsprinkles 14y ago
  MongoDB capped collections. Solved.
- jacques_chester 14y ago
  Most serious RDBMSes can handle online DDL without locking the table.
wtracy 14y ago
Now I'm curious: Assume for the sake of argument that we're dealing with an app on a single physical machine, no separate DB server. If you wanted to have production data and logs both in, say, Postgres, would you run two separate Postgres daemons, or is there some saner way of handling this?
- shazow 14y ago
  I don't see why two daemons are necessary, simply having a separate database (in the CREATE DATABASE sense) should be sufficient to not make a big mess. There are lots of fun optimizations you can do like move its storage location to a separate disk to reduce IO contention. You could also avoid making indices on live log tables to improve INSERT performance. You could add an async buffering layer and do bulk-inserts which have much better performance. There's some more interesting discussion on large append-only tables here: http://archives.postgresql.org/pgsql-performance/2012-02/msg00083.php http://archives.postgresql.org/pgsql-performance/2012-02/msg... If you're sufficiently careful, I imagine you could find similar performance with PostgreSQL-based logging as you would with a rotating flatfile log. Except you wouldn't need to worry about consolidating and parsing them.
- deleted 14y ago
  [deleted]
- fusiongyro 14y ago
  It's not a good idea to run more than one database on the same physical machine. Especially a well-tuned database. The database always thinks it is the only and most important process on your machine. If you run more than one, they will constantly be contending with each other for the same I/O bandwidth. You will not see 1/N performance with N databases on the same machine, you will see something much worse. On top of that, it's a bad idea because, where are you going to put your database logs? In the database? What happens when you run out of disk? All your applications are going to be trying desperately to log that they're out of space, and your database won't have anywhere to put those messages. This is why /var/log is traditionally a separate filesystem in Unix. But if you log to the database, unless you go through some hoops (tablespaces) they're all going to the same partition and the same physical disk. Believe me, these scenarios happen in real life, and usually on a weekend. Even worse, some applications (though not Postgres, I think) will try and hold onto pending writes until there is free disk, so the moment you free up space, you lose it. You don't want to have to shutdown the production database just because your logs ate up a bunch of space. The right thing to do is let your database have its own machine (yes, physical machine) and its own disks. Ideally, your Postgres install has separate physical disks for the data and the WAL, and I'd probably put the regular logs on another disk as well if I could afford it. You'll be shocked how much better it will perform, too. Don't fool yourself. A bunch of VPSes is not the same as real hardware running a real database like Postgres.
ChuckMcM 14y ago
I was kinda going there too, while I agree that long term long storage is much better done in a Hadoop style system I used 24hr log grabs to provide visibility into bad actor behavior over those periods. As a search engine its not easy to see someone doing a .05 query/sec scrape but over 24 hrs those 4,000 queries in alphabetical order really stand out :-) So putting a section of time from a log into a data base for generating some reports and then dumping it, I don't have a problem with that.
- jsprinkles 14y ago
  Completely agree. I'm a strong advocate of MongoDB capped collections for this purpose. MongoDB had issues, but capped collections are great for rotating data.
  - frankwiles 14y ago
    Yeah I think a LOT of the criticism of my post would have been cleared up if I had specified RDBMS more clearly. In my experience, most people simply put a logline with a timestamp column in a table and then rarely if ever reference them except to tail/search through which is unnecessary workload on the DB in most cases.

koko775 14y ago

Altering the logging table scheme once it reaches several million rows? Not fun. I think flat files are much easier to scale.

sukuriant 14y ago
You could create a new table called Logs2 and use your database's union functionality when querying it. Perhaps even a view to make things a whole lot easier.
- Yeroc 14y ago
  If you're talking about Postgres most people would likely implement this using date/time-based partitioning which provides a more seamless solution to this problem.
  - chris_wot 14y ago
    Not just Postgres folks - SQL Server and Oracle also :-)
  - sukuriant 14y ago
    Hehe. Advanced SQL is unfortunately not my thing. I only dabble in it; and the solution seemed easy enough given my ... limited knowledge of views and UNION
jsprinkles 14y ago
MongoDB capped collections. Solved.
jacques_chester 14y ago
Most serious RDBMSes can handle online DDL without locking the table.

wtracy 14y ago

Now I'm curious: Assume for the sake of argument that we're dealing with an app on a single physical machine, no separate DB server. If you wanted to have production data and logs both in, say, Postgres, would you run two separate Postgres daemons, or is there some saner way of handling this?

shazow 14y ago
I don't see why two daemons are necessary, simply having a separate database (in the CREATE DATABASE sense) should be sufficient to not make a big mess. There are lots of fun optimizations you can do like move its storage location to a separate disk to reduce IO contention. You could also avoid making indices on live log tables to improve INSERT performance. You could add an async buffering layer and do bulk-inserts which have much better performance. There's some more interesting discussion on large append-only tables here: http://archives.postgresql.org/pgsql-performance/2012-02/msg00083.php http://archives.postgresql.org/pgsql-performance/2012-02/msg... If you're sufficiently careful, I imagine you could find similar performance with PostgreSQL-based logging as you would with a rotating flatfile log. Except you wouldn't need to worry about consolidating and parsing them.
deleted 14y ago
[deleted]
fusiongyro 14y ago
It's not a good idea to run more than one database on the same physical machine. Especially a well-tuned database. The database always thinks it is the only and most important process on your machine. If you run more than one, they will constantly be contending with each other for the same I/O bandwidth. You will not see 1/N performance with N databases on the same machine, you will see something much worse. On top of that, it's a bad idea because, where are you going to put your database logs? In the database? What happens when you run out of disk? All your applications are going to be trying desperately to log that they're out of space, and your database won't have anywhere to put those messages. This is why /var/log is traditionally a separate filesystem in Unix. But if you log to the database, unless you go through some hoops (tablespaces) they're all going to the same partition and the same physical disk. Believe me, these scenarios happen in real life, and usually on a weekend. Even worse, some applications (though not Postgres, I think) will try and hold onto pending writes until there is free disk, so the moment you free up space, you lose it. You don't want to have to shutdown the production database just because your logs ate up a bunch of space. The right thing to do is let your database have its own machine (yes, physical machine) and its own disks. Ideally, your Postgres install has separate physical disks for the data and the WAL, and I'd probably put the regular logs on another disk as well if I could afford it. You'll be shocked how much better it will perform, too. Don't fool yourself. A bunch of VPSes is not the same as real hardware running a real database like Postgres.

ChuckMcM 14y ago

I was kinda going there too, while I agree that long term long storage is much better done in a Hadoop style system I used 24hr log grabs to provide visibility into bad actor behavior over those periods. As a search engine its not easy to see someone doing a .05 query/sec scrape but over 24 hrs those 4,000 queries in alphabetical order really stand out :-) So putting a section of time from a log into a data base for generating some reports and then dumping it, I don't have a problem with that.

jsprinkles 14y ago
Completely agree. I'm a strong advocate of MongoDB capped collections for this purpose. MongoDB had issues, but capped collections are great for rotating data.
- frankwiles 14y ago
  Yeah I think a LOT of the criticism of my post would have been cleared up if I had specified RDBMS more clearly. In my experience, most people simply put a logline with a timestamp column in a table and then rarely if ever reference them except to tail/search through which is unnecessary workload on the DB in most cases.