2021年1月12日星期二

MySQL 大量資料 insert 加速方式

這裏

Bulk inserts of data(區塊式 insert)

Extended inserts

一次 insert 多筆

Transactions

把 insert 放到 transaction 裡

Avoid indexes

先 drop index, 等 insert 完成再加回去

Skipping the binary log

暫時關閉 binary log
set sql_log_bin=0;

Multiple connections

Truncate table with foreign keys

Index is used for sort

搜尋覆蓋

2020年12月31日星期四

MySQL 記一次還原指定日期資料且避開auto increment問題

1 mysqldump 只能備份所有欄位

無法跳過有auto increment column，

所以必須複製一個相同table, 然後刪掉不要的欄位，再dump出來

2 mysqldump 常用參數

//用cluster 請勿使用 --no-autocommit

mysqldump -u root -p --skip-add-drop-table --skip-add-locks --no-create-db --no-create-info --no-autocommit --complete-insert --net-buffer-length=1000000 --max-allowed-packet=1000000000

--skip-add-drop-table 還原時不drop原有table

--skip-add-locks 還原時不lock

--no-create-db 還原時不create db

--no-create-info 還原時不create table

--no-autocommit 還原時不auto commit

--complete-insert 備份完整scheme

--net-buffer-length=1000000 加大網路buffer, 適用大檔還原

--max-allowed-packet=1000000000 加大網路packet，適用大檔還原

3 完整mysqldump參數

mysqldump -u root -p --skip-add-drop-table --skip-add-locks --no-create-db --no-create-info --complete-insert --no-autocommit --net-buffer-length=1000000 --max-allowed-packet=1000000000 --where="(date >='2020-12-21' and date<'2020-12-22')" db table | gzip > table.sql.gz

只dump db 的指定table內的 where 條件的資料

4 還原(因為備份時有指定參數，所以大檔也適用)

myslq login

use db;

source /home/..... sql file

5 完整流程

CREATE new_table LIKE old_table;

INSERT INTO new_table SELECT * FROM old_table;

ALTER TABLE new_table DROP COLUMN column;

mysqldump ....

restore ....

2020年12月24日星期四

MySQL import big table

參考這裡

2020年12月2日星期三

MySQL規格與基本優化

MySQL版本

5.7 (配合gcp 的 cloud sql)

DB引擎

InnoDB

DB規格

character 使用 UTF8mb4
collate 使用 utf8mb4_unicode_ci

CREATE DATABASE  database_name 
	character set UTF8mb4 collate utf8mb4_unicode_ci;

Table規格

character 使用 UTF8mb4
collate 使用 utf8mb4_unicode_ci

CREATE TABLE tbl_name (column_list) ENGINE=INNODB 
	CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

my.cnf設定

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
#紀錄slow query 
log-slow-queries = /var/log/mysql/mysql-slow.log 
long_query_time = 2
log-long-format
key_buffer 能夠把所有index 載入最好

基本優化觀念

至少看以下項目：

1 資料正規化/反正規化

2 Primary Key / Index 建立概念

3 資料庫記憶體管理

4 基本效能評估

基本優化方向

1 基本上遵循資料正規化，有必要時就反正規化，不必拘泥於規則。

2 減少資料量

3 使用速度快的資料類型

4 優化查詢語句速度

5 硬體的影響：RAM > SSD > CPU。MySQL可以快取，RAM越大越好。

基本優化項目

1 RAM最好>=DB資料的3倍，避免swap SSD。

2 資料類型的SQL計算速度

1boolean > int > timestamp > datetime > double > char > varchar

3 日期盡量用timestamp，因為速度快，且有cache

timestamp儲存UTC時間，會依據time zone設定返回不同時間

4 有where就要建index，有order就要建index

primary key / index 屬性速度
1boolean > int > char > varchar

5 ID盡量不要使用string，因為ID是所有查詢的基本，使用string 佔空間、速度又慢。

6 可以算的先算，不要讓MySQL去算。

雖然MySQL有很多內建函式，但是不會比你程式算的快，而且不好維護。

