Posts tagged " mysql query "

ericvanier.com MySQL Query

#8: How to Make the most out of a MySQL Query?

Posted by MySQL NEWS No Comment yet

Database Management systems have evolved over the years, yet it is crucial to stick to the basics to optimize their performance. From a developer’s perspective, one of the most critical components for an efficient, database-driven application is an SQL Query.

 

One ill-written SQL Query can not only slow down the entire database but also affect other components of the application. Good observation and statistical analysis are critical, but, MySQL comes loaded with specific tools and techniques to optimize SQL Queries and improve the overall time of the database. Let’s have a look at some of these MySQL query techniques.

 

Benchmarking

Assumptions are limited to certain conditions. Hence, numbers are required to support your results and give you clarity on the efficiency. Benchmarking points out the issues in the queries such as bottlenecks, and bad queries.

 

With the help of certain profiling tools such as SuperSmack and SysBench, it is possible to simulate high-stress situations and find faults in the database. This process creates the pathway to solution through simulation.

Profiling a MySQL Query

Post the simulation of high-stress situations through the benchmarking process, it is important to point out the cracks in the database. This can be done using profiling. The primary function of the profiling process is to point to the bottlenecks present in the CPU, memory, disk I/O, network, and/or any other component.

After the identification of slow queries, use of internal MySQL tools like EXPLAIN, STATUS, and SHOW will give you information regarding the use of resources and their side effects. This process helps identify the slow queries.

Schema Designing

This step is a pre-prep for query execution. A schema design needs to be completed before any query can even be written. When creating a schema, keeping in mind the details such as memory requirements and the type of data to be entered can help optimize MySQL query execution.

This process of normalizing a database, unlike a popular opinion of creating a complex schema, leads to minimizing redundancy of the data, hence speeding up the query. To make the most of your MySQL query, normalizing is essential. Moreover, if required, de-normalization can be processed later.

 

Indexing

A query is only as good as the choice of indices, so selecting the index is very important. An index, usually a B-tree, speeds up the process of a query. If a query is executed without any index, then the DBMS must scan every row of the table and compare it with the given condition. However, MySQL query can be optimized, by merely using an index.

During the process, one needs to maintain a balance. Not only do indexes take up memory space, but also affect the write performance as they have to be updated after every ‘write’ operation. Profiling helps to understand this balance, which will vary from situation to situation.

Partitioning the tables

For any database, it is essential to identify and differentiate between frequently used data and otherwise. A common example could be a blog where data such as ‘title of the topic’ might be used more regularly than other content. Now, based on this differentiation, if partitioning is performed the infrequently used data will take up less memory and help optimize the query executed.

When the data is kept in separate tables, the caching process is simplified. The reason being that MySQL will no longer have to change cache for the unaffected data.

MySQL queries might seem extremely simple or extremely complex, but some simple tips mentioned above can change the entire perspective and give a more efficient MySQL database.

 

I have almost 2 decades of experience in MySQL database tuning and performance. Get in touch with me and I can show you how you can improve your application performance.

MySQL Optimization

#3: Top 3 must have MySQL DBA skills for 2017

Posted by MySQL NEWS No Comment yet

Top 3 must have MySQL DBA skills for 2017

Database administrators are faced with the rapid advancements in database technologies. There are new updates, features or releases that are rolled out frequently. This makes it important to make sure that your DBA skills are up to date. I have worked closely with some of the fortune 500 companies as a database consultant and a DB performance expert. In this article, I share with you the top DBA skills that you must have to become a better DBA.

Why is it necessary to improve your DBA skills?

 

As of today, DBAs are faced with many key challenges. These challenges involve a paradigm shift in the way applications are developed, turning all the focus to them. The fact still remains that at the heart of it lies the database.

 

A few years ago, having MySQL DBA skills or even more specific like the Oracle DBA skills were considered sufficient. But now with the advancement of technologies like NoSQL, a DBA is faced with the challenge of being an expert with these newer technologies.

 

The graph below is the result of a study made by Gleanster Research. The graph indicates the most common reasons for performance issues in applications. As you can see, database related issues are number one with it occurring 88% of the time. Being a database Performance Expert, I have helped many organizations to solve their issues and improve the overall performance.

 

