MySQL 版本之間的 DB 磁碟空間需求比較
我有 2 個安裝了 MySQL 的實驗室。數據庫由某些內部程序動態創建和刪除。InnoDB 是使用的引擎。
在一個實驗室中,安裝了 MySQL 版本5.0.77。每個 DB 的 DB 目錄平均約為230 KB。
select engine from information_schema.tables where table_schema = 'test_1389772840' and table_name = 'test'; +--------+ | engine | +--------+ | InnoDB | +--------+ 1 row in set **(0.82 sec)**
在另一個實驗室中,安裝了 MySQL 版本5.6.11。每個 DB 的 DB 目錄平均約為500 MB。
select engine from information_schema.tables where table_schema = 'test_1389586231' and table_name = 'test'; +--------+ | engine | +--------+ | InnoDB | +--------+ 1 row in set **(0.00 sec)**
綜上所述,兩個實驗室的數據庫和表都差不多,使用相同的引擎,但舊版本速度慢,磁碟空間要求少,而新版本速度快,磁碟空間要求大。
這是預期的嗎?為什麼緩慢?為什麼需要巨大的空間?如何將查詢速度和磁碟使用率提高到好的水平?
我檢查了 MySQL 文件,但沒有幫助。它建議優化表(此處為http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html),但磁碟使用量沒有變化。
我找到了一些我嘗試配置的壓縮參數(這裡https://blogs.oracle.com/mysqlinnodb/entry/innodb_compression_improvements_in_mysql ):
在 5.0.77:
show variables like '%compr%' ; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | have_compress | YES | | slave_compressed_protocol | OFF | +---------------------------+-------+
在 5.6.11 更改值之前:
show variables like '%compr%' ; +------------------------------------------+-------+ | Variable_name | Value | +------------------------------------------+-------+ | have_compress | YES | | innodb_compression_failure_threshold_pct | 5 | | innodb_compression_level | 6 | | innodb_compression_pad_pct_max | 50 | | innodb_log_compressed_pages | ON | | slave_compressed_protocol | OFF | +------------------------------------------+-------+
在 5.6.11 更改值後:
show variables like '%compr%' ; +------------------------------------------+-------+ | Variable_name | Value | +------------------------------------------+-------+ | have_compress | YES | | innodb_compression_failure_threshold_pct | 0 | | innodb_compression_level | 9 | | innodb_compression_pad_pct_max | 0 | | innodb_log_compressed_pages | OFF | | slave_compressed_protocol | OFF | +------------------------------------------+-------+
我將在接下來的幾天內監控平均大小。
必須注意一件事,您可能從未意識到:innodb_file_per_table選項的設置將決定每個數據庫文件夾中有多少空間。
首先看一下 InnoDB 架構
請注意系統表空間(已知在作業系統中名為 ibdata1)。
禁用 innodb_file_per_table(預設為 5.0),所有 InnoDB 表的數據和索引頁都儲存在 ibdata1 中。創建 table
mydb.mytable
時,您將獲得以下資訊:
/var/lib/mysql/mydb/mytables.frm
- 數據和索引頁
mydb.mytable
在 ibdata1 內啟用 innodb_file_per_table(5.6 中的預設值)後,所有 InnoDB 表的數據和索引頁都在 ibdata1 之外。創建 table
mydb.mytable
時,您將獲得以下資訊:
/var/lib/mysql/mydb/mytables.frm
- 數據和索引頁
mydb.mytable
儲存在/var/lib/mysql/mydb/mytables.ibd
概括
不設置 innodb_file_per_table 只儲存數據
- MySQL 5.0 下的數據庫將只有
.frm
其 InnoDB 表的文件。因此,每個數據庫中只有千字節的文件。- MySQL 5.6 下的數據庫將具有其 InnoDB 表
.frm
的.ibd
文件。因此,您將在每個數據庫中擁有兆字節或千兆字節的文件。