pt-duplicate-key-checker

pt-duplicate-key-checker is a command-line tool to find duplicate indexes and foreign keys on MySQL tables.

ref:
https://www.percona.com/doc/percona-toolkit/2.2/pt-duplicate-key-checker.html

# check all tables
$ pt-duplicate-key-checker --user=abc --password=123 --databases=streetvoice

# check specific tables
$ pt-duplicate-key-checker --user=abc --password=123 --databases=streetvoice --tables=svapp_like
MySQL schema design for large tables

MySQL schema design for large tables

把大字段拆出來

主要是指那些 text 類型的欄位
例如 Article 的 content 內文欄位
可以拆出來變成 ArticleContent 表
因為在大部分的 query 裡你其實都不會需要 content 欄位
通常只有顯示單一 article 的時候才需要

例如 Profile 表
phone_number, address, occupation 之類的很少被用來 query 的欄位
其實也可以拆出來到另外一個表
ExtendedProfile

大表的水平拆分:置頂訊息

假設你有一個存放論壇文章的表 Post
文章分成一般用戶的「普通文章」和管理者發布的「置頂文章」
你可能會在 Post 表新增一個 boolean 欄位
區分是不是置頂文章

但是置頂文章和普通文章的數量可能差很多
而且置頂文章的 query 次數也會多很多(每一頁都要顯示)
所以另一個可行的辦法是幫置頂文章獨立開一個 table

這種方式就是所謂的「水平拆分」

不是所有的 COUNT 都需要實時

很多 count 可以透過 crontab 定時跑
然後存進某個欄位裡
前端顯示時直接去讀那個欄位的值即可

把同一個 counter 的資料存成多筆 record

這樣可以減少同時有多個 request 修改了同一個 row 所引起的 lock 的問題
Deadlock found when trying to get lock; try restarting transaction

在你的 counter table 新增一個 slot 欄位(槽)
每次寫入的時候隨機選擇(透過 slot 欄位)某一個 record 寫入
要讀取的時候就把這幾筆 SUM 起來

song_id count slot
100 2 1
100 5 2
100 3 3
100 8 4
101 1 1
101 19 2
101 3 3
101 7 4

UPDATE song_counter SET count = count + 1 WHERE song_id = 100 AND slot = 1;
UPDATE song_counter SET count = count + 1 WHERE song_id = 100 AND slot = 2;
UPDATE song_counter SET count = count + 1 WHERE song_id = 100 AND slot = 3;

缺點就是 record 會變超多
可以用 crontab 定期整理數據
把 counter 資料寫到 slot 1
然後刪掉其他的 slot

ref:
High Performance MySQL, Chapter 4.4

MySQL index 索引

index 和 key 在 MySQL 裡面都是指索引

Which columns should be indexed?

唯一性太差的欄位不適合 "單獨" 建立 index
應該盡量選那些能夠「最大地」區分資料的欄位(id 就是一個最好的例子)
也就是說用這個欄位當 WHERE 條件去跑
出來的資料筆數越少則「唯一性」越高

ref:
http://talentluke.iteye.com/blog/1843868

ADD INDEX

建議使用 pt-online-schema-change

# 建立索引
mysql> ALTER TABLE writing_article ADD INDEX writing_article_last_modified (last_modified);
# or
mysql> CREATE INDEX writing_article_last_modified ON writing_article (last_modified);

time pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--execute \
--alter "ADD INDEX relationships_relationship_0e7efed3 (from_user_id), ADD INDEX relationships_relationship_bc172800 (to_user_id)" \
D=streetvoice,t=relationships_relationship

# 24 cores, 8G RAM
real 21m24.237s
user 0m2.532s
sys 0m0.392s

# 列出索引,Key_name 就是索引的名字
mysql> SHOW INDEX FROM relationships_relationship;
mysql> SHOW KEYS FROM relationships_relationship;
mysql> SHOW CREATE TABLE relationships_relationship;

# 刪除索引
mysql> DROP INDEX relationships_relationship_0e7efed3 ON relationships_relationship;
mysql> DROP INDEX relationships_relationship_bc172800 ON relationships_relationship;
mysql> DROP INDEX writing_article_953cd7bc ON writing_article;
mysql> DROP INDEX writing_article_last_modified ON writing_article;