MySQL Performance

MySQL Performance

 

Let us now walk through the top DBA skills required for 2017.

 

1.Managing Distributed Database systems

Managing Distributed Database systems

Managing Distributed Database systems

 

With the advancement in cloud computing and cloud data storage, most of the applications are built with distributed databases. An organization’s critical data might be present on the “on-premise data center” . However, the remaining majority amount of data might be present in the cloud. This makes it very important for DBAs to have the knowledge on distributed data systems and its implications on the application’s performance.

 

When it comes to distributed data systems, knowing what has to be done where is the most valuable skill among all the DBA skills. Following are some challenges that you have to be ready to face and how you can optimize.

 

  1. When there is a choice of deployment between on-premise and cloud, the performance implications due to latency and bottlenecks should be considered.
  2. Deployment of distributed systems can become unpredictable due to factors like backup load time, internet speed, etc, and to mitigate these factors, consistency must be maintained while using the deployment tools or software and strategies.
  3. There is a chance of problems not being solved only by moving databases to the cloud. In such cases, the inherent performance issues must be taken into consideration.
  4. Moving to the cloud will have a security risk attached to it. This requires you to move beyond the traditional encryption based security service and provide a much more secure platform.
  5. The backup, recovery and restoring strategies must be thought of thoroughly as distributed systems can cause issues only in certain places.

2. Adapting to the shift in development approaches

 

From the research that I have stated earlier, DBs are one of the major reasons for performance issues. They are the heart of almost every application. With organizations relying heavily on applications to run their business, the focus is shifting to building better applications. When a shift like this occurs, the DBA no longer is responsible for just the database.

 

The responsibility increases to the whole application. This does not mean that the DBA should know application development. What it means is that the DBA needs to make sure that the application performance is not compromised due to database related implications. The required DBA skills, in this case, would be as follows.

 

  1. The DBA must maintain a performance trend of the application over time. This trend will contain in detail the performance measure of the application over days, weeks, and months. This would then form a tool for identifying patterns, or anomalies in application performance which intern might be related to issues in the database.

 

  1. The resources that are allocated to the DBs come under the umbrella of traditional SQL DBA skills. But, with the shift in approach, the DBA must move a step further and be accountable for the application as a whole. This can be done by managing and maintaining the logs of end-user wait times. Keeping a tab on what the end-user is waiting on and what kind of data is affecting the load time is important. This information can be used to tune the DBs to improve performance.
  2. The DBA must explore and set up all the necessary tools for monitoring not just the state of the DB servers, but also the other infrastructures like storage machines, hosts, networks, etc. Without this, it can get extremely difficult to perform any check on the application while monitoring for errors and anomalies.

 

3. Diversification of Database platforms

 

Diversification of Database platforms

Diversification of Database platforms

 

The applications are no longer always monolithic. As of now, applications are typically broken down into manageable sub units each having an architecture that suits the type of data it consumes. When situations arise where these units consume unstructured data, No-SQL databases like Mongodb or Cassandra are used. This makes it important for the DBAs to adapt to newer Databases and know how to manage all the different databases efficiently. Following are some of the DBA skills with respect to multiple databases.

 

  1. Evaluating the cost and performance measures with regard to moving various databases to the cloud vs keeping it on-premise. As a DBA, you will have to assess the performance parameters before moving a particular database to the cloud or moving it away from cloud to a local data center.

 

  1. Just like the other skills, you should find a single point tool for all the different databases. Maintaining a different dashboard for each type of database will make it cumbersome to manage them. A better solution would be to invest on a software that provides a single dashboard for all the different types of databases.

 

  1. As much as possible try to maintain consistency when it comes to integrity and security of the data. Also, use consistent strategies for backup and restore. This will make sure that the failure due to mixing up of strategies reduces, thereby improving the capability of the database.

 

Conclusion

 

With this, I hope you are ready to transform, adapt, and embrace the challenges that a DBA encounters. With the change in trends, we must adapt to those changes so as to remain in the game and become better at it.

I have designed and created a series of webinars which aims to help DBAs like yourself to become better and stay up to date with the technology. I have shared the knowledge I have gained through my experience in the industry  and I can assure you that you will have many things you can take away from it.