[精讚] [會員登入]
2389

[Mysql/MariaDB] 使用Memory引擎修改及設定最大值的方法

Mysql/MariaDB 使用Memory引擎預設是16MB,修改及設定最大值的方法。

此文完整連結 http://n.sfs.tw/10639

複製連結 [Mysql/MariaDB] 使用Memory引擎修改及設定最大值的方法@新精讚
(文章歡迎轉載,務必尊重版權註明連結來源)
2017-01-22 14:12:57 最後編修
2017-01-19 23:55:04 By 瘦河馬
 

Mysql的Memory引擎是使用記憶體當作存放資料的地方,好處是讀寫都非常快速,適合需要快速讀寫的資料存放需求。但是既然是使用記憶體,就會在每次重啟mysql或重新開機(也算重啟)後清空他的內容。因此不能放永久性或太重要的資料,但是拿來放臨時性(例如ip位址)就非常適合。

當容量超過上限時出錯

Error Number: 1114

The table 'your_table' is full

查看最大值

SELECT @@max_heap_table_size

SELECT @@tmp_table_size

SHOW variables like 'max_heap_table_size'

SHOW variables like 'tmp_table_size'

單位是bytes,預設這兩個值大小都是 16MB

max_heap_table_size 和 tmp_table_size 的意義

1. memory 引擎表格的大小由這兩個值的最小值共同決定

2. max_heap_table_size 決定你的表格最大容量,單位是bytes,

3. tmp_table_size 決定暫存在記憶體中臨時表格的最大容量,這數值和你建立的Momory引擎表格最大容量無關。

4. 如果你的max_heap_table_size大於tmp_table_size,而且容量已超過tmp_table_size,表格會自動轉成 myisam 的型態儲存在磁碟中。但是事實上我沒有觀察到官網所說的現象,就算我把tmp_table_size設的極小,寫入還是會寫到 max_heap_table_size的大小限制為止,於我我研究了一下mysql的官網,發現只有部分的情況mysql會使用temporary table[3],這時temp_table才會有作用,沒使用temporary table 的時候,這個值並不會影響到儲存的上限。

會用到臨時表的情況有使用union, group_concat, group by, distinct時等等。因此,我們不必要把這個值和 max_heap_table_size 設成一樣,

要看有沒有用到的情況,可以使用 explain table_name的指令,看看extra的欄位有沒有註明。

4. 相反的,如果你的tmp_table_size大於max_heap_table_size,而且容量已超過max_heap_table_size,資料庫就會出現上面的1114錯誤,並停止寫入。

5.如果你修改這兩個值但不重新啟動Mysql,你原有的Momory表格不會有任何改變或影響。換句話說,你沒有辦法改運作中的Momory表格上限,除非你要新建或是重啟資料庫,但代價就是重啟後原有的資料就消失。

6. 要改變上限但不要重啟的方法,就是改變完後新建一個表格,再把資料移進去。

執行期修改最大值的方法

設定30MB
SET tmp_table_size = 1024 * 1024 * 30

設定32MB
SET max_heap_table_size = 1024 * 1024 * 32;

* 你的設定不會套用到已存在的表格,除非你把內容清空(truncate),重建create或是修改alter

現存的表格不重啟要增加上限的方法,照抄[1]的範例

CREATE TABLE sns_memory SELECT * FROM sns WHERE 1=2;
ALTER TABLE sns_memory ENGINE=MEMORY;
INSERT INTO sns_memory SELECT * FROM sns;
DROP TABLE sns;
ALTER TABLE sns_memory RENAME sns;

修改全域設定

執行期設定沒什麼意義,除非你只是要臨時增加容量,永久的全域設定比較會常用到

不重新啟動的設定法,會套用到所有資料庫的所有Memory表格,但還是得把內容清空(truncate),重建create或是修改alter才會生效

SET global tmp_table_size=1024*1024*20;

SET global max_heap_table_size=1024*1024*32;

重啟的設定法,直接修改 my.cnf

[mysqld]
tmp_table_size = 9M
max_heap_table_size = 18M

重啟後生效

參考資料

[1] http://stackoverflow.com/questions/9842720/how-to-make-the-mysql-memory-engine-store-more-data

[2] http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_heap_table_size

[3] http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html

你可能感興趣的文章

[Mysql/MariaDB] 清除及設定 MySQL 的死連接 mysql 伺服器若有太多的連接,無疑的會耗用非常多的資源,若資源耗盡會影響新的連線,勢必要做一些調整

[Mysql] 資料型態int, float, double, text, char, varchar, blob大小 Mysql中資料型態int, float, text, char, varchar, blob大小

[MYSQL] 查看資料表狀態 要看資料表的狀態,不是結構喔,以前我都會用 procedure analyse(): SELECT `colname`

兩次使用InnoDB的慘痛經驗 Mysql 的Innodb引擎雖然好用,但是我得說說我兩次的慘痛經驗,這讓我考慮以後可能不會再使用innodb了

[Mysql/MariaDB] 表格的引擎 (engine):查看及修改 MySQL表格引擎預設為 MyISAM,但在4.0以上的mysql 其實支援不只一種引擎,各有優缺點,這篇介紹引擎的操作。

[Mysql] Trigger 觸發使用方法 trigger 在資料庫的使用上,具有極大的方便性,該如何使用?

[MySQL] console mode 的亂碼處理 使用MySQL的console mode如果出現亂碼 在,要怎麼處理?

[mysqldump] Mysqldump時出現記憶體錯誤Out of memory 當進行 mysqldump 時出現 Out of memory (Needed xxxxx bytes) 的錯誤,該如何處理?

[Mysql/Mariadb] GROUP_CONCAT 函數 MySQL 中和 group by 一起用的函式,如果我們要算數值的總和或平均,可以使用 sum() 或 avg() 。但是我們如果要在 group by 中把字串「黏起來」,不是使用 concat(

[Mysql/Mariadb] Left Join 和 Right Join 合併表單 SQL語法中的 Left Join 和 Right Join 很常用,但是我常會忘記,所以寫下來備忘。

我有話要說


限制:留言最高字數1000字,超過部分會被截掉。請注意:留言不可帶有網址,會被濾掉。 限制:未登入訪客,每則留言間隔需超過10分鐘,每日最多5則留言。

訪客留言

[無留言]

隨機好文

HP SAS硬碟leds燈號說明 HP SAS硬碟leds燈號(hp g7/g6系統適用)說明

UTF8中文字/全形一覽 快速查詢urf-8的中文字,共計13246中文字(5401常用字+7652罕用字+日文或編號),292全形符號,27半形符號。

[CodeIgniter 3] 資料庫的使用方法整理1/2 --Select的使用 [CodeIgniter 3] 資料庫的使用方法整理:Select的使用

正則表達式:Email名稱防止連續輸入點(.)及下底線(_) Email的名稱中要允許特殊符號,但又不允許連續出現的正則表達式

[NetBeans] 使用git複製別人的專案 NetBeans使用git複製別人的專案及版本控制