ref:
https://dev.mysql.com/doc/refman/5.5/en/show-index.html
http://blog.xuite.net/hsiung03/blog/64217097
http://stackoverflow.com/questions/5213339/how-to-see-indexes-for-a-database-or-table

ForeignKey creates an index automatically in MySQL

所以你其實不需要特地幫 models.ForeignKey(User) 欄位加上 db_index=True

$ ./manage.py sqlindexes relationships

ref:
http://stackoverflow.com/questions/304317/does-mysql-index-foreign-key-columns-automatically

EXPLAIN

注意結尾的 \Q 是指 output 的格式使用條列式,而不是表格

mysql> EXPLAIN SELECT COUNT(*) FROM `relationships_relationship` WHERE `relationships_relationship`.`to_user_id` = 910188;
mysql> EXPLAIN SELECT COUNT(*) FROM `relationships_relationship` WHERE `relationships_relationship`.`to_user_id` = 841766;
mysql> EXPLAIN SELECT COUNT(*) FROM `relationships_relationship` WHERE `relationships_relationship`.`from_user_id` = 841766;

mysql> EXPLAIN SELECT COUNT(*) FROM `relationships_relationship` WHERE `relationships_relationship`.`to_user_id` = 841766\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: relationships_relationship
         type: index
possible_keys: relationships_relationship_to_user_id
          key: relationships_relationship_to_user_id
      key_len: 4
          ref: const
         rows: 20
        Extra: Using index
1 row in set (0.00 sec)

The possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table.
The key column indicates the key (index) that MySQL actually decided to use.
The rows column indicates the number of rows MySQL believes it must examine to execute the query.

type: 優 > 劣
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

rows 的值越小越好,表示 MySQL 在越小的資料集中查找資料

Extra: Using index 表示使用了索引,但是仍舊有可能是使用了錯誤的索引

ref:
https://blog.hinablue.me/entry/mysql-note-mysql-query-explain-optimization/ << 值得一讀
http://baike.baidu.com/view/9416916.htm

Query Profiler

mysql> SET profiling=1;
mysql> DO YOUR QUERY;
mysql> SHOW profiles;
mysql> SHOW profile cpu, block io for query 6;

The Slow Query Log

[mysqld]
...
slow_query_log
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
log_slow_verbosity = query_plan
log_queries_not_using_indexes
...
$ sudo su root
$ pt-query-digest /var/log/mysql/mariadb-slow.log

ref:
http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

Terms of MySQL Scalability

scale up 垂直擴展
scale out 水平擴展

replication

同樣的資料被複製好幾份到不同的機器上
這個動作就叫做 replication
replication 只能解決讀的問題
寫的瓶頸還是在 master

有 master-slave 這種架構
只有 master 才能寫,而 slave 只能讀
所謂的「讀寫分離」

也有像 MariaDB Galera Cluster 這種 multi-master 的架構
就是每一台都能讀,也能寫

partitioning

把原本在同一台機器上的數據拆分到多台機器
以減少單一機器的負載

分庫
把整個表獨立出來放到另一台機器
自己一個資料庫

分表
分為「垂直拆分」和「水平拆分」
拆出來的表可以在同一台機器上
也可以是在另一台機器上(就會是另一個資料庫,分庫)
通常都會同時採用分庫和分表

vertical partitioning

把原本一個表的 schema 拆成多個表
例如把「文章內文」這個欄位從 Article 表中拆出來
變成 ArticleContent 表
也就是說垂直拆分拆出來的表的 schema 會不一樣
有一點 denormalization 的意味

直接把整個表拆出來放到另一台資料庫也是垂直拆分
例如:
PlayRecord 表獨立出來放到 warehouse 這個資料庫
User 相關的表自己一個資料庫
Photo 相關的表也自己一個資料庫

垂直拆分其實沒有解決單一表中存在太多 record 的問題

horizontal partioning == sharding

把原本一個表的 record 依據一個 shard key
拆分到不同的表(但是 schema 都一樣)
例如:
id % 3 = 0 的 record 存到 A 表
id % 3 = 1 的 record 存到 B 表
id % 3 = 2 的 record 存到 C 表

