mysql dba training session 17 optimizing mysql queries
Post on 16-Apr-2017
66 Views
Preview:
TRANSCRIPT
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
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
WWW.RNSANGWAN.COM 11
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;
WWW.RNSANGWAN.COM 12
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/
WWW.RNSANGWAN.COM 13
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
WWW.RNSANGWAN.COM 14
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;
WWW.RNSANGWAN.COM 15
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
WWW.RNSANGWAN.COM 16
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
WWW.RNSANGWAN.COM 17
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
WWW.RNSANGWAN.COM 18
top related