10 performance tips we tend to forget

10 PERFORMANCE TIPS WE TEND TO

FORGETEli Aschkenasy@EliAschkenasy

#JDNL16April 16, 2016

Eli Aschkenasy - @EliAschkenasy - #JDNL16

3 Categories

1. Indexing2. Query Optimization3. Resource Handling

Indexes have two purposes:

1. Speed up access to data2. (help) Enforce constraints

Query Optimization

Happens in conjunction with index strategy and only with real data.

Resource Handling

Might be the most overlooked yet easiest-to-fix part, but requires real data as well.

Gather Data

DB Optimization – Logging Queries

mysql> SET GLOBAL slow_query_log = ON;

mysql> SET GLOBAL long_query_time = 0;

mysql> SET GLOBAL log_queries_not_using_indexes = ON;

DB Optimization – Logging Queries

Change to ON

DB Optimization – Analyzing Queries

Percona Toolkit for MySQL

DB Optimization – Analyzing Queries

http://nk.gl/slow_queries/analyze

For those of us who don’t want to commit.

WITH THIS DATAWE CAN CHECK THE IMPLEMENTATION

DB Optimization

Here is my schema, what indexes do I need?

DB Optimization

Index choice depends on the queries you run, not the data you have!

relational schema design is based on

indexdesign is based on

QUERIES

Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)

idx (l_name, f_name, phone)

SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’

Simple/Compound Searches

SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’AND f_name = ‘Hans’

SELECT * FROM PhoneBookWHERE f_name = ‘Hans’

doesn‘t work

SELECT * FROM PhoneBookWHERE l_name LIKE ‘Ku%’

Range Lookup

SELECT * FROM PhoneBookWHERE l_name = ‘Ku%’AND f_name = ‘Hans’

doesn‘t work

Range Lookup

SELECT * FROM PhoneBookWHERE l_name = ‘Ku%’AND f_name = ‘Hans’

doesn‘t work

Range Lookup

Tip #1:Remember: Any Range Comparison breaks index benefit for subsequent columns

SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’ORDER BY f_name

Sorting

SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’ORDER BY phone

doesn‘t work

Sorting

SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’ORDER BY phone

doesn‘t work

Tip #2:Remember: Index can only benefit the sorting of the column immediately following last column used for search

SELECT phone FROM PhoneBookWHERE l_name = ‘Kuijpers’AND f_name = ‘Hans’

Index – Only Search

Because phone is included in index we can select it without overhead even though we didn’t include it in the search

SELECT phone FROM PhoneBookWHERE l_name = ‘Kuijpers’AND f_name = ‘Hans’

Index – Only Search

Tip #3:Putting columns in index even if they are not part of the query might speed things up – COVERING INDEX

Because phone is included in index we can select it without overhead even though we didn’t include it in the search

SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’OR f_name = ‘Hans’

doesn‘t work

Disjoint Selector

additional index (f_name, phone) would be required

Index PrimerInsurance Company:PolicyNumberAgentType

idx (policy, name, type)

Index Size

idx (policy, name, type)

SELECT * FROM InsuranceCompanyWHERE type = ‘Vervoerdersaansprakelijkheidsverzekering’

Index Size

Type Options: 1. Vervoerdersaansprakelijkheidsverzekering2. Bestuurdersaansprakelijkheidsverzekering3. Overeenstemmingsbeoordelingsprocedures

idx (type(1))

Index Size

idx (type(1))

Index Size

Trick #1:Use only part of the column as length of the index, but remember that it will break the ‘covering’ property.

idx (type(1))

Index Size

Trick #1a:Specificity Check

SELECTCOUNT(DISTINCT(type)) AS total,COUNT(DISTINCT(LEFT(type,10))) AS t10,COUNT(DISTINCT(LEFT(type,20))) AS t20FROM Insurance Company;

idx (type(1))

Index Estimation

Trick #1b:Cardinality

ANALYZE TABLES;

MySQL 5.5

idx (l_name) – traditionalidx (l_name, f_name, phone) – covering

Range Lookup

SELECT phone FROM PhoneBookWHERE l_name = ‘Kuijpers’AND f_name LIKE ‘%Hans%’

MySQL 5.6

idx (l_name, f_name)Range access l_name, Filter clause on f_name (only read if full row match)

Index StrategyChoose Index order which benefits more queries• SELECT * FROM t WHERE a=1 AND b=2• SELECT * FROM t WHERE a>1 AND b=2

Index StrategyChoose Index order which benefits more queries• SELECT * FROM t WHERE a=1 AND b=2• SELECT * FROM t WHERE a>1 AND b=2KEY (b, a) is better than KEY (a, b)

Index Strategy (Trick #2)

Choose Index order which benefits more queries• SELECT * FROM t WHERE a=1 AND b=2• SELECT * FROM t WHERE a>1 AND b=2KEY (b, a) is better than KEY (a, b)

Index order (without restrictions) should be most selective to least selective

• SELECT * FROM t WHERE a=1 AND b=2• SELECT * FROM t WHERE a>1 AND b=2KEY (a, b)

• SELECT * FROM t WHERE a BETWEEN 2 AND 4 AND b=2

• SELECT * FROM t WHERE a BETWEEN 2 AND 4 AND b=2KEY (a, b)

Query Optimization (Trick #1)

• SELECT * FROM t WHERE a BETWEEN 2 AND 4 AND b=2KEY (a, b)

• SELECT * FROM t WHERE a IN(2,3,4) AND b=2KEY (a, b)

Enumerating Ranges will ensure the usage of both key parts

CREATE TABLE profile( id INT, gender CHAR(1), age TINYINT(3), city VARCHAR(100), PRIMARY KEY(id));

SELECT id FROM profileWHERE city = “Amsterdam”AND age BETWEEN 35 AND 40

KEY (city, age, id)

SELECT id FROM profileWHERE city = “Amsterdam”AND gender = “F”AND age BETWEEN 35 AND 40

KEY (city, gender, age, id)

KEY (city, gender, age, id)Trick #3:Use as little indexes as possible. A little query rewrite could save massive overhead on the index-side.

SELECT id FROM profileWHERE city = “Amsterdam”AND gender IN(‘M’,’F’,’O’)AND age IN(35,36,37,38,39,40)

SELECT id FROM profileWHERE city = “Amsterdam”AND gender = “F”AND age IN(35,36,37,38,39,40)

Resource Handling (Trick #1)

SELECT id, name, picture, … , age FROM profileWHERE city = “Amsterdam”AND gender IN(‘M’,’F’,’O’)AND age IN(35,36,37,38,39,40)ORDER BY ratingLIMIT 100, 10

(page 11)

Retrieves 110 rows and discards 100

SELECT id, name, picture, … , age FROM profileWHERE city = “Amsterdam”AND gender IN(‘M’,’F’,’O’)AND age IN(35,36,37,38,39,40)ORDER BY ratingLIMIT 100, 10

(page 11)

Retrieves 110 rows and discards 100

SELECT id, name, picture, … , ageFROM profile INNER JOIN( SELECT id FROM profile

WHERE city = “Amsterdam”AND gender IN(‘M’,’F’,’O’)AND age IN(35,36,37,38,39,40)ORDER BY ratingLIMIT 100, 10)

AS xUSING (id);

(page 11)

Retrieves 110 ids and discards 100Retrieves only 10 rows of data

SELECT id FROM urlWHERE url="http://www.joomladagen.nl";

CREATE TABLE pseudohash ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, url VARCHAR(255) NOT NULL, url_crc INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY(id));

CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN SET NEW.url_crc=crc32(NEW.url);

CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN SET NEW.url_crc=crc32(NEW.url);

SELECT id FROM url WHERE url_crc=CRC32("http://www.joomladagen.nl") AND url="http://www.joomladagen.nl";

SELECT id FROM urlWHERE url="http://www.joomladagen.nl";

CREATE TABLE pseudohash ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, url VARCHAR(255) NOT NULL, url_crc INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY(id));

CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN SET NEW.url_crc=crc32(NEW.url);

CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN SET NEW.url_crc=crc32(NEW.url);

SELECT id FROM url WHERE url_crc=CRC32("http://www.joomladagen.nl") AND url="http://www.joomladagen.nl";

Fixed length index Fixed length data

SELECT * FROM comment AS c LEFT JOIN article AS a ON a.id_comment = c.id LEFT JOIN users AS u ON a.created_by = u.idWHERE a.title=‘joomladagen‘AND u.name = ‘Hans Kuijpers’;

SELECT * FROM comment AS c LEFT JOIN article AS a ON a.id_comment = c.id LEFT JOIN users AS u ON a.created_by = u.idWHERE a.title=‘joomladagen‘AND u.name = ‘Jisse Reitsma’;

CREATE TABLE comment ( id INT(11) NOT NULL…, tag VARCHAR(255)…, importance TINYINT(3)…, image BLOB…, comment TEXT…, created DATETIME…, created_by INT(11)…, modified…, …., …., publish_up DATETIME, PRIMARY KEY(`id`)) ENGINE=InnoDB;

SELECT c.comment FROM comment AS c LEFT JOIN article AS a ON a.id_comment = c.id LEFT JOIN users AS u ON a.created_by = u.idWHERE a.title=‘joomladagen‘AND u.name = ‘Jisse Reitsma’;

Resource Handling (Trick #2a)

SELECT SUBSTRING(c.comment, 0, 25) AS short_comment FROM comment AS c LEFT JOIN article AS a ON a.id_comment = c.id LEFT JOIN users AS u ON a.created_by = u.idWHERE a.title=‘joomladagen‘AND u.name = ‘Jisse Reitsma’;

SELECT id FROM usersWHERE name = ‘Jisse Reitsma’; 57125

SELECT id FROM articleWHERE title = ‘Joomladagen’AND created_by = 57125; (123,223,934,1145)

SELECT SUBSTRING(comment, 0, 25) AS short_comment FROM commentWHERE id IN(123,223,934,1145);

Trick #4:Query cache validation of 3 tables.Complex queries reduce likelihood of repetition.

THANK YOU

MySQL 5.7 – JSON CREATE TABLE profile( id INT(11)…, profile TEXT…, PRIMARY KEY (`id`)) ENGINE = InnoDB;

INSERT INTO profile (id, profile)VALUES (1, ‘{“handle”: “@EliAschkenasy”}’);

SELECT id FROM profileWHERE field_name REGEXP '"handle":"([^"]*)@EliAschkenasy([^"]*)"';

Full Table Scan (not cacheable just like full text search)

MySQL 5.7 – JSON CREATE TABLE profile( id INT(11)…, profile JSON, PRIMARY KEY (`id`)) ENGINE = InnoDB;

INSERT INTO profile (id, profile)VALUES (1, ‘{“id”: 1, “handle”: “@EliAschkenasy”}’);

SELECT JSON_EXTRACT(profile, ‘$.handle’) FROM profileWHERE id = 1; - @EliAschkenasy

MySQL 5.7 – JSON CREATE TABLE profile( id INT(11)…, profile JSON, PRIMARY KEY (`id`)) ENGINE = InnoDB;

INSERT INTO profile (id, profile)VALUES (1, ‘{“id”: 1, “handle”: “@EliAschkenasy”}’);

SELECT JSON_EXTRACT(profile, ‘$.handle’) FROM profileWHERE id = 1; - @EliAschkenasy

JSON data type consumes about 5% more memory JSON columns can’t have a default value JSON columns can’t be indexed BETWEEN, IN(), GREATEST(), LEAST() Are NOT supported yet!

10 performance tips we tend to forget

Software

forget these vehiclevehicle titles a guide for titling...

exam writing strategies: tips for december · pdf fileexam...

epropertyextra reference -...

“to forget how to dig the earth and tend the soil is to

sunshinedeleonblog.files.wordpress.comme the reality of what...

forget the glass ceiling: 3 tips to build your business...

forget the weights, forget the cardio

caroom presentation may12-2016 -...

15 what do we know when we forget? · what do we know when...

never forget srebrenica genocide – july 11, …...

apd field services in the loop...don’t forget - date stamp...

top tips on building an effective linkedin profile ·...

posture panel needed perfect -...

weight loss tips - united states naval academy+ forget...

7 ways to beat the heat!! - safetywithfc.weebly.com · 7...

the “forget-me-not - kingston hospital · forget-me-not...

edl · web view“people tend to forget that the word...

the long and short of tips - vanguard · 2 receive the same...

managing expectations - public...managing expectations 2...

the power of naming, or the construction of ethnic and...