Track individual sql statements executed in a DB

Elastic Stack Beats
1

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.

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

lsbeat is an example of how to build a beat.

https://www.elastic.co/guide/en/beats/devguide/current/ls-beat.html

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.

4

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: https://www.elastic.co/guide/en/beats/devguide/6.x/newbeat-generate.html

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.

5

Great!

Thanks for the info.

6

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

7

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