MySQL Performance Tuning: The stages of Tuning

This article is a fraction of a Number of Articles on MySQL, to access them click here.

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 and downsizing is the better option. This may result in multiple levels of savings to include maintenance.

• 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 of unhappiness and loses business.

• 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 an advantage. Tuning the access to the data helps business analysts, who are utilizing business intelligence initiatives based on corporate data, make faster and more precise decisions.

What is Tuned?

Careful design of systems and applications is essential to the optimal performance of any database. In most cases the greatest gain in performance can be achieved through tuning the application. The most opportune time to consider performance issues is when the application is in the very early stages of the SDLC.

• · Application Design
• · Application Development
• · Database Structures
• · Hardware

 

Stages of Tuning

• Application design
• Application development
• Database configuration
• Application maintenance and growth
• Troubleshooting

 

 

 

Application Development

(Optimizing Queries)

 

Indexes

• In MySQL there are several types of indexes:

– Tree Indexes

• B-Trees

– FULLTEXT indexes (based on words instead of whole

columns)

• B+Trees (InnoDB)
• T-Trees (NDB)
• Red-black binary trees (MEMORY)
• R-Trees (MyISAM, spatial indexes)

– Hash indexes (MEMORY and NDB)

• The use of indexes to find rows speedes up most queries
• Writes become slower with each added index

Query Execution Plan

(EXPLAIN)

• With EXPLAIN the query is sent all the way to the optimizer,

but not to the storage engine

• Instead EXPLAIN returns the query execution plan
• 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

EXPLAIN Types

system The table has only one row

const At the most one matching row, treated as a constant

eq_ref One row per row from previous tables

ref Several rows with matching index value

ref_or_null Like ref, plus NULL values

index_merge Several index searches are merged

unique_subquery Same as ref for some subqueries

index_subquery As above for non-unique indexes

range A range index scan

index The whole index is scanned

ALL A full table scan

Using index The result is created straight from the index

Using where Not all rows are used in the result

Distinct Only a single row is read per row combination

Not exists A LEFT JOIN missing rows optimization is used

Using filesort An extra row sorting step is done

Using temporary A temporary table is used

Range checked for each record

The read type is optimized individually for each combination of rows from the previous tables

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 server to

use disk-based temp tables

SQL_BUFFER_RESULTS Tells the server to use a temp table, thus releasing locks

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

Database Designing

(Optimizing Schemas)

Normalization

• Normalization is a key factor in optimizing your database structure

– Good normalization prevents redundant data from being stored in the same tables

– By moving redundant data to their own table, this reduces storage requirements and overhead when processing queries

– Transactional databases should be in the 3rd normal form

• For data warehousing and reporting system a star-schema might be a better solution

Table Optimizations

• Use columns that are as short as possible;

– INT instead of BIGINT

– VARCHAR(10) instead of VARCHAR(255)

– etc.

• Pay special attention to columns that are used in joins
• Define columns as NOT NULL if possible
• For hints on saving space, use PROCEDURE ANALYSE()
• For data warehousing or reporting systems consider using summary tables for speed

Index Optimizations

• An index on the whole column is not always necessary

– Instead index just a prefix of a column

– Prefix indexes take less space and the operations are faster

• Composite indexes can be used for searches on the first column(s) in the index
• Minimize the size of PRIMARY KEYs that are used as references in other tables

– Using an auto_increment column can be more optimal

• A FULLTEXT index is useful for

– word searches in text

– searches on several columns

MyISAM-Specific Optimizations

• Consider which row format to use, dynamic, static or compressed

– Speed vs. space

• Consider splitting large tables into static and dynamic parts
• Important to perform table maintenance operations regularly or after big DELETE/UPDATE operations

– Especially on tables with dynamic row format

• Change the row-pointer size (default 6b) for large (>256Tb) tables or smaller (< 4Gb) tables

InnoDB-Specific Optimizations

• InnoDB uses clustered indexes

– The length of the PRIMARY KEY is extremely important

