MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.11.4.1 MySQL のメモリーの使用方法

8.0 English
5.7 English
5.6 English
5.5 English

MySQL 5.6 リファレンスマニュアル / ... / MySQL のメモリーの使用方法

8.11.4.1 MySQL のメモリーの使用方法

次のリストに、mysqld サーバーがメモリーを使用する方法のいくつかを示します。該当する場合、メモリー使用に関連するサーバー変数の名前も示しています。

すべてのスレッドは MyISAM キーバッファーを共有し、そのサイズは key_buffer_size 変数によって決定されます。サーバーによって使用されるほかのバッファーは、必要に応じて割り当てられます。セクション8.11.2「サーバーパラメータのチューニング」を参照してください。
クライアント接続の管理に使用される各スレッドは、いくらかのスレッド固有の領域を使用します。次のリストに、これらとそれらのサイズを制御する変数を示します。
- スタック (変数 thread_stack)
- 接続バッファー (変数 net_buffer_length)
- 結果バッファー (変数 net_buffer_length)
接続バッファーと結果バッファーはそれぞれ net_buffer_length バイトに等しいサイズから開始されますが、必要に応じて max_allowed_packet バイトまで動的に拡大されます。結果バッファーは各 SQL ステートメントのあとに net_buffer_length バイトに縮小されます。ステートメントの実行中は現在のステートメント文字列のコピーも割り当てられます。
すべてのスレッドで同じベースメモリーを共有します。
スレッドが必要ない場合、それに割り当てられたメモリーが解放され、スレッドがスレッドキャッシュに戻らないかぎり、システムに返されます。その場合、メモリーは割り当てられた状態のままになります。
myisam_use_mmap システム変数を 1 に設定して、すべての MyISAM テーブルのメモリーマッピングを有効にできます。
テーブルの順次スキャンを実行する各リクエストは、read buffer (変数 read_buffer_size) を割り当てます。
行を任意の順序で読み取る場合 (たとえば、ソートに続いて)、random-read buffer (変数 read_rnd_buffer_size) を割り当てて、ディスクシークを避けることができます。
すべての結合は単一のパスで実行され、ほとんどの結合は一時テーブルも使用せずに実行できます。ほとんどの一時テーブルはメモリーベースのハッシュテーブルです。大きな行長 (すべてのカラム長の合計として算出される) を持つか BLOB カラムを含む一時テーブルはディスク上に格納されます。

内部インメモリー一時テーブルが大きくなりすぎると、MySQL は、テーブルをインメモリーから、MyISAM ストレージエンジンによって処理されるディスク上フォーマットに変更して、これを自動的に処理します。セクション8.4.4「MySQL が内部一時テーブルを使用する仕組み」に説明するように、許可される一時テーブルのサイズを増やすことができます。
ソートを実行するほとんどのリクエストは、ソートバッファーおよび結果セットサイズに応じた 0 から 2 つの一時ファイルを割り当てます。セクションB.5.4.4「MySQL が一時ファイルを格納する場所」を参照してください。
ほとんどすべての解析と計算は、スレッドローカルの再利用可能なメモリープールで実行されます。小さい項目にはメモリーオーバーヘッドが不要であるため、通常の低速メモリーの割り当てと解放が回避されます。メモリーは、予測外に大きな文字列にのみ割り当てられます。
開かれる MyISAM テーブルごとにインデックスファイルが 1 回開かれ、データファイルは同時実行中のスレッドごとに 1 回開かれます。同時スレッドごとに、テーブル構造、各カラムのカラム構造、およびサイズ 3 * N のバッファーが割り当てられます (ここで N は最大行長で、BLOB カラムをカウントしていません)。BLOB カラムには、5 から 8 バイト+ BLOB データの長さが必要です。MyISAM ストレージエンジンは、内部使用のため 1 つ余分な行バッファーを保持します。
BLOB カラムがあるテーブルごとに、大きな BLOB 値を読み取るためにバッファーが動的に拡大されます。テーブルをスキャンする場合は、最大の BLOB 値と同じ大きさのバッファーが割り当てられます。
使用中のすべてのテーブルのハンドラ構造がキャッシュに保存され、FIFO として管理されます。初期キャッシュサイズは、table_open_cache システム変数の値から取得されます。テーブルが同時に 2 つの実行中のスレッドによって使用されている場合、キャッシュにはそのテーブルの 2 つのエントリが含まれます。セクション8.4.3.1「MySQL でのテーブルのオープンとクローズの方法」を参照してください。
FLUSH TABLES ステートメントまたは mysqladmin flush-tables コマンドは、使用中でないすべてのテーブルを一度に閉じ、現在実行中のスレッドの終了時に閉じられるように使用中のすべてのテーブルをマークします。これにより、事実上ほとんどの使用中のメモリーが解放されます。FLUSH TABLES はすべてのテーブルが閉じられるまで戻りません。
GRANT、CREATE USER、CREATE SERVER、および INSTALL PLUGIN ステートメントの結果として、サーバーは情報をメモリーにキャッシュします。このメモリーは、対応する REVOKE、DROP USER、DROP SERVER、および UNINSTALL PLUGIN ステートメントによって解放されないため、キャッシュを発生させるステートメントの多数のインスタンスを実行するサーバーでは、メモリー使用量が増加します。このキャッシュされたメモリーは FLUSH PRIVILEGES で解放できます。

