Oftentimes, database professionals make the mistake of jumping to conclusions when trying to improve MySQL database performance. They assume that the database must be the reason why the application has slowed down.
In most cases, they may be right- which is why it’s important to start looking for possible bottlenecks and removing them to reduce lag. However, make sure you consider multiple forms of diagnostic data when attempting to uncover the root cause behind poor MySQL database performance. Don’t stick to just monitoring CPU usage or disk IO as relying on a single metric has greater chances of leading you to an incorrect diagnosis.
We need to look at the full picture to understand the complex interdependencies among CPU, memory, and IO. It is important to do so before making reactive changes, such as increasing disk capacity or memory. In this blog, we will take a look at one such reason behind performance bottlenecks- large data volumes.
How Large Data Volumes Affect MySQL Database Performance
Statements that cover a wide scope of data or are unrefined may fetch unreasonably large quantities of information from the database. This doesn’t seem like a problem at first when the database is new and has minimal data.
The true issue emerges as it grows in size, gradually leading to the requirement of Database Server. This is because when a statement fetches data, the data must be scanned into memory. The bigger the size of the data that needs scanning, the greater the load on the CPU, resulting in the need for burst mode due to sudden CPU spikes. This kind of usage increases the chances of your database server crashing.
Additionally, in case the data does make it from the database server, your app server may not be sufficiently provisioned to handle it. Known as over-fetching, you can overcome this problem by limiting the scope of data selection to relevant records. One way to do that is to opt for the WHERE clause in such queries- after you find them, of course.
The key to locating them is by searching through the database logs and metrics for tell-tale signs of large-scale data fetching. Although you might be able to spot CPU spikes or burst credit utilization from these metrics, it might not be easy to tell which statements are causing this specifically.
Things You Can Do to Improve MySQL Database Performance
Query optimization is one of the best places to begin when you have to improve MySQL database performance. But it differs from case to case and is far from a one-size-fits-all endeavor. That said, there are certain tasks that help in a lot of cases:
- As mentioned above, you can prevent large result sets and decrease data volume by limiting the search to relevant records using the WHERE clause.
- Go through the database schema to uncover ways that decrease complexity. For instance, keep an eye out on queries that contain a lot of joins since they take more time than most queries. You can make them run faster by reducing their relationships.
- A large number of queries also fetch unnecessary fields from tables. You can set them to return only those fields that are important to keep from over-fetching again.
- Views can help in some, but not all cases. A view is similar to a table that you can create beforehand by executing a statement to predetermine values that may require on-the-spot calculation otherwise.
- Change the syntax of the SQL to influence database SQL optimizer to generate a better query plan.
If your application is performing poorly, the problem often lies with the database, with inefficient queries. While there isn’t any solution that works for every single query out there, database experts can hone in on the ones that require optimization using diligent analysis and monitoring, along with the right SQL optimizer tool for sql server.
After they successfully find the queries behind slow database performance, all they have to do is take the right steps to resolve this issue. These include optimization techniques, such as adding indexes, editing out unnecessary fields, and inserting the WHERE clause wherever necessary.
DBAs can’t ignore disk operations when working on query performance tuning. When talking about databases, ‘disk’ may be called by one of its many names, such as ‘storage’, ‘I\O’, ‘Reads’, or disk operations.
Although database professionals know all these terms mean the same thing, these might confuse those outside this field. When referring to one of these terms, they usually mean the number of disk operations required to fetch the data from the Disk resource.
Why You Can’t Ignore Disk Operations During Query Performance Tuning
The fact remains, however, that an overwhelming majority of SQL Server databases face the bottleneck issue when it comes to the disk resource. This doesn’t change, regardless of whether you have old-school hard drives or the latest flash storage arrays. Given below are some major reasons behind this, and how these can be affected with MySQL query optimization:
- Most slow queries are slow because they have to scan a large amount of data. A lot of the time, this is unnecessary and it’s making your SQL Server perform a lot of unneeded and really sluggish read operations.
- When the database reads data, it requires a place to store that information- which it does in the RAM. However, since the RAM has a limited capacity, older information starts getting removed with newer data coming in.
- Because RAM is never enough, it is often unable to store all the data that SQL Server fetches. Therefore, the remaining data has to be kept on the disk, which is far slower than the RAM. The information that isn’t present in the RAM has to be fetched from the disk- an operation that is known as the slowest in all of the database operations. Some DBAs even compare data fetching from the RAM and the disk to sprinting and tip-toeing.
- So, if we tune a query to read less data than it did before, such as twenty rows instead of twenty thousand, it will help in two ways. Not only will it reduce the workload on the database in terms of disk operations, but it will also require far fewer resources, including CPU and RAM, to process all the data. That said, the end-user is unaware of all these operations- all they know and appreciate is the speed or the time it takes for the query to fetch information. To put it simply, they just want the screen on the app to return as quickly as possible. This is why query performance tuning focuses on decreasing disk reads.
- DBAs also perform tuning to lower the other resources, such as CPU or RAM usage. But they only do this in certain special situations where such resources are consistently being overused at dangerous levels. For instance, if the CPU is in constant use of 90% or above, then the DBA will consider CPU tuning.
- Tuning queries that fetch large volumes of data to fetch much smaller volumes instead improves SQL Server capacity. This is because when a query takes up fewer resources, it leaves room for more users and queries. This allows the same server to take greater loads than it could. Performing MySQL query optimization also improves the lifespan of the same server, delaying the requirement for a hardware upgrade.
The above-mentioned reasons shed light on the fact that disk operations play a major role in enabling efficient database query performance. You can’t always blame the CPU; in fact, you can rarely do so since 95% of bottlenecks occur on the disk resource.
The CPU, on the other hand, is only a lagging indicator whose use can decrease if the storage reads differ.
Indexes are among the most useful and underutilized components of SQL. The user can create an Oracle index and store values along with their location in it.
Similar to the index at the end of a book, an index enables the user to go straight to the data they are interested in. Indexes are most useful when a user has to find a few rows. Therefore, they can use an index in statements that return a handful of rows – after creating one, of course!
Simple Techniques to Create an Index in Oracle Database
Creating an index is a simple task in MySQL query optimization as you only need to know two things:
- The columns that require indexing
- The name you will give the index
Here’s how to create one:
create index <indexname> on <tablename> ( <col1>, <col2>, <col3>, … <coln> );
Eg. create index cars_colour_metallic on cars (colour);
However, there are a few things to know about indexes before you begin:
- You can place several columns in a single index, which then becomes a composite or compound index.
For instance, in the above example, you could also add the types of cars in the index like this: create index cars_colour_metallic on cars (colour, type);
- The order in which you set columns in the index affects its use by the optimizer.
Next, let’s take a look at two of the most important index types users create in Oracle.
Two Major Index Types – and When to Pick Each
There are several kinds of indexes in the Oracle database that can improve your SQL. However, one of the most significant decisions you’ll have to make is likely to involve choosing between B-trees and bitmaps.
Create Index Oracle: B-tree Versus Bitmap Indexes
B-trees:– Indexes are in balanced B-tree format by default, which means all the leaf nodes are located at the same depth. It takes equal effort (O(log n)) to access any value, and one leaf index entry contains one row of data.
Bitmap:- Bitmaps also store indexed values, but in a completely different manner as compared to B-trees. In it, one value entry is associated with a range of row values. A bitmap has a series of 1s (yes) and 0s (no) to indicate whether any of the range rows contains the value or not.
One major difference between these two index types is that a B-tree doesn’t include null indexed values; a bitmap does. A bitmap can, therefore, answer some statements during MySQL query optimization, such as targeted index searches in which the column has a null value.
Although this won’t work for a B-tree, the user can add a constant at the end of an index to turn it into a composite index.
Bitmaps are also helpful because compressing the bits is simpler, which is why a bitmap index is generally smaller as compared to a B-tree index with identical data.
Why You Need to Keep a Check on the Indexes You Create
With all the benefits an index provides, it is important to create as few of them as possible. This is because you may end up creating one for every specific requirement and forget about them over time. The same goes for other users who may come and go on your team. And no one will have a clue why Brad needed to create that six-column function-based nightmare.
Since you don’t know if the index in question is only used for year-end reporting or never used, you cannot drop an index whenever you want. This can result in awkward situations where a table contains more indexes than columns!
So, if you’re unsure between two excellent indexes and one “good enough” index, it is better to choose the latter. And don’t forget to test!
A database is the cornerstone of any application. For this reason, maintaining one or more backup and recovery options remains a priority for every database professional. There are multiple alternatives you can choose from as per the specific needs of your organization’s database.
In this post, we will examine some of the most popular back-ups and restore strategies for MySQL database and SQL. We will also touch upon the reasons why databases require backups on a regular basis.
Why Do We Need Backups for MySQL Database?
As a DBA, you’ll need backup and recovery to support data in multiple cases, such as:
Discrepancies in Data: Users may accidentally delete or update incorrect data in the primary or replica node.
Data Centre Failure: An indefinite power outage or internet connectivity issue can spell trouble for your organization.
Disk Damage: If the disk is stalling for too long due to some kind of damage, it can greatly reduce performance. In cloud services for Oracle database, it translates into a broken DB instance that cuts access.
Broken Data: In case of a power outage, MySQL may fails to write data and close files as usual. There are also instances where MySQL fails to restart and doesn’t work despite the crash recovery process because of corruption in data.
Legislation/Regulation: Backups and recovery options ensure business value and client satisfaction.
Various Kinds of Backups for MySQL Database
Given below are some common backup categories that suit a range of needs:
Physical: These comprise the exact copies of database files and may contain part or all of the MySQL directory. The most common use of this type of backup is to make a new replica node and respond to host failure in a convenient manner. Experts recommend restoring data with the help of the same MySQL database version.
Offsite: This is one of the most recommended backup alternatives as it guarantees an untouched copy in case of data centre or host failure. It involves copying the data to the cloud, an external file server or another external source. However, sometimes it may take longer to download the files from the cloud or server than the recovery process. Therefore, experienced database professionals keep about a week of data locally on a backup server for quick recovery.
Logical: It is useful for smaller quantities of data as it is slower in comparison to physical backup methods. It essentially consists of dumps from INSERT and CREATE statements. It is useful in addressing data corruption or when you must recover a subset of tables. Although the output is greater in logical backups, especially when that data is present in text format, you can perform a quick compression if the software you’re using requires it. For instance, you can use Mydumper and mysqldump to compress and redirect the data to the zip folder.
Incremental: This type of backup contains all the changes made in the organization’s Oracle database and SQL server since the last backup. It is, therefore, quite useful for enormous datasets since it allows you to take small backups (experts recommend this after you’ve taken a full backup) as data comes later.
Differential: It consists of copying the modifications since your previous backup. One advantage of a differential backup is that it saves disk space. This is because the data in these backups mostly remains the same, so the result leads to backups that are substantially smaller in size.
Every Oracle database admin dreads the day when they receive a flurry of complaint calls from users regarding slow online performance.
That’s why, if you’re responsible for managing database performance in your organization, you’ll want to ensure this doesn’t happen. How? By preparing your infrastructure to handle increased loads. Supporting it with performance tuning is also essential, especially when the systems slow down or become entirely unresponsive.
In this post, we’ll consider six important (yet often overlooked) methods and tips to improve Oracle database performance. These will also help you gain the most from your efforts and make sure your environment stays fully operational.
6 Ways to Optimize the Performance of Your Oracle Database
Take a look at the following tricks and tactics to improve Oracle database performance, explained in a simple manner:
1. Maintain Optimum CPU Utilization Levels
It may be necessary to carry out manual tuning processes if, for some reason, you cannot apply the automatic diagnostic features Oracle provides. For this, you must make sure of complete CPU utilization in the user space. Check it to determine whether any non-database tasks are taking up CPU and restricting shared CPU resources.
2. Have a Firm Grasp of System Capabilities
Understanding the importance of baselines is essential to improve performance of SQL query. It helps to preserve a set performance baseline that you can refer to in case of performance issues.
You can do this by keeping an eye out for peak usage periods while measuring current performance. This will enable you to manage bottlenecks easily.
3. Make Use of an AWR (Automatic Workload Repository) Tool
An AWR is extremely useful as it gathers, processes, and maintains performance stats. It also provides a clear picture of your database’s health. The Automatic Database Diagnostic Monitor (ADDM) can help you gain actionable insights from the information you gain here. Together, these tools will save you time and money while enhancing the performance tuning process.
4. Tune Inefficient SQLs Using the Right Tools
The quickest and the least risky way to get rid of Oracle database problems is to tune the SQL using the right query plan. You can also use tuning tools for the Oracle database, such as those provided by Tosska Technologies Limited.
Tosska’s SQL Tuning Expert (TSE™) for Oracle® is ideal for all levels of experience and expertise. Its professional version, Tosska SQL Tuning Expert Pro (TSE Pro™) for Oracle®, can improve performance of SQL query with or without source code. However, these tools work best when you define and measure system issues correctly first.
5. Boost Storage Efficiency
It is possible to improve database performance by enhancing the performance of the storage platform. Review the existing storage for your Oracle database server and see if you can opt for alternatives.
For instance, flash technology offers considerably greater bandwidth and decreased latency to IO-bound workloads. It allows targeted provisioning, thereby boosting performance while eliminating the need to refresh the whole database. The key is to use flash storage for database objects that will benefit the most, leaving the rest in traditional storage.
6. Opt for a Dedicated Database Infrastructure
Sometimes, a common infrastructure platform can be the reason behind reduced performance results. No amount of tuning efforts proves sufficient in such cases. One of the most effective solutions is to provide a dedicated infrastructure to the database. This helps stabilize performance for database workloads and convenient management as well as effective monitoring and security.
Ready to improve Oracle database performance in the simple ways mentioned above? You may choose to seek assistance from Tosska’s professional tuning tools for Oracle. They are designed to help you get the most out of your database and maintain a fully operational environment.
If there is one complaint business users may have about their production database, it is slow performance. Database professionals, therefore, often try to focus strictly on finding out and resolving the source of the issue in the database.
The Database Engine Tuning Advisor (DTA) is one of the best tools in this regard. It helps DBAs analyze workloads and uncover areas that can be improved. In this blog, we will discuss the working of this tool along with a few additional details.
What is the Database Engine Tuning Advisor?
It is a tool that assists with the optimization of SQL queries and was introduced in SQL Server 2005. Before it, SQL Server had a feature known as the Index Tuning Wizard.
The DTA is designed to assess a workload and provide recommendations to boost query performance. Some of its suggestions include:
- Making partitions
- Incorporating indexes
- Adding statistics (this helps resolve the issue of automatic statistics not getting created despite the auto_create_statistics option being ON)
Apart from locating every type of potential for enhancement, the Oracle Database Engine Tuning Advisor will build a T-SQL script for users to execute in order to carry out the recommendations made by it.
How to Launch the DTA for the Optimization of SQL Queries
You can commence its use through multiple methods:
- Open the Start menu, scroll to the SQL Server application group and look for the Performance Tools folder.
- If you’re using the SQL Server Management Studio application, you can select the tool from the Tools menu.
- The SQL Server Profiler also has this tool in its Tools menu.
- You can find it with the select analyze query in DTA on the SQL Server Management Studio Query menu. This also enables you to pass a T-SQL section to the tool for analysis.
- Open the Command Prompt, type “DTA -?” for a glimpse at the available alternatives.
Normally, you can create a workload by collecting multiple statements in a file or with the help of the SQL Server Profiler. An important point to bear in mind is that the workload being sent to the Advisor for evaluation needs to be representative of the average workload.
What the Database Engine Tuning Advisor Does
The Database Engine Tuning Advisor makes recommendations on the basis of the workload you send for analysis. Therefore, a limited workload will result in inadequate recommendations. The best thing to do is to collect the workload through the Profiler, save the results in a text file, and send the trace to the Advisor.
The SQL Server Profiler plays an important role in the optimization of SQL queries. It can be launched from its location in the SQL Server application folder (in the Start menu) or among the list of tools in the SQL Server Management Studio.
In case you want to know the properties of the new trace, follow these steps –
- Click on the dropdown list in the dropdown menu of the tool window (for the trace template) and select Tuning. The trace template gathers the events considered necessary by DTA.
- Select the ‘Save to file’ and type in the file name you want before passing it to the Database Engine Tuning Advisor once the trace is complete.
Choosing the right version of SQL Server is important for the performance you desire. If you’re installing an older one because your organization’s management prefers an older build or the vendor is unable to support newer versions, it is important to let them know which version your company needs, and why.
For this reason, we will discuss some popular versions of SQL Server from older to newer and mention their advantages in this blog.
Which SQL Server Version Works Best with SQL Performance Tuning?
Knowing the versions that support this task is extremely important because it will give you the ability to improve the SQL Server database and SQL performance.
To that effect, we will discuss the SQL Server 2016, 2017, and 2019 versions here.
SQL Server 2016
This version was chosen by a lot of independent software vendors or ISVs for one reason – 2016’s Service Pack 1 edition came with Enterprise features in Standard mode. These helped create a single application version that worked simultaneously for both Standard as well as Enterprise clients.
Advantages of Choosing this Version:
- It is easy to find support material online as this version is quite popular and numerous database professionals are well-versed with this version’s tools.
- Standard Edition users may find this version appealing since it supports 128GB RAM and additional space for internal functions such as query plans.
- Support for this version ends after 2026 – longer than the older versions (2012/2014).
- Newer applications that have additional compliance requirements will benefit from features in this version such as Always Encrypted, temporal tables, and Dynamic Data Masking. These will make it somewhat easier to protect and monitor sensitive information.
- You can have both row store and column store indexes in this version, unlike the earlier ones that only had row store indexes.
- If you need query plan monitoring to help with SQL performance tuning, you can use the Query Store’s features provided in SQL Server 2016 for this purpose.
SQL Server 2017
Being a newer release, it is one of the most regularly updated versions with patches coming in almost every other month. These patches are important because they resolve significant problems. It also comes with a minimum commit replica configuration to ensure commits are accepted by several replicas.
Advantages of Choosing this Version:
- The upgrades are easier to get from this version onward due to a Distributed Availability Group that contains multiple SQL Server versions in it. Before this, we had AG version upgrades that were not as convenient, leading most users to construct a new cluster and migrate to it rather than opt for an upgrade.
- This version contains batch mode execution plans, which gives those who require high-performance column store statements an advantage.
- If you must run your SQL Server on Linux, you may consider SQL Server 2017 as several bugs have been resolved in the Cumulative Updates.
- It’s a newer version so support will last longer than that of its predecessor.
SQL Server 2019
Released on November 4, 2019, this version is the latest in the SQL Server series. Naturally, it comes with the longest support lifespan, i.e. it will be supported until 2030. This version also receives regular patch updates to fix many significant issues in the form of Cumulative Updates.
Changes and Features in this Version:
- Patch contents aren’t documented anymore. Moreover, you are likely to receive updates with undocumented new features – something to consider in case you require it for mission-critical production environments.
- There is a bit of a learning curve thanks to some cutting-edge features in this version, so be prepared to perform some experimentation as you learn.
- Some of the best performance features are included in the 2019 compatibility mode. However, you will have to keep a close eye on all SQL Server databases and SQL queries – even the ones running fast at present – as these will alter your current execution plans. In other words, you will have to test both slow and fast queries to make sure the slow ones speed up and the fast ones don’t fall behind in performance.
- Table variables have gotten better in this version along with user-defined functions.
- Additional features to watch out for including Big Data Clusters, Java support, and high container availability, so you may want to explore this version if you’re looking for perks like these in the SQL Server you want.
At this point, SQL Server 2017 might seem like the best version to go with, thanks to a balance of features, stability, and support lifespan. Furthermore, you’ll receive plenty of help with SQL performance tuning – a lifesaver for overworked professionals who may not have the time or resources to upgrade every server every year.