//使用MySQL內建函式
SELECT * FROM table WHERE DATE_ADD(NOW(),INTERVAL 10 DAY) > create_date;

//更好，先算好時間
SELECT * FROM table WHERE DATE_ADD('2019-01-01 00:00:00',INTERVAL 10 DAY) > create_date;

//更好，改成int
SELECT * FROM table WHERE DATE_ADD(1324335674,INTERVAL 10 DAY) > create_date;

7 order by 的標的要設成index，因為order by會讓MySQL指定key做index搜尋，權重大於WHERE條件式。

MySQL 常用查詢優化

分頁查詢

limit m,n

limit offset m ,只取n

實際上mysql 會取m+n,最後返回丟掉m

MySQL Partition

Partition

官方文件

用來把大表資料分區，分不同檔案存放。

Partitioning enables you to distribute portions of individual tables 
across a file system according to rules which you can set largely as needed. 
In effect, different portions of a table are stored as separate tables 
in different locations. — MySQL 5.7

Partitioning 方式

MySQL partitioning 支援 4 種分區方式，分別是 range、list、hash、key，
要採用哪種分區方式要取決於需求以及資料本身特性，
如果選到不適合的分區方式，效能甚至會不進反退。
不同的分區方式，也有不同的維護方法，在決定方式前一定都要考量到

Range：範圍分區。例如用時間當做 partition key，每一個月為一個分區。

CREATE TABLE table (
    id INT NOT NULL,
    data varchar(255) NOT NULL
    timeMS timestamp
);
//用id 當 range
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (11),
    PARTITION p1 VALUES LESS THAN (21),
    PARTITION p2 VALUES LESS THAN (31),
    PARTITION p3 VALUES LESS THAN MAXVALUE);

//用 timeMS 當 range
PARTITION BY RANGE (timeMs) (
    PARTITION p0 VALUES LESS THAN (1430409600000),
    PARTITION p201505 VALUES LESS THAN (1433088000000),
    ...中略
    PARTITION p202005 VALUES LESS THAN (1590940800000),
    PARTITION p202006 VALUES LESS THAN (1593532800000),
    PARTITION p202007 VALUES LESS THAN (1596211200000),
    PARTITION pMax VALUES LESS THAN MAXVALUE); 
      
    //p0 為台灣時區 2015/5/1 以前的資料
    //p201505 為台灣時區 2015/05 的資料 (2015/5/1 ~ 2015/5/31)，而後每個月依此類推，
    //p202007 為台灣時區 2020/07 的資料 (2020/7/1 ~ 2020/7/31)
    //pMax 為台灣時區 2020/8/1 之後的資料，
    //pMax 不應該有資料，因為會預先建立未來月份的分區，讓資料寫入對應月份分區。
    //pMax 有兩個目的，其一個是捕捉不預期時間的資料，例如未來時間，另一個則是為了用來建立新分區

若是InnoDB，且 innodb_file_per_table 有開啟，
產生的分區檔案：table#p#p0.ibd、table#p#p1.ibd、table#p#p2.ibd、table#p#p3.ibd

List：用預設的集合分區，指定集合內的元素分別屬於哪個分區。例如以國家當做 partition key，自定義集合如下圖，國家代碼 1 就會屬於 partition 1。

CREATE TABLE tb (
    id INT NOT NULL,
    data varchar(255) NOT NULL
);
PARTITION BY LIST (id) (
    PARTITION pA VALUES IN (1,3,5),
    PARTITION pB VALUES IN (12,15));

id 等於 1、3、5，分在 pA 區
id 等於 12、15，分在 pB 區
如果寫入的資料不在這些範圍會出錯。

Hash：指定分區數量 N，以單一 int 欄位或
以回傳 int 的表達式結果 mod N決定資料屬於哪個分區。

CREATE TABLE tb (
    id INT NOT NULL,
    mydate DATE NOT NULL DEFAULT '1970-01-01'
);
PARTITION BY HASH(mydate)
PARTITIONS 3;

