Database performance for teams

Each year since 2011, Stack Overflow has asked developers about their favorite technologies, coding habits and work preferences. This year 64,000 developers took the annual survey. It was the largest group of respondents in their history. For the fifth year in a row, SQL was the second most commonly used programming language.

SQL stands by Structured Query Language is a domain-specific language used in programming and designed for managing data held in a relational database management system. SQL was one of the first commercial languages for Edgar F. Codd’s relational model, as described in his influential 1970 paper, “A Relational Model of Data for Large Shared Data Banks.” https://en.wikipedia.org/wiki/SQL

SQL and RDBMS are well proved technologies. They have been adopted in a huge amount of applications around the world by companies of all sizes since the 80’s. From small and medium businesses using one single database instance to corporations powered by tens of instances or even global companies like Facebook and Linkedin that rely on clusters of hundreds or thousands of relational databases to scale their operations at maximum performance, SQL is dominant.

As an old and robust technology, most of the SQL techniques used to achieve performance and scalability are well known. Index creation, bulk inserts, stored procedures, table partitions, multiple schemas, sharding, clustering and others are richly documented with lots of examples available (ex: Sharding & IDs at Instagram; Sharding Pinterest: How we scaled our MySQL fleet; Query Analyzer: A Tool for Analyzing MySQL Queries Without Overhead). However, what once was common knowledge for many of my colleagues programmers in the 90’s, does not seem to be the case nowadays. This lack of due attention to the SQL code has cost lots of money.

Throwing machines at the problem

This lack of due attention with the SQL code, will probably turn in a performance issue when the application goes to production and user base grows or when the data volume increases.

When performance issues arises, the preferred solution for many companies around the world is to increase the computing resources available. That is specially true in cloud environments where this action is one click away and wasted dollars to pay.

On the other hand, for those that decide to investigate the root cause of the problem, there is a set of approaches and tools one could use. None of them is better than another. There are risks and trade-offs to be aware in any choice. It will depend of the database engine, environment (Cloud, on-premises or hybrid), load of the database and the team expertise. If the company can afford a dedicated database team like Linkedin, it can even develop own tools and processes to help in the quest. However, for most small and medium businesses, small teams and developers with little previous SQL experience this can be a hard problem to solve that will waste plenty of time and money.

Offensive queries

Our preferred approach when facing performance issues, is to find the top offensive queries. It consists in using the database slow query logs/performance dynamic views with a threshold (ex. 100 ms) and capturing all the queries crossing the threshold, which are then analyzed.

Although this approach cannot capture all the queries, it gives great visibility about which commands the application is sending to the database. We’ve been using this approach with great success in our customers, always obtaining very significant performance improvements, stability, scalability and, in most of the cases, reducing the infrastructure costs by 40% in average, and 90% for some special cases.

*This is not a one size fits all solution*. Logging leads to high IO and, depending of your database load, this can drastically reduces the throughput and degrades performance. But, if used correctly, one can rapidly identify and solve performance issues in those stacks without touching the source code.

This approach has been proved very effective in the following scenarios:

Small and medium businesses

• When there is none or only a couple of developers with little previous SQL experience;
• When buying proprietary third-party software with poor support and you lose $ due performance issues.

Managed services

• When the customer application is killing the database resources in the cloud provider and you need evidence to support your customer service operations;
• When managing multiple deployments, from different customers, with distinct database engines in a multi cloud, on-premises or hybrid environment.

Corporations

• When there are multiple teams responsible for the development, security and operations where often takes too much time and effort to coordinate the teams;
• When there are databases shared by multiple applications inside an organization.

However, setup the environment, tools and context to find the offensive queries for every new customer or application is a very repetitive and time consuming task that usually involves high skilled professionals and makes this task expensive. Another point is that, although most of techniques to fix performance issues in applications backed by RDBMS are very well known for decades, many companies, specially when there are multiple teams involved, struggle to follow the book due to communication problems between them and defensive positions adopted by the teams. That is nonsense because there is real time and money being wasted burning CPUs around the globe with BAD SQL code.

“It is not lack of hardware. It is not network traffic. It is not slow front ends. The main performance problem in the huge majority of database applications is BAD SQL code.” — Joe Celko.

By seeing this hundreds of times happening in front of our eyes, we’ve decided to build a tool to help with this problem. So we built NAZAR. Database performance for teams.

NAZAR is an offensive query monitoring SaaS. It helps teams to solve performance issues, democratizing the access to the BAD SQL commands and enabling collaboration in order to fix the issues faster. Its a productivity tool to save time and money.

Using NAZAR we’ve been successful in identifying and solve the performance issues for our customers in less than 14 days in average. These fixes also lead to infrastructure reduction costs in 40% average, and in 90% for some special cases among our customers. It has saved real money yet.

NAZAR currently supports Aurora, MariaDB, MySQL, PostgreSQL, Oracle and SQL Server relational databases and is platform agnostic. You can also use NAZAR in any cloud, on-premise or hybrid environment.

NAZAR continually monitors the database because applications are like living organisms. They are continually evolving and there will be always fresh and new problems to avoid.

*This is not a one size fits all solution*. If you are skeptical about activating logs to investigate and solve performance issues, please request a demo and we will happily show you how NAZAR works and how we can safely and successfully use this approach.

NAZAR is your amulet against performance issues

Agentless No software to install. Less friction when dealing with multiple teams (IT support, developers, managers, etc).

Database It’s all about data. In the database layer we can see and monitor all applications interacting with the data.

Performance We look for slow queries only. These slow queries are, most of the times, the root cause of high CPU and IO degrading performance.

Monitoring We monitor database top offensive queries by 3 different views:

• most executed
• most time consuming
• slowest executions