MySQL Memory (RAM) Calculator

MySQL Memory (RAM) Calculator

This tool will help you calculate the amount of memory RAM that MySQL should use depends on your configuration in “my.cnf” file.

MySQL Memory (RAM) Calculator

MySQL Server System Variables Information

MySQL Server System Variables
Variables Information Details
key_buffer_size
Command-Line Format --key_buffer_size=#
System Variable Name key_buffer_size
Variable Scope Global
Dynamic Variable Yes
Permitted Values Type numeric
Default 8388608
Min Value 8
Max Value 4294967295
Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.The maximum permissible setting for key_buffer_size is 4GB–1 on 32-bit platforms. As of MySQL 5.0.52, larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB–1 with a warning). The effective maximum size might be less, depending on your available physical RAM and per-process RAM limits imposed by your operating system or hardware platform. The value of this variable indicates the amount of memory requested. Internally, the server allocates as much memory as possible up to this amount, but the actual allocation might be less. You can increase the value to get better index handling for all reads and multiple writes; on a system whose primary function is to run MySQL using the MyISAM storage engine, 25% of the machine’s total memory is an acceptable value for this variable. However, you should be aware that, if you make the value too large (for example, more than 50% of the machine’s total memory), your system might start to page and become extremely slow. This is because MySQL relies on the operating system to perform file system caching for data reads, so you must leave some room for the file system cache. You should also consider the memory requirements of any other storage engines that you may be using in addition to MyISAM. For even more speed when writing many rows at the same time, use LOCK TABLES.
query_cache_size
Command-Line Format --query_cache_size=#
System Variable Name query_cache_size
Variable Scope Global
Dynamic Variable Yes
Permitted Values (32-bit platforms) Type numeric
Default 0
Min Value 0
Max Value 4294967295
Permitted Values (64-bit platforms) Type numeric
Default 0
Min Value 0
Max Value 18446744073709547520
The amount of memory allocated for caching query results. The default value is 0, which disables the query cache. The permissible values are multiples of 1024; other values are rounded down to the nearest multiple. Note that query_cache_size bytes of memory are allocated even if query_cache_type is set to 0The query cache needs a minimum size of about 40KB to allocate its structures. (The exact size depends on system architecture.) If you set the value of query_cache_size too small, a warning will occur.
tmp_table_size
Command-Line Format --tmp_table_size=#
System Variable Name tmp_table_size
Variable Scope Global, Session
Dynamic Variable Yes
Permitted Values (<= 5.0.85) Type numeric
Default 33554432
Min Value 1024
Max Value 4294967295
Permitted Values (>= 5.0.86) Type numeric
Default 33554432
Min Value 1024
Max Value 9223372036854775807
The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory. This variable does not apply to user-created MEMORY tables.You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.
innodb_buffer_pool_size
Command-Line Format --innodb_buffer_pool_size=#
System Variable Name innodb_buffer_pool_size
Variable Scope Global
Dynamic Variable No
Permitted Values Type numeric
Default 8388608
Min Value 1048576
The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 8MB. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system. Also, the time to initialize the buffer pool is roughly proportional to its size. On large installations, this initialization time may be significant. For example, on a modern Linux x86_64 server, initialization of a 10GB buffer pool takes approximately 6 seconds
innodb_additional_mem_pool_size
Command-Line Format --innodb_additional_mem_pool_size=#
System Variable Name innodb_additional_mem_pool_size
Variable Scope Global
Dynamic Variable No
Permitted Values Type numeric
Default 1048576
Min Value 524288
Max Value 4294967295
The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures. The more tables you have in your application, the more memory you need to allocate here. If InnoDB runs out of memory in this pool, it starts to allocate memory from the operating system and writes warning messages to the MySQL error log. The default value is 1MB.
innodb_log_buffer_size
Command-Line Format --innodb_log_buffer_size=#
System Variable Name innodb_log_buffer_size
Variable Scope Global
Dynamic Variable No
Permitted Values Type numeric
Default 1048576
Min Value 1048576
Max Value 4294967295
The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 1MB. Sensible values range from 1MB to 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.
max_connections
Command-Line Format --max_connections=#
System Variable Name max_connections
Variable Scope Global
Dynamic Variable Yes
Permitted Values (<= 5.1.14) Type numeric
Default 100
Permitted Values (>= 5.1.15) Type numeric
Default 151
Min Value 1
Max Value 16384
Permitted Values (>= 5.1.17) Type numeric
Default 151
Min Value 1
Max Value 100000
The maximum permitted number of simultaneous client connections. By default, this is 151, beginning with MySQL 5.1.15. (Previously, the default was 100.)Increasing this value increases the number of file descriptors that mysqld requires.
sort_buffer_size
Command-Line Format --sort_buffer_size=#
System Variable Name sort_buffer_size
Variable Scope Global, Session
Dynamic Variable Yes
Permitted Values (<= 5.1.22) Type numeric
Default 2097144
Min Value 32768
Max Value 4294967295
Permitted Values (Windows, >= 5.1.23) Type numeric
Default 2097144
Min Value 32768
Max Value 4294967295
Permitted Values (Other, 32-bit platforms, >= 5.1.23) Type numeric
Default 2097144
Min Value 32768
Max Value 4294967295
Permitted Values (Other, 64-bit platforms, >= 5.1.23) Type numeric
Default 2097144
Min Value 32768
Max Value 18446744073709551615
Each session that needs to do a sort allocates a buffer of this size. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimizationIf you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing. The entire buffer is allocated even if it is not all needed, so setting it larger than required globally will slow down most queries that sort. It is best to increase it as a session setting, and only for the sessions that need a larger size. On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload. The maximum permissible setting for sort_buffer_size is 4GB–1. As of MySQL 5.1.23, larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB–1 with a warning).
read_buffer_size
Command-Line Format --read_buffer_size=#
System Variable Name read_buffer_size
Variable Scope Global, Session
Dynamic Variable Yes
Permitted Values Type numeric
Default 131072
Min Value 8200
Max Value 2147479552
Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not a multiple of 4KB, its value will be rounded down to the nearest multiple of 4KB.This option is also used in the following context for all search engines:

  • For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.
  • For bulk insert into partitions.
  • For caching results of nested queries.

