improve MySQL database performance - Tosska Technologies Limited

2022-09-06

How to Tune SQL Statement with LCASE function on index field?

Some business requirements may need to compare the lower case of an indexed column to a given string as a data retrieval criterion.

Here is an example SQL that retrieves records from the EMPLOYEE table employee if the lower case of the name is equal to the string ‘richard’.

select *
from employee
where LCASE(emp_name)=‘richard’

Here the following are the query plans of this SQL, it takes 17 seconds to finish. The query shows a “Full Table Scan Employee”

You can see that this SQL cannot utilize index scan even if the emp_name is an indexed field. Let me add a “Force Index(emp_name_inx)“hint to the SQL and hope it can help MySQL SQL optimizer to use index scan, but it fails to enable the index scan anyway, so I add one more dummy condition “emp_name >= ””, it is an always true condition that emp_name should be greater or equal to a smallest empty character, it is used to increase the cost of not using emp_name_inx index. There is another condition added “emp_name is null” to correct this condition if emp_name is a null value.

select *
from employee force index(EMPS_NAME_INX)
where LCASE(emp_name) = ‘richard’
and ( emp_name >= ”
or emp_name is null )

Here is the query plan of the rewritten SQL and it is running much faster. The new query plan shows that an Index Scan is used now and takes 2.79 seconds only.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 6 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

2022-02-102022-03-16

How to use FORCE INDEX Hints to tune an UPDATE SQL statement?

We used to use FORCE INDEX hints to enable an index search for a SQL statement if a specific index is not used. It is due to the database SQL optimizer thinking that not using the specific index will perform better. But enabling an index is not as simple as just adding an index search in the query plan, it may entirely change the structure of the query plan, which means that forecasting the performance of the new Force Index hints is not easy. Here is an example to show you how to use FORCE INDEX optimization hints to tune a SQL statement.

A simple example SQL that updates EMP_SUBSIDIARY if the emp_id is found in EMPLOYEE with certain criteria.