• The rows are always dynamic

– Using VARCHAR instead of CHAR is almost always better

• Maintenance operations needed after

– Many UPDATE/DELETE operations

• The pages can become underfilled

MEMORY-Specific

Optimizations

• Use BTREE (Red-black binary trees) indexes

– When key duplication is high

– When you need range searches

• Set a size limit for your memory tables

– With —max_heap_table_size

• Remove unused memory

– TRUNCATE TABLE to completely remove the contents of the

Table

– A null ALTER TABLE to free up deleted rows

Optimizing the Server

Performance Monitoring

• Server performance can be tracked using native OS tools

– vmstat, iostat, mpstat on Unix

– performance counters on Windows

• The mysqld server tracks crucial performance counters

– SHOW STATUS gives you a snapshot

– Can use Cricket, SNMP, custom scripts to graph over time

– MySQL Administrator

• Default graphs
• Allows you to create your own graphs
• Queries can be tracked using log files

– Can collect every query submitted to the server

– Slow queries can be logged easily

Monitoring Threads in MySQL

• To get a snapshot of all threads in MySQL

– SHOW FULL PROCESSLIST

– The state column shows what’s going on for each query

• Performance problems can often be detected by

– Monitoring the processlist

– Verifying status variables

• Imminent problems can be eliminated by

– Terminating runaway or unnecessary threads with KILL

Tuning MySQL Parameters

• Some MySQL options can be changed online
• The dynamic options are either

– SESSION specific

• Changing the value will only affect the current connection

– GLOBAL

• Changing the value will affect the whole server

– Both

• When changing the value SESSION/GLOBAL should be specified
• Online changes are not persistant over a server restart

– The configuration files have to be changed as well

• The current values of all options can be found with

SHOW SESSION/GLOBAL VARIABLES

Status Variables

• MySQL collects lots of status indicators

– These can be monitored with SHOW STATUS

• The variables provide a way of monitoring the server activity
• They also act as guides when optimizing the server
• The variables can also be viewed with

– mysqladmin extended-status

– MySQL Administrator

• Provides graphical interface for monitoring the variables
• Can be very efficient for tracking the health of the server

Some Global Options

• table_cache (default 64)

– Cache for storing open table handlers

– Increase this if Opened_tables is high

• thread_cache (default 0)

– Number of threads to keep for reuse

– Increase if threads_created is high

– Not useful if the client uses connection pooling

• max_connections (default 100)

– The maximum allowed number of simultaneous connections

– Very important for tuning thread specific memory areas

– Each connection uses at least thread_stack of memory

MyISAM Global Options

• key_buffer_size (default 8Mb)

– Cache for storing indices

– Increase this to get better index handling

– Miss ratio (key_reads/key_read_requests) should be

very low, at least < 0.03 (often < 0.01 is desirable)

• Row caching is handled by the OS

MyISAM Thread-Specific

Options

• myisam_sort_buffer_size (default 8Mb)

– Used when sorting indexes during REPAIR/ALTER TABLE

• myisam_repair_threads (default 1)

– Used for bulk import and repairing

– Allows for repairing indexes in multiple threads

• myisam_max_sort_file_size

– The max size of the file used while re-creating indexes

InnoDB-Specific Optimization

1/2

• innodb_buffer_pool_size (default 8Mb)

– The memory buffer InnoDB uses to cache both data and

indexes

– The bigger you set this the less disk i/o is needed

– Can be set very high (up to 80% on a dedicated system)

• innodb_flush_log_at_trx_commit (default 1)

– 0 writes and sync’s once per second (not ACID)

– 1 forces sync to disk after every commit

– 2 write to disk every commit but only sync’s about once per

Second

InnoDB-Specific Optimization

2/2

• innodb_log_buffer_size (default 1Mb)

– Larger values allows for larger transactions to be logged in

memory

– Sensible values range from 1M to 8M

• innodb_log_file_size (default 5Mb)

– Size of each InnoDB redo log file

– Can be set up to buffer_pool_size