A session can have many temporary tables created by one query. They are used for materialized subqueries and for aggregation. Not all queries use a temporary table and not all temp tables are of the max size. Thus multiplying each connection by the tmp_table_size is a bad approximation, but probably the best approximation possible.

Further complicating things are MEMORY tables, but you might not use those.

Sent from my iPhone

On Apr 29, 2015, at 8:19 AM, Anna Jonna Armannsdottir annajonna@gmail.com wrote:

Hello, I am new to this list and the issue I am presenting here might surely be answered on the development list, but I do not want to impose this issue on the developers, I just hope some of them listen here.

There seems to be general aggreement on the calculation of memory consumption for MariaDB and MySQL. There is however an insignificant dispute but very important dispute over the role of tmp_table_size . https://mariadb.com/kb/en/mariadb/server-system-variables/#tmp_table_size

The dispute is whether tmp_table_size is a session variable or a kind of global variable. Thus MariaDB memory consumption is calculated as: max_connections * (sum of session variables) + (global variables)

Thus if tmp_table_size is considered to be a session variable, it will be multiplied by max_connections .

In my config the settings are: max-connections = 1000 tmp-table-size = 32M max-heap-table-size = 32M

In my case this means that MariaDB could consume 1000 + 32M + 1000 + (sum of sess.var.) + (global variables) .

This point of view was put forward in a discussion on mysql.com: QUOTE:

...

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.

https://dev.mysql.com/doc/refman/5.0/en/memory-use.html

I honestly do not believe, this the case. On this discussion list we have examples that have tmp_table_size = 256 MB and max-connections = 1000 . 256 Gigabytes!!! https://lists.launchpad.net/maria-discuss/msg02376.html

On the other hand we have sites like: http://www.mysqlcalculator.com/

This site calculates memory consumption in a way I find more agreeable, by not multiplying tmp_table_size by max_connections.

This is an issue in a Munin monitoring plugin that reports mysql connection memory. The calculation code is from lines 1941 to 1949. https://github.com/munin-monitoring/munin/blob/devel/plugins/node.d/mysql_

$data->{'mysql_connection_memory'} = $data->{'read_buffer_size'} + $data->{'read_rnd_buffer_size'} + $data->{'sort_buffer_size'} + $data->{'join_buffer_size'} + $data->{'binlog_cache_size'} + $data->{'thread_stack'} + ( $tmp_table_size >= $max_heap_table_size ? $tmp_table_size : $max_heap_table_size ) + ( $data->{'tokudb_read_buf_size'} || 0 );

# wsrep_thread_count was separated from max_connections for mariadb-5.5.38 https://mariadb.atlassian.net/browse/MDEV-6206 $data->{'mysql_connection_memory'} *= $data->{'max_connections'} + ( $data->{'wsrep_thread_count'} || 0 );

I ask for your opinion and support for correcting this issue, so that we may get correct reports in Munin.

-- Anna Jonna Armannsdottir annaj@hi.is University of Iceland Computing Services -- Anna Jonna Armannsdottir annajonna@gmail.com

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp