maria-discuss team mailing list archive

[Anna Jonna Armannsdottir <annajonna@xxxxxxxxx>]

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@xxxxx>
University of Iceland Computing Services
-- 
Anna Jonna Armannsdottir <annajonna@xxxxxxxxx>