MySQL

11 月 30 2014

[MySQL] shrink ibdata file

最近幫忙處理某台 MySQL 5.0 server，做了個小實驗，寫個文章把處理過程紀錄下來。

因為該 server 沒有 innodb_file_per_table 這個設定，所有的 InnoDB 資料都放在 ibdata 這個檔案裡，而我們希望能讓該 server 持續運作，盡可能在不停機的狀況下，把 InnoDB 的資料切開。

實驗後，我們透過這些步驟完成切割/轉移：

  1. 確認舊的 server 有開啟 binlog，準備跑 replication。
  2. 用 Percona XtraBackup 備份舊的 MySQL 資料庫。
  3. 準備新機器，把備份檔丟到新的 server。
  4. 在新機器的 my.cnf 加入 innodb_file_per_table 設定，把 MySQL daemon 跑起來。
  5. 在新機器用以下指令把 InnoDB table 挑出來：
    SELECT `TABLE_SCHEMA`, `TABLE_NAME` FROM `TABLES` WHERE `ENGINE`='InnoDB' ORDER BY `TABLE_SCHEMA`,`TABLE_NAME`;
  6. 在新機器用 mysqldump 匯出 table schema & data ：
    mysqldump -u root -pMY_PASSWORD --max_allowed_packet=512M -Cx --opt TABLE_SCHEMA TABLE_NAME > TMP_DIR/TABLE_SCHEMA.TABLE_NAME.sql
  7. 在新機器 drop 掉已匯出的 table：
    DROP TABLE `TABLE_SCHEMA`.`TABLE_NAME`;
  8. 停掉新機器的 MySQL daemon，把 datadir 的 ib* 搬走，再啟動 MySQL daemon。
  9. 重新匯入 table：
    mysql -u root -pMY_PASSWORD TABLE_SCHEMA < TMP_DIR/TABLE_SCHEMA.TABLE_NAME.sql
  10. 用 CHANGE MASTER TO … 指令開始讓新機器的 MySQL 成為舊 server 的 SLAVE。
  11. 同步完成後，找時間讓舊的 server 退下，讓新的 server 佔用舊機器的 IP。
  12. 在新機器的 MySQL 執行 RESET SLAVE，停掉 replication。

By • 0 • Tags: , , , , ,

8 月 5 2013

[Ubuntu] 安裝 & 設定 libapache2-mod-auth-mysql

這兩天試著在 Ubuntu server 玩 Apache HTTP authentication with MySQL backend 。
記一下幾個要注意的地方：

  • 設定參數在 /usr/share/doc/libapache2-mod-auth-mysql/DIRECTIVES.gz ，可以透過以下這個指令查：
    # dpkg-query --listfiles libapache2-mod-auth-mysql
  • 為避免 Apache 混合使用驗證而導致驗證失敗，加上這行比較保險：
    AuthBasicAuthoritative Off

.htaccess 檔內容大概像這樣：

AuthName "JoeHorn's Secret Web"
AuthType Basic
AuthBasicAuthoritative Off
AuthUserFile /dev/null
Auth_MySQL on
Auth_MySQL_User DB_USERNAME
Auth_MySQL_Password DB_PASSWORD
Auth_MySQL_DB AUTH_INFO_DB
Auth_MySQL_Password_Table AUTH_INFO_TABLE
Auth_MySQL_Username_Field USERNAME_FIELD
Auth_MySQL_Password_Field PASSWORD_FIELD
Auth_MySQL_Empty_Passwords off
Auth_MySQL_Encryption_Types MySQL
Require valid-user

By • 0 • Tags: , , , ,

3 月 13 2012

在 FreeBSD 用 ports 安裝 MySQL 5.5

我在 FreeBSD 用 ports 安裝 databases/mysql41-server 、 databases/mysql50-server 時，會放這些 configuration parameters：

  • WITH_CHARSET=big5
  • WITH_XCHARSET=all
  • BUILD_OPTIMIZED

（databases/mysql51-server 會多放個 WITH_FAST_MUTEXES）

之前要安裝 databases/mysql55-server 時發現…
databases/mysql55-server 只剩下 WITHOUT_OPENSSL 與 WITH_FASTMTX 這兩個 configuration parameters 可以用，而預設的 WITH_EXTRA_CHARSETS（文章前頭提到的 WITH_XCHARSET）就是 all；以往使用的 WITH_CHARSET=big5 必須從 my.cnf 作設定（這也的確是比較好的作法）；BUILD_OPTIMIZED 則是不需要了（也不支援？）。

另外，databases/mysql55-server 預設只有這些 storage engines：

mysql> SHOW ENGINES;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                      | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)

