Using logging hooks for real-time log analysis
Eisentraut, Peter
If you want to know right now what your server has been doing When problems arise, every DBA wants to know what the database has been up to. If your best idea so far has been to parse the log files or load the CSV logs into another database, you might want to learn about a better way. With logging hooks, you can attach data sinks directly to the PostgreSQL server log stream and analyze logging events as they happen. Many useful applications arise this way: put log data into another PostgreSQL database for ad hoc querying store log data in Hadoop for analytics throw log data into a graphing system generate alerts directly from logging events I'll show you how to put these things together and use them in practice.
 Google Translate thinks this page is in Tagalog, so up there. So if you can't understand me, I'm sorry about that. I don't have a lot of slides. I'm going to do some demos. But I'm told that everything here is being recorded in video and audio, so you can review that later. And I also have all the materials on public repositories, which I'll get into later. So I am Peter Eisenchraut. I'm PostgreSQL contributor. I currently work at Meet Me, which is a social networking platform. I live and work in Pennsylvania. And you can reach me on the internet at these places and others if you search for me. So for reasons that will become obvious in a moment, when I sort of started working on this problem space, which I'll get to in a moment, I thought of this. I was beyond query logging. But last year, I was already at this conference. In another conference, I was already at call beyond query logging. And so in some sense, this is sort of the next generation after that, perhaps. So my thing here says I'm a senior data architect, but in practice, most of my time, I'm a DBA. And we are, as I mentioned, a social network or social networking platform, depending on how you want to put it. So we have regular web infrastructure backed by Postgre databases, which are, there's a question there. Sorry, what? Volume? I'm not in control of that, but I can have someone help me here. So we have a website open to the internet. We do deployments multiple times a day. So the whole thing is in constant motion. We are the DBAs. We have to keep the bottom of the tier running. And we have monitoring setup, and every so often, some alarm triggers, and it would be fun if it triggers right now, but it doesn't so. And in my mind, or in the mind of our team, there are two kinds of alerts that we're interested. One is sort of what I think of maybe the hardware level alerts when the box has died, or the file system has gone read-only, or the disk has disappeared, and things like that. And now I can hear myself. And in those cases, we pass this off to the SysOps team, and they either have to repair the box, or repair the disk, or things like that. So we're not really concerned about that. We have the normal standby infrastructure, so we do a fail over if needed, and things go on. The more interesting and more problematic alerts are when certain metrics change. And the metrics that we're interested in, for example, is things like connection count, transactions per second, errors per second, CPU load, IO, weight, times, those sort of things. And those can change for all kinds of reasons. The website is getting hammered that new things were deployed that maybe don't work as they should, and things like that. And then we need to go in and find out what happened there. And that's what this is about. So one thing we actually have on the show is, in case a code was deployed that didn't work out well, we have this what we call the developer attitude readjustment tool, called short for dart, which is this thing here. That's our dart. So if you're a developer and your code sucked, that's what you get. So now let's take as an example, which is easy to understand. Let's say your monitoring tells you that the errors per second count went up. And now you want to know why. So the straightforward way to do this is you go into your log files and you grab around, perhaps. And after a while of grabbing, you think, it wouldn't be nice if you could put this into a more structured form, and then we can use a real query language on this. So many years ago, the CSV log format was created for that purpose ostensibly. And that's a reasonable idea, but for various reasons that doesn't really work for us. One is that just the time to take the log files and load them somewhere else and analyze them, that takes too long. And we had this infrastructure set up for that, but it wasn't fast enough. So if you want to know, OK, error count went up, I have to fix this within a few minutes. I can't wait five minutes, 10 minutes, for all the stuff to be copied over and loaded and indexed and all that stuff. The other problem with that is that if you have ongoing events, you want to track them as they go on. So if you take the log file and copy it over and take a look and see what happens, then what do you do with the data that came in in those couple minutes? Then you need to take the mark where you left off and then take the next chunk of the log file and do all kinds of stuff, and that's very complicated. I know some people do that, but it's not nice. And just as a side note, if your server is getting hammered, don't rotate the log files. That takes your server down. We've tried that once and didn't try it again. So there are other solutions in this space that you probably heard of, like something like PG Badger, PG Fooin, PG Stat Statements. Those are all tools that are related to that. But they don't really solve this problem of what was the query that threw the most errors in the last five minutes. What was the query that, how did that query behave yesterday at the same time and things like that? That didn't really help us either. So looking around, we came across an overlooked feature in Postgres 9.2, which is the logging hooks. So logging hooks are available in Postgres 9.2, so you can use them today. All of the stuff I'm describing here is what we're using in the real world. Like all hooks in Postgres, they're not documented. So that's why they get overlooked. But I have all this in the material later, but just to show you the commit that, make it bigger. So this was committed in 9.2. And the feature came from this fellow, Martin Pihlach, who mentioned down here, who works for Skype. And he's the guy who actually wrote the SQL Met stuff that I got blamed for four years ago. So I have sort of a, I'm always elatching onto his features, I suppose. And what it does is pretty simple. If you look at the diff, there's a huge comment here, which is actually the documentation. And actually, just all it does is introduces a new variable here, emit log hook. No, emit log hook. That's the pointer variable, which you can assign to a function. And that function gets called every time a log message is written to the text file that you would otherwise have. And so if you load a module using the normal mechanisms, you can execute any code you want at that point. And so now you have endless possibilities, right? So going back to the use case, what I want to do is take the log data and throw it into a structured storage, for example, the database. So basically what I want to do is something like that, right? So I have my production database here. I use this hook and then some extra code, which I'll get to. And then you can throw it in the other database, and then you can do your analysis there. And all of this is in quasi real time, right? Not actual real time, but as it happens, as a stream. So you could now write your own hook for that. And it's not very hard if you know C. But I think everyone who knows C is in the other talk about hacking Postgres. So the idea here is you use existing components to do the work. And Martin Peekelock also wrote a, I guess what you could consider the reference implementation for, a logging hook, which is this tool called PGLogForward, which you can search for. And I'll have the URL in the notes, but you can just search for that and you'll find it on GitHub. Now, as far as I understand, Skype's motivation behind this whole feature is not necessarily what I'm describing here, but they were concerned about that just plain logging and the log volume takes their servers down or has too much load on the server. So they wanted just to have a way to get the log data off quickly. But it lends itself to other uses as well. So, and now I'd like to show you how that works. So here's the GitHub repository for that. So you don't have to write the link down, but I'll have a little. And actually in the patches sub directory, there's the actual logging hook patch is in there. So you can actually backpatch that all the way to 8.4. And I have also done that. So that's very simple. As you saw, the patch is not big. So if you want to use this in older versions and you want to roll your own, you can just install that patch there and you can use the same feature there. Other than that, it's just one simple C file and you build it like a normal PGXS module. Which gets me to the demo. So I have this, all of the stuff I'm showing now is I have that on my GitHub. So you can play with that later also if you want. Is that reasonably big? So the first thing you want to do is you build it, right? And then you get this error, which you've probably seen a lot in if you want to build extension modules, which is really annoying me. So you need to do this thing here, right? All right, so that builds install. So that was easy. Now, this is Ubuntu here. So the paths are always a little bit different. So there it is. Well, you can't see. It's the green one because it's executable in the other ones or not. That's another platform difference, but it doesn't matter. Now, the way you configure this is as follows. So this is a Postgres configuration file with a bunch of stuff in it. And then at the end, you add these statements there. So there are two ways to load this. One is using shared preload libraries and one is using local preload libraries. And the difference for those who don't know is shared preload libraries get loaded into the Postmaster at the time you start the instance. And local preload libraries is loaded at the time you start a session. And through PGSatState, I'll just give you an example. But I recommend putting it into local preload libraries for the reason that, A, you don't have to restart your server to even install this. And two, it keeps the stuff out of the Postmaster, which you want to have stable. But you don't have to do it that way. You can also put it into shared preload libraries. In order to load stuff from shared preload libraries, you have to do the following. And you need to make a directory called plugins here and then move that thing in there. And that's totally bizarre. And I'm planning to get rid of that. And those who follow all the patches I sent to hackers, that's the reason why I introduced this feature, which didn't get into 9.3.2. Have sort of a white list of things you can put into local preload libraries. So in the future, I hope to have a better solution than that. And so now, that's the installation. So you need to do this, put it into the plugins directory. If you want to load it through local preload libraries, otherwise you just leave it where it was. And now you take this configuration file that I just showed as an example and put it into place. Reload, so you don't need to restart. It's nice. So while this loads, I have a back on job here which creates some logging. So we'll see something in a moment. Let's get back to that file here. So once you have loaded it one way or the other, you have this log forward namespace of new configuration parameters. And all right. Nope. All right. This isn't works fine. Sorry. All right. That's a good idea. And you can create multiple destinations, but this is just a simple case of you have one destination. You use the target names variable here, which you just invent a random name here. This doesn't mean anything. And then based on that name, you have additional parameters that get kind of created on the fly once it reads this here. And then you give it the destination of where this is supposed to go. And what PG log forward does, it sends the log data as UDP packets to the destinations that you give here in the formats that you give here. And it supports JSON, syslog, and net string. So syslog you know. So if you just keep using syslog, you can use that. But I guess JSON is the most interesting because it already gives you the log data in the structured form. And this is the destination of where you have the agent who receives that. And then you need to render port number. So once you have that running, you can use NETCAT to test this. Well, PG log format only supports JSON, syslog, and net string. But it's not hard to add more formats. And you should also consider this as a tool kit to some degree. I think the JSON format is just fine. But if you want to have something different, you just need to assemble that as a string and ship it out over UDP. So here's the UDP packet that is being sent around. So that's all the typical logging fields that you are familiar with in JSON format. And now you can imagine what you want to do with that. You just write your own little thing and process that. Unbundle the JSON. And then you have the logging data and you can do whatever you want with that. So you probably need to do some programming now. So in the PG log forward repository, there's also a, in the testing directory, is actually sort of an example implementation in Python, which kind of gives you a start if you want. So it's not long, two pages here. It actually handles JSON net string answers log. So this is even longer than you really need it to be. And so this you can also use to test it if you don't like netcard. And it prints out the stuff. And then you can actually use that as a template and just insert your own code there. So in order to, let me just show you how you would do multiple destinations. So here's a different configuration file which contains the stuff I just showed. But it adds a second destination here. So this is the stuff we already saw. And here I just invented a new target name which uses a slightly different destination. It uses a syslog format, uses a different port. And there's also a small number of additional arguments where you can pre-filter the stuff that gets processed by this. So this is not very user-friendly. Something perhaps to address is that you can give the minimum error level as an integer, which is the internal encoding of what is a debug info-warning error. So that's kind of, it's not really good, but you can do it and you can probably easily change that. So this would be, you can then write your second agent which just gets the errors in syslog format and then you can hook that into maybe an alerting system or a system that counts the errors or things like that. So I guess it's partially obvious. The reason UDP is used here is that you don't want this whole additional processing to hold up your actual session. So I have experimented with other logging hooks that, for example, using AMQP to put this information in the queue. But then if your broker is down, has problems, then the queue locks everything. So UDP in that sense is useful. But of course, this is not going to be an auditing system because it can lose packets on the load. So now getting back to the original goal is putting that into a database is not difficult from here. So create a new, you can put it into the same cluster if you want, but then you have kind of a looping problem if you don't configure this correctly. And in practice, you probably want to have this on a different host. So I'll make a separate cluster here and make a database, make a very small demo schema. Again, all this data is online. You can try this out yourself. So now I just created a very, I'll show the table in a moment. And I wrote, I essentially took the demo Python code I just showed and put some database stuff in there. So instead of just printing it out, it takes some of the log data and didn't put everything in here right now. But some of the log data just puts in the table. So that's pretty easy. This is literally one page of code here almost. So let's start that. Just running this as Postgres now so I don't have to set up authentication, all that kind of stuff now. So with any kind of luck, we have logging data in the table. And this is essentially very simple of what I'm actually using in practice. Now, if you look at that, you can imagine all kinds of things that you can improve here. So first of all, you want to change the E-level thing to something you can read. But for example, here, durations are being logged here. And for example, one thing I do in the actual code is I run this through a regular expression and split it up into two columns. So I have the duration in a separate column from the statement, and then I can run analysis on the query durations. And that's super useful. Or if you're specifically interested in disconnections or connections, you can take this and split it up and mangle it any way you want. So that's essentially the solution to my original problem. Now, of course, you also want to partition this table, maybe normalize it to split out the actual strings, which are obviously going to be repeated into a separate table. So you manage the storage that way. And the way I run this, I start this agent on SupervisorD, and that way you can also spin up multiple instances quite easily, because this thing is not going to be very concurrent, or it's not concurrent, despite it doesn't support that very easily. So you can spin up a bunch of different instances, give them different port numbers and point different places, different production sources of different ports, and achieve concurrency there. It's not very pretty, but it seems to work just fine. So that's what I'm doing. One thing I'm actually playing with it in the terms of concurrency is I re-implemented that agent I just showed in Go, the programming language, which has better concurrency support. And I put that in GitHub, so if someone wants to experiment with Go, perhaps you can look at that. And this is not what I'm actually using in production, but it's something I'm playing with to make this optimize this perhaps. Now again, this is something everyone here can just install and use, or set up something that suits their particular use case. Something that came up during the developer meeting yesterday is to perhaps write a pre-packaged solution here that is more easy to deploy, and you don't have to write any code yourself. So that might be a future direction here to just write a different log-in hook that logs straight back into the same instance, avoiding loops and stuff like that, of course. And then for small instances, you can perhaps just load that and use it without having to do any extra work. That might be something to try. Well, the advantage of loading it in local pre-load libraries is that you can actually set that per database or use also. So that might also be useful. But of course, one of the advantages of not having the same instance, or not even having the same host, is that you can also aggregate data from multiple machines. So you have multiple machines logging into the same table, or partition set of tables, and compare things that way. So that is also definitely an advantage of this over some of the other solutions that just look at a single log file. So now, that said, this essentially solves my use case of what happened in the last five minutes. If you want to think a little bit long term, perhaps you and you want to basically keep all the logs and do big data analysis later, you can also, for example, throw this stuff into Hadoop. And I have some things to show how to do that. So getting back to this awesome picture I made here. So in the setup I'm using, I'm still using PG log forward as the hook, so I'm not writing my own hook, but instead of the agent, I'm using something called Apache flume. That's an Apache project, which is at flumeapach.org. And it has a nice picture on the home page, which looks somewhat familiar to what we want to do. And so flume describes itself as a distributed, reliable, available service for efficiently collecting, aggregating, and moving large amounts of log data. So it seems to be purposeful for this sort of thing. Being an Apache project, their main primary use case is having web server log data put into a big data store. But it doesn't have to be web server on this side, and it doesn't also have to be HFS on this side. It's kind of geared toward putting things as a front end to HFS, essentially. But the things on this side can be pretty much anything. And they support even tailing a log file and throwing it in here, or getting syslog data, or all kinds of other things. Or you can write your own, which is what I did. So all this is in Java, so you just download the JAR file and unpack it, and then you're ready to go. So if you dig deep into this space of moving structured data from one end to some other structured storage, there's about eight different formats you'll encounter in five different programming languages. And you have to somehow pick a combination that is the least headache and loses the least amount of data on the way. And I'm sure there's other permutations of getting that data. So one of the most straightforward ways to do this would be to take data in syslog format, which Flume supports out of the box, and throw the syslog data into Flume over UDP or TCP, if you want, and then throw this into HGFS unstructured, and then maybe use PIG or Hive to get it out. And that would work, but it's not really what I set out to do. So I wrote a few pieces of code of my own, so I wrote a sync that takes that JSON-UDP format, unpacks it, into Flume's native internal transport format. And then on the other side, takes that structured format and throws it into HBase as separate columns, essentially. So the way you can figure Flume has sources and syncs. So here's a bunch of sources you can use. I just threw a few in as an example. Here's a source that I wrote, which is not published at the moment, but I plan to do that. This just has to go through some internal clearances. But this is not the rocket science code. So I'm running this code on this port here, which should look familiar from the previous examples. That's where PgLogForward publishes. Flume has also additional features here as a thing called an interceptor, which sort of mangles the data. And one interceptor you can use, for example, is to throw a timestamp into the existing data, because PgLogForward doesn't actually provide a timestamp, which is kind of weird. And on the sync side, here's an example for a sync using the built-in HGFS support, which just throws the data in an unstructured way into HGFS files. And if you don't know what to do with it, there's actually talking in the next slot. That would be one file per log. No, it actually sort of rotates file as it wants. It just throws stuff into files. And then after, there's additional parameters here. You can tweak this to death of what files it creates and what they're named. And then you would use something like Hive, which just sort of transparently wraps over that. And you can mangle that data however you want. So that's one example. And the other, what I've sort of been concentrating on is using HBase. This is the HBase sync. It's shipped with Flume, but it doesn't actually know how to unpack the stuff I'm sending in. So I wrote a separate serializer. So there's a bunch of Java classes in this pipe. And you have to know how to wire them together correctly. So just to show that as well. See if HBase works today. Just create a table, one column family. Very exciting. Very slow. Oh, I have no space. Well, this is actually running locally on SleshTam, so it shouldn't be that slow. Right. Now, maybe I didn't actually start it. Let's start it first. This is also bizarre. If you don't start HBase, you can start the shell, which is like a PS girl equivalent, but you can't do anything. So it's very interesting. All right, so that was actually 2.4 seconds to create a table in HBase. Awesome. So this Flume has not many options, actually. Quite usable. Flume, start an agent, to do some claspath stuff, to get your custom jar stuff in there. You have to give it a config file, which is the one I just showed you. Then you have to tell which agent to use. And let's see if that works. OK. So this is debugging output from my code, so you can see it's already doing something. And with any kind of luck, we'll have something over here. OK, there's all your data. And you can do stuff with HBase then. So if you want to play with HBase, but you don't have any data put in it, this is a way to generate lots of data. So that's basically why I'm at. As I mentioned, the first half of the talk, putting log data into Postgres is what I'm using. This is something I'm looking into actively now, the HBase stuff. Something that I originally wanted to do, but have not found a good solution for it. But have not found a good solution for it yet is to actually do the actual rate monitoring using this. So in the beginning, I mentioned I'm looking at metrics like number of errors per second or number of transactions per second. And that stuff is being computed by a separate kind of legacy monitoring system. And ideally, if you have this kind of data, you can throw it into some system that can do the counting for you. But I haven't really found a good one to do that yet. So a couple of things I looked at was a thing called rocksteady, which I have a link for. Which also comes with a nice picture, but has lots of moving parts. So if you can't read all the small print. So this is your stuff. This is RabbitMQ. This is rocksteady. This is graphite. This is Nagios. This is dashboard. So there's way too many moving parts here that I have to monitor the monitoring here, essentially. So that is a bit to me at the moment. But maybe a lot of effort can probably make this work. Another thing that I'm actively looking at is a thing called Riemann, like the mathematician. So Riemann.io, that would actually do exactly what I need. It just kind of haven't managed to set it up yet. But maybe that's going to be something to look at in the next couple of months. And of course, graphite you can also use. And I'm also looking into throwing log data into graphite, just to graph it. But the rate monitoring of graphite, I haven't found that too reliable. It gives you some useful numbers. But if you want to tie that directly to the monitoring, it's not exact enough. So I'm not really using that at the moment. So that said, there are a growing number of commercial platform or software-as-a-service type solutions out there that overlap a lot with all of these problems, such as New Relic or Splunk. You've probably heard of one or the other. So Splunk is, you throw all your SysLog data into Splunk and it sort of does a full text indexing on that. And you can do analysis based on that. But that doesn't have any knowledge of the structure of the Postgres log, so that it's not exactly what we want. And New Relic is also something we're using. But it doesn't have any knowledge of Postgres either. It's sort of getting there, but it's not there yet. So but perhaps in a few years when we look back, we don't have to do this anymore. But we don't know yet. Of course, all of these solutions cost a huge amount of money, so if you don't want to spend that money, then these are the toolkits for you. So to conclude. So look into the logging hooks and look into PGLog forward. I think you can probably use that. And if you have been wanting for additional features into PGStats statements, maybe you don't need to wait for that. Maybe you can roll your own analysis based on that. So here are a couple links. So in theory, I have all this material in the GitHub repository, which in theory should be linked from the PGcon website. It wasn't as of a couple hours ago, but I think it's syncing to Pentaparf once in a while. So eventually, it'll show up there. Or you can find it through me if you don't find it otherwise. So I have the link to all the tools. Here's the logging hook commit, which essentially serves at the documentation if you want to look that up. The PGLog forward tool I showed, the thing I started writing in Go, if you want to experiment with that. The link to Flume. Another thing, actually, I forgot, we're looking into is throwing the log data into RabbitMQ. So I mentioned I tried to write a logging hook that publishes straight into MQP, but I sort of abandoned that because of the blocking issue. But there is a UDP plug-in for RabbitMQ, which a small amount of tweaking, you could actually just throw the UDP data that PGLog forward publishes into that. And then you have it in the queue. And then you don't have to write your own agents and stuff. You just write consumers that use that data. I am told by our resident RabbitMQ expert that there are some architectural problems with that UDP exchange as it's implemented, given time, which is scarce, that can be fixed. But that's certainly very interesting. And then link to Rocks. So that is what I have. Any questions? Josh. So now PGLog forward doesn't have any way to filter inside? It does have a very rudimentary way to filter. So the question for the audio here is, Josh asked if the PGLog forward doesn't have any way of filtering. It does have very simple ways of filtering. One I show it by the log level. The other is it has a way to filter by just a text string it looks for. So you can do a small amount of filtering with that. If you want to go beyond that, of course, you can change the source code. The question in that then becomes how much load do you want to put into that component versus the downstream components? I don't think there's a universal answer for that. As I mentioned, Skype was sort of building this as a way of offloading load from the server. So they're probably not going to be very interested in that. That's how I should mention, Martin does take patches to PGLog forward. So I've sent him a few fixes and many features so that is certainly inactive development. There are certainly probably ways you can add a little bit more filtering, but if it's too complex, you probably want to put that somewhere downstream. Susanna? Do you know the license of PGLog forward? The license of PGLog forward, probably as many things on GitHub doesn't have a license. Oh, actually, it has a license. All right, that's the license. Well, you can't see the letters, but you can guess from the shape of the license of this. Yeah, it's fine to use. Yeah, OK, so you're saying you want to use triggers on the logging database to inform about events? Basically, you can parse it. What's in that comes in? You can basically, or you're triggered to parse it. You can basically put it in tables. You can basically break that data out to sort of type things in those people. You can basically put it in different buckets. And then you can go to the front end and actually look at it. Yeah, OK, so to repeat that, so you're saying you want to put triggers on the logging table to look for, based on text or other things to look for events. Put them in other places. Yes, OK, yes, certainly. I mean, the possibilities here are endless. And part of the reason why I want to show this here is to just get more people involved in these sort of ideas to see what kind of a common ground would be. One caveat there is, of course, if you produce a lot of logging data, you can also overload your logging database. So I would be a little bit hesitant to put lots of extra triggers and stuff on the receiving end, and perhaps have more asynchronous processing for that. But you have to find that out for yourself. But that's certainly a possibility. Jan? That was one thing I was wondering about. If you overload your logging database, all these things are basically huge e-pays, right? So if you overload your logging database, you lose messages or not. Yeah, but that's not different from how the stats collector works. So at some point, you either have to wait, or you have to lose the data. That's the trade-off you have to make for yourself. I have what I'm using, I spend a fair amount of tuning it for our workload and network and all that stuff. So it seems to work well. I would like to throw more data in there, but I have found a sweet spot that works, and we're all OK. So yeah, we do our partitioning and indexes. Of course, the basic stuff, right? Do you have a business card or anything? No. What's a business card? OK, my business card is this. Yeah, but feel free to come up afterwards if you want to stay in contact. People who know me know where to find me. If you don't know where to find me, please send me an email or tweet me. I would certainly like to chat with more people about what they have interest in this space to develop more common solutions. Nope. OK, if you're interested in Hadoop, there's a Hadoop session in the next slot. So otherwise, thanks.
 Google Translate thinks this page is in Tagalog, so up there.  So if you can't understand me, I'm sorry about that.  I don't have a lot of slides.  I'm going to do some demos.  But I'm told that everything here is being recorded in  video and audio, so you can review that later.  And I also have all the materials on public  repositories, which I'll get into later.  So I am Peter Eisenchraut.  I'm PostgreSQL contributor.  I currently work at Meet Me, which is a social networking  platform.  I live and work in Pennsylvania.  And you can reach me on the internet at these places and  others if you search for me.  So for reasons that will become obvious in a moment, when I  sort of started working on this problem space, which I'll  get to in a moment, I thought of this.  I was beyond query logging.  But last year, I was already at this conference.  In another conference, I was already at  call beyond query logging.  And so in some sense, this is sort of the next generation  after that, perhaps.  So my thing here says I'm a senior data architect, but in  practice, most of my time, I'm a DBA.  And we are, as I mentioned, a social network or social  networking platform, depending on how you want to put it.  So we have regular web infrastructure backed by  Postgre databases, which are, there's a question there.  Sorry, what?  Volume?  I'm not in control of that, but I can have  someone help me here.  So we have a website open to the internet.  We do deployments multiple times a day.  So the whole thing is in constant motion.  We are the DBAs.  We have to keep the bottom of the tier running.  And we have monitoring setup, and every so often, some alarm  triggers, and it would be fun if it triggers right now, but  it doesn't so.  And in my mind, or in the mind of our team, there are two  kinds of alerts that we're interested.  One is sort of what I think of maybe the hardware level  alerts when the box has died, or the file system has gone  read-only, or the disk has disappeared, and things like  that.  And now I can hear myself.  And in those cases, we pass this off to the SysOps team,  and they either have to repair the box, or repair the disk,  or things like that.  So we're not really concerned about that.  We have the normal standby infrastructure, so we do a fail  over if needed, and things go on.  The more interesting and more problematic alerts are when  certain metrics change.  And the metrics that we're interested in, for example, is  things like connection count, transactions per second,  errors per second, CPU load, IO, weight, times, those  sort of things.  And those can change for all kinds of reasons.  The website is getting hammered that new things were  deployed that maybe don't work as they should, and things  like that.  And then we need to go in and find out what happened there.  And that's what this is about.  So one thing we actually have on the show is, in case a code  was deployed that didn't work out well, we have this what we  call the developer attitude readjustment tool, called  short for dart, which is this thing here.  That's our dart.  So if you're a developer and your code sucked, that's  what you get.  So now let's take as an example, which is easy to  understand.  Let's say your monitoring tells you that the errors per  second count went up.  And now you want to know why.  So the straightforward way to do this is you go into your  log files and you grab around, perhaps.  And after a while of grabbing, you think, it wouldn't be  nice if you could put this into a more structured form, and  then we can use a real query language on this.  So many years ago, the CSV log format was created for  that purpose ostensibly.  And that's a reasonable idea, but for various reasons that  doesn't really work for us.  One is that just the time to take the log files and load  them somewhere else and analyze them, that takes too long.  And we had this infrastructure set up for that, but it  wasn't fast enough.  So if you want to know, OK, error count went up, I have to  fix this within a few minutes.  I can't wait five minutes, 10 minutes, for all the stuff to  be copied over and loaded and indexed and all that stuff.  The other problem with that is that if you have ongoing  events, you want to track them as they go on.  So if you take the log file and copy it over and take a  look and see what happens, then what do you do with the data  that came in in those couple minutes?  Then you need to take the mark where you left off and then  take the next chunk of the log file and do all kinds of  stuff, and that's very complicated.  I know some people do that, but it's not nice.  And just as a side note, if your server is getting hammered,  don't rotate the log files.  That takes your server down.  We've tried that once and didn't try it again.  So there are other solutions in this space that you probably  heard of, like something like PG Badger, PG Fooin, PG  Stat Statements.  Those are all tools that are related to that.  But they don't really solve this problem of what was the  query that threw the most errors in the last five  minutes.  What was the query that, how did that query behave yesterday  at the same time and things like that?  That didn't really help us either.  So looking around, we came across an overlooked feature  in Postgres 9.2, which is the logging hooks.  So logging hooks are available in Postgres 9.2, so you can  use them today.  All of the stuff I'm describing here is what we're  using in the real world.  Like all hooks in Postgres, they're not documented.  So that's why they get overlooked.  But I have all this in the material later, but just to  show you the commit that, make it bigger.  So this was committed in 9.2.  And the feature came from this fellow, Martin Pihlach, who  mentioned down here, who works for Skype.  And he's the guy who actually wrote the SQL Met stuff that  I got blamed for four years ago.  So I have sort of a, I'm always elatching onto his  features, I suppose.  And what it does is pretty simple.  If you look at the diff, there's a huge comment here,  which is actually the documentation.  And actually, just all it does is introduces a new variable  here, emit log hook.  No, emit log hook.  That's the pointer variable, which you can assign to a  function.  And that function gets called every time a log message is  written to the text file that you would otherwise have.  And so if you load a module using the normal mechanisms,  you can execute any code you want at that point.  And so now you have endless possibilities, right?  So going back to the use case, what I want to do is take the  log data and throw it into a structured storage, for example,  the database.  So basically what I want to do is something like that, right?  So I have my production database here.  I use this hook and then some extra code, which I'll get to.  And then you can throw it in the other database, and then  you can do your analysis there.  And all of this is in quasi real time, right?  Not actual real time, but as it happens, as a stream.  So you could now write your own hook for that.  And it's not very hard if you know C. But I think everyone  who knows C is in the other talk about hacking Postgres.  So the idea here is you use existing components to do  the work.  And Martin Peekelock also wrote a, I guess what you could  consider the reference implementation for, a logging  hook, which is this tool called PGLogForward, which you  can search for.  And I'll have the URL in the notes, but you can just search  for that and you'll find it on GitHub.  Now, as far as I understand, Skype's motivation behind this  whole feature is not necessarily what I'm describing  here, but they were concerned about that just plain logging  and the log volume takes their servers down or has too much  load on the server.  So they wanted just to have a way to get the log data off  quickly.  But it lends itself to other uses as well.  So, and now I'd like to show you how that works.  So here's the GitHub repository for that.  So you don't have to write the link down, but I'll have a  little.  And actually in the patches sub directory, there's the  actual logging hook patch is in there.  So you can actually backpatch that all the way to 8.4.  And I have also done that.  So that's very simple.  As you saw, the patch is not big.  So if you want to use this in older versions and you want to  roll your own, you can just install that patch there and  you can use the same feature there.  Other than that, it's just one simple C file and you build it  like a normal PGXS module.  Which gets me to the demo.  So I have this, all of the stuff I'm showing now is I have  that on my GitHub.  So you can play with that later also if you want.  Is that reasonably big?  So the first thing you want to do is you build it, right?  And then you get this error, which you've probably seen a  lot in if you want to build extension modules, which is  really annoying me.  So you need to do this thing here, right?  All right, so that builds install.  So that was easy.  Now, this is Ubuntu here.  So the paths are always a little bit different.  So there it is.  Well, you can't see.  It's the green one because it's executable in the other  ones or not.  That's another platform difference, but it doesn't matter.  Now, the way you configure this is as follows.  So this is a Postgres configuration file with a  bunch of stuff in it.  And then at the end, you add these statements there.  So there are two ways to load this.  One is using shared preload libraries and one is using  local preload libraries.  And the difference for those who don't know is shared  preload libraries get loaded into the Postmaster at the  time you start the instance.  And local preload libraries is loaded at the time you start  a session.  And through PGSatState, I'll just give you an example.  But I recommend putting it into local preload libraries for  the reason that, A, you don't have to restart your server  to even install this.  And two, it keeps the stuff out of the Postmaster, which you  want to have stable.  But you don't have to do it that way.  You can also put it into shared preload libraries.  In order to load stuff from shared preload libraries, you  have to do the following.  And you need to make a directory called plugins here and  then move that thing in there.  And that's totally bizarre.  And I'm planning to get rid of that.  And those who follow all the patches I sent to hackers,  that's the reason why I introduced this feature, which  didn't get into 9.3.2.  Have sort of a white list of things you can put into local  preload libraries.  So in the future, I hope to have a better solution than that.  And so now, that's the installation.  So you need to do this, put it into the plugins directory.  If you want to load it through local preload libraries,  otherwise you just leave it where it was.  And now you take this configuration file that I  just showed as an example and put it into place.  Reload, so you don't need to restart.  It's nice.  So while this loads, I have a back on job here which creates  some logging.  So we'll see something in a moment.  Let's get back to that file here.  So once you have loaded it one way or the other, you have  this log forward namespace of new configuration  parameters.  And all right.  Nope.  All right.  This isn't works fine.  Sorry.  All right.  That's a good idea.  And you can create multiple destinations, but this is just  a simple case of you have one destination.  You use the target names variable here, which you just  invent a random name here.  This doesn't mean anything.  And then based on that name, you have additional parameters  that get kind of created on the fly once it reads this here.  And then you give it the destination of where this  is supposed to go.  And what PG log forward does, it sends the log data as UDP  packets to the destinations that you give here in the  formats that you give here.  And it supports JSON, syslog, and net string.  So syslog you know.  So if you just keep using syslog, you can use that.  But I guess JSON is the most interesting because it already  gives you the log data in the structured form.  And this is the destination of where you have the agent who  receives that.  And then you need to render port number.  So once you have that running, you can use NETCAT to  test this.  Well, PG log format only supports JSON, syslog, and  net string.  But it's not hard to add more formats.  And you should also consider this as a tool kit to some  degree.  I think the JSON format is just fine.  But if you want to have something different, you just  need to assemble that as a string and ship it out over UDP.  So here's the UDP packet that is being sent around.  So that's all the typical logging fields that you are  familiar with in JSON format.  And now you can imagine what you want to do with that.  You just write your own little thing and process that.  Unbundle the JSON.  And then you have the logging data and you can do  whatever you want with that.  So you probably need to do some programming now.  So in the PG log forward repository, there's also a,  in the testing directory, is actually sort of an example  implementation in Python, which kind of gives you a  start if you want.  So it's not long, two pages here.  It actually handles JSON net string answers log.  So this is even longer than you really need it to be.  And so this you can also use to test it if you don't like  netcard.  And it prints out the stuff.  And then you can actually use that as a template and just  insert your own code there.  So in order to, let me just show you how you would do  multiple destinations.  So here's a different configuration file which  contains the stuff I just showed.  But it adds a second destination here.  So this is the stuff we already saw.  And here I just invented a new target name which uses a  slightly different destination.  It uses a syslog format, uses a different port.  And there's also a small number of additional arguments  where you can pre-filter the stuff that gets  processed by this.  So this is not very user-friendly.  Something perhaps to address is that you can give the  minimum error level as an integer, which is the internal  encoding of what is a debug info-warning error.  So that's kind of, it's not really good, but you can do  it and you can probably easily change that.  So this would be, you can then write your second agent  which just gets the errors in syslog format and then you  can hook that into maybe an alerting system or a system  that counts the errors or things like that.  So I guess it's partially obvious.  The reason UDP is used here is that you don't want this  whole additional processing to hold up your actual session.  So I have experimented with other logging hooks that, for  example, using AMQP to put this information in the queue.  But then if your broker is down, has problems, then the queue  locks everything.  So UDP in that sense is useful.  But of course, this is not going to be an  auditing system because it can lose  packets on the load.  So now getting back to the original goal is putting that  into a database is not difficult from here.  So create a new, you can put it into the same cluster if you  want, but then you have kind of a looping problem if you  don't configure this correctly.  And in practice, you probably want to have this on a  different host.  So I'll make a separate cluster here and make a database,  make a very small demo schema.  Again, all this data is online.  You can try this out yourself.  So now I just created a very, I'll show the  table in a moment.  And I wrote, I essentially took the demo Python code I just  showed and put some database stuff in there.  So instead of just printing it out, it takes some of the log  data and didn't put everything in here right now.  But some of the log data just puts in the table.  So that's pretty easy.  This is literally one page of code here almost.  So let's start that.  Just running this as Postgres now so I don't have to  set up authentication, all that kind of stuff now.  So with any kind of luck, we have logging data in the table.  And this is essentially very simple of what I'm actually  using in practice.  Now, if you look at that, you can imagine all kinds of  things that you can improve here.  So first of all, you want to change the E-level thing to  something you can read.  But for example, here, durations are being logged here.  And for example, one thing I do in the actual code is I  run this through a regular expression and  split it up into two columns.  So I have the duration in a separate column from the  statement, and then I can run analysis on the query  durations.  And that's super useful.  Or if you're specifically interested in disconnections or  connections, you can take this and split it up and  mangle it any way you want.  So that's essentially the solution to my original  problem.  Now, of course, you also want to partition this table, maybe  normalize it to split out the actual strings, which are  obviously going to be repeated into a separate table.  So you manage the storage that way.  And the way I run this, I start this agent on  SupervisorD, and that way you can also spin up multiple  instances quite easily, because this thing is not going  to be very concurrent, or it's not concurrent, despite  it doesn't support that very easily.  So you can spin up a bunch of different instances, give them  different port numbers and point different places, different  production sources of different ports, and achieve  concurrency there.  It's not very pretty, but it seems to work just fine.  So that's what I'm doing.  One thing I'm actually playing with it in the terms of  concurrency is I re-implemented that agent I just  showed in Go, the programming language, which has better  concurrency support.  And I put that in GitHub, so if someone wants to experiment  with Go, perhaps you can look at that.  And this is not what I'm actually using in production,  but it's something I'm playing with to make this  optimize this perhaps.  Now again, this is something everyone here can just install  and use, or set up something that suits their  particular use case.  Something that came up during the developer meeting  yesterday is to perhaps write a pre-packaged solution here  that is more easy to deploy, and you don't have to write  any code yourself.  So that might be a future direction here to just write  a different log-in hook that logs straight back into the  same instance, avoiding loops and stuff like that, of course.  And then for small instances, you can perhaps just load that  and use it without having to do any extra work.  That might be something to try.  Well, the advantage of loading it in local pre-load  libraries is that you can actually set that per  database or use also.  So that might also be useful.  But of course, one of the advantages of not having the  same instance, or not even having the same host, is that  you can also aggregate data from multiple machines.  So you have multiple machines logging into the same table, or  partition set of tables, and compare things that way.  So that is also definitely an advantage of this over some  of the other solutions that just look at a single log file.  So now, that said, this essentially solves my use case  of what happened in the last five minutes.  If you want to think a little bit long term, perhaps you  and you want to basically keep all the logs and do big data  analysis later, you can also, for example, throw this stuff  into Hadoop.  And I have some things to show how to do that.  So getting back to this awesome picture I made here.  So in the setup I'm using, I'm still using PG log forward as  the hook, so I'm not writing my own hook, but instead of the  agent, I'm using something called Apache flume.  That's an Apache project, which is at flumeapach.org.  And it has a nice picture on the home page, which looks  somewhat familiar to what we want to do.  And so flume describes itself as a distributed, reliable,  available service for efficiently collecting,  aggregating, and moving large amounts of log data.  So it seems to be purposeful for this sort of thing.  Being an Apache project, their main primary use case is  having web server log data put into a big data store.  But it doesn't have to be web server on this side, and it  doesn't also have to be HFS on this side.  It's kind of geared toward putting things as a front end  to HFS, essentially.  But the things on this side can be pretty much anything.  And they support even tailing a log file and throwing it in  here, or getting syslog data, or all kinds of other things.  Or you can write your own, which is what I did.  So all this is in Java, so you just download the JAR file  and unpack it, and then you're ready to go.  So if you dig deep into this space of moving structured  data from one end to some other structured storage, there's  about eight different formats you'll encounter in five  different programming languages.  And you have to somehow pick a combination that is the least  headache and loses the least amount of data on the way.  And I'm sure there's other permutations of getting that  data.  So one of the most straightforward ways to do  this would be to take data in syslog format, which Flume  supports out of the box, and throw the syslog data into  Flume over UDP or TCP, if you want, and then throw this  into HGFS unstructured, and then maybe use PIG or Hive to  get it out.  And that would work, but it's not really what I set out to  do.  So I wrote a few pieces of code of my own, so I wrote a  sync that takes that JSON-UDP format, unpacks it, into  Flume's native internal transport format.  And then on the other side, takes that structured  format and throws it into HBase as separate columns,  essentially.  So the way you can figure Flume has sources and syncs.  So here's a bunch of sources you can use.  I just threw a few in as an example.  Here's a source that I wrote, which is not published at  the moment, but I plan to do that.  This just has to go through some internal clearances.  But this is not the rocket science code.  So I'm running this code on this port here, which should  look familiar from the previous examples.  That's where PgLogForward publishes.  Flume has also additional features here as a thing  called an interceptor, which sort of mangles the data.  And one interceptor you can use, for example, is to throw a  timestamp into the existing data, because PgLogForward  doesn't actually provide a timestamp, which is kind of  weird.  And on the sync side, here's an example for a sync using the  built-in HGFS support, which just throws the data in an  unstructured way into HGFS files.  And if you don't know what to do with it, there's actually  talking in the next slot.  That would be one file per log.  No, it actually sort of rotates file as it wants.  It just throws stuff into files.  And then after, there's additional parameters here.  You can tweak this to death of what files it creates and  what they're named.  And then you would use something like Hive, which just  sort of transparently wraps over that.  And you can mangle that data however you want.  So that's one example.  And the other, what I've sort of been concentrating on is  using HBase.  This is the HBase sync.  It's shipped with Flume, but it doesn't actually know how to  unpack the stuff I'm sending in.  So I wrote a separate serializer.  So there's a bunch of Java classes in this pipe.  And you have to know how to wire them together correctly.  So just to show that as well.  See if HBase works today.  Just create a table, one column family.  Very exciting.  Very slow.  Oh, I have no space.  Well, this is actually running locally on SleshTam, so it  shouldn't be that slow.  Right.  Now, maybe I didn't actually start it.  Let's start it first.  This is also bizarre.  If you don't start HBase, you can start the shell, which is  like a PS girl equivalent, but you can't do anything.  So it's very interesting.  All right, so that was actually 2.4 seconds to  create a table in HBase.  Awesome.  So this Flume has not many options, actually.  Quite usable.  Flume, start an agent, to do some claspath stuff, to get  your custom jar stuff in there.  You have to give it a config file, which is the one I just  showed you.  Then you have to tell which agent to use.  And let's see if that works.  OK.  So this is debugging output from my code, so you can see  it's already doing something.  And with any kind of luck, we'll have something over here.  OK, there's all your data.  And you can do stuff with HBase then.  So if you want to play with HBase, but you don't have  any data put in it, this is a way to generate lots of data.  So that's basically why I'm at.  As I mentioned, the first half of the talk, putting log  data into Postgres is what I'm using.  This is something I'm looking into actively now, the HBase  stuff.  Something that I originally wanted to do, but have not  found a good solution for it.  But have not found a good solution for it yet is to  actually do the actual rate monitoring using this.  So in the beginning, I mentioned I'm looking at  metrics like number of errors per second or number of  transactions per second.  And that stuff is being computed by a separate kind of  legacy monitoring system.  And ideally, if you have this kind of data, you can throw  it into some system that can do the counting for you.  But I haven't really found a good one to do that yet.  So a couple of things I looked at was a thing called  rocksteady, which I have a link for.  Which also comes with a nice picture, but has lots of  moving parts.  So if you can't read all the small print.  So this is your stuff.  This is RabbitMQ.  This is rocksteady.  This is graphite.  This is Nagios.  This is dashboard.  So there's way too many moving parts here that I have to  monitor the monitoring here, essentially.  So that is a bit to me at the moment.  But maybe a lot of effort can probably make this work.  Another thing that I'm actively looking at is a  thing called Riemann, like the mathematician.  So Riemann.io, that would actually do exactly what I  need.  It just kind of haven't managed to set it up yet.  But maybe that's going to be something to look at in the  next couple of months.  And of course, graphite you can also use.  And I'm also looking into throwing log data into  graphite, just to graph it.  But the rate monitoring of graphite, I haven't found  that too reliable.  It gives you some useful numbers.  But if you want to tie that directly to the monitoring,  it's not exact enough.  So I'm not really using that at the moment.  So that said, there are a growing number of commercial  platform or software-as-a-service type solutions  out there that overlap a lot with all of these problems,  such as New Relic or Splunk.  You've probably heard of one or the other.  So Splunk is, you throw all your SysLog data into Splunk  and it sort of does a full text indexing on that.  And you can do analysis based on that.  But that doesn't have any knowledge of the structure of  the Postgres log, so that it's not exactly what we want.  And New Relic is also something we're using.  But it doesn't have any knowledge of Postgres either.  It's sort of getting there, but it's not there yet.  So but perhaps in a few years when we look back, we don't  have to do this anymore.  But we don't know yet.  Of course, all of these solutions cost a huge amount of  money, so if you don't want to spend that money, then these  are the toolkits for you.  So to conclude.  So look into the logging hooks and look into PGLog forward.  I think you can probably use that.  And if you have been wanting for additional features into  PGStats statements, maybe you don't need to wait for that.  Maybe you can roll your own analysis based on that.  So here are a couple links.  So in theory, I have all this material in the GitHub  repository, which in theory should be linked from the  PGcon website.  It wasn't as of a couple hours ago, but I think it's  syncing to Pentaparf once in a while.  So eventually, it'll show up there.  Or you can find it through me if you don't find it otherwise.  So I have the link to all the tools.  Here's the logging hook commit, which essentially serves at  the documentation if you want to look that up.  The PGLog forward tool I showed, the thing I started  writing in Go, if you want to experiment with that.  The link to Flume.  Another thing, actually, I forgot, we're looking into is  throwing the log data into RabbitMQ.  So I mentioned I tried to write a logging hook that  publishes straight into MQP, but I sort of abandoned that  because of the blocking issue.  But there is a UDP plug-in for RabbitMQ, which a small amount  of tweaking, you could actually just throw the UDP data  that PGLog forward publishes into that.  And then you have it in the queue.  And then you don't have to write your own agents and stuff.  You just write consumers that use that data.  I am told by our resident RabbitMQ expert that there are  some architectural problems with that UDP exchange as it's  implemented, given time, which is scarce, that can be fixed.  But that's certainly very interesting.  And then link to Rocks.  So that is what I have. Any questions?  Josh.  So now PGLog forward doesn't have any way to filter inside?  It does have a very rudimentary way to filter.  So the question for the audio here is, Josh asked if the  PGLog forward doesn't have any way of filtering.  It does have very simple ways of filtering.  One I show it by the log level.  The other is it has a way to filter by just a text string  it looks for.  So you can do a small amount of filtering with that.  If you want to go beyond that, of course, you can change  the source code.  The question in that then becomes how much load do you  want to put into that component versus the downstream  components?  I don't think there's a universal answer for that.  As I mentioned, Skype was sort of building this as a way of  offloading load from the server.  So they're probably not going to be very  interested in that.  That's how I should mention, Martin does take patches to  PGLog forward.  So I've sent him a few fixes and many features so that is  certainly inactive development.  There are certainly probably ways you can add a little bit  more filtering, but if it's too complex, you probably want to  put that somewhere downstream.  Susanna?  Do you know the license of PGLog forward?  The license of PGLog forward, probably as many things on  GitHub doesn't have a license.  Oh, actually, it has a license.  All right, that's the license.  Well, you can't see the letters, but you can guess from  the shape of the license of this.  Yeah, it's fine to use.  Yeah, OK, so you're saying you want to use triggers on the  logging database to inform about events?  Basically, you can parse it.  What's in that comes in?  You can basically, or you're triggered to parse it.  You can basically put it in tables.  You can basically break that data out to sort of type  things in those people.  You can basically put it in different buckets.  And then you can go to the front end and  actually look at it.  Yeah, OK, so to repeat that, so you're saying you want to put  triggers on the logging table to look for, based on text or  other things to look for events.  Put them in other places.  Yes, OK, yes, certainly.  I mean, the possibilities here are endless.  And part of the reason why I want to show this here is to  just get more people involved in these sort of ideas to see  what kind of a common ground would be.  One caveat there is, of course, if you produce a lot of  logging data, you can also overload your  logging database.  So I would be a little bit hesitant to put lots of  extra triggers and stuff on the receiving end, and perhaps  have more asynchronous processing for that.  But you have to find that out for yourself.  But that's certainly a possibility.  Jan?  That was one thing I was wondering about.  If you overload your logging database, all these things  are basically huge e-pays, right?  So if you overload your logging database, you lose  messages or not.  Yeah, but that's not different from how the  stats collector works.  So at some point, you either have to wait, or you have to  lose the data.  That's the trade-off you have to make for yourself.  I have what I'm using, I spend a fair amount of tuning it  for our workload and network and all that stuff.  So it seems to work well.  I would like to throw more data in there, but I have found a  sweet spot that works, and we're all OK.  So yeah, we do our partitioning and indexes.  Of course, the basic stuff, right?  Do you have a business card or anything?  No.  What's a business card?  OK, my business card is this.  Yeah, but feel free to come up afterwards if you want to stay  in contact.  People who know me know where to find me.  If you don't know where to find me, please send me an email  or tweet me.  I would certainly like to chat with more people about what  they have interest in this space to develop more  common solutions.  Nope.  OK, if you're interested in Hadoop, there's a Hadoop  session in the next slot.  So otherwise, thanks.
