JessesNet />

View Project Source: https://github.com/jessecascio/querypro

QueryPro is a tool for monitoring queries of various applications as well as database server performance metrics. QueryPro works with any database server and allows for the monitoring of an unlimited number of applications. An application can be either be a single system, or components of a complex system, allowing for customizable monitoring granularity.

Inspiration

I wanted a way to track database performance over time. There are a variety of command line tools out there such as msqlslap and mtop, but those require a server login to check stats, which becomes complicated as the number of servers grows, and doesn't track performance over time. I was also curious how many queries we're being run, which queries were being run most frequently, and what the overall performance of these queries were. MySQL has a general log, which stores all the query data but the overhead of running the log on production is too great. There is also the slow query log with MySQL, but again that requires a server login to verify, or a system for parsing the log files and storing else where. And in doing so the log is per server with no easy way of determining information per application.

At the same time, I was working on getting more proficient with Docker, AngularJS, and NodeJS. After reading through some books, and doing various coding challenges, it came to the point where I wanted to build something to help solidify my knowledge. The QueryPro project seemed like a great way for me to apply what I had been learning and reading about into a functional product.

Architecture

Having read about Docker and worked with some basic containers I wanted to see how easy I was to develop a complete software system using multiple images. The idea being that all the server setup and configurations could be hid inside the Docker containers allowing for easy installation and portability. QueryPro was built on various systems and services and Docker offered a great way to package everything into a single product.

Data

QueryPro needed a data store for storing all the query analytics and server performance data. Due to the nature of the data, the container was built on InfluxDB. InfluxDB is a time series database which offers a rich query language and clustering for horizontal growth. InfluxDB was built to handle millions of time series data points and fit the design goals of QueryPro nicely. InfluxDB also offers a built in API which allowed for easy integration with a frontend framework.

Backend

Although InfluxDB has a query language, not all of the stats I wanted were able to be compiled using queries. Therefore a backend system was needed to periodically rollup stats into rollup tables. This is done with NodeJS. By having continuously running NodeJS scripts, I can define what data needs to be rolled up and how to store it. In future versions this container will also host an API for retrieving more complex data and data that may not be stored in InfluxDB, all accessible via a NodeJS and Express.

Frontend

Once the data was being collected the user needed a way of analyzing it. The frontend is built with AngularJS and is the entry point for all user interactions. This allowed for keeping the backend and data components abstracted from the user. After the Docker installation the UI is availble via the IP of the server and has access to all the data.

Client Libraries

In order to ensure data is sent in the proper format to the QueryPro data store, various client libraries have been (or are being) written. The client libraries send information to the QueryPro data store via UDP therfore incurring unmeasurable** overhead to existing code bases, and can be added to existing ORM systems with only a couple lines of code. The client libraries work with a variety of different data stores and only require queries and execution times be passed in.

Experience

Docker truly has revolutionized the way complex web applications can be deployed. In systems that have a lot of moving parts, it can be difficult to relay to a user the proper way to set up an application. By using Docker, I was able to embed all the server configurations inside the Docker images, requiring only that user installs Docker, pulls the code, and runs the containers.

Building QueryPro really accelerated my learning of Docker, NodeJS, and AngularJS. While I wrote most of the code while traveling, so there is much refinement to be done, I now have starting points of where I can dive deeper. I am looking forward to going back and continually updating QueryPro to help evolve my understanding of the new technologies as well as provide useful analysis and metrics of my various database systems.

Going forward I will continually polish the code and add new functionality. There are numerous different metrics I want to add for monitoring the queries. Also, the other side to QueryPro is automatic server monitoring opposed to just application query analyzing. Using tools such as collectD and Python Fabric, I can incorporate stats about database server performance and server performance on which the database is running. I also want to incorporate a different database server into QueryPro, probably MongoDB, for incorporating things like alerts, user permissions, and then have a NodeJS server, using Express, to access the data. Stay tuned!