Track individual sql statements executed in a DB

Hello group,

I am an ES newbie and one of the things I am looking at is to use ES to monitor our databases to track the sql statements that are being executed in the database over time. With this solution I would like to track sql statement string, executions, cpu, and waits (most likely other stats) per sql statement. What I would like to know is the following, 1) would the lsbeat be a good example to follow to collect info on each statement and 2) would you store a single statement per document or does it make sense to use some other index structure. I am still grasping the document structure and best practices around storing everything in one document vs splitting it out in multiple documents.

Your input is appreciated.

  • What is the lsbeat you are referencing here?
  • It sounds like 1 doc per statement should be the way to go.

lsbeat is an example of how to build a beat.

So if I am checking for all the sql statements that have been run in the database for the last 15 seconds and keeping track of all of this for more than 5000 databases I was worried about the number of documents I would be storing. I would also need to keep a history of at least 30 days. I am sure it can be done I just want to make sure I start with a good solution before finding out I need to change it because I hit some limitation.

I just had a quick look at lsbeat and it's probably not the best example to get started as it was last updated 2 years ago and quite a few things have changed since then. Better have a look at the Beat generator here:

In think whatever way you will store it, you will have quite a lot of data assuming your databases are under load and have many queries. I would still recommend the approach one statement per document as I would assume it gives you more freedom on querying later on.

As you will get a larger dataset it's important to scale your Elasticsearch cluster properly. And also: Test it first with a few servers to see if you get the expected result and performance you expect.


Thanks for the info.

Just an FYI that I've created this PR to remove the outdated section about lsbeat from the dev guide.

This topic was automatically closed after 28 days. New replies are no longer allowed.