15 個有用的 MySQL/MariaDB 效能調整和最佳化技巧

標籤：targe   lemp   結果   原因   png   資料   rdbms   rac   符號連結   

原文地址

MySQL 是一個強大的開源關聯式資料庫管理系統（簡稱 RDBMS）。它發佈於 1995 年（20年前）。它採用結構化查詢語言 (SQL)（SQL），這可能是資料庫內容管理中最流行的選擇。最新的 MySQL 版本是 5.6.25，於 2015 年 5 月 29 日發布。

關於 MySQL 一個有趣的事實是它的名字來自於 Michael Widenius（MySQL 的創始人）的女兒“ My”。儘管有許多關於 MySQL 有趣的傳聞，不過本文主要是向你展示一些有用的實踐，以協助你管理你的 MySQL 伺服器。

MySQL 效能最佳化

2009 年 4 月，MySQL 被 Oracle 收購。其結果是MySQL 社區分裂，建立了一個叫 MariaDB 的分支 。建立該分支的主要原因是為了保持這個項目可以在 GPL 下的自由。

今天，MySQL 和 MariaDB 是用於類似 WordPress、Joomla、Magento 和其他 web 應用程式的最流行的 RDMS 之一（如果不是最多的）。

這篇文章將告訴你一些基本的，但非常有用的關於如何最佳化 MySQL/MariaDB 效能的技巧。注意，本文假定您已經安裝了 MySQL 或 MariaDB。如果你仍然不知道如何在系統上安裝它們，你可以按照以下說明去安裝：

• 在 RHEL/CentOS 7 上安裝 LAMP
• 在 Fedora 22 上安裝 LAMP
• 在 Ubuntu 15.04 安裝 LAMP
• 在 Debian 8 上安裝 MariaDB
• 在 Gentoo Linux 上安裝 MariaDB
• 在 Arch Linux 上安裝 MariaDB

重要提示： 在開始之前，不要盲目的接受這些建議。每個 MySQL 設定都是不同的，在進行任何更改之前需要謹慎考慮。

你需要明白這些：

• MySQL/MariaDB 設定檔位於 /etc/my.cnf。 每次更改此檔案後你需要重啟 MySQL 服務，以使更改生效。
• 這篇文章使用 MySQL 5.6 版本。

1. 啟用 InnoDB 的每張表一個資料檔案設定

首先，有一個重要的解釋， InnoDB 是一個儲存引擎。MySQL 和 MariaDB 使用 InnoDB 作為預設儲存引擎。以前，MySQL 使用系統資料表空間來儲存資料庫中的表和索引。這意味著伺服器唯一的目的就是資料庫處理，它們的儲存盤不用於其它目的。

InnoDB 提供了更靈活的方式，它把每個資料庫的資訊儲存在一個 .ibd 資料檔案中。每個 .idb 檔案代表它自己的資料表空間。通過這樣的方式可以更快地完成類似 “TRUNCATE” 的資料庫操作，當刪除或截斷一個資料庫表時，你也可以回收未使用的空間。

這樣配置的另一個好處是你可以將某些資料庫表放在一個單獨的存放裝置。這可以大大提升你磁碟的 I/O 負載。

MySQL 5.6及以上的版本預設啟用 innodb_file_per_table。你可以在 /etc/my.cnf 檔案中看到。該指令看起來是這樣的：

1. innodb_file_per_table=1

2. 將 MySQL 資料庫資料存放區到獨立分區上

注意：此設定只在 MySQL 上有效, 在 MariaDB 上無效。

有時候作業系統的讀/寫會降低你 MySQL 伺服器的效能，尤其是如果作業系統和資料庫的資料位元於同一塊磁碟上。因此，我建議你使用單獨的磁碟（最好是 SSD）用於 MySQL 服務。

要完成這步，你需要將新的磁碟串連到你的電腦/伺服器上。對於這篇文章，我假定磁碟掛在到 /dev/sdb。

下一步是準備新的分區：

1. # fdisk /dev/sdb

現在按 “N” 來建立新的分區。接著按 “P”，使其建立為主要磁碟分割。在此之後，從 1-4 設定分區號。之後，你可以選擇分區大小。這裡按 enter。在下一步，你需要配置分區的大小。

如果你希望使用全部的磁碟，再按一次 enter。否則，你可以手動設定新分區的大小。準備就緒後按 “w” 儲存更改。現在，我們需要為我們的新分區建立一個檔案系統。這可以用下面命令輕鬆地完成：

1. # mkfs.ext4 /dev/sdb1

現在我們會掛載新分區到一個目錄。我在根目錄下建立了一個名為 “ssd” 的目錄：

1. # mkdir /ssd/

掛載新分區到剛才建立的目錄下：

1. # mount /dev/sdb1 /ssd/

你可以在 /etc/fstab 檔案中添加如下行設定為開機自動掛載：

1. /dev/sdb1 /ssd ext3 defaults 0 0

現在我們將 MySQL 移動到新磁碟中

首先停止 MySQL 服務：

1. # service mysqld stop

我建議你??同時停止 Apache/nginx，以防止任何試圖寫入資料庫的操作：

1. # service httpd stop
2. # service nginx stop

現在複製整個 MySQL 目錄到新分區中:

1. # cp /var/lib/mysql /ssd/ -Rp

這可能需要一段時間，具體取決於你的 MySQL 資料庫的大小。一旦這個過程完成後重新命名 MySQL 目錄：

1. # mv /var/lib/mysql /var/lib/mysql-backup

然後建立一個符號連結：

1. # ln -s /ssd/mysql /var/lib/mysql

現在啟動你的 MySQL 和 web 服務：

1. # service mysqld start
2. # service httpd start
3. # service nginx start

以後你的資料庫將使用新的磁碟訪問。

原文地址

15 個有用的 MySQL/MariaDB 效能調整和最佳化技巧