Posts tagged " mysql performance tuning "

Step by Step Advanced MySQL Query Tuning

#4: MySQL SQL Query Optimization and Database Tuning for Beginners

June 19th, 2017 Posted by MySQL NEWS No Comment yet

MySQL SQL Query Optimization and Database Tuning for Beginners

It is not easy to find highly optimized SQL queries and well-structured databases. You might have the task to design a DB or to improve the performance of queries, either way, you need to be prepared to handle the challenges. MySQL SQL Query Optimization and database tuning are important skills that will help you make the application perform better.

In this article, we will go through what these tuning and optimization techniques mean. I will demonstrate some basic examples. The scope of this article is for beginners, but towards the end, we will talk about how to learn other skills such as advanced MySQL query tuning.

 

What is SQL Query Optimization

Step by Step Advanced MySQL Query Tuning

Step by Step Advanced MySQL Query Tuning

Query optimization in DBMS  or query tuning is the process of altering the query to make it run faster. Often, the queries which are initially written aren’t designed for scale. This leads to the query slowing down the application as the amount of data increases.

As a beginner, you must understand some of the basic concepts behind an SQL query. This will help you identify potential problems with queries that you are tuning. Following are some of the SQL Optimization Techniques:

 

  1. The very first technique is to check the select statement. Very often, developers tend to use the “*” in a select query.

 

Example:

SELECT * FROM EMPLOYEE;

What you must understand here is that the “*” should be used only when all the fields need to be fetched.To make the query faster, mention only the fields that need to be fetched. Example:

SELECT FIRST_NAME, AGE, GENDER FROM EMPLOYEE;

 

  1. Using the operators IN, EXISTS appropriately. You must understand when each of them has to be used. It has been found that using “IN” is the slowest when it comes to query execution time. Following is an example of how you can perform SQL query optimization when you find a query having the IN operator.

SELECT NAME, AGE, GENDER FROM EMPLOYEE 

WHERE EMP_ID IN ( SELECT EMP_ID FROM MANAGERS)

 

            Now, instead of the above query, you can use EXISTS as follows.

 

SELECT NAME, AGE, GENDER FROM EMPLOYEE E

            WHERE EXISTS ( SELECT EMP_ID FROM MANAGERS M

            WHERE M.EMP_ID = E.EMP_ID)

 

  1. You can also use simple MySQL query optimization tools like the “EXPLAIN” operator. This operator, when added to the beginning of a query, gives you a sense of time the query will take to execute. It might not be fully accurate, but as a beginner, this will be very useful to you. The output of this query is called “QUERY PLAIN”. Following is an example:

EXPLAIN

            SELECT * FROM EMPLOYEE

            WHERE SALARY> 100000;

 

This will give you a cost associated with the query and higher the cost indicates, longer run time.

 

What is Database tuning

 

There are many things which overlap Database tuning and SQL query optimization. These two concepts are interrelated. However, the database tuning refers to the way in which database needs to be designed, and how to choose the right DBMS application and how to best set up the DB environment. Though this skill comes with time and experience, there are a few things you can look out for that would make your database more efficient.

 

  • The very first thing you do is Normalize the database. Database Normalization is the process of removing redundant columns or to restructure the tables. Normalization helps to decrease anomalies that tend to occur when various queries are executed. The normalization process is divided into 4 different forms each addressing the various ways in which redundant data can be identified and used.

 

  • Creation of optimized indexes. Index creation is a tricky subject. No indexes will lead to queries being slow and too many indexes will slow down the DML queries ( queries to insert, update and delete). This means that indexes must be created only when it is required. In my upcoming webinar series: “Advanced MySQL query tuning”, I take you through each task of SQL query optimization step by step. Enrolling in the course gives you a lot of benefits that can boost you to the next level.

 

  • DB statistics tools provide information on table indexes and their distribution. These statistics help the DBA to find paths that not only satisfy a query but is also inexpensive.

 

Conclusion: what to do next?

 

We saw how you can quickly identify some of the most common mistakes developers or DBAs make. You can identify such anomalies and correct them to improve the database performance.

 