ps およびその他のステータスプログラムが、mysqld が大量のメモリーを使用していることをレポートすることがあります。これは、さまざまなメモリーアドレス上のスレッドスタックによって発生する可能性があります。たとえば、Solaris バージョンの ps はスタック間の未使用のメモリーが使用されているメモリーとしてカウントされます。これを確認するには、swap -s で使用可能なスワップをチェックします。いくつかのメモリーリーク検出ツール (市販とオープンソースの両方の) で mysqld をテストしているため、メモリーリークはないはずです。

PREV HOME UP NEXT

User Comments

Posted by sheila yao on October 2, 2007

I got this formula from mysql error log complaining it doesn't have enough memory to start mysqld:
key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory

I hope this document could be straight forward by providing a formula to calculate the memory usage for mysqld.

Sheila

Posted by Guy Baconniere on May 6, 2009

I use the following SQL query to guess MySQL memory usage
of MySQL unfortunately innodb_* and thread_stack are not
part of MySQL system variables so you need to fill them
manually.

Best Regards,
Guy Baconniere

--

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_additional_mem_pool_size';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
SHOW VARIABLES LIKE 'thread_stack';
SET @kilo_bytes = 1024;
SET @mega_bytes = @kilo_bytes * 1024;
SET @giga_bytes = @mega_bytes * 1024;
SET @innodb_buffer_pool_size = 2 * @giga_bytes;
SET @innodb_additional_mem_pool_size = 16 * @mega_bytes;
SET @innodb_log_buffer_size = 8 * @mega_bytes;
SET @thread_stack = 192 * @kilo_bytes;
SELECT
( @@key_buffer_size + @@query_cache_size + @@tmp_table_size
+ @innodb_buffer_pool_size + @innodb_additional_mem_pool_size
+ @innodb_log_buffer_size
+ @@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
+ @@join_buffer_size + @@binlog_cache_size + @thread_stack
) ) / @giga_bytes AS MAX_MEMORY_GB;

+---------------+
| MAX_MEMORY_GB |
+---------------+
|        3.7002 |
+---------------+

1 row in set (0.00 sec)

Posted by Lig Isler-Turmelle on March 19, 2010

I disagree with how the previous comment handles the tmp_table_size value. They treat it as a single allocation on the global scope when for memory consumption purposes it is more in line with a per thread buffer.

A single connection/query can use a single or multiple temporary tables in the duration of its processing. The connections do not use a single temporary table "area" reserved just for that purpose.

If you are going to use a formula for memory consumption, the tmp_table-size should be located with the other per thread buffers - not in the single allocation listing.

Posted by Christopher Schultz on January 30, 2013

