MySQL 在磁碟上創建臨時表。我該如何阻止它？
我們正在執行一個使用者目前覺得速度很慢的網站（Moodle）。我想我已經找到了 MySQL 在磁碟上創建臨時表的問題。我在 Mysql Workbench 伺服器管理中觀察了這個變數
created_tmp_disk_tables，這個數字以大約 50 個表/秒的速度增加。使用一天后，created_tmp_disk_tables> 100k。此外，記憶體似乎沒有被釋放。使用量不斷增加，直到系統變得幾乎無法使用，我們必須重新啟動 MySQL。我幾乎每天都需要重新啟動它，它開始使用大約 30-35% 的可用記憶體並以 80% 結束一天。我在數據庫中沒有 blob，也無法控制查詢，因此我無法嘗試優化它們。我還使用Percona 配置嚮導生成配置文件，但 my.ini 也沒有解決我的問題。
問題
- 我應該改變什麼來阻止 MySQL 在磁碟上創建臨時表？我需要更改設置嗎？我應該給它更多的記憶體嗎？
- 如何阻止 MySQL 佔用我的記憶體？
編輯
我啟用
slow_queries了日誌，發現查詢SELECT GET_LOCK()記錄得很慢。快速搜尋顯示我在 PHP 配置 (mysqli.allow_persistent = ON) 中允許了持久連接。我把它關掉了。這降低了 MySQL 消耗記憶體的速度。儘管它仍在創建臨時表。我還檢查了它
key_buffer size是否足夠大。我看著變數key_writes。這應該為零。如果沒有，增加key_buffer_size.I 有零key_reads和零key_writes，所以我假設key_buffer_size足夠大。我將
tmp_table_sizeandmax-heap-table-size增加到 1024M，因為 created_tmp_disk_tables 的增加可能表明這些表無法放入記憶體。這並沒有解決它。編輯 2
如果您
sort_merge_passes在 SHOW GLOBAL STATUS 輸出中看到許多每秒，您可以考慮增加該sort_buffer_size值。我sort_merge_passes在一小時內有 2 個，所以我認為sort_buffer_size足夠大。參考：Mysql 手冊
sort_buffer_size編輯 3
我已經按照@RolandoMySQLDBA 的建議修改了排序和連接緩衝區。結果顯示在下表中，但我認為
created_tmp_tables_on_disk仍然很高。更改值後，我重新啟動了 mysql 伺服器並檢查created_tmp_tables_on_disk了一天（8 小時）後的平均值併計算了平均值。還有其他建議嗎？在我看來，有些東西不適合某種容器，但我無法弄清楚它是什麼。+---------------------+-------------+-------------+--------------------+ | Tmp_table_size, | Sort_buffer | Join_buffer | No of created | | max_heap_table_size | | | tmp_tables on disk | +---------------------+-------------+-------------+--------------------+ | 125M | 256K | 256K | 100k/h | +---------------------+-------------+-------------+--------------------+ | 125M | 512K | 512K | 100k/h | +---------------------+-------------+-------------+--------------------+ | 125M | 1M | 1M | 100k/h | +---------------------+-------------+-------------+--------------------+ | 125M | 4M | 4M | 100k/h | +---------------------+-------------+-------------+--------------------+這是我的配置：
+-----------------------+-----------------------+ |DATABASE SERVER |WEB SERVER | +-----------------------+-----------------------+ |Windows Server 2008 R2 |Windows Server 2008 R2 | +-----------------------+-----------------------+ |MySQL 5.1.48 |IIS 7.5 | +-----------------------+-----------------------+ |4 Core CPU |4 Core CPU | +-----------------------+-----------------------+ |4GB RAM |8GB RAM | +-----------------------+-----------------------+附加資訊
+--------------------+---------+ |PARAM |VALUE | +--------------------+---------+ |Num of tables in Db |361 | +--------------------+---------+ |Size of database |2.5G | +--------------------+---------+ |Database engine |InnoDB | +--------------------+---------+ |Read/write ratio |3.5 | |(Innodb_data_read/ | | |innodb_data_written)| | +--------------------+---------+ |Avg table size |15k rows | +--------------------+---------+ |Max table size |744k rows| +--------------------+---------+這個設置是給我的，所以我對它的控制有限。Web 伺服器使用的 CPU 和 RAM 非常少，因此我已將該機器排除為瓶頸。大多數 MySQL 設置來自配置自動生成工具。
幾天來，我使用 PerfMon 監控了系統。由此，我得出結論，交換到磁碟的不是作業系統。
My.ini [client] port=3306 [mysql] default-character-set=utf8 [mysqld] port=3306 basedir="C:/Program Files/MySQL/MySQL Server 5.1/" datadir="D:/DBs/Data/" default-character-set=utf8 default-storage-engine=INNODB sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" max_connections=125 query_cache_size=350M table_cache=1520 tmp_table_size=125M table-definition-cache= 1024 max-heap-table-size= 32M thread_cache_size=38 MyISAM Specific options myisam_max_sort_file_size=100G myisam_sort_buffer_size=125M key_buffer_size=55M read_buffer_size=1024K read_rnd_buffer_size=256K sort_buffer_size=1024K join_buffer_size=1024K INNODB Specific options innodb_data_home_dir="D:/DBs/" innodb_additional_mem_pool_size=32M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=16M innodb_buffer_pool_size=2G innodb_log_file_size=407M innodb_thread_concurrency=8
看了下
my.ini，有兩個建議建議 #1
我會在你的
my.inisort_buffer_size=4M join_buffer_size=4M這將使一些連接和排序留在記憶體中。當然，一旦 a
JOIN或 anORDER BY需要超過4M，它將作為 MyISAM 表分頁到磁碟。如果您無法登錄 as
root@localhost，請使用以下命令重新啟動 mysqlC:\> net stop mysql C:\> net start mysql如果您可以作為 root@localhost 登錄，則無需重新啟動 mysql 即可使用這些設置。
只需在 MySQL 客戶端中執行它：
SET @FourMegs = 1024 * 1024 * 4; SET GLOBAL sort_buffer_size = @FourMegs; SET GLOBAL join_buffer_size = @FourMegs;建議 #2
由於您的數據在 Drive 上
D:，因此您可能在 Drive 上有 Disk I/OC:。請執行此查詢：
mysql> show variables like 'tmpdir'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tmpdir | C:\Windows\TEMP | +---------------+-----------------+ 1 row in set (0.00 sec)由於我在桌面上使用預設值執行 mysql，因此我的臨時表正在寫入 Drive
C:。如果 Drive D 是比 Drive 更好的磁碟，也許您可以通過如下設置tmpdirC:將臨時表映射到 Drive ：D:``my.initmpdir="D:/DBs/"您將不得不重新啟動 mysql，因為tmpdir不是動態變數。
試一試 ！！！
更新 2013-11-29 10:09 EST
建議#3
鑑於 MySQL 在 Windows 中執行並且您無法觸及核心包中的查詢，我有兩個想法必須一起完成。
想法 #1：將數據庫移動到 Linux 機器
你應該能夠
- 設置 Linux 機器
- 在 Linux 機器上安裝 MySQL
- 在 Windows 中為 MySQL 啟用二進制日誌記錄
- mysqldump 數據庫到文本 SQL 文件
- 將 SQL 文件載入到在 Linux 中執行的 MySQL
- 設置從 MySQL/Windows 到 MySQL/Linux 的複制
IDEA #2：重新配置 Moodle 以指向 Linux 機器
Moodle 最初是為 LAMP 設計的。只需將配置文件更改為指向 Linux 機器而不是 localhost。
這是關於設置 MySQL 的舊 Moodle 2.3 文件的連結：http: //docs.moodle.org/23/en/Installing_Moodle#Create_an_empty_database
我相信最新的文件也是可用的。
將數據庫遷移到 Linux 有什麼意義？？？
這對臨時表的情況有什麼幫助？？？
然後，我建議將 RAM 磁碟設置為臨時表的目標文件夾
Jan 04, 2013:是否有 MySQL 引擎或技巧來避免將這麼多臨時表寫入磁碟？Dec 17, 2012:為什麼 MySQL 會產生這麼多的臨時 MYD 文件？（實際說明）Nov 30, 2012:同時創建多個mysql臨時表不好嗎？臨時表的創建仍然會發生，但它會被寫入 RAM 而不是磁碟。減少磁碟 I/O。
更新 2013-11-29 11:24 EST
建議 #4
我建議使用快速 RAID-0 磁碟 (32+ GB) 重新訪問 SUGGESTION #2，將其配置為 Drive T: (T for Temp)。安裝此類磁碟後，將其添加到
my.ini：[mysqld] tmpdir="T:\"MySQL 需要重新啟動，使用
net stop mysql net start mysql順便說一句，我故意說 RAID-0，以便您可以通過 RAID-1、RAID-10 獲得良好的寫入性能。tmp 表磁碟不是我要冗餘的東西。
如果不像@RaymondNijland 評論的那樣優化查詢，您將無法以任何方式減少臨時表創建計數。
SUGGESTION #3並SUGGESTION #4提供加速臨時表創建和臨時表 I/O 作為唯一選擇。