and in one other storage engine-specific way: to determine the memory block size for MEMORY tables. The maximum permissible setting for read_buffer_size is 2GB.

read_rnd_buffer_size
Command-Line Format --read_rnd_buffer_size=#
System Variable Name read_rnd_buffer_size
Variable Scope Global, Session
Dynamic Variable Yes
Permitted Values Type numeric
Default 262144
Min Value 8200
Max Value 2147483647
When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks.Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries. The maximum permissible setting for read_rnd_buffer_size is 2GB.
join_buffer_size
Command-Line Format --join_buffer_size=#
System Variable Name join_buffer_size
Variable Scope Global, Session
Dynamic Variable Yes
Permitted Values (<= 5.1.22) Type numeric
Default 131072
Min Value 8200
Max Value 4294967295
Permitted Values (Windows, 32-bit platforms, >= 5.1.23) Type numeric
Default 131072
Min Value 8200
Max Value 4294967295
Permitted Values (Windows, 64-bit platforms, >= 5.1.23) Type numeric
Default 131072
Min Value 8228
Max Value 4294967295
Permitted Values (Other, 32-bit platforms, >= 5.1.23) Type numeric
Default 131072
Min Value 8200
Max Value 4294967295
Permitted Values (Other, 64-bit platforms, >= 5.1.23) Type numeric
Default 131072
Min Value 8228
Max Value 18446744073709547520
The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.There is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it. The maximum permissible setting for join_buffer_size is 4GB–1. As of MySQL 5.1.23, larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB–1 with a warning).
thread_stack
Command-Line Format --thread_stack=#
System Variable Name thread_stack
Variable Scope Global
Dynamic Variable No
Permitted Values (32-bit platforms) Type numeric
Default 196608
Min Value 131072
Max Value 4294967295
Block Size 1024
Permitted Values (64-bit platforms) Type numeric
Default 262144
Min Value 131072
Max Value 18446744073709547520
Block Size 1024
The stack size for each thread. Many of the limits detected by the crash-me test are dependent on this value.The default of 192KB (256KB for 64-bit systems) is large enough for normal operation. If the thread stack size is too small, it limits the complexity of the SQL statements that the server can handle, the recursion depth of stored procedures, and other memory-consuming actions.
binlog_cache_size
Command-Line Format --binlog_cache_size=#
System Variable Name binlog_cache_size
Variable Scope Global
Dynamic Variable Yes
Permitted Values (32-bit platforms) Type numeric
Default 32768
Min Value 4096
Max Value 4294967295
Permitted Values (64-bit platforms) Type numeric
Default 32768
Min Value 4096
Max Value 18446744073709547520
The size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (–log-bin option). If you often use large, multiple-statement transactions, you can increase this cache size to get better performance. The Binlog_cache_use and Binlog_cache_disk_use status variables can be useful for tuning the size of this variable.