ARCHIVE、BLACKHOLE、EXAMPLE 跟 FEDERATED 這四個 storage engines 可以透過 INSTALL PLUGIN 的方式進行安裝，例：

mysql> INSTALL PLUGIN archive SONAME 'ha_archive.so';
mysql> INSTALL PLUGIN blackhole SONAME 'ha_blackhole.so';
mysql> INSTALL PLUGIN example SONAME 'ha_example.so';
mysql> INSTALL PLUGIN federated SONAME 'ha_federated.so';

By • , 0 • Tags: , ,

3 月 2 2012

MySQL 的 max key length

我曾試著在 MYISAM 格式的 table 裡，把兩個 VARCHAR(255)，UTF8 編碼的欄位設定成一組 UNIQUE KEY。
當時遇到這個 Error：

#1071 – Specified key was too long; max key length is 1000 bytes

VARCHAR(255) 代表該欄位可以紀錄 255 個字元，每個字元以 UTF8 encoded 儲存，一個欄位可以佔用 765 bytes，所以兩個欄位總和超過 1000 bytes，無法設為一組 UNIQUE KEY。

前陣子，我把 MySQL 從 5.1 系列升版到 5.5 系列。
在 5.1（含）之前，MySQL 使用 3 個 bytes 紀錄 UTF8 encoded data。
開發團隊在 MySQL 5.5.3 加入了 utf8mb4 編碼，用 4 個 bytes 紀錄 UTF8 encoded data，支援更多的文字。

utf8mb4 讓我聯想到 max key length 的問題，所以進行了一些小測試，並追了一下 source code。
從目前的最新版本的 source code 看來：

By • 0 • Tags:

8 月 25 2010

PHP 的 mysqlnd（MySQL Native Driver）

PHP 自 5.3.0 開始，引入 mysqlnd 這個 extension。
在 5.3.0 的 Full ChangeLog 中可以看到這行：

Added mysqlnd extension as replacement for libmysql for ext/mysql, mysqli and PDO_mysql. (Andrey, Johannes, Ulf)

依照個人的過往經驗，native driver 的效能都會比較好，而 mysqlnd 也不例外（有興趣的可以看看這篇文章）。

比較可惜的是，mysqlnd 在 Windows 平台的 PHP 是預設套件；在 *NIX 平台，使用前必須在編譯前就先做好設定（configure；參數可參考 PHP 官方的mysqlnd 安裝文件）。

剛才，很高興看到 FreeBSD 的 PHP5 ports 加入了 option，讓我們可以輕鬆搞定。 8-)

By • , , 0 • Tags: , , ,

4 月 1 2010

[MySQL] 處理 weekday 欄位

最近在 MySQL 處理包含 weekday 型態的資料，發覺… 在 MySQL 處理 weekday 真的是「輕鬆寫意」… 8-)

根據 DAYOFWEEK() 與 WEEKDAY() 的定義，回傳的資料都是數值。
而大多數 DBMS 都有取得 weekday 的方法。

我會說「 MySQL 處理 weekday 輕鬆寫意」的原因是… ENUM ！
我把將欄位型態定義為 enum(‘Sun’, ‘Mon’, ‘Tue’, ‘Wed’, ‘Thu’, ‘Fri’, ‘Sat’)。
今天（2010/04/01，愚人節）是星期四，若是要抓出該欄位為 Thu 的資料，只要用這種語法：

SELECT ... WHERE `col`= DAYOFWEEK( NOW() )

用這類方法要注意的是，DAYOFWEEK() 與 WEEKDAY() 對於每個 weekday，回傳的數值都不一樣。

By • 0 • Tags: , , ,

3 月 8 2010

[MySQL] Primary Key、UNIQUE、INDEX 與 LIMIT 有多重要

我利用前兩天的空閒時間寫了個 PHP 的小程式，運用 PHP 的 PDO 元件，對 MySQL 進行小測試。

Server 作業系統與硬體：

  • 作業系統：FreeBSD 8.0-RELEASE
  • CPU：Intel(R) Pentium(R) 4 CPU 3.00GHz (3042.62-MHz 686-class CPU)，Hyper-Threading 開啟
  • RAM：2G DDR2 800
  • 放資料庫的 HDD：WDC WD800JB-00JJA0，UDMA 100

MySQL 5.1.44 的設定（/etc/my.cnf）：

# defult setting (maybe changed)
key_buffer_size = 128M
max_allowed_packet = 4M
table_open_cache = 4096
sort_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# added for tunning by Joe Horn
skip-name-resolve
connect_timeout = 60
join_buffer_size = 1M
max_connect_errors = 10000
max_connections = 100
max_heap_table_size = 1G
query_cache_size = 16M
slave_net_timeout = 30
sync_binlog=1
thread_cache_size = 512
thread_concurrency = 8
tmp_table_size = 1G
table_definition_cache = 512
# choose one ( depend on query amount )
#concurrent_insert=2
low_priority_updates=1