Based upon the previous two comments, I re-worked the max-mem-usage query and made it work (there weren't enough @'s on some variables and @giga_bytes isn't defined). This returns a non-null value on MySQL 5.5.29:

SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_additional_mem_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;

Posted by Eduardo Franceschi on December 26, 2013

I wrote a shell script based on above examples. I've added a min/max memory suggestions also. The minimum memory is estimated using the Max_used_connections variable from SHOW STATUS.

#!/bin/sh

mysql -e "show variables; show status" | awk '
{
VAR[$1]=$2
}
END {
MAX_CONN = VAR["max_connections"]
MAX_USED_CONN = VAR["Max_used_connections"]
BASE_MEM=VAR["key_buffer_size"] + VAR["query_cache_size"] + VAR["innodb_buffer_pool_size"] + VAR["innodb_additional_mem_pool_size"] + VAR["innodb_log_buffer_size"]
MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["tmp_table_size"]
MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN
MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN

printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576
printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN
printf "| %40s | %18d |\n", "max_connections", MAX_CONN
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576
printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576
printf "+------------------------------------------+--------------------+\n"
}'

Posted by Shane Bester on September 2, 2014

folks above using their own formulas, don't forget about memory used by performance schema (SHOW ENGINE PERFORMANCE_SCHEMA STATUS) and global variable innodb_ft_total_cache_size.

Posted by kedar vaijanapurkar on February 27, 2015

Long back I had created a stored procedure which resides in mysql and should be as easy as giving a call to the procedure to estimate the memory usage based on Global and Per Thread Variables.

mysql> call my_memory();

+---------------------+------------+
| Parameter           | Value (M)  |
+---------------------+------------+
| Global Buffers      | 531 M      |
| Per Thread          | 1.890625 M |
| Maximum Connections | 160        |
| Total Memory Usage  | 833.5 M    |
| + Per Heap Table    | 16 M       |
| + Per Temp Table    | 26 M       |
+---------------------+------------+

Source: http://kedar.nitty-witty.com/blog/calculte-mysql-memory-usage-quick-stored-proc

(Do read the cursor declaration note in the post which may cause an error due to bug)

DELIMITER $$

DROP PROCEDURE IF EXISTS `my_memory` $$
CREATE PROCEDURE `my_memory` ()
BEGIN

DECLARE var VARCHAR(100);
DECLARE val VARCHAR(100);
DECLARE done INT;

#Variables for storing calculations
DECLARE GLOBAL_SUM DOUBLE;
DECLARE PER_THREAD_SUM DOUBLE;
DECLARE MAX_CONN DOUBLE;
DECLARE HEAP_TABLE DOUBLE;
DECLARE TEMP_TABLE DOUBLE;

#Cursor for Global Variables

#### For < MySQL 5.1
#### DECLARE CUR_GBLVAR CURSOR FOR SHOW GLOBAL VARIABLES;

#### For MySQL 5.1+
DECLARE CUR_GBLVAR CURSOR FOR SELECT * FROM information_schema.GLOBAL_VARIABLES;
#### Ref: http://bugs.mysql.com/bug.php?id=49758

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

SET GLOBAL_SUM=0;
SET PER_THREAD_SUM=0;
SET MAX_CONN=0;
SET HEAP_TABLE=0;
SET TEMP_TABLE=0;

OPEN CUR_GBLVAR;

mylp:LOOP
FETCH CUR_GBLVAR INTO var,val;
IF done=1 THEN
LEAVE mylp;
END IF;
IF var in ('key_buffer_size','innodb_buffer_pool_size','innodb_additional_mem_pool_size','innodb_log_buffer_size','query_cache_size') THEN
#Summing Up Global Memory Usage
SET GLOBAL_SUM=GLOBAL_SUM+val;
ELSEIF var in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','thread_stack','max_allowed_packet','net_buffer_length') THEN
#Summing Up Per Thread Memory Variables
SET PER_THREAD_SUM=PER_THREAD_SUM+val;
ELSEIF var in ('max_connections') THEN
#Maximum allowed connections
SET MAX_CONN=val;
ELSEIF var in ('max_heap_table_size') THEN
#Size of Max Heap tables created
SET HEAP_TABLE=val;
#Size of possible Temporary Table = Maximum of tmp_table_size / max_heap_table_size.
ELSEIF var in ('tmp_table_size','max_heap_table_size') THEN
SET TEMP_TABLE=if((TEMP_TABLE>val),TEMP_TABLE,val);
END IF;

END LOOP;
CLOSE CUR_GBLVAR;
#Summerizing:
select "Global Buffers" as "Parameter",CONCAT(GLOBAL_SUM/(1024*1024),' M') as "Value" union
select "Per Thread",CONCAT(PER_THREAD_SUM/(1024*1024),' M') union
select "Maximum Connections",MAX_CONN union
select "Total Memory Usage",CONCAT((GLOBAL_SUM + (MAX_CONN * PER_THREAD_SUM))/(1024*1024),' M') union
select "+ Per Heap Table",CONCAT(HEAP_TABLE / (1024*1024),' M') union
select "+ Per Temp Table",CONCAT(TEMP_TABLE / (1024*1024),' M') ;

END $$
DELIMITER ;