依據 mydate 進行 HASH 分區在 3 個 partition。
也可以使用函式，例如 PARTITION BY HASH(YEAR(mydate))，
每次有異動，YEAR()都會執行一次，所以複雜的運算會影響效能。

Key：指定分區數量 N，支援多個欄位為 partition key，用內建 hash function 決定資料屬於哪個分區。
跟 HASH 分區類似，差在 HASH 分區使用使用者自訂的運算方式，
KEY 分區使用 MySQL server 內部的運算方式。

  
CREATE TABLE tb (
    mydate DATE NOT NULL DEFAULT '1970-01-01',
    data varchar(255) NOT NULL
);
PARTITION BY KEY(mydate);
PARTITIONS 3;

子分區

  //Range 分區底下，再建立 Range 分區
CREATE TABLE tb (
    id INT NOT NULL,
    data varchar(255) NOT NULL
);
PARTITION BY RANGE(id)
SUBPARTITION BY RANGE(id)(
    PARTITION p0 VALUES LESS THAN (10)(
        PARTITION s0 VALUES LESS THAN (6),
        PARTITION s1 VALUES LESS THAN MAXVALUE
    ),
    PARTITION p1 VALUES LESS THAN MAXVALUE(
        PARTITION s2 VALUES LESS THAN (30),
        PARTITION s3 VALUES LESS THAN MAXVALUE
    )
);

Partitioning key 必須是Primary key

如果partition key 不是 pk, 那麼可以將兩個做成組合鍵

新增 Partitioning

 
//語法1
ALTER TABLE tb ADD PARTITION (PARTITION p0 VALUES LESS THAN(11));

//語法2
ALTER TABLE 'tb2' REORGANIZE PARTITION pMax INTO (
    PARTITION p202008 VALUES LESS THAN (1598889600000),
    PARTITION pMax VALUES LESS THAN MAXVALUE
);

合併相鄰分區

 ALTER TABLE aa REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (11));

刪除分區(刪除後，儲存在該分區的資料也會不見)

ALTER TABLE aa DROP PARTITION p0;

2020年12月1日星期二

Golang pprof

go tool pprof

pprof 是 go 內建的執行狀態分析工具

參考1

參考2

pprof兩類

runtime/pprof 是收集程式 runtime 資訊

net/http/pprof 是收集 http 執行時資訊

使用方式

直接生成報告

web interface：使用web介面

terminal interface：使用終端介面

可以分析什麼

cpu profiling：依據頻率收集監控程式的cpu使用情況

memory profiling：收集監控程式的記憶體heap,stack使用狀況。可監控是否記憶體洩漏。

mutex profiling：阻塞分析，監控goroutine阻塞等待狀況。

block profiling：互斥分析，報告互斥鎖競爭狀況。

使用http pprof

import _ net/http/pprof

//啟動http server 會自動掛載到 default mux

http.ListenAndServer("0.0.0.0:6060",nil)

//會有 localhost/debug/pprof endpoint 可瀏覽

然後可使用瀏覽器瀏覽：

http://localhost:6060/debug/pprof

會列出各種可分析的項目

也可使用終端機取的資訊

go tool pprof http://localhost:6060/debug/pprof/profile?seconds=60
go tool pprof http://localhost:6060/debug/pprof/heap
go tool pprof http://localhost:6060/debug/pprof/block
go tool pprof http://localhost:6060/debug/pprof/mutex
go tool pprof http://localhost:6060/debug/pprof/threadcreate
go tool pprof http://localhost:6060/debug/pprof/trace

指定輸出格式

go tool pprof http://localhost:6060/debug/pprof/profile > cpu.pprof

//會輸出protobuf格式

觀看檔案

go tool pprof cpu.pprof

指定輸出圖檔：(需要安裝 Graphviz)

go tool pprof -png http://localhost:6060/debug/pprof/heap > out.png

特別紀錄：
輸出trace的檔案，要用trace觀看
go tool pprof http://localhost:6060/debug/pprof/trace > trace.out
要用 go tool trace trace.out 觀看

訂閱：文章 (Atom)

2021年1月12日 星期二