These, however, were just the basics. A drop in the ocean. As a DBA or someone performing SQL Query Optimization or Database tuning, there is much more to learn and apply. Over the 17 years in the industry, many of the Fortune 500 companies have hired me as a performance expert consultant and I have successfully helped them in overcoming their challenges.

 

mysql_mariadb_performance_security

#2: How To Solve The Biggest Problems With MySQL Database?

November 9th, 2016 Posted by MySQL NEWS No Comment yet
mysql performance tuning tools

MySQL Performance Security

Today, let’s talk about one of the biggest problems that a lot of organizations have with MySQL performance tuning, security and compliance posture.  

Since years I see the same kind of problems with MySQL performance issues that I have been solving, like: 

  • Every 20-25 hours, all MySQL queries seem to be stuck for 1-2 minutes.  
  • MySQL is consuming too much CPU and disk.  
  • Replication slaves can’t keep up anymore.  
  • This query shouldn’t be taking this long. It’s 10 times faster in dev. 
  • Things were running fine for three months and suddenly performance slowed down, we’re not sure what to do. 
  • Hundreds of other interesting challenges, each special in their own way. 

 

Let me ask you this important question?


How many of you : 

  • Monitor their MySQL databases ? 
  • Monitor performance metrics ? 
  • Monitor security of their databases? 

Did you know ? 

Database monitoring is essential for two main reasons: performance and security of the database. 

 

Database monitoring should be important to you !

MySQL database monitoring is an important part of your organizations security and compliance posture. Knowing what to monitor, who should monitor it, and how often to set up alerts and scans is crucial for maintaining system health. Most importantly, implementing the right tools can save your organization a tremendous amount of time and agony, as well as help prevent potential breaches, performance issues and get answers to a lot of questions. 


How often do you have to monitor your MySQL Databases ? 


It depends how critical it is. If it’s a mission-critical system, you need to be on top of both performance and security issues at least daily. However, you can reduce your need to view the dashboard of your monitoring tool to a weekly check-in if you have alerts set up to inform you of critical issues in real-time. 


For both database security and database performance, real-time alerts are highly recommended for any critical databases. Real-time alerts ensure that you can take care of critical issues as soon as they occur. Real-time preventive measures ensure that your database is secure, even if you don’t take any immediate measures. 

 

What should you be monitoring? 


Every organization is different, but following the MySQL database check list that could point to performance and security. Here is the list: 

  • mysql_performance_tuning_consulting

    MySQL Performance Tuning Consulting

    Identification of all privileged users 

  • Database performance and history tracking 
  • Connections usage 
  • Content of queries and responses of queries 
  • Inactive users 
  • Configuration changes (Security article
  • Storage of the database monitoring and log information
  • Top 10 query consumers of data 
  • MySQL replicated cluster 
  • Deadlock 
  • Information on disk usage

 

Get information before the server runs out of resources. 

 

I have used in my MySQL DBA career many monitoring tools like for instance: MySQL Enterprise Monitor and MONyog monitoring tool and for me the best monitoring tool that is easy to use, configure and gives you all performance and security metrics that you need is MONyog   

But why MONyog? 

MONyog is the best out-of-the-box GUI monitoring tool for MySQL that I have seen so far. It “just works.” You can get it up and running quickly and having a centralized location for monitoring is very useful. The graphs are beautiful and the statistics that are graphed are useful time-savers. 

The biggest difference between MySQL Enterprise Monitor and MONyog is that MONyog is agentless. that agentless operation works for every feature, including log analysis as well as operating system statistics. 

 

Conclusion 

 

The biggest challenge for organizations is to identify the SQL statement that runs slow, get the information before the server runs out of resources and get alerts on time. 

The best advice I can give you is to get the right MySQL monitoring tool that will help you by advising you before a problem will occur, give you some advice based on real time metrics. 

In other words, having a monitoring tool that will advise you is as a “MYSQL TUNING EXPERT” in a box will help you sleep at night and also help you to tune your current MySQL servers and find and fix problems before they can become serious or costly outages.
  

Stay Connected!

Get email MySQL Optimization PRO tips delivered straight to your inbox!
SUBSCRIBE
Click Me