maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

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@xxxxxxxxx> 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@xxxxx>
> University of Iceland Computing Services
> -- 
> Anna Jonna Armannsdottir <annajonna@xxxxxxxxx>
> 
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp