Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
DOs and DON'Ts of MySQL Performance
Search
TheBerg
July 10, 2014
Technology
5
140
DOs and DON'Ts of MySQL Performance
TheBerg
July 10, 2014
Tweet
Share
Other Decks in Technology
See All in Technology
Janus
bkuhlmann
1
490
非同期推論システムによるコスト削減と信頼性向上
koki_nishihara
0
260
APIファーストなプロダクトマネジメントの実践 〜SaaSus Platformでの例〜 / "Practicing API-First Product Management - An Example with SaaSus Platform
oztick139
0
110
推しは推せるときに推せ! プロダクトにフィードバックしていこう
nakasho
0
320
地理空間データ可視化・解析・活用ソリューション Pacific Spatial Solutions (PSS)
pacificspatialsolutions
0
300
アクセシビリティを考慮したUI/CSSフレームワーク・ライブラリ選定
yajihum
2
1k
Gitlab本から学んだこと - そーだいなるプレイバック / gitlab-book
soudai
4
440
自己改善からチームを動かす! 「セルフエンジニアリングマネージャー」のすゝめ
shoota
6
800
【NW X Security JAWS#3】L3-4:AWS環境のIPv6移行に向けて知っておきたいこと
shotashiratori
0
380
Delivering Millions of Messages within seconds @ Duolingo
pelelgrino
0
350
複雑な構成要素を持つUIとの向き合い方 〜新・支出グラフでの実例〜 / B43 TECH TALK
nakamuuu
0
140
Azure Container Apps + Bicep 〜 こんな感じで運用しています
kaz29
2
480
Featured
See All Featured
How to Ace a Technical Interview
jacobian
272
22k
Become a Pro
speakerdeck
PRO
11
4.5k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
mongodb
34
8.9k
GraphQLとの向き合い方2022年版
quramy
32
12k
How to Create Impact in a Changing Tech Landscape [PerfNow 2023]
tammyeverts
14
1.6k
Rebuilding a faster, lazier Slack
samanthasiow
73
8.2k
What the flash - Photography Introduction
edds
64
11k
Designing on Purpose - Digital PM Summit 2013
jponch
110
6.5k
Building a Scalable Design System with Sketch
lauravandoore
456
32k
Music & Morning Musume
bryan
41
5.6k
The World Runs on Bad Software
bkeepers
PRO
61
6.7k
How to name files
jennybc
65
93k
Transcript
DOs DON’Ts of MySQL Performance and
BERG developer JEFF planning center
data centers of data years 3 191GB 8 rows >750M
DO USE MySQL
its solid
everyone uses it
DON’T BE BLIND
monitor it
scout
newrelic
newrelic
DO USE INDEXES
when? • On almost every column ending in _id •
On any column in WHERE, GROUP or ORDER • Be careful about adding too many
left to right Index: first_name, last_name Query: SELECT *
FROM people WHERE first_name = ‘Jeff’ AND last_name = ‘Berg’
left to right Index: first_name, last_name Query: SELECT *
FROM people WHERE last_name = ‘Berg’ AND first_name = ‘Jeff’
left to right Index: first_name, last_name Query: SELECT *
FROM people WHERE first_name = ‘Jeff’
left to right Index: first_name, last_name Query: SELECT *
FROM people WHERE last_name = ‘Jeff’
DON’T USE LIKE WRONG
don’t use like wrong Index: first_name, last_name Query: SELECT
* FROM people WHERE phone_number LIKE ‘760%’
don’t use like wrong Index: first_name, last_name Query: SELECT
* FROM people WHERE phone_number LIKE ‘%1234’
DO USE EXPLAIN & PROFILE
do use explain EXPLAIN SELECT * FROM ccli_songs WHERE title
LIKE ‘query%’
do use explain EXPLAIN SELECT * FROM ccli_songs WHERE title
LIKE ‘%query%’
do use profiling SET PROFILING = 1; ! SELECT *
FROM ccli_songs WHERE title LIKE ‘Title%'; ! SHOW PROFILE;
DON’T PAGINATE
don’t paginate SELECT * from people LIMIT 100 OFFSET
100000; Query Time: 699ms
don’t paginate SELECT * from people WHERE id > 4560177
LIMIT 100; Query Time: 60ms
DO CHECK YOUR CONFIGURATION
check your config innodb_buffer_pool_size, innodb_log_file_size, max_connections, innodb_file_per_table, innodb_flush_log_at_trx_commit, innodb_flush_method, innodb_log_buffer_size,
query_cache_size, log_bin, skip_name_resolve http://www.mysqlperformanceblog.com/2014/01/28/10-mysql-settings-to-tune-after-installation/
DON’T MESS UP PRIMARY KEYS
primary keys • data is organized via primary key •
integer auto increment primary key puts rows on disk in order created • UUID primary key puts rows in random order • combined PK, puts items in order grouped
primary keys Primary Key: id
primary keys Primary Key: organization_id, id
DO HAVE THE RIGHT HARDWARE
the right hardware • SSDs are worth their weight in
gold • Try to have enough RAM to hold your whole database in memory
DON’T USE MySQL
don’t use mysql • use percona server or maria db
• many Google & Facebook patches applied • many percona ideas • ran by many of original mysql team after oracle acquisition
don’t use mysql • MySQL acquired by Sun • Sun
acquired by Oracle • Oracle makes a lot of money charging for databases
DO HIRE SOMEONE
hire someone • Percona.com MySQL Consulting & Services • PSCE.com
MySQL Consulting & Services
questions? jeff@pco.bz @TheBerg about.me/jeffberg ! slides at: https://speakerdeck.com/theberg