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 <[email protected]> > 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 <[email protected]> > University of Iceland Computing Services > -- > Anna Jonna Armannsdottir <[email protected]> > > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