這台機器跑 Super Smack 的結果：

# super-smack -d mysql update-select.smack 80 1000
Query Barrel Report for client smacker
connect: max=14ms  min=1ms avg= 4ms from 80 clients
Query_type      num_queries  max_time  min_time  q_per_s
select_index    80000        101       6         717.91
update_index    80000        108       1         717.91

我用測試程式產生了一百萬筆資料的 MyISAM table，各種測試各循環 1000 次，產生如下的測試結果（後面的數字單位是秒）：

SELECT BY PK_COL WITH_QUERY_CACHE:              0.058
SELECT BY PK_COL WITHOUT_QUERY_CACHE:           0.062
SELECT BY PK_COL LIMIT WITH_QUERY_CACHE:        0.058
SELECT BY PK_COL LIMIT WITHOUT_QUERY_CACHE:     0.062
SELECT BY UNIQUE_COL WITH_QUERY_CACHE:          0.058
SELECT BY UNIQUE_COL WITHOUT_QUERY_CACHE:       0.062
SELECT BY UNIQUE_COL LIMIT WITH_QUERY_CACHE:    0.058
SELECT BY UNIQUE_COL LIMIT WITHOUT_QUERY_CACHE: 0.062
SELECT BY INDEX_COL WITH_QUERY_CACHE:           0.058
SELECT BY INDEX_COL WITHOUT_QUERY_CACHE:        0.061
SELECT BY INDEX_COL LIMIT WITH_QUERY_CACHE:     0.058
SELECT BY INDEX_COL LIMIT WITHOUT_QUERY_CACHE:  0.063
SELECT BY COL WITH_QUERY_CACHE:                 0.786
SELECT BY COL WITHOUT_QUERY_CACHE:              0.063
SELECT BY COL LIMIT WITH_QUERY_CACHE:           0.059
SELECT BY COL LIMIT WITHOUT_QUERY_CACHE:        0.062
UPDATE BY PK_COL WITH_QUERY_CACHE:              1.139
UPDATE BY PK_COL WITHOUT_QUERY_CACHE:           1.199
UPDATE BY PK_COL LIMIT WITH_QUERY_CACHE:        0.125
UPDATE BY PK_COL LIMIT WITHOUT_QUERY_CACHE:     0.142
UPDATE BY UNIQUE_COL WITH_QUERY_CACHE:          2.734
UPDATE BY UNIQUE_COL WITHOUT_QUERY_CACHE:       1.203
UPDATE BY UNIQUE_COL LIMIT WITH_QUERY_CACHE:    0.147
UPDATE BY UNIQUE_COL LIMIT WITHOUT_QUERY_CACHE: 0.163
UPDATE BY INDEX_COL WITH_QUERY_CACHE:           1.183
UPDATE BY INDEX_COL WITHOUT_QUERY_CACHE:        1.063
UPDATE BY INDEX_COL LIMIT WITH_QUERY_CACHE:     0.138
UPDATE BY INDEX_COL LIMIT WITHOUT_QUERY_CACHE:  0.154
UPDATE BY COL WITH_QUERY_CACHE:                 4704.859
UPDATE BY COL WITHOUT_QUERY_CACHE:              4641.191
UPDATE BY COL LIMIT WITH_QUERY_CACHE:           0.156
UPDATE BY COL LIMIT WITHOUT_QUERY_CACHE:        0.167

根據測試結果，大概可以看到出以下幾個要點：

  1. 根據第 21 行與 22 行的差異看來，MySQL Query cache 還是有點用處，不過效用不大。
  2. 根據第 13 行與第 29 行的結果看來，有沒有設定 Primary Key、UNIQUE、INDEX 的影響不小。
  3. 根據 UPDATE 語法的測試結果看來，有沒有 LIMIT 頗重要。

By • 0 • Tags: , , , , , , , , ,

11 月 20 2009

一些增進 MySQL 效能的 tips

幾天前，跟 Solaris 叔叔 談到 MySQL 的 ENUM，回家後上網找了些資料，剛好找出了一些使用 MySQL 時，與效能有關的 tips。

稍微整理過後，放在這裡：

  • 善用 EXPLAIN SELECT 分析 SQL statement，以下列舉狀態解讀：
    EQ_REF一對一比對
    REF一對多比對
    RANGE特定範圍的資料會被傳回
    INDEX使用 INDEX 裡的資料
    ALL對整個 TABLE 作掃瞄（最差）
  • 善用 INDEX，以這個 SQL statement 為例：
    SELECT `col_a`