困難的點在於如何橫跨多個表產生一個 unique 的 id
以及多增加一個表之後的數據遷移

ref:
http://j.mp/1HwmOLZ << MySQL 性能调优与架构设计.pdf

不論是垂直拆分還是水平拆分
拆了之後就會遇到沒辦法 JOIN 和 ORDER BY 的問題
解決的辦法基本上就是改成在 application 層做

pt-online-schema-change

pt-online-schema-change can alter a table's structure without blocking reads or writes.

pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. Any modifications to data in the original tables during the copy will be reflected in the new table, because the tool creates triggers on the original table to update the corresponding rows in the new table.

Install

$ sudo apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

# add this to `/etc/apt/sources.list`
# remember to replace `precise` with the name of your distribution
deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main

$ sudo apt-get update
$ sudo aptitude -V install libio-socket-ssl-perl libnet-ssleay-perl percona-toolkit

ref:
http://www.percona.com/doc/percona-server/5.6/installation/apt_repo.html

Usage

$ time pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--dry-run \
--alter "ADD COLUMN extra_data TEXT NULL" \
D=streetvoice,t=member_userprofile

# you must specify `execute` option explicitly to alter tables
$ time pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--execute \
--alter "ADD COLUMN extra_data TEXT NULL" \
D=streetvoice,t=member_userprofile

$ pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--execute \
--alter " \
DROP COLUMN get_sms, \
DROP COLUMN job_title, \
DROP COLUMN exp, \
DROP COLUMN non_member_message, \
DROP COLUMN fans_amount, \
DROP COLUMN new_fans, \
DROP COLUMN last_time_in_giftbox \
" \
D=streetvoice,t=member_userprofile

$ pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--execute \
--alter " \
ADD COLUMN playlist_id integer NULL AFTER user_id, \
ADD CONSTRAINT playlist_id_refs_id_1d572331 FOREIGN KEY (playlist_id) REFERENCES music_playlist (id), \
ADD INDEX music_playrecord_97bd5154 (playlist_id) \
" \
D=streetvoice,t=music_playrecord

# 2 cores, 2GB RAM
real 105m27.484s
user 0m8.541s
sys 0m1.414s

$ pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--execute \
--alter-foreign-keys-method=rebuild_constraints \
--alter " \
ADD COLUMN play_count integer UNSIGNED NOT NULL DEFAULT 0 AFTER buy_link \
" \
D=streetvoice,t=music_musicalbum

$ pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--execute \
--alter " \
DROP COLUMN starsigns \
" \
D=streetvoice,t=member_usertemp

$ pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--dry-run \
--alter " \
DROP COLUMN is_cc, \
DROP COLUMN cc_license \
" \
D=streetvoice,t=music_song

ref:
http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html
http://www.percona.com/blog/2014/11/18/avoiding-mysql-alter-table-downtime/
http://www.cnblogs.com/zhoujinyi/p/3491059.html
http://huanghualiang.blog.51cto.com/6782683/1360873

alter-foreign-keys-method

如果你要修改的 table 有欄位是其他 table 的 foreign key
你必須明確地指定 alter-foreign-keys-method 參數
否則 pt-online-schema-change 會不讓你執行
因為會有 constraint 的問題()

官方文件說 rebuild_constraints 是比較建議的作法

$ time pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--execute \
--alter-foreign-keys-method=rebuild_constraints \
--alter "ADD COLUMN length int(10) UNSIGNED NOT NULL DEFAULT 0 AFTER file_size" \
D=streetvoice,t=music_song

# 2 cores, 2G RAM
real 2m57.298s
user 0m0.338s
sys 0m0.043s

ref:
http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html#cmdoption-pt-online-schema-change--alter-foreign-keys-method

MariaDB Galera Cluster

This tool works fine with MariaDB Galera Cluster, only makes sure wsrep_OSU_method value is TOI.

ref:
http://www.severalnines.com/blog/online-schema-upgrade-mysql-galera-cluster-using-toi-method
http://galeracluster.com/documentation-webpages/schemaupgrades.html