Use_logging_hooks_for_real-time_log_analysis_2_0_6323.png Use_logging_hooks_for_real-time_log_analysis_2_6324_6563.png Use_logging_hooks_for_real-time_log_analysis_2_6564_11759.png Use_logging_hooks_for_real-time_log_analysis_2_11760_12047.png Use_logging_hooks_for_real-time_log_analysis_2_12048_12095.png Use_logging_hooks_for_real-time_log_analysis_2_12096_13139.png Use_logging_hooks_for_real-time_log_analysis_2_13140_13751.png Use_logging_hooks_for_real-time_log_analysis_2_13752_15671.png Use_logging_hooks_for_real-time_log_analysis_2_15672_15743.png Use_logging_hooks_for_real-time_log_analysis_2_15744_16127.png Use_logging_hooks_for_real-time_log_analysis_2_16128_16391.png Use_logging_hooks_for_real-time_log_analysis_2_16392_16511.png Use_logging_hooks_for_real-time_log_analysis_2_16512_16643.png Use_logging_hooks_for_real-time_log_analysis_2_16644_16859.png Use_logging_hooks_for_real-time_log_analysis_2_16860_18071.png Use_logging_hooks_for_real-time_log_analysis_2_18072_19007.png Use_logging_hooks_for_real-time_log_analysis_2_19008_20135.png Use_logging_hooks_for_real-time_log_analysis_2_20136_24095.png Use_logging_hooks_for_real-time_log_analysis_2_24096_24155.png Use_logging_hooks_for_real-time_log_analysis_2_24156_24359.png Use_logging_hooks_for_real-time_log_analysis_2_24360_24431.png Use_logging_hooks_for_real-time_log_analysis_2_24432_42635.png Use_logging_hooks_for_real-time_log_analysis_2_42636_42719.png Use_logging_hooks_for_real-time_log_analysis_2_42720_42803.png Use_logging_hooks_for_real-time_log_analysis_2_42804_52475.png Use_logging_hooks_for_real-time_log_analysis_2_52476_52823.png Use_logging_hooks_for_real-time_log_analysis_2_52824_54683.png Use_logging_hooks_for_real-time_log_analysis_2_54684_54767.png Use_logging_hooks_for_real-time_log_analysis_2_54768_54851.png Use_logging_hooks_for_real-time_log_analysis_2_54852_55559.png Use_logging_hooks_for_real-time_log_analysis_2_55560_55679.png Use_logging_hooks_for_real-time_log_analysis_2_55680_64127.png Use_logging_hooks_for_real-time_log_analysis_2_64128_64511.png Use_logging_hooks_for_real-time_log_analysis_2_64512_64631.png Use_logging_hooks_for_real-time_log_analysis_2_64632_64811.png Use_logging_hooks_for_real-time_log_analysis_2_64812_64919.png Use_logging_hooks_for_real-time_log_analysis_2_64920_65135.png Use_logging_hooks_for_real-time_log_analysis_2_65136_65759.png Use_logging_hooks_for_real-time_log_analysis_2_65760_66011.png Use_logging_hooks_for_real-time_log_analysis_2_66012_66251.png Use_logging_hooks_for_real-time_log_analysis_2_66252_66299.png Use_logging_hooks_for_real-time_log_analysis_2_66300_66347.png Use_logging_hooks_for_real-time_log_analysis_2_66348_66503.png Use_logging_hooks_for_real-time_log_analysis_2_66504_66563.png Use_logging_hooks_for_real-time_log_analysis_2_66564_66635.png Use_logging_hooks_for_real-time_log_analysis_2_66636_66875.png Use_logging_hooks_for_real-time_log_analysis_2_66876_66923.png Use_logging_hooks_for_real-time_log_analysis_2_66924_67007.png Use_logging_hooks_for_real-time_log_analysis_2_67008_67115.png Use_logging_hooks_for_real-time_log_analysis_2_67116_67307.png Use_logging_hooks_for_real-time_log_analysis_2_67308_67511.png Use_logging_hooks_for_real-time_log_analysis_2_67512_67835.png Use_logging_hooks_for_real-time_log_analysis_2_67836_68135.png Use_logging_hooks_for_real-time_log_analysis_2_68136_68255.png Use_logging_hooks_for_real-time_log_analysis_2_68256_68471.png Use_logging_hooks_for_real-time_log_analysis_2_68472_69478.png
Google Translate thinks this page is in Tagalog, so up there. So if you can't understand me, I'm sorry about that. I don't have a lot of slides. I'm going to do some demos. But I'm told that everything here is being recorded in video and audio, so you can review that later. And I also have all the materials on public repositories, which I'll get into later. So I am Peter Eisenchraut. I'm PostgreSQL contributor. I currently work at Meet Me, which is a social networking platform. I live and work in Pennsylvania. And you can reach me on the internet at these places and others if you search for me. So for reasons that will become obvious in a moment, when I sort of started working on this problem space, which I'll get to in a moment, I thought of this. I was beyond query logging. But last year, I was already at this conference. In another conference, I was already at call beyond query logging. And so in some sense, this is sort of the next generation after that, perhaps. So my thing here says I'm a senior data architect, but in practice, most of my time, I'm a DBA. And we are, as I mentioned, a social network or social networking platform, depending on how you want to put it. So we have regular web infrastructure backed by Postgre databases, which are, there's a question there. Sorry, what? Volume? I'm not in control of that, but I can have someone help me here. So we have a website open to the internet. We do deployments multiple times a day. So the whole thing is in constant motion. We are the DBAs. We have to keep the bottom of the tier running. And we have monitoring setup, and every so often, some alarm triggers, and it would be fun if it triggers right now, but it doesn't so. And in my mind, or in the mind of our team, there are two kinds of alerts that we're interested. One is sort of what I think of maybe the hardware level alerts when the box has died, or the file system has gone read-only, or the disk has disappeared, and things like that. And now I can hear myself. And in those cases, we pass this off to the SysOps team, and they either have to repair the box, or repair the disk, or things like that. So we're not really concerned about that. We have the normal standby infrastructure, so we do a fail over if needed, and things go on. The more interesting and more problematic alerts are when certain metrics change. And the metrics that we're interested in, for example, is things like connection count, transactions per second, errors per second, CPU load, IO, weight, times, those sort of things. And those can change for all kinds of reasons. The website is getting hammered that new things were deployed that maybe don't work as they should, and things like that. And then we need to go in and find out what happened there. And that's what this is about. So one thing we actually have on the show is, in case a code was deployed that didn't work out well, we have this what we call the developer attitude readjustment tool, called short for dart, which is this thing here. That's our dart. So if you're a developer and your code sucked, that's what you get. So now let's take as an example, which is easy to understand. Let's say your monitoring tells you that the errors per second count went up. And now you want to know why. So the straightforward way to do this is you go into your log files and you grab around, perhaps. And after a while of grabbing, you think, it wouldn't be nice if you could put this into a more structured form, and then we can use a real query language on this. So many years ago, the CSV log format was created for that purpose ostensibly. And that's a reasonable idea, but for various reasons that doesn't really work for us. One is that just the time to take the log files and load them somewhere else and analyze them, that takes too long. And we had this infrastructure set up for that, but it wasn't fast enough. So if you want to know, OK, error count went up, I have to fix this within a few minutes. I can't wait five minutes, 10 minutes, for all the stuff to be copied over and loaded and indexed and all that stuff. The other problem with that is that if you have ongoing events, you want to track them as they go on. So if you take the log file and copy it over and take a look and see what happens, then what do you do with the data that came in in those couple minutes? Then you need to take the mark where you left off and then take the next chunk of the log file and do all kinds of stuff, and that's very complicated. I know some people do that, but it's not nice. And just as a side note, if your server is getting hammered, don't rotate the log files. That takes your server down. We've tried that once and didn't try it again. So there are other solutions in this space that you probably heard of, like something like PG Badger, PG Fooin, PG Stat Statements. Those are all tools that are related to that. But they don't really solve this problem of what was the query that threw the most errors in the last five minutes. What was the query that, how did that query behave yesterday at the same time and things like that? That didn't really help us either. So looking around, we came across an overlooked feature in Postgres 9.2, which is the logging hooks. So logging hooks are available in Postgres 9.2, so you can use them today. All of the stuff I'm describing here is what we're using in the real world. Like all hooks in Postgres, they're not documented. So that's why they get overlooked. But I have all this in the material later, but just to show you the commit that, make it bigger. So this was committed in 9.2. And the feature came from this fellow, Martin Pihlach, who mentioned down here, who works for Skype. And he's the guy who actually wrote the SQL Met stuff that I got blamed for four years ago. So I have sort of a, I'm always elatching onto his features, I suppose. And what it does is pretty simple. If you look at the diff, there's a huge comment here, which is actually the documentation. And actually, just all it does is introduces a new variable here, emit log hook. No, emit log hook. That's the pointer variable, which you can assign to a function. And that function gets called every time a log message is written to the text file that you would otherwise have. And so if you load a module using the normal mechanisms, you can execute any code you want at that point. And so now you have endless possibilities, right? So going back to the use case, what I want to do is take the log data and throw it into a structured storage, for example, the database. So basically what I want to do is something like that, right? So I have my production database here. I use this hook and then some extra code, which I'll get to. And then you can throw it in the other database, and then you can do your analysis there. And all of this is in quasi real time, right? Not actual real time, but as it happens, as a stream. So you could now write your own hook for that. And it's not very hard if you know C. But I think everyone who knows C is in the other talk about hacking Postgres. So the idea here is you use existing components to do the work. And Martin Peekelock also wrote a, I guess what you could consider the reference implementation for, a logging hook, which is this tool called PGLogForward, which you can search for. And I'll have the URL in the notes, but you can just search for that and you'll find it on GitHub. Now, as far as I understand, Skype's motivation behind this whole feature is not necessarily what I'm describing here, but they were concerned about that just plain logging and the log volume takes their servers down or has too much load on the server. So they wanted just to have a way to get the log data off quickly. But it lends itself to other uses as well. So, and now I'd like to show you how that works. So here's the GitHub repository for that. So you don't have to write the link down, but I'll have a little. And actually in the patches sub directory, there's the actual logging hook patch is in there. So you can actually backpatch that all the way to 8.4. And I have also done that. So that's very simple. As you saw, the patch is not big. So if you want to use this in older versions and you want to roll your own, you can just install that patch there and you can use the same feature there. Other than that, it's just one simple C file and you build it like a normal PGXS module. Which gets me to the demo. So I have this, all of the stuff I'm showing now is I have that on my GitHub. So you can play with that later also if you want. Is that reasonably big? So the first thing you want to do is you build it, right? And then you get this error, which you've probably seen a lot in if you want to build extension modules, which is really annoying me. So you need to do this thing here, right? All right, so that builds install. So that was easy. Now, this is Ubuntu here. So the paths are always a little bit different. So there it is. Well, you can't see. It's the green one because it's executable in the other ones or not. That's another platform difference, but it doesn't matter. Now, the way you configure this is as follows. So this is a Postgres configuration file with a bunch of stuff in it. And then at the end, you add these statements there. So there are two ways to load this. One is using shared preload libraries and one is using local preload libraries. And the difference for those who don't know is shared preload libraries get loaded into the Postmaster at the time you start the instance. And local preload libraries is loaded at the time you start a session. And through PGSatState, I'll just give you an example. But I recommend putting it into local preload libraries for the reason that, A, you don't have to restart your server to even install this. And two, it keeps the stuff out of the Postmaster, which you want to have stable. But you don't have to do it that way. You can also put it into shared preload libraries. In order to load stuff from shared preload libraries, you have to do the following. And you need to make a directory called plugins here and then move that thing in there. And that's totally bizarre. And I'm planning to get rid of that. And those who follow all the patches I sent to hackers, that's the reason why I introduced this feature, which didn't get into 9.3.2. Have sort of a white list of things you can put into local preload libraries. So in the future, I hope to have a better solution than that. And so now, that's the installation. So you need to do this, put it into the plugins directory. If you want to load it through local preload libraries, otherwise you just leave it where it was. And now you take this configuration file that I just showed as an example and put it into place. Reload, so you don't need to restart. It's nice. So while this loads, I have a back on job here which creates some logging. So we'll see something in a moment. Let's get back to that file here. So once you have loaded it one way or the other, you have this log forward namespace of new configuration parameters. And all right. Nope. All right. This isn't works fine. Sorry. All right. That's a good idea. And you can create multiple destinations, but this is just a simple case of you have one destination. You use the target names variable here, which you just invent a random name here. This doesn't mean anything. And then based on that name, you have additional parameters that get kind of created on the fly once it reads this here. And then you give it the destination of where this is supposed to go. And what PG log forward does, it sends the log data as UDP packets to the destinations that you give here in the formats that you give here. And it supports JSON, syslog, and net string. So syslog you know. So if you just keep using syslog, you can use that. But I guess JSON is the most interesting because it already gives you the log data in the structured form. And this is the destination of where you have the agent who receives that. And then you need to render port number. So once you have that running, you can use NETCAT to test this. Well, PG log format only supports JSON, syslog, and net string. But it's not hard to add more formats. And you should also consider this as a tool kit to some degree. I think the JSON format is just fine. But if you want to have something different, you just need to assemble that as a string and ship it out over UDP. So here's the UDP packet that is being sent around. So that's all the typical logging fields that you are familiar with in JSON format. And now you can imagine what you want to do with that. You just write your own little thing and process that. Unbundle the JSON. And then you have the logging data and you can do whatever you want with that. So you probably need to do some programming now. So in the PG log forward repository, there's also a, in the testing directory, is actually sort of an example implementation in Python, which kind of gives you a start if you want. So it's not long, two pages here. It actually handles JSON net string answers log. So this is even longer than you really need it to be. And so this you can also use to test it if you don't like netcard. And it prints out the stuff. And then you can actually use that as a template and just insert your own code there. So in order to, let me just show you how you would do multiple destinations. So here's a different configuration file which contains the stuff I just showed. But it adds a second destination here. So this is the stuff we already saw. And here I just invented a new target name which uses a slightly different destination. It uses a syslog format, uses a different port. And there's also a small number of additional arguments where you can pre-filter the stuff that gets processed by this. So this is not very user-friendly. Something perhaps to address is that you can give the minimum error level as an integer, which is the internal encoding of what is a debug info-warning error. So that's kind of, it's not really good, but you can do it and you can probably easily change that. So this would be, you can then write your second agent which just gets the errors in syslog format and then you can hook that into maybe an alerting system or a system that counts the errors or things like that. So I guess it's partially obvious. The reason UDP is used here is that you don't want this whole additional processing to hold up your actual session. So I have experimented with other logging hooks that, for example, using AMQP to put this information in the queue. But then if your broker is down, has problems, then the queue locks everything. So UDP in that sense is useful. But of course, this is not going to be an auditing system because it can lose packets on the load. So now getting back to the original goal is putting that into a database is not difficult from here. So create a new, you can put it into the same cluster if you want, but then you have kind of a looping problem if you don't configure this correctly. And in practice, you probably want to have this on a different host. So I'll make a separate cluster here and make a database, make a very small demo schema. Again, all this data is online. You can try this out yourself. So now I just created a very, I'll show the table in a moment. And I wrote, I essentially took the demo Python code I just showed and put some database stuff in there. So instead of just printing it out, it takes some of the log data and didn't put everything in here right now. But some of the log data just puts in the table. So that's pretty easy. This is literally one page of code here almost. So let's start that. Just running this as Postgres now so I don't have to set up authentication, all that kind of stuff now. So with any kind of luck, we have logging data in the table. And this is essentially very simple of what I'm actually using in practice. Now, if you look at that, you can imagine all kinds of things that you can improve here. So first of all, you want to change the E-level thing to something you can read. But for example, here, durations are being logged here. And for example, one thing I do in the actual code is I run this through a regular expression and split it up into two columns. So I have the duration in a separate column from the statement, and then I can run analysis on the query durations. And that's super useful. Or if you're specifically interested in disconnections or connections, you can take this and split it up and mangle it any way you want. So that's essentially the solution to my original problem. Now, of course, you also want to partition this table, maybe normalize it to split out the actual strings, which are obviously going to be repeated into a separate table. So you manage the storage that way. And the way I run this, I start this agent on SupervisorD, and that way you can also spin up multiple instances quite easily, because this thing is not going to be very concurrent, or it's not concurrent, despite it doesn't support that very easily. So you can spin up a bunch of different instances, give them different port numbers and point different places, different production sources of different ports, and achieve concurrency there. It's not very pretty, but it seems to work just fine. So that's what I'm doing. One thing I'm actually playing with it in the terms of concurrency is I re-implemented that agent I just showed in Go, the programming language, which has better concurrency support. And I put that in GitHub, so if someone wants to experiment with Go, perhaps you can look at that. And this is not what I'm actually using in production, but it's something I'm playing with to make this optimize this perhaps. Now again, this is something everyone here can just install and use, or set up something that suits their particular use case. Something that came up during the developer meeting yesterday is to perhaps write a pre-packaged solution here that is more easy to deploy, and you don't have to write any code yourself. So that might be a future direction here to just write a different log-in hook that logs straight back into the same instance, avoiding loops and stuff like that, of course. And then for small instances, you can perhaps just load that and use it without having to do any extra work. That might be something to try. Well, the advantage of loading it in local pre-load libraries is that you can actually set that per database or use also. So that might also be useful. But of course, one of the advantages of not having the same instance, or not even having the same host, is that you can also aggregate data from multiple machines. So you have multiple machines logging into the same table, or partition set of tables, and compare things that way. So that is also definitely an advantage of this over some of the other solutions that just look at a single log file. So now, that said, this essentially solves my use case of what happened in the last five minutes. If you want to think a little bit long term, perhaps you and you want to basically keep all the logs and do big data analysis later, you can also, for example, throw this stuff into Hadoop. And I have some things to show how to do that. So getting back to this awesome picture I made here. So in the setup I'm using, I'm still using PG log forward as the hook, so I'm not writing my own hook, but instead of the agent, I'm using something called Apache flume. That's an Apache project, which is at flumeapach.org. And it has a nice picture on the home page, which looks somewhat familiar to what we want to do. And so flume describes itself as a distributed, reliable, available service for efficiently collecting, aggregating, and moving large amounts of log data. So it seems to be purposeful for this sort of thing. Being an Apache project, their main primary use case is having web server log data put into a big data store. But it doesn't have to be web server on this side, and it doesn't also have to be HFS on this side. It's kind of geared toward putting things as a front end to HFS, essentially. But the things on this side can be pretty much anything. And they support even tailing a log file and throwing it in here, or getting syslog data, or all kinds of other things. Or you can write your own, which is what I did. So all this is in Java, so you just download the JAR file and unpack it, and then you're ready to go. So if you dig deep into this space of moving structured data from one end to some other structured storage, there's about eight different formats you'll encounter in five different programming languages. And you have to somehow pick a combination that is the least headache and loses the least amount of data on the way. And I'm sure there's other permutations of getting that data. So one of the most straightforward ways to do this would be to take data in syslog format, which Flume supports out of the box, and throw the syslog data into Flume over UDP or TCP, if you want, and then throw this into HGFS unstructured, and then maybe use PIG or Hive to get it out. And that would work, but it's not really what I set out to do. So I wrote a few pieces of code of my own, so I wrote a sync that takes that JSON-UDP format, unpacks it, into Flume's native internal transport format. And then on the other side, takes that structured format and throws it into HBase as separate columns, essentially. So the way you can figure Flume has sources and syncs. So here's a bunch of sources you can use. I just threw a few in as an example. Here's a source that I wrote, which is not published at the moment, but I plan to do that. This just has to go through some internal clearances. But this is not the rocket science code. So I'm running this code on this port here, which should look familiar from the previous examples. That's where PgLogForward publishes. Flume has also additional features here as a thing called an interceptor, which sort of mangles the data. And one interceptor you can use, for example, is to throw a timestamp into the existing data, because PgLogForward doesn't actually provide a timestamp, which is kind of weird. And on the sync side, here's an example for a sync using the built-in HGFS support, which just throws the data in an unstructured way into HGFS files. And if you don't know what to do with it, there's actually talking in the next slot. That would be one file per log. No, it actually sort of rotates file as it wants. It just throws stuff into files. And then after, there's additional parameters here. You can tweak this to death of what files it creates and what they're named. And then you would use something like Hive, which just sort of transparently wraps over that. And you can mangle that data however you want. So that's one example. And the other, what I've sort of been concentrating on is using HBase. This is the HBase sync. It's shipped with Flume, but it doesn't actually know how to unpack the stuff I'm sending in. So I wrote a separate serializer. So there's a bunch of Java classes in this pipe. And you have to know how to wire them together correctly. So just to show that as well. See if HBase works today. Just create a table, one column family. Very exciting. Very slow. Oh, I have no space. Well, this is actually running locally on SleshTam, so it shouldn't be that slow. Right. Now, maybe I didn't actually start it. Let's start it first. This is also bizarre. If you don't start HBase, you can start the shell, which is like a PS girl equivalent, but you can't do anything. So it's very interesting. All right, so that was actually 2.4 seconds to create a table in HBase. Awesome. So this Flume has not many options, actually. Quite usable. Flume, start an agent, to do some claspath stuff, to get your custom jar stuff in there. You have to give it a config file, which is the one I just showed you. Then you have to tell which agent to use. And let's see if that works. OK. So this is debugging output from my code, so you can see it's already doing something. And with any kind of luck, we'll have something over here. OK, there's all your data. And you can do stuff with HBase then. So if you want to play with HBase, but you don't have any data put in it, this is a way to generate lots of data. So that's basically why I'm at. As I mentioned, the first half of the talk, putting log data into Postgres is what I'm using. This is something I'm looking into actively now, the HBase stuff. Something that I originally wanted to do, but have not found a good solution for it. But have not found a good solution for it yet is to actually do the actual rate monitoring using this. So in the beginning, I mentioned I'm looking at metrics like number of errors per second or number of transactions per second. And that stuff is being computed by a separate kind of legacy monitoring system. And ideally, if you have this kind of data, you can throw it into some system that can do the counting for you. But I haven't really found a good one to do that yet. So a couple of things I looked at was a thing called rocksteady, which I have a link for. Which also comes with a nice picture, but has lots of moving parts. So if you can't read all the small print. So this is your stuff. This is RabbitMQ. This is rocksteady. This is graphite. This is Nagios. This is dashboard. So there's way too many moving parts here that I have to monitor the monitoring here, essentially. So that is a bit to me at the moment. But maybe a lot of effort can probably make this work. Another thing that I'm actively looking at is a thing called Riemann, like the mathematician. So Riemann.io, that would actually do exactly what I need. It just kind of haven't managed to set it up yet. But maybe that's going to be something to look at in the next couple of months. And of course, graphite you can also use. And I'm also looking into throwing log data into graphite, just to graph it. But the rate monitoring of graphite, I haven't found that too reliable. It gives you some useful numbers. But if you want to tie that directly to the monitoring, it's not exact enough. So I'm not really using that at the moment. So that said, there are a growing number of commercial platform or software-as-a-service type solutions out there that overlap a lot with all of these problems, such as New Relic or Splunk. You've probably heard of one or the other. So Splunk is, you throw all your SysLog data into Splunk and it sort of does a full text indexing on that. And you can do analysis based on that. But that doesn't have any knowledge of the structure of the Postgres log, so that it's not exactly what we want. And New Relic is also something we're using. But it doesn't have any knowledge of Postgres either. It's sort of getting there, but it's not there yet. So but perhaps in a few years when we look back, we don't have to do this anymore. But we don't know yet. Of course, all of these solutions cost a huge amount of money, so if you don't want to spend that money, then these are the toolkits for you. So to conclude. So look into the logging hooks and look into PGLog forward. I think you can probably use that. And if you have been wanting for additional features into PGStats statements, maybe you don't need to wait for that. Maybe you can roll your own analysis based on that. So here are a couple links. So in theory, I have all this material in the GitHub repository, which in theory should be linked from the PGcon website. It wasn't as of a couple hours ago, but I think it's syncing to Pentaparf once in a while. So eventually, it'll show up there. Or you can find it through me if you don't find it otherwise. So I have the link to all the tools. Here's the logging hook commit, which essentially serves at the documentation if you want to look that up. The PGLog forward tool I showed, the thing I started writing in Go, if you want to experiment with that. The link to Flume. Another thing, actually, I forgot, we're looking into is throwing the log data into RabbitMQ. So I mentioned I tried to write a logging hook that publishes straight into MQP, but I sort of abandoned that because of the blocking issue. But there is a UDP plug-in for RabbitMQ, which a small amount of tweaking, you could actually just throw the UDP data that PGLog forward publishes into that. And then you have it in the queue. And then you don't have to write your own agents and stuff. You just write consumers that use that data. I am told by our resident RabbitMQ expert that there are some architectural problems with that UDP exchange as it's implemented, given time, which is scarce, that can be fixed. But that's certainly very interesting. And then link to Rocks. So that is what I have. Any questions? Josh. So now PGLog forward doesn't have any way to filter inside? It does have a very rudimentary way to filter. So the question for the audio here is, Josh asked if the PGLog forward doesn't have any way of filtering. It does have very simple ways of filtering. One I show it by the log level. The other is it has a way to filter by just a text string it looks for. So you can do a small amount of filtering with that. If you want to go beyond that, of course, you can change the source code. The question in that then becomes how much load do you want to put into that component versus the downstream components? I don't think there's a universal answer for that. As I mentioned, Skype was sort of building this as a way of offloading load from the server. So they're probably not going to be very interested in that. That's how I should mention, Martin does take patches to PGLog forward. So I've sent him a few fixes and many features so that is certainly inactive development. There are certainly probably ways you can add a little bit more filtering, but if it's too complex, you probably want to put that somewhere downstream. Susanna? Do you know the license of PGLog forward? The license of PGLog forward, probably as many things on GitHub doesn't have a license. Oh, actually, it has a license. All right, that's the license. Well, you can't see the letters, but you can guess from the shape of the license of this. Yeah, it's fine to use. Yeah, OK, so you're saying you want to use triggers on the logging database to inform about events? Basically, you can parse it. What's in that comes in? You can basically, or you're triggered to parse it. You can basically put it in tables. You can basically break that data out to sort of type things in those people. You can basically put it in different buckets. And then you can go to the front end and actually look at it. Yeah, OK, so to repeat that, so you're saying you want to put triggers on the logging table to look for, based on text or other things to look for events. Put them in other places. Yes, OK, yes, certainly. I mean, the possibilities here are endless. And part of the reason why I want to show this here is to just get more people involved in these sort of ideas to see what kind of a common ground would be. One caveat there is, of course, if you produce a lot of logging data, you can also overload your logging database. So I would be a little bit hesitant to put lots of extra triggers and stuff on the receiving end, and perhaps have more asynchronous processing for that. But you have to find that out for yourself. But that's certainly a possibility. Jan? That was one thing I was wondering about. If you overload your logging database, all these things are basically huge e-pays, right? So if you overload your logging database, you lose messages or not. Yeah, but that's not different from how the stats collector works. So at some point, you either have to wait, or you have to lose the data. That's the trade-off you have to make for yourself. I have what I'm using, I spend a fair amount of tuning it for our workload and network and all that stuff. So it seems to work well. I would like to throw more data in there, but I have found a sweet spot that works, and we're all OK. So yeah, we do our partitioning and indexes. Of course, the basic stuff, right? Do you have a business card or anything? No. What's a business card? OK, my business card is this. Yeah, but feel free to come up afterwards if you want to stay in contact. People who know me know where to find me. If you don't know where to find me, please send me an email or tweet me. I would certainly like to chat with more people about what they have interest in this space to develop more common solutions. Nope. OK, if you're interested in Hadoop, there's a Hadoop session in the next slot. So otherwise, thanks.
up. And now you want to know why. So the them somewhere else and analyze them, that takes rotate the log files. That takes your server logging hooks. So logging hooks are available in Postgres 9.2, so you can use them today. All of the database. So basically what I want to do is very, I'll show the table in a moment. And I also, for example, throw this stuff into Hadoop. log data put into a big data store. But it tie that directly to the monitoring, it's not how much load do you want to put into that as a way of offloading load from the server. So it has a license. All right, that's the license. basically, or you're triggered to parse it. You I was wondering about. If you overload your logging database, all these things are basically