Re: Mysql using 10GB more in ps aux than in MysqlTuner

[Shawn Green]

Hello Trebonius

If those are not static allocations from mysqld for global buffers, then perhaps that extra 10GB is tied up in the per-query-per-session buffers instead?

For example, you list these values
max_heap_table_size = 4G
max_connections = 256

That means you can have up to 256 sessions each create as many 4GB tables as they want using the MEMORY storage engine. If they all created just one table with the max size you permit, you would need 1TB of RAM to hold them all.

The MEMORY storage engine is also used internally for per-query purposes (if the data to be processed is supported by the engine). It is possible that one command (like a SELECT) could create more than one intrinsic temporary table and each of them could become as large as 4GB according to your settings.

More details are in the manual:
https://dev.mysql.com/doc/refman/5.7/en/memory-use.html
https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_tmp_table_size
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_heap_table_size