MySQL性能调优最佳实践
Overview
● Profiling and Benchmarking Concepts
● Sources of Problems
● Indexing Guidelines
● Schema Guidelines
● Coding Guidelines
● Server Parameters
Copyright MySQL AB The World’s Most Popular Open Source Database 5
Benchmarking Concepts
● Provides a track record of changes
➢ Baseline is the starting point
➢ Testing done iteratively
➢ Deltas between tests show difference that the
change(s) made
● Stress/Load testing of application and/or database
● Harness or framework useful to automate many
benchmark tasks
Copyright MySQL AB The World’s Most Popular Open Source Database 6
Benchmarking Tips
● Always give yourself a target
● Record everything
✔ Schema dump
✔ my.cnf files
✔ hardware/os configuration files as needed
● Isolate the problem
✔ Shut down unnecessary programs
✔ Stop network traffic to machine
✔ Disable the query cache
✔ Change one thing at a time
Copyright MySQL AB The World’s Most Popular Open Source Database 7
Benchmarking Toolbox
● SysBench
➢ http://sysbench.sourceforge.net/
● mysqlslap (5.1+)
➢ http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html
● Apache Bench (ab)
● supersmack
➢ http://www.vegan.net/tony/supersmack/
● MyBench
● http://jeremy.zawodny.com/mysql/mybench/
Copyright MySQL AB The World’s Most Popular Open Source Database 8
Profiling Concepts
● Diagnose a running system
● Low hanging fruit
➢ Diminishing returns
➢ Be careful not to over-optimize
● Identify performance bottlenecks in
➢ Memory
➢ CPU
➢ I/O (Disk)
➢ Network and OS
Copyright MySQL AB The World’s Most Popular Open Source Database 9
Profiling Toolbox
● SHOW Commands
➢ SHOW PROCESSLIST | STATUS | INNODB STATUS
➢ http://dev.mysql.com/show
● EXPLAIN
➢ http://dev.mysql.com/explain
● MyTop
➢ http://jeremy.zawodny.com/mysql/mytop/
● Whole host of Linux power tools
➢ gprof / oprofile
➢ vmstat / ps / top / mpstat / procinfo
● apd for PHP developers
➢ http://pecl.php.net/package/apd
Copyright MySQL AB The World’s Most Popular Open Source Database 10
Slow Query Log
● Slow Query Log
● log_slow_queries=/var/lib/mysql/slowqueries.
log
● long_query_time=2
● Use mysqldumpslow
● (5.1+) Can log directly to a table, plus does not require
restart of server
● SET GLOBAL SLOW_QUERY_LOG = { ON | OFF }
● http://dev.mysql.com/doc/refman/5.1/en/logtables.
html
Copyright MySQL AB The World’s Most Popular Open Source Database 11
Profiling Tips
● Get very familiar with EXPLAIN
➢ Access types
➢ Learn the type, key, ref, rows, Extra columns
● Low hanging fruit (diminishing returns)
● Use MyTop to catch locking and long-running queries
in real-time
Copyright MySQL AB The World’s Most Popular Open Source Database 12
Sources of Problems
● Poor or nonexistent indexing
● Inefficient or bloated schema design
● Bad SQL Coding Practices
● Server variables not tuned properly
● Hardware and/or network bottlenecks
Copyright MySQL AB The World’s Most Popular Open Source Database 13
Indexing Guidelines
● Poor or missing index fastest way to kill a system
● Ensure good selectivity on field
● Look for covering index opportunities
● On multi-column indexes, pay attention to the order of
the fields in the index (example ahead)
● As database grows, examine distribution of values
within indexed field
● Remove redundant indexes for faster write
performance
Copyright MySQL AB The World’s Most Popular Open Source Database 14
Common Index Problem
CREATE TABLE Tags (
tag_id INT NOT NULL AUTO_INCREMENT
, tag_text VARCHAR(50) NOT NULL
, PRIMARY KEY (tag_id)
) ENGINE=MyISAM;
CREATE TABLE Products (
product_id INT NOT NULL AUTO_INCREMENT
, name VARCHAR(100) NOT NULL
// many more fields...
, PRIMARY KEY (product_id)
) ENGINE=MyISAM;
CREATE TABLE Products2Tags (
product_id INT NOT NULL
, tag_id INT NOT NULL
, PRIMARY KEY (product_id, tag_id)
) ENGINE=MyISAM;
// This top query uses the index
// on Products2Tags
SELECT p.name
, COUNT(*) as tags
FROM Products2Tags p2t
INNER JOIN Products p
ON p2t.product_id = p.product_id
GROUP BY p.name;
// This one does not because
// index order prohibits it
SELECT t.tag_text
, COUNT(*) as products
FROM Products2Tags p2t
INNER JOIN Tags t
ON p2t.tag_id = t.tag_id
GROUP BY t.tag_text;
Copyright MySQL AB The World’s Most Popular Open Source Database 15
Common Index Problem Solved
CREATE TABLE Tags (
tag_id INT NOT NULL AUTO_INCREMENT
, tag_text VARCHAR(50) NOT NULL
, PRIMARY KEY (tag_id)
) ENGINE=MyISAM;
CREATE TABLE Products (
product_id INT NOT NULL AUTO_INCREMENT
, name VARCHAR(100) NOT NULL
// many more fields...
, PRIMARY KEY (product_id)
) ENGINE=MyISAM;
CREATE TABLE Products2Tags (
product_id INT NOT NULL
, tag_id INT NOT NULL
, PRIMARY KEY (product_id, tag_id)
) ENGINE=MyISAM;
CREATE INDEX ix_tag
ON Products2Tags (tag_id);
// or... create a covering index:
CREATE INDEX ix_tag_prod
ON Products2Tags (tag_id, product_id);
// But, only if not InnoDB... why?
Copyright MySQL AB The World’s Most Popular Open Source Database 16
Schema Guidelines
● Inefficient schema another great way to kill
performance
● Use the smallest data types necessary
➢ Do you really need that BIGINT?
● Normalize first, denormalize only in extreme cases
Copyright MySQL AB The World’s Most Popular Open Source Database 17
Schema Tips
● Consider horizontally splitting many-columned tables
(example ahead)
● Consider vertically partitioning many-rowed tables
➢ Merge tables (MyISAM only)
➢ Homegrown
➢ Partitioning (5.1+)
● Fewer fields = Narrow rows = More records per block
● Use “counter” tables to mitigate query cache issues
(example ahead)
➢ Essential for InnoDB
Copyright MySQL AB The World’s Most Popular Open Source Database 18
Horizontal Partitioning Example
CREATE TABLE Users (
user_id INT NOT NULL AUTO_INCREMENT
, email VARCHAR(80) NOT NULL
, display_name VARCHAR(50) NOT NULL
, password CHAR(41) NOT NULL
, first_name VARCHAR(25) NOT NULL
, last_name VARCHAR(25) NOT NULL
, address VARCHAR(80) NOT NULL
, city VARCHAR(30) NOT NULL
, province CHAR(2) NOT NULL
, postcode CHAR(7) NOT NULL
, interests TEXT NULL
, bio TEXT NULL
, signature TEXT NULL
, skills TEXT NULL
, company TEXT NULL
, PRIMARY KEY (user_id)
, UNIQUE INDEX (email)
) ENGINE=InnoDB;
CREATE TABLE Users (
user_id INT NOT NULL AUTO_INCREMENT
, email VARCHAR(80) NOT NULL
, display_name VARCHAR(50) NOT NULL
, password CHAR(41) NOT NULL
, PRIMARY KEY (user_id)
, UNIQUE INDEX (email)
) ENGINE=InnoDB;
CREATE TABLE UserExtra (
user_id INT NOT NULL
, first_name VARCHAR(25) NOT NULL
, last_name VARCHAR(25) NOT NULL
, address VARCHAR(80) NOT NULL
, city VARCHAR(30) NOT NULL
, province CHAR(2) NOT NULL
, postcode CHAR(7) NOT NULL
, interests TEXT NULL
, bio TEXT NULL
, signature TEXT NULL
, skills TEXT NULL
, company TEXT NULL
, PRIMARY KEY (user_id)
) ENGINE=InnoDB;
Copyright MySQL AB The World’s Most Popular Open Source Database 19
Horizontal Partitioning Benefits
● Main table has narrow rows, so...
✔ More records fit into a single data page
✔ Fewer reads from memory/disk to get same number
of records
● Less frequently queried data doesn't take up memory
● More possibilities for indexing and different storage
engines
➢ Allows targeted multiple MyISAM key caches for hot
and cold data
Copyright MySQL AB The World’s Most Popular Open Source Database 20
Counter Table Example
CREATE TABLE Products (
product_id INT NOT NULL AUTO_INCREMENT
, name VARCHAR(80) NOT NULL
, unit_cost DECIMAL(7,2) NOT NULL
, description TEXT NULL
, image_path TEXT NULL
, num_views INT UNSIGNED NOT NULL
, num_in_stock INT UNSIGNED NOT NULL
, num_on_order INT UNSIGNED NOT NULL
, PRIMARY KEY (product_id)
, INDEX (name(20))
) ENGINE=InnoDB; // Or MyISAM
// Getting a simple COUNT of products
// easy on MyISAM, terrible on InnoDB
SELECT COUNT(*)
FROM Products;
CREATE TABLE Products (
product_id INT NOT NULL AUTO_INCREMENT
, name VARCHAR(80) NOT NULL
, unit_cost DECIMAL(7,2) NOT NULL
, description TEXT NULL
, image_path TEXT NULL
, PRIMARY KEY (product_id)
, INDEX (name(20))
) ENGINE=InnoDB; // Or MyISAM
CREATE TABLE ProductCounts (
product_id INT NOT NULL
, num_views INT UNSIGNED NOT NULL
, num_in_stock INT UNSIGNED NOT NULL
, num_on_order INT UNSIGNED NOT NULL
, PRIMARY KEY (product_id)
) ENGINE=InnoDB;
CREATE TABLE ProductCountSummary (
total_products INT UNSIGNED NOT NULL
) ENGINE=MEMORY;
Copyright MySQL AB The World’s Most Popular Open Source Database 21
Counter Table Benefits
● Critical for InnoDB because of complications of MVCC
● Allows query cache to cache specific data set which
will be invalidated only infrequently
● Allows you to target SQL_NO_CACHE for SELECTs against
counter tables, freeing query cache
● Allows MEMORY storage engine for summary
counters, since stats can be rebuilt
Copyright MySQL AB The World’s Most Popular Open Source Database 22
Schema Tips (cont'd)
● Ensure small clustering key (InnoDB)
● Don't use surrogate keys when a naturally occurring
primary key exists
● Example (of what not to do):
CREATE TABLE Products2Tags (
record_id INT UNSIGNED NOT NULL AUTO_INCREMENT
, product_id INT UNSIGNED NOT NULL
, tag_id INT UNSIGNED NOT NULL
, PRIMARY KEY (record_id)
, UNIQUE INDEX (product_id, tag_id)
) ENGINE=InnoDB;
Copyright MySQL AB The World’s Most Popular Open Source Database 23
Coding Guidelines
● Use “chunky” coding habits (KISS)
● Use stored procedures for a performance boost (5.0+)
● Isolate indexed fields on one side of equation
(example ahead)
● Use calculated fields if necessary (example ahead)
● Learn to use joins (!)
➢ Eliminate correlated subqueries using standard joins
(examples ahead)
● Don't try to outthink the optimizer
➢ Sergey, Timour and Igor are really, really smart...
Copyright MySQL AB The World’s Most Popular Open Source Database 24
Isolating Indexed Fields Example
// Bad idea
SELECT *
FROM Orders
WHERE
TO_DAYS(order_created) –
TO_DAYS(CURRENT_DATE()) >= 7;
// Better idea
SELECT *
FROM Orders
WHERE
order_created >= CURRENT_DATE() – INTERVAL 7 DAY;
// Best idea is to factor out the CURRENT_DATE
// nondeterministic
function in your application
// code and replace the function with a constant.
// Now, query cache can actually cache the query!
SELECT order_id, order_created, customer_id
FROM Orders
WHERE order_created >= '20060524'
– INTERVAL 7 DAY;
✔ Task: get the Order ID, date of order, and Customer ID
for all orders in the last 7 days
Copyright MySQL AB The World’s Most Popular Open Source Database 25
Calculated Fields Example
// Initial schema
CREATE TABLE Customers (
customer_id INT NOT NULL
, email VARCHAR(80) NOT NULL
// more fields
, PRIMARY KEY (customer_id)
, INDEX (email(40))
) ENGINE=InnoDB;
// Bad idea, can't use index
// on email field
SELECT *
FROM Customers
WHERE email LIKE '%.com';
// So, we enable fast searching on a reversed field
// value by inserting a calculated field
ALTER TABLE Customers
ADD COLUMN rv_email VARCHAR(80) NOT NULL;
// Now, we update the existing table values
UPDATE Customers SET rv_email = REVERSE(email);
// Then, we make a trigger to keep our data in sync
DELIMITER ;;
CREATE TRIGGER trg_bi_cust
BEFORE INSERT ON Customers
FOR EACH ROW BEGIN
SET NEW.rv_email = REVERSE(NEW.email);
END ;;
// same trigger for BEFORE UPDATE...
// Then SELECT on the new field...
WHERE rv_email LIKE CONCAT(REVERSE('.com'), '%');
✔ Task: search for top-level domain in email addresses
Copyright MySQL AB The World’s Most Popular Open Source Database 26
Correlated Subquery Conversion Example
// Bad practice
SELECT p.name
, (SELECT MAX(price)
FROM OrderItems
WHERE product_id = p.product_id)
AS max_sold_price
FROM Products p;
// Good practice
SELECT p.name
, MAX(oi.price) AS max_sold_price
FROM Products p
INNER JOIN OrderItems oi
ON p.product_id = oi.product_id
GROUP BY p.name;
✔ Task: convert a correlated subquery in the SELECT
clause to a standard join
Copyright MySQL AB The World’s Most Popular Open Source Database 27
Derived Table Example
// Bad performance
SELECT
c.company
, o.*
FROM Customers c
INNER JOIN Orders o
ON c.customer_id = o.customer_id
WHERE order_date = (
SELECT MAX(order_date)
FROM Orders
WHERE customer = o.customer
) GROUP BY c.company;
// Good performance
SELECT
c.company
, o.*
FROM Customers c
INNER JOIN (
SELECT
customer_id
, MAX(order_date) as max_order
FROM Orders
GROUP BY customer_id
) AS m
ON c.customer_id = m.customer_id
INNER JOIN Orders o
ON c.customer_id = o.customer_id
AND o.order_date = m.max_order
GROUP BY c.company;
✔ Task: convert a correlated subquery in the WHERE
clause to a standard join on a derived table
Copyright MySQL AB The World’s Most Popular Open Source Database 28
Server Parameters
● Be aware of what is global vs per thread
● Make small changes, then test
● Often provide a quick solution, but temporary
● Query Cache is not a panacea
● key_buffer_size != innodb_buffer_size
➢ Also, remember mysql system database is MyISAM
● Memory is cheapest, fastest, easiest way to increase
performance
Copyright MySQL AB The World’s Most Popular Open Source Database 29
Additional Resources
✔ http://www.mysqlperformanceblog.com/
➢ Peter Zaitsev's blog – Excellent material
✔ Optimizing Linux Performance
➢ Philip Ezolt (HP Press)
✔ http://dev.mysql.com/tech-resources/articles/promysql-
ch6.pdf
➢ Pro MySQL (Apress) chapter on profiling (EXPLAIN)
✔ Advanced PHP Programming
➢ George Schlossnagle (Developer's Library)