mysql dba training session 17 optimizing mysql queries

MySQL DBA Training Session 17. Optimizing MySQL Queries

RAM N SANGWAN

WWW.RNSANGWAN.COM

YOUTUBE CHANNEL : HTTP://YOUTUBE.COM/USER/THESKILLPEDIA

TO LEARN OR TEACH JOIN WWW.THESKILLPEDIA.COM

WWW.RNSANGWAN.COM 1

http://www.rnsangwan.com/

http://youtube.com/user/theskillpedia

http://www.theskillpedia.com/

Coming up in this Session..

• Why Tune a Database?

• Who Tunes?

• What is Tuned?

• When should Tuning be accomplished?

• How much tuning is enough?

• Stages of Tuning

• Measurement Methods

• Query Execution Plan (EXPLAIN)

• How EXPLAIN Works

• Explain in Action

• Optimizer Hints

• Selecting Queries to Optimize

WWW.RNSANGWAN.COM 3

Why Tune a Database?

• Cost-effectiveness• A system that is tuned can minimize the need to buy additional hardware and other

resources to meet the needs of the end users.

• Tuning may demonstrate that the system being used is excessive for the end users anddownsizing is the better option.

• This may result in multiple levels of savings to include maintenance.

WWW.RNSANGWAN.COM 4

Why Tune a Database – Contd..

• Performance• A high-performance, well-tuned system produces faster response time and better

throughput within the organization.

• This increases the productivity of the end users.

• A well-tuned system benefits the organization’s customers, poor response time causes lot ofunhappiness and loses business.

WWW.RNSANGWAN.COM 5

Why Tune a Database – Contd..

• Competitive Advantage• Tuning a system for optimal performance gives the end users the ability to glean more

critical information faster than the competitors thus giving the company as a whole anadvantage.

• Tuning the access to the data helps business analysts, who are utilizing businessintelligence initiatives based on corporate data, make faster and more precise decisions.

WWW.RNSANGWAN.COM 6

Who Tunes?

• All persons involved with the MySQL software should be concerned withperformance and involved in tuning.

• Consider all phases of the System Development Life Cycle (SDLC) asopportunities to create and enhance an effective, well designed and efficientsystem.◦ Application Designers

◦ Application Developers

◦ Database Administrators

◦ System Administrators

WWW.RNSANGWAN.COM 7

What is Tuned?

• In most cases the greatest gain in performance can be achieved throughtuning the application.

• The most opportune time to consider performance issues is when theapplication is in the very early stages of the SDLC.◦ Application Design

◦ Application Development

◦ Database Structures

◦ Hardware

WWW.RNSANGWAN.COM 8

When should Tuning be done?

• From the beginning to…◦ Routine tune-ups

◦ Response to problems/bottlenecks

◦ Proactive Tuning

◦ End-of-life

Bottom line, performance tuning is never finished.

Tuning Outcome

• Minimize or decrease the response time Provide high throughput scalability at

comparable response times

Tuning Targets◦ How much data needs to be processed?

◦ How quickly can the information be returned to the user?

◦ What is the total number of users that can efficiently access the data?

WWW.RNSANGWAN.COM 9

How much tuning is enough?

• Too Little Tuning

• Too Much Tuning

• Other Questions to Consider

• Is the cost of improving the database architecture or adding additionalresources cost effective for the return on investment that the data is providingto the organization?

• Are changes made to database systems that are in production?

• Is the integrity of the data being compromised for speed?

There comes a point when the system is in balance, and it is better not to adjustsettings to achieve infinitesimally small performance improvements.

WWW.RNSANGWAN.COM 10

Stages of Tuning

• Application design◦ Application development

◦ Database configuration

◦ Application maintenance and growth

◦ Troubleshooting


Measurement Methods

• Benchmarking

◦ Tools: Sysbench and many more. By benchmarking you will really know how far your current

setup will go.

• Profiling with :

SHOW PROFILE [type [, type] ... ]

[FOR QUERY n]

[LIMIT row_count [OFFSET offset]]

Type:

ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE |

SWAPS

It display profiling information that indicates resource usage for statements executed during

the course of the current session.

It is controlled by the profiling session variable (default 0 -OFF). To Enable:

mysql> SET profiling = 1;


Were do I find a benchmark?

• Make your own – Can use general query log output

• DBT2 http://osdldbt.sourceforge.net/

http://samuraimysql.blogspot.com/2009/03/settingup-dbt-2.html

• mysqlslap MySQL 5.1 + –http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html

• Sysbench –http://sysbench.sourceforge.net/

• supersmack –http://vegan.net/tony/supersmack/

• mybench –http://jeremy.zawodny.com/mysql/mybench/


Query Execution Plan (EXPLAIN)

EXPLAIN tells you:

• In which order the tables are read

• What types of read operations that are made

• Which indexes could have been used

• Which indexes are used

• How the tables refer to each other

• How many rows the optimizer estimates to retrieve from each table


How EXPLAIN Works

• To use EXPLAIN, write your SELECT query as you normally would, but placethe keyword EXPLAIN in front of it.

• As a very simple example, take the following statement:SELECT 1;

• To see what EXPLAIN will do with it, issue the statement like this:mysql> EXPLAIN SELECT 1;


Explain in Action

• EXPLAIN can be applied to any SELECT query.

• consider these two simple single-table queries:SELECT * FROM Country WHERE Name ='France';

SELECT * FROM Country WHERE Code ='FRA';

• Both queries produce the same output, but they are not equally efficient.

• How do you know that?

• Because EXPLAIN tells you so. When you use EXPLAIN with each of the twoqueries, It provides the following information about how the MySQL optimizerviews them:mysql> EXPLAIN SELECT * FROM Country WHERE Name ='France'\G

mysql> EXPLAIN SELECT * FROM Country WHERE Code ='FRA'\G


Optimizer Hints

• STRAIGHT_JOIN Forces the optimizer to join the tables in the given order

• SQL_BIG_RESULTS Together with GROUP BY or DISTINCT tells the serverto use disk-based temp tables

• SQL_BUFFER_RESULTS Tells the server to use a temp table, thus releasinglocks early (for table-locks)

• USE INDEX Hints to the optimizer to use the given index

• FORCE INDEX Forces the optimizer to use the index (if possible)

• IGNORE INDEX Forces the optimizer not the use the index


Selecting Queries to Optimize

• The slow query log

• Logs all queries that take longer than long_query_time

• Can also log all queries that don’t use indexes with--log-queries-not-using-indexes

• To log slow administrative commands use--log-slow-admin-statements

• To analyze the contents of the slow log use mysqldumpslow

• The general query log can be use to analyze:◦ Reads vs. writes

◦ Simple queries vs. complex queries

◦ etc


Thank You


mysql dba training session 17 optimizing mysql queries

Education