FROM `table_a` LEFT JOIN `table_b`
ON `table_a`.`col_b` = `table_b`.`col_c`
WHERE `col_d` = ....;

-- SELECT 的欄位不需 INDEX，ex： `col_a`。
-- JOIN 的欄位需要 INDEX，ex： `table_a`.`col_b` 與 `table_b`.`col_c`。
-- WHERE 的欄位需要 INDEX，ex： `col_d`。
  • 儘量使用固定大小的欄位，MyISAM 搜尋固定大小欄位比較快；也就是說，能用 CHAR 就別用 VARCHAR。
  • 儘量將欄位指定為 NOT NULL。
  • ENUM 格式的欄位，資料處理速度很快（實測結果在這）。
  • 不要取不需要的資料，下面這種語法就是不好的例子：
    SELECT * ...
  • 把 BLOB 與 TEXT 拆開，減少 MySQL 開啟單一大檔（table 資料檔案過大）的機會。
  • JOIN 用的欄位資料格式最好是相同的，以免 MySQL 作 FULL TABLE SCAN。
  • 使用 LIKE 時，避免把 % 放在字串開頭，以免 INDEX 利用率不佳。
    -- 下列這種語法無法利用 INDEX
SELECT ... WHERE `col` LIKE '%string%';

-- 下列這種語法會利用 INDEX
SELECT ... WHERE `col` LIKE 'string%';
  • 注意 WHERE 子句內的運算式。
    -- 下列這種語法會使用 INDEX，速度快。
SELECT ... WHERE `col` < 100 / 10;

-- 下列這種語法會導致 FULL TABLE SCAN，速度慢。
SELECT ... WHERE `col` * 10 < 100;

By • 0 • Tags: , , , , , , , , , ,

5 月 29 2009

網頁系統的使用者驗證與 MySQL 的 “SELECT … LIMIT m,n”

幾個小時前，PTT 的 Database 板 又有人問到 MySQL 的 “SELECT … LIMIT m,n” 相關問題。
其實，在大多數的網頁系統中，”SELECT … LIMIT m,n” 應該是一定要用的語法，尤其是在使用者驗證時。

簡單舉例，MySQL 的 “SELECT …” 語法運作可說是拿著籃子去裝東西，若以 LIMIT 語法限制籃子裝東西的數量，MySQL 會在籃子裝滿後就停止動作，並傳回結果。
在網頁系統中，因為使用者帳號都是獨立的，所以，進行使用者登入驗證時，只需要抓出一筆資料來進行比對。

因此，在網頁系統中，我時常用這種語法來進行使用者登入的驗證：

SELECT `使用者身份` FROM `users` WHERE `username`='使用者名稱' AND `password`='雜湊後的密碼' LIMIT 1;

如果找不到資料，代表使用者的帳號/密碼錯誤。
而直接把密碼加入 WHERE 子句，不挑出來用程式比對則是為了杜絕 SQL Injection 導致的密碼外洩。

另外，這樣作也不錯：

SELECT `使用者身份` FROM `users` WHERE `username`='使用者名稱' AND `password`=PASSWORD( '雜湊前或雜湊後的密碼' ) LIMIT 1;

:-P

By • 0 • Tags: ,

9 月 3 2008

FreeBSD i386 與 amd64 platform 的差異

除了已知的效能差異（以前用 Ubench 測過的，gaod 長輩也貢獻了一些資訊，都 紀錄在這）、預設支援的記憶體容量之外，這兩天又找出另外一項。

某台機器跑 FreeBSD i386 platform，搭配 PAE 與 SMP。
根據以往的經驗，這種記憶體容量超過 4G 的機器，我會選 amd64 platform。
（至少經手過的幾台機器到現在都很健康…）

近來這台機器的 MySQL daemon process 時常會掛掉…
追蹤 error log 之後，發現這種錯誤訊息：

Out of memory (Needed 16391 bytes)

我把錯誤訊息丟上 Google 後，才發現 FreeBSD i386 platform 版本，其預設之單 process 記憶體容量限制為 512 MB，而 MySQL daemon process 使用超過 512 MB 後就會噴出上述的錯誤訊息，接著就是嗝屁… (驚)

我沒碰過這種瓶頸，似乎是 amd64 platform 沒有這種限制。
i386 platform 遭遇到這種瓶頸的解法是在 /boot/loader.conf 裡面丟進類似這三行：

kern.maxdsiz=”2G”
kern.dfldsiz=”2G”
kern.maxssiz=”256M”

（拉高單 process 的記憶體容量限制，變為 2G）
然後 reboot，準備驗收成果…

By • , 0 • Tags: , , ,

1 2 3