15 個有用的 MySQL/MariaDB 效能調校和優化技巧

教學

MySQL是一個強大的開源關聯式數據庫管理系統，簡稱RDBMS。它於1995年發布（已有20年歷史）。它使用結構化查詢語言，這可能是管理數據庫內容的最受歡迎選擇。最新的MySQL版本是5.6.25，於2015年5月29日發布。

關於MySQL的一個有趣的事實是，名稱來自Michael Widenius（MySQL的創建者）的女兒My。儘管有很多有趣的事實關於MySQL，本文旨在向您展示一些有用的實踐，以幫助您管理MySQL服務器。

MySQL Performance Tuning

2009年4月，MySQL項目被Oracle收購。因此，創建了一個名為MariaDB的MySQL社區分叉項目。創建分叉的主要原因是為了在通用公共許可證下保持該項目的免費性。

今天，MySQL和MariaDB是最常用的RDBMS之一（如果不是最常用的），用於諸如WordPress、Joomla、Magento等Web應用程式。

本文將向您展示一些基本但有用的技巧，如何優化調整MySQL/MariaDB的性能。請記住，本文假設您已經安裝了MySQL或MariaDB。如果您仍在猶豫如何在系統上安裝它們，您可以按照我們這裡的廣泛指南。

  1. 在RHEL/CentOS 7上安裝LAMP
  2. 在Fedora 22上安裝LAMP
  3. 在Ubuntu 15.04上設置LAMP
  4. 在Debian 8上安裝MariaDB
  5. 在Gentoo Linux上安裝MariaDB
  6. 在Arch Linux上安裝MariaDB

重要提示：在開始之前，不要盲目接受這些建議。每個MySQL設置都是獨特的，需要在進行任何更改之前進行額外思考。

你需要知道的事情：

  1. MySQL/MariaDB配置文件位於/etc/my.cnf。每次修改此文件，都需要重新啟動MySQL服務，以使新更改生效。
  2. 為了編寫本文章，MySQL版本5.6已被用作模板。

1. 啟用InnoDB文件每表

首先重要的是要解釋，InnoDB是一種存儲引擎。MySQL和MariaDB使用InnoDB作為默認存儲引擎。過去，MySQL曾將數據庫表和索引保存在系統表空間中。這種方法適用於唯一目的是數據庫處理並且其存儲磁盤未用於任何其他目的的服務器。

InnoDB提供了更靈活的方法，每個數據庫信息都保存在一個.ibd數據文件中。每個.ibd文件代表一個表空間。這樣的話，像“TRUNCATE”這樣的數據庫操作可以更快地完成，並且在刪除或截斷數據庫表時，您也可以回收未使用的空間。

此配置的另一個好處是您可以將一些數據庫表保存在單獨的存儲設備中。這可以大大改善磁盤上的I/O負載。

innodb_file_per_table在MySQL 5.6及以上版本中默認啟用。您可以在/etc/my.cnf文件中看到這一指令。指令如下：

innodb_file_per_table=1

2. 將MySQL數據庫數據存儲在單獨的分區上

注意：此設置僅適用於MySQL，而不適用於MariaDB。

有時，操作系統的讀寫可能會降低MySQL服務器的性能，特別是如果它們位於同一硬盤上。因此，我建議為MySQL服務使用單獨的硬盤（最好是SSD）。

要完成這一步，您需要將新硬盤連接到計算機/服務器上。本文將假設該硬盤位於/dev/sdb下。

接下來要準備新的硬碟：

# fdisk /dev/sdb

現在按“n”建立新分割區。接著按“p”將新分割區設為主要分割區。然後，設定分割區編號從 1-4。然後您將選擇分割區大小。在這裡按 enter。在下一個步驟，您需要配置分割區的大小。

如果要使用整個磁碟，再按一次 enter。否則，您可以手動設定新分割區的大小。準備好後按“w”來寫入更改。現在我們需要為新分割區創建文件系統。這可以輕鬆完成：

# mkfs.ext4 /dev/sdb1

現在我們將在一個文件夾中掛載新分割區。我已經將文件夾命名為“ssd”，並在根目錄中創建：

# mkdir /ssd/

我們準備好在新文件夾中掛載新分割區：

# mount /dev/sdb1  /ssd/

您可以通過在 /etc/fstab 文件中添加以下行來在啟動時執行掛載。

/dev/sdb1 /ssd ext3 defaults 0 0

現在您準備好將 MySQL 移至新磁碟了。首先使用以下命令停止 MySQL 服務：

# service mysqld stop

I would recommend you stopping Apache/nginx as well to prevent any attempts to write in the databases:

# service httpd stop
# service nginx stop

現在將整個 MySQL 目錄複製到新磁碟：

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

這可能需要一段時間，具體取決於 MySQL 數據庫的大小。一旦此過程完成，請重新命名 MySQL 目錄：

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

接下來，我們將創建一個符號連結。

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

現在您可以啟動 MySQL 和 Web 服務了：

# service mysqld start
# service httpd start
# service nginx start

此時，您的 MySQL 數據庫將從新磁碟訪問。

Pages: 1 2 3 4

Source:
https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/