今天研究下關于mysql數據的相關參數以及innodb引擎下的共享表空間和獨立表空間介紹，參數主要包括innodb_data_file_path、innodb_data_home_dir、innodb_buffer_pool_size、innodb_buffer_pool_instances四個參數。 一：首先介紹innodb_buffer_pool_size 我們知道mysql數據庫在操作數據頁的時候，會先把數據頁pin到內存中，之后再做相關的處理，那么mysql可以使用的mysql的內存大小就是由innodb_buffer_pool_size參數控制的，這個參數主要作用是緩存innodb表的索引，數據，插入數據時的緩沖。在高并發高I/O時正確的配置非常重要，可能帶來很大的性能提升，這是InnoDB最重要的設置，對InnoDB性能有決定性的影響。默認的設置只有8M，所以默認的數據庫設置下面InnoDB性能很差。在只有 InnoDB存儲引擎的數據庫服務器上面，可以設置60-80%的內存。這個參數是非動態的，要修改這個值，需要重啟mysqld服務 # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. #innodb，不像myisam，使用緩沖池來緩存索引和 #行數據。您設置的越大，那么訪問表中的數據所需的磁盤I /o就越少。 #在專用的數據庫服務器上，您可以設置這個 #參數最多80%的計算機物理內存大小。不要設置它 #太大了，因為物理內存的競爭可能 #導致操作系統中的分頁。注意，在32位系統上 #可能僅限于每個進程的2-3.5g用戶級內存，所以不要 把它定得太高了。 二：innodb_data_home_dir和innodb_data_file_path參數 innodb_data_file_path用來指定innodb 共享 表空間文件，如果我們不在My.cnf文件中指定innodb_data_home_dir和innodb_data_file_path那么默認會在datadir目錄下創建ibdata1 作為innodb tablespace。 雖然不指定innodb_data_home_dir和指定它為空顯示是一樣的，但是作用確不一樣，如果不指定那么所有的innodb tablespace 文件只能存放在datadir目錄下。如果顯示指定為空，則可以為innodb tablespace 文件指定不同的路徑 1. 在同一個路徑下指定多個innodb tablespace 文件 1) 在my.cnf文件指定如下 [mysqld] innodb_data_file_path = ibdata1:1G;ibdata2:12M:autoextend:max:500M note：The autoextend and max attributes can be used only for the last data file in the innodb_data_file_path line. note：因為沒有指定innodb_data_home_dir，所以ibdata1和ibdata2都在datadir(innodb_data_home_dir默認值為datadir)目錄下創建 2) 在my.cnf文件指定如下 [mysqld] innodb_data_home_dir = /data/mysql/mysql3306/data innodb_data_file_path = ibdata1:1G;ibdata2:12M:autoextend:max:500M note：可以顯示的指定innodb tablespace文件的路徑，如果指定innodb_data_home_dir，那么就不能為innodb tablespace不同文件指定不同路徑 2. 在不同路徑下指定innodb tablespace 在my.cnf文件下指定 [mysqld] innodb_data_home_dir = innodb_data_file_path = ibdata1:12M;/data/mysql/mysql3306/data1/ibdata2:12M:autoextend note：如果想為innodb tablespace指定不同目錄下的文件，必須指定innodb_data_home_dir =。這個例子中會在datadir下建立ibdata1，在/data/mysql/mysql3306/data1/目錄下創建ibdata2 如果我不指定innodb_data_home_dir =，只在my.cnf文件中做如下指定： innodb_data_file_path =/data/data/ibdata1:18m;/data/data/1ibdata2:100m:autoextend:max:2000M bogon:root@~>service mysqld start Starting MySQL. ERROR! The server quit without updating PID file (/tmp/mysql.pid). 查看錯誤日志。。。如下 bogon:root@/>tail -n 100 /data/log/error.log 171127 21:08:16 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. InnoDB: File name .//data/data/ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. ##所以如果想為innodb tablespace文件指定不同路徑，那么必須在my.cnf文件中指定innodb_data_home_dir = 注意： 1）設定文件大小時一定要注意你的OS是否有最大文件尺寸為2GB的限制！InnoDB是不會注意你的OS文件尺寸限制的， 在一些文件系統中你可能要設定最大容量限制： innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M 2）如果是修改共享表空間的文件個數，注意在描述原來文件的時候，一定要找到該文件，查看他當前的實際大小，然后寫在innodb_data_file_path =哪個文件的后面。否則會報錯。 三：innodb_buffer_pool_instances innodb緩沖池被劃分為多個內存緩沖池的數量。對于具有多GB范圍的緩沖池的系統，將緩沖池劃分為多個緩沖池可以提高并發性，減少對不同線程讀取和寫入到緩存時頁面的爭用。從緩沖池中存儲或讀取的每個頁面都隨機分配給其中一個緩沖池實例，使用哈希函數。每個緩沖池管理自己的自由列表、刷新列表、LRU和連接到緩沖池的所有其他數據結構，并由自己的buffer pool mutex（互斥體）保護。 只有當您將innodb _ buffer _ pool _size設置為1gb或更高的大小時，此選項才會生效。所有緩沖池的大小之和是您指定的總的緩存池大小。為了獲得最佳效率，請指定innodb _ buffer _ pool _instances和innodb _ buffer _ pool _size的組合，以便每個緩沖池實例至少為1gb。 在MySQL 5.6.6之前,默認是1， 默認值 在MySQL 5.6.6和更高版本在32位系統取決于innodb_buffer_pool_size的值 1 innodb_buffer_pool_instances可以開啟多個內存緩沖池，把需要緩沖的數據hash到不同的緩沖池中，這樣可以并行的內存讀寫。 2 innodb_buffer_pool_instances 參數顯著的影響測試結果，特別是非常高的 I/O 負載時。 3 實驗環境下， innodb_buffer_pool_instances=8 在很小的 buffer_pool 大小時有很大的提升，而使用大的 buffer_pool 時，innodb_buffer_pool_instances=1 的表現最棒。 最后說說mysql的innodb引擎下的表空間： 一、針對innodb引擎，分為兩種情況一種是共享表空間存儲方式，還有一種是獨享表空間存儲方式。 共享表空間： Innodb的所有數據保存在一個單獨的表空間里面，而這個表空間可以由很多個文件組成，一個表可以跨多個文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。從Innodb的官方文檔中可以看到，其表空間的最大限制為64TB，也就是說，Innodb的單表限制基本上也在64TB左右了，當然這個大小是包括這個表的所有索引等其他相關數據。 獨立表空間： 而當使用獨享表空間來存放Innodb的表的時候，每個表的數據以一個單獨的文件來存放，這個時候的單表限制，又變成文件系統的大小限制了 針對myasam引擎： MySQL單表最大限制就已經擴大到了64PB了（官方文檔顯示）。也就是說，從目前的技術環境來看，MySQL數據庫的MyISAM存儲 引擎單表大小限制已經不是有MySQL數據庫本身來決定，而是由所在主機的OS上面的文件系統來決定了。 二、查看數據庫的表空間 MySQL [(none)]> show variables like 'innodb_data%'; +-----------------------+----------------------------------------------------------------------+ | Variable_name | Value | +-----------------------+----------------------------------------------------------------------+ | innodb_data_file_path | /data/data/ibdata1:100m;/data/data1/ibdata2:100m:autoextend:max:2000M | | innodb_data_home_dir | | +-----------------------+----------------------------------------------------------------------+ 表空間有四個文件組成：ibdata1、ibdata2每個文件的大小為100M，當ibdata1文件滿了的時候，ibdata2會自動擴展； 當前的存儲空間滿的時候，可以在其他的磁盤添加數據文件，語法如下：語法如下所示： pathtodatafile:sizespecification;pathtodatafile:sizespec;.;pathtodatafile:sizespec[:autoextend[:max:sizespecification]] 如果用 autoextend 選項描述最后一個數據文件，當 InnoDB 用盡所有表自由空間后將會自動擴充最后一個數據文件，每次增量為 8 MB。示例： 不管是共享表空間和獨立表空間，都會存在innodb_data_file文件，因為這些文件不僅僅要存放數據，而且還要充當著類似于ORACLE的UNDO表空間等一些角色。 三、共享表空間優缺點 既然Innodb有共享表空間和獨立表空間兩種類型，那么這兩種表空間存在肯定都有時候自己的應用的場景，存在即合理。以下是摘自mysql官方的一些介紹： 3.1 共享表空間的優點 表空間可以分成多個文件存放到各個磁盤，所以表也就可以分成多個文件存放在磁盤上，表的大小不受磁盤大小的限制，方便擴容，放到多個磁盤磁盤上可以分散io,提高性能。 3.2 共享表空間的缺點 所有的數據和索引存放到一個文件，雖然可以把一個大文件分成多個小文件，但是多個表及索引在表空間中混合存儲，當數據量非常大的時候，表做了大量刪除操作后表空間中將會有大量的空隙，特別是對于統計分析，日志系統這類應用最不適合用共享表空間，對于經常刪除操作的這類應用最不適合用共享表空間。 共享表空間分配后不能回縮：當出現臨時建索引或是創建一個臨時表的操作表空間擴大后，就是刪除相關的表也沒辦法回縮那部分空間了（可以理解為oracle的表空間10G，但是才使用10M，但是操作系統顯示的表空間為10G），進行數據庫的冷備很慢； 四、獨立表空間的優缺點 4.1 獨立表空間的優點 每個表都有自已獨立的表空間，每個表的數據和索引都會存在自已的表空間中，可以實現單表在不同的數據庫中移動。 空間可以回收（除drop table操作外，表空不能自已回收） Drop table操作自動回收表空間，如果對于統計分析或是日志表，刪除大量數據后可以通過:alter table TableName engine=innodb;回縮不用的空間。 對于使innodb-plugin的Innodb使用turncate table也會使空間收縮。 對于使用獨立表空間的表，不管怎么刪除，表空間的碎片不會太嚴重的影響性能； 4.2 獨立表空間的缺點 單表增加過大，當單表占用空間過大時，存儲空間不足，只能從操作系統層面思考解決方法，比如做軟連接； 五、共享表空間和獨立表空間之間的轉換 5.1 查看當前數據庫的表空間管理類型 mysql> show variables like "innodb_file_per_table"; ON代表獨立表空間管理，OFF代表共享表空間管理；（查看單表的表空間管理方式，需要查看每個表是否有單獨的數據文件） 5.2 修改數據庫的表空間管理方式 修改innodb_file_per_table的參數值即可，但是修改不能影響之前已經使用過的共享表空間和獨立表空間； innodb_file_per_table=1 為使用獨占表空間 innodb_file_per_table=0 為使用共享表空間 題外話 碎片的產生： MySQL具有相當多不同種類的存儲引擎來實現列表中的數據存儲功能。每當MySQL從你的列表中刪除了一行內容，該段空間就會被留空。而在一段時間內的大量刪除操作，會使這種留空的空間變得比存儲列表內容所使用的空間更大。當MySQL對數據進行掃描時，它掃描的對象實際是列表的容量需求上限，也就是數據被寫入的區域中處于峰值位置的部分。如果進行新的插入操作，MySQL將嘗試利用這些留空的區域，但仍然無法將其徹底占用。 針對oracle行鏈接和行遷移：新insert的時候，可能發生行鏈接；update已經存在的行時，可能發生行遷移； 行鏈接產生在第一次插入數據的時候如果一個block不能存放一行記錄的情況下。這種情況下，Oracle將使用鏈接一個或者多個在這個段中保留的block存儲這一行記錄，行鏈接比較容易發生在比較大的行上，例如行上有LONG、LONG RAW、LOB等數據類型的字段，這種時候行鏈接是不可避免的會產生的。 當一行記錄初始插入的時候事可以存儲在一個block中的，由于更新操作導致行長增加了，而block的自由空間已經完全滿了，這個時候就產生了行遷移。在這種情況下，Oracle將會遷移整行數據到一個新的block中（假設一個block中可以存儲下整行數據），Oracle會保留被遷移行的原始指針指向新的存放行數據的block，這就意味著被遷移行的ROW ID是不會改變的。 當發生了行遷移或者行鏈接，對這行數據操作的性能就會降低，因為Oracle必須要掃描更多的block來獲得這行的信息。 總結：原來mysql也有類似于oracle的undo表空間，也就是innodb共享表空間（ibdata1），默認就是自動擴展的，每次擴展8M，共享表空間類似于oracle的表空間，可以實現把mysql的數據和索引放到不同的磁盤下，分散io，進而提高性能，就是不像oracle那樣可以動態的給表空間添加文件，mysql如果想要添加共享表空間的文件，需要重啟mysql服務才能生效。
