Generate Common DB Data Report By ELK

Usually critical data is stored in DB. We can periodically poll and combine key metrics from DB into a data report, such as how many tables/collections in DB, how many items in each critical table, etc. Data report enables people to visualize the history and trend of key metrics.

Furthermore if the basic data report can be generated automatically, people can easily add more metrics, which may make more business sense. The most exciting part is that we can get a general solution which applies to different projects! So how we can get this idea started?

DB History Report


Original Article: https://dennyzhang.com/generate_data_report.

Connect with Denny In linkedin.png Or MailList.


Step 1: Define key metrics to collect and display.

Here are metrics I’ve been actively using.

  1. TOTAL_ITEM_COUNT: The sum for item count in all critical tables/collections. This gives us a rough understanding for how much data we have in general.
  2. TABLE_ITEM_COUNT: Item count for each major table/collection and its history. It shows how fast the data grows from day to day.
  3. TOTAL_DISK_OCCUPIED: Total occupied disk capacity. The physical measurement of our data helps us to do a better capacity planning.

All metrics above give project owners a general estimation for current status and some sort of prediction.

People might have problems to define suitable metrics to fit their projects in the beginning. Don’t worry. Once we’ve finished the basic version, we can revisit it and add more fancy metrics later.

Step 2: Collect metric from DB.

The management tools (CLI/API) of different database may be different. The way how we caculate each metric may be different as well. The good thing is that we can always unify different metrics into below format.

[Date, ItemName, PropName, PropValue]

# Sample:
# 2016-07-11 14:15:20 my_table1 MYSQLItemNum 2000
# 2016-07-11 15:15:18 es_index1 ESItemNum 500

Step 3: Feed data to ELK and draw useful diagrams.

Design For DB Report

To combine all pieces together, here is a sample report. In below example, the system uses CouchBase and Elasticsearch as Key DB stores.

DB Sample Report



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.