update EMP_SUBSIDIARY set emp_name=concat(emp_name,'(Headquarter)’)
where emp_id in
(SELECT emp_id
FROM EMPLOYEE
WHERE emp_salary <1000000
and emp_grade<1150)

Here the following is the query plan of this SQL, it takes 18.38 seconds. The query shows a Full Table Scan of EMPLOYEE and then Nested Loop to EMP_SUBSIDIARY with a Unique Key Lookup of Emp_sub_PK index.

We can see that the filter condition “emp_salary <1000000 and emp_grade<1150” is used for the full table scan of EMPLOYEE. The estimated “filtered (ratio of rows produced per rows examined): 3.79%”, it seems the MySQL SQL optimizer is failed to use an index to scan the EMPLOYEE table. We should consider forcing MySQL to use either one of emp_salary or emp_grade index.

Unless you fully understand the data distribution and do a very precise calculation, otherwise you are not able to tell which index is the best?

Let’s try to force the index of emp_salary first.

update   EMP_SUBSIDIARY
set   emp_name=concat(emp_name,‘(Headquarter)’)
where emp_id in (select  emp_id
from    EMPLOYEE FORCE INDEX(`emps_salary_inx`)
where emp_salary < 1000000
and emp_grade < 1150)

This SQL takes 8.92 seconds and is 2 times better than the original query plan without force index hints.

Let’s try to force the index of emp_grade again.

update   EMP_SUBSIDIARY
set   emp_name=concat(emp_name,‘(Headquarter)’)
where emp_id in (select  emp_id
from    EMPLOYEE FORCE INDEX(`emps_grade_inx`)
where emp_salary < 1000000
and emp_grade < 1150)

Here is the result query plan of the Hints FORCE INDEX(`emps_grade_inx`) injected SQL and the execution time is reduced to 3.95 seconds. The new query plan shows an Index Range Scan of EMPLOYEE by EMP_GRADE index, the result is fed to a subquery2(temp table) and Nested Loop to EMP_SUBSIDIARY for the update. This query plan’s estimated cost is lower and performs better than the original SQL. It is due to the limited plan space in the real-time SQL optimization process, so this query plan cannot be generated for the original SQL text, so manual hints injection is necessary for this SQL statement to help MySQL database SQL optimizer to find a better query plan.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the Hints injected SQL is more than 4.6 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

2022-01-142023-02-18

Tackling Large Tables to Improve MySQL Database Performance

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.

Conclusion
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.

2021-11-262022-03-16

How to Tune SQL Statements with NO_RANGE_OPTIMIZATION Hints Injection?

There are some SQL statements with performance problem can be tuned by Hints injection only. Here is an example to show you how to use NO_RANGE_OPTIMIZATION optimization hints to tune a SQL statement.

A simple example SQL that retrieves data from EMPLOYEE and EMP_SAL_HIST tables.

select * from employee a,emp_sal_hist h
where a.emp_id =h.sal_emp_id
and a.emp_dept < ‘B’
and h.sal_salary between 1000000 and 2000000

Here the following are the query plans of this SQL, it takes 24.3 seconds. The query shows an Index Range Scan (EMPS_DPT_INX) of EMPLOYEE and then Nested Loop to EMP_SAL_HIST with a Non-Unique Key Lookup of SALS_EMP_INX index.

The EMP_SAL_HIST is the employee’s salary history table which keeps more than one salary record for each employee. So, EMPLOYEE to EMP_SAL_HIST is a one-to-many relationship. The speed of a nested loop operation is highly dependent on the driving path of two nested loop tables. MySQL SQL optimizer estimated that the condition (a.emp_dept < ‘B’) can rapidly reduce the result set, so the driving path that “from EMPLOYEE to EMP_SAL_HIST” is selected.

Unless you fully understand the data distribution and do a very precise calculation, otherwise you are not able to tell whether this driving path is the best or not.

How to make MySQL consider another driving path “from EMP_SAL_HIST to EMPLOYEE”? Let’s take a look at MySQL documentation:

NO_RANGE_OPTIMIZATION: Disable index range access for the specified table or indexes. This hint also disables Index Merge and Loose Index Scan for the table or indexes. By default, range access is a candidate optimization strategy, so there is no hint for enabling it.

This hint may be useful when the number of ranges may be high and range optimization would require many resources.

To disable the Index Range Scan of the EMPLOYEE table, I explicitly add a Hints /*+ QB_NAME(QB1) NO_RANGE_OPTIMIZATION(`a`@QB1) */ to the SQL statement and hope that MySQL will use the Index Range Scan by the condition (h.sal_salary between 1000000 and 2000000) as the first driving table.

select /*+ QB_NAME(QB1) NO_RANGE_OPTIMIZATION(`a`@QB1) */ *
from    employee a,
  emp_sal_hist h
where a.emp_id = h.sal_emp_id
  and a.emp_dept < ‘B’
  and h.sal_salary between 1000000 and 2000000

Here is the result query plan of the Hints injected SQL and the execution time is reduced to 10.01 seconds. The new query plan shows that the driving path is changed from EMP_SAL_HIST table nested loop to EMPLOYEE table. So, sometimes you may make use of the NO_RANGE_OPTIMIZATION hint to control the driving path order to see if MySQL can run your SQL faster.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the Hints injected SQL is more than 2 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

2021-07-272023-02-18

Oracle Database and SQL: The Pros & Cons of Bind Variables

Bind Variables are typically considered as one of the major aspects of enhanced SQL query performance. According to Oracle documentation, they serve as a placeholder in a SQL query, getting replaced by a particular value that helps in statement execution.

The use of these variables enables users to create statements that can receive time-running parameters or inputs. One may think of bind variables as “value” given to the SQL query that acts as any function in programming languages. Here, we will talk more about them as well as their advantages and disadvantages in Oracle database and SQL.

Bind Variables: Examples of Their Uses

Consider the following statements in SQL –

Select * from Staff where S_No = 1 ;

Select * from Staff where S_No = :a ;

In the first query, a proper value (1) is applied to operate the query, whereas, in the next query, we have used a bind variable (:a) to operate the statement. This bind variable is given to Oracle when the query is run.

Defining a bind variable in the SQL statements in the place of literal values ensures that a single Parent Cursor is utilized by Oracle for the query. This helps improve database performance because Oracle searches for precise text matches for the query to check whether it already exists in the shared pool. Using a bind variable rather than a literal value saves an expensive hard parse for each run of that query.

Bind variables prove especially useful in OLTP-type environments because their use facilitates soft parsing. In other words, it takes less processing time to re-generate execution plan.

How Bind Variables Help Improve Database Performance

Given below are some advantages of using bind variables:

Optimal Use of Shared Pool – The Shared Pool in Oracle Database needs to hold a single query instead of possibly numerous queries, thanks to bind variables.
Improved Performance Due to Zero Hard Parsing – There is no need for hard parsing because SQL queries only diverge in terms of values.
Decreased “library cache” Latch Contention – Since library cache latch contention is needed during a hard parse, its requirement reduces when bind variables are used.

Shortcomings of Bind Variables

The disadvantages of using bind variables are few. Although bind variables prove excellent if you want to improve Oracle database performance, there are instances where their use can negatively impact results:

They can decrease the flow of information needed to compute the best access path for CBO (Cost Based Optimizer). The CBO, in turn, may fail to identify the correct selectivity and create insufficient bad execution plans, opting for a complete table scan instead of using indexes.
Sometimes, the CBO requires the literal value to be used by SQL in order to build a robust execution plan. With bind variables, the literal value gets “hidden”, so the CBO is likely to create a subpar plan.

To overcome this issue, Oracle has tried to provide further assistance to the CBO by enabling it to take a look at the bind variable’s value during execution plan creation, which is known as “Bind Variable Peeking”.

Conclusion

The use of bind variables is extremely useful in Oracle database performance, especially when it comes to OLTP environments. However, as a user, you need to be careful while using bind variables. . It is recommended to use bind variables for short runtime SQL, but use literals for long time SQL statements to more information to CBO to generate good query plans.

2021-07-20

How to Tune SQL Statements with Rewrite and Hints Injection for MySQL?

There are some SQL statements with performance problem have to be tuned by SQL syntax rewrite and Hints injection, it is a little bit difficult for SQL tuning newcomers to master this technique. Developers not only have to understand the relationship between SQL syntax and the final query plan generation but have to understand the usage of optimizer hints and its limitations. Sometimes these two tuning techniques application will affect each other in a complex SQL statement.

Here is a simple example SQL that retrieves data from EMPLOYEE and DEPARTMENT tables.

select * from employee,department
where emp_dept=dpt_id
and emp_dept<‘L’
and emp_id<1500000
and emp_salary= dpt_avg_salary
order by dpt_avg_salary

Here the following are the query plans of this SQL, it takes 7.7 seconds to finish. The query shows a “Full Table Scan Department” and nested loop Employee table with a Non-Unique Key Lookup EMPS_SALARY_INX.

You can see that this SQL cannot utilize index scan even though the dpt_dept is an indexed field. It is because the condition emp_dept<‘L’ is not explicitly induced the condition dpt_id < ‘L’ although emp_dept=dpt_id is also listed in the where clause.

To enable the index search of Department table, I explicitly add a condition dpt_id < ‘L’ to the SQL statement as the following:

select *
from employee,
department
where emp_dept = dpt_id
and dpt_id < ‘L’
and emp_dept < ‘L’
and emp_id < 1500000
and emp_salary = dpt_avg_salary
order by dpt_avg_salary

Here is the query plan of the rewritten SQL and the execution time is reduced to 3.4 seconds. The new query plan shows that an Index Range Scan is used for the Department table and nested loop Employee table.

You may find that the nested loop to Employee by EMPS_SALARY_INX lookup may result into a lot of random access to the Employee table. Let me add a BKA hint to ask MySQL to use ‘Batched Key Access’ to join the two tables.

select /*+ QB_NAME(QB1) BKA(`employee`@QB1) */ *
from employee,
department
where emp_dept = dpt_id
and dpt_id < ‘L’
and emp_dept < ‘L’
and emp_id < 1500000
and emp_salary = dpt_avg_salary
order by dpt_avg_salary

The new query plan shows a Batched Key Access is used to join Department and Employee tables, you can BAK information from MySQL manual for details, the new plan takes only 1.99 seconds and it is more than 3 times better than the original SQL syntax.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 3 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

2021-05-22

How to Tune SQL Statements to Run SLOWER… but Make Users Feel BETTER (MySQL)?

Your end-users may keep on complaining about some functions of their database application are running slow, but you may found that those SQL statements are already reached their maximum speed in the current MySQL and hardware configuration. There may be no way to improve the SQL unless you are willing to upgrade your hardware. To make your users feel better, sometimes, you don’t have to tune your SQL to run faster but to tune your SQL to run slower for certain application’s SQL statements.

This is an example SQL that is used to display the information from tables Emp_subsidiary and Employee if they are satisfied with certain criteria. This SQL is executed as an online query and users have to wait for at least 5 seconds before any data will be shown on screen after the mouse click.

select *
from employee a,
emp_subsidiary b
where a.emp_id = b.emp_id
and a.emp_grade < 1050
and b.emp_salary < 5000000
order by a.emp_id

Here the following is the query plan and execution statistics of the SQL, it takes 5.48seconds to extract all 3645 records and the first records return time ”Response Time(Duration)” is 5.39 seconds. The query shows a “Full Table Scan b (emp_subsidiary)” to Nested-Loop “a (employee)” table, an ORDER operation is followed by sorting the returned data by emp_id. You can see there is a Sort Cost=7861.86 at the ORDER step on the query plan. It is the reason that users have to wait at least 5 seconds before they can see anything shows on the screen.

To reduce the sorting time of a.emp_id, since a.emp_id=b.emp_id, so I can rewrite the order by clause from “order by a.emp_id” to “order by b.emp_id”, MySQL now can eliminate the sorting time by using the EMPLOYEE_PK after the nested loop operation.

select *
from employee a,
emp_subsidiary b
where a.emp_id = b.emp_id
and a.emp_grade < 1050
and b.emp_salary < 5000000
order by b.emp_id

Although the overall Elapsed Time is higher in the new query plan, you can see that the response time is reduced from 5.397 seconds to 0.068, so the users can see the first page of information on the screen instantly and they don’t care whether there are 2 more seconds for all 3,645 records to be returned. That is why SQL tuning is an art rather than science when you are going to manage your users’ expectations.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

2021-04-292023-02-19

Query Performance Tuning: Making an SQL Monitor Report

Creating a SQL Monitor Report plays an important role in database optimization as it helps the user observe other occurrences during the execution of long-running statements.

In this post, we’ll discuss how to create one such report that may help you during query performance tuning.

SQL Monitor Report: Bringing DBAs One Step Closer to Database Query Optimization

To begin with, you need to make sure your database has the tuning and diagnostic pack. Otherwise, Oracle will not authorize the creation of SQL Monitor Reports.

Also, such reports can be made after an adequate amount of time has passed. The wait is to allow query bottlenecks to reveal themselves. This is typically done for seemingly endless queries that run for long periods of time. However, in general, creating SQL monitor reports is recommended for completed queries.

Let’s look at an example: A DBA has a simple plan with a hash join involving two big tables. Suppose one of these tables takes two seconds to undergo a complete table scan, whereas the second one takes nine seconds.

Although only around two seconds out of a total of eleven seconds are sent on the first table, it will appear as though a hundred percent of the query time is being spent on it if you create a SQL Monitor report during the first two seconds.

Creating Reports for Excessively Long Execution Plans

Really long execution plans – those that exceed three hundred lines – don’t have a SQL monitor report generated for them by default. This gets cumbersome because long execution plans are where these reports are needed the most!

In such cases, there are two things you can do to make the database generate a report. These are –

Prior to issuing the query in question, generate the following in the session operating the query:

alter session set “_sqlmon_max_planlines” = 800;

2. Apply the following hint while executing the query:

/* + monitor */

How to Create an HTML Version of the Monitor Report

The HTML version of a SQL Monitor report offers some more details as compared to its text report. This is why it is often recommended by database professionals, with the help of the following query:

Select dbms_sqltune.report_sql_monitor(

sql_id => ‘&v_sql_id.’,

Session_id => ‘&v_session_id.’,

Session_serial => ‘&v_serial.’,

Type => ‘HTML’,

Report_level => ‘ALL’,

Inst_num => ‘&v_instance.’ )report

from dual;

Not every variable needs to be plugged in – you just require variables sufficient to enable Oracle to recognize the particular SQL\session combination. And if there is only a single session executing the statement on the entire database, only the sql_id is enough.

Creating a Text Monitor Report Instead

In case you’d rather make a text report – whether if it’s due to some problems with an HTML report, or simply preference – here’s how to do it –

Select dbms_sqltune.report_sql_monitor(

sql_id => ‘&v_sql_id.’,

Session_id => ‘&v_session_id.’,

Session_serial => ‘&v_serial.’,

Type => ‘TEXT’,

Report_level => ‘ALL’,

Inst_num => ‘&v_instance.’ )report

from dual;

2021-04-082021-04-08

How to Tune SQL Statements with CONCAT Operator for MySQL?

There may be some business requirements that need to compare concatenate strings and column with a given unknown length of the bind variable. Here is an example SQL that retrieves data from EMPLOYEE and DEPARTMENT tables where employee’s department ID must concatenate two strings before it is compared to an unknown length of variable @dpt_var

select * from employee,department
where concat(concat(‘A’,emp_dept),‘B’) = @dpt_var
and emp_dept= dpt_id

Here the following are the query plans of this SQL, it takes 23.8 seconds to finish. The query shows a “Full Table Scan Employee” to nested loop Department table.

You can see that this SQL cannot utilize index scan even the emp_dept is an indexed field. Let me add a “force index(EMPS_DPT_INX) hints to the SQL and hope it can help MySQL SQL optimizer to use index scan, but it fails to enable the index scan anyway, so I add one more dummy condition emp_dept >= ” , it is an always true condition that emp_dept should be greater or equal to a smallest empty character. It is to fool MySQL SQL optimizer that emp_dept’s index is a reasonable step.

select *
from employee force index(EMPS_DPT_INX),
  department
where concat(concat(‘A’,emp_dept),‘B’) = @dpt_var
  and emp_dept >= ”
  and emp_dept = dpt_id

Here is the query plan of the rewritten SQL and it is running faster. The new query plan shows that an Index Range Scan is used for Employee table first and then nested loop Department table.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 3 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

2021-02-052021-02-04

How to Tune SQL statement with Transitive Dependency Improvement for MySQL?

The following is an example shows a SQL statement with two conditions “emp_dept=dpt_id and emp_dept<‘L’”

select  *  from employee,department
where  emp_dept=dpt_id
and  emp_dept<‘L’
and  emp_id<1500000
and  emp_salary= dpt_avg_salary
order    by  dpt_avg_salary

Here the following is the query plan of this SQL in Tosska proprietary tree format, it takes 8.84 seconds to finish.

The query plan looks reasonable that shows a full table scan of DEPARTMENT to nested-loop EMPLOYEE table, the records in EMPLOYEE table being nested-loop must satisfy with the condition “emp_id<1500000” and the corresponding index EMPS_SALARY_INX is also used. Due to the number of records in the first driving table in a Nested Loop Join is very critical to the join performance, we should find a way to narrow down the number of result records of DEPARTMENT table before it is used to nested-loop EMPLOYEE table.

As the conditions “emp_dept=dpt_id and emp_dept<‘L’”, it implies that “dpt_id < ‘L’” is also true, let me add this extra condition to the SQL, it helps MySQL SQL optimizer to make a better decision with more information provided by the new SQL syntax, this technique is especially useful for MySQL database.
Remark:
Oracle or MS SQL Server are doing very good on their internal Transitive Dependency Improvement in their SQL optimizer already, so this technique may not work for Oracle and MS SQL Server.

select      *
from        employee,
    department
where     emp_dept = dpt_id
and dpt_id < ‘L’
and emp_dept < ‘L’
and emp_id < 1500000
and emp_salary = dpt_avg_salary
order by dpt_avg_salary

Let’s see the DEPARTMENT is now being filtered by the new condition “dpt_id < ‘L’ “ with an index range scan. You can see the estimated Rows 401 of DEPARTMENT table is now being trimmed down to 176. The rewritten SQL now takes only 3.8 seconds with such a simple change in syntax.

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that this rewrite is more than 2 times faster than the original SQL with such an easy change in the syntax.
https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/