MySQL 5.6.17 打開具有多個分區的表時記憶體不足

我們目前正在 Amazon db.m1.large RDS 實例上設置 MySQL 數據庫（版本 5.6.17），用於儲存時間序列數據。對象的每個時間序列屬性都儲存在單獨的 InnoDB 表中（例如，建築物 42 的內部溫度儲存在表中building__inside_temperature）。大約有 100 個時間序列表，每個表包含三列：

• 以毫秒為單位的時間戳bigint
• 對象 ID (as char(22))
• 數值 (as double)

主鍵是(timestamp, ID). 我們在時間戳列上使用範圍分區，每天一個分區，持續 2.5 年，並​​在 ID 上使用 8 個子分區的鍵子分區。這會產生大約 800 個分區，每個表有 8 個子分區。我們使用innodb_file_per_table=OFF，因為否則會有太多打開的文件。其他 MySQL 參數保持不變。

當並行連接 100 個執行緒（和連接）並打開時序表時，MySQL 實例記憶體不足。程序列表顯示崩潰前處於“正在打開表”狀態的所有執行緒。隨著更少的分區或更少的並行連接打開表，問題就消失了。為此，我們使用以下mysqlslap命令： mysqlslap --create=create_timeseries.sql --delimiter=";" --query=insert_datapoint.sql --number-of-queries=1 --concurrency=100

文件內容在這裡： http: //pastebin.com/x4YMMF3h

錯誤如下：

2014-08-12 06:25:53 26178 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 1208.

2014-08-12 06:25:53 2b119160d7002014-08-12 06:25:53 26178 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 1248.

2014-08-12 06:25:53 2b11913c4700 InnoDB: Assertion failure in thread 47354451085056 in file ha_innodb.cc line 17080
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
06:25:53 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=102
max_threads=604
thread_count=102
connection_count=102
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 333908 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x13aeb5a80
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
2014-08-12 06:25:53 26178 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 3784.

2014-08-12 06:25:53 2b1192035700 InnoDB: Assertion failure in thread 47354464130816 in file ha_innodb.cc line 17080
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

http://dev.mysql.com/doc/refman/5.6/en/table-cache.html只是告訴，每個 MySQL 執行緒獨立打開一個表。

1. 為什麼打開具有許多分區的表會佔用大量記憶體？
2. 如何查看一個表打開了多少次以及消耗了多少記憶體？show open tables from mysqlslap;沒有給我每個執行緒的資訊，也沒有記憶體消耗。

我發現由於這個錯誤，MySQL 5.6.17 每個打開的子分區使用 6kb 記憶體。這將導致 100 個連接使用大約 500 GB 的記憶體，每個連接有 100 個表，每個表有 800 個分區，每個有 8 個子分區。

引用自：https://dba.stackexchange.com/questions/73917