DROP PROCEDURE IF EXISTS mysqlmem;
DELIMITER go
CREATE PROCEDURE mysqlmem( detail bool )
BEGIN
Declare mb Char(3) Default ' Mb';
Declare global_sum, per_thread_sum, max_conn, heap_table, temp_table
BigInt Unsigned Default 0;
Set global_sum = ( Select @@key_buffer_size + @@innodb_buffer_pool_size +
@@innodb_log_buffer_size +
@@innodb_ft_total_cache_size + @@tmp_table_size +
@@thread_stack
);
If ( select @@log_bin ) > '0' Then
Set global_sum = global_sum +
( select @@binlog_cache_size ) +
( select @@binlog_stmt_cache_size );
If detail Then
Select @@key_buffer_size, @@innodb_buffer_pool_size,
@@innodb_log_buffer_size, @@innodb_ft_total_cache_size;
Select @@tmp_table_size, @@thread_stack,
@@binlog_cache_size, @@binlog_stmt_cache_size;
End If;
ElseIf detail Then
Select @@key_buffer_size, @@innodb_buffer_pool_size,
@@innodb_log_buffer_size, @@innodb_ft_total_cache_size;
Select @@tmp_table_size, @@thread_stack;
End if;
If Left( Version(), 1 ) = '5' Then -- NEED PREPARE ELSE 8.x COMPLAINS
set @sum5 = 0; -- THAT THESE VARS DON'T EXIST
set @sql = "Set @sum5 = ( Select @@query_cache_size +
@@innodb_additional_mem_pool_size )";
Prepare stmt From @sql;
Execute stmt;
Drop Prepare stmt;
Set global_sum = global_sum + @sum5;
If detail Then
Set @sql = "Select @@query_cache_size, @@innodb_additional_mem_pool_size";
Prepare stmt from @sql;
Execute stmt;
Drop Prepare stmt;
End If;
End If;
Set per_thread_sum = ( Select @@read_buffer_size + @@read_rnd_buffer_size +
@@sort_buffer_size + @@bulk_insert_buffer_size +
@@join_buffer_size + @@thread_stack +
@@max_allowed_packet + @@net_buffer_length
);
if detail Then
Select @@read_buffer_size, @@read_rnd_buffer_size,
@@sort_buffer_size, @@bulk_insert_buffer_size;
Select @@join_buffer_size, @@thread_stack,
@@max_allowed_packet, @@net_buffer_length;
End If;
Set max_conn = ( Select @@max_connections );
Set heap_table = ( Select @@max_heap_table_size );
Set temp_table = Least( ( Select @@tmp_table_size ),
( Select @@max_heap_table_size )
);
#Summary:
Select "Global Buffers" As "Parameter",
Concat( Round( Global_Sum/(1024*1024), 2 ), mb ) As "Value"
Union
Select "Per Thread",
Concat( Round( Per_Thread_Sum/(1024*1024), 2), mb )
Union
Select "Maximum Connections", Max_Conn
Union
Select "Maximum Memory Usage",
Concat( Round( ( ( Max_Conn * Per_Thread_Sum ) + Global_Sum ) / 1024/1024, 2 ), mb )
Union
Select "+ Per Heap Table",
Concat( Round(Heap_Table / (1024*1024), 2 ), mb)
Union
Select "+ Per Temp Table",
Concat( Round(Temp_Table / (1024*1024), 2 ), mb ) ;
END ;
go
DELIMITER ;
-- result without details on a small development system ...
Call mysqlmem(0);
+----------------------+-------------+
| Parameter | Value |
+----------------------+-------------+
| Global Buffers | 1055.60 Mb |
| Per Thread | 533.26 Mb |
| Maximum Connections | 20 |
| Maximum Memory Usage | 11720.76 Mb |
| + Per Heap Table | 16.00 Mb |
| + Per Temp Table | 16.00 Mb |
+----------------------+-------------+
Since version 5.7, MySQL can give you a current memory usage summary after you make this performance_schema change:
update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory/%';Then queries like these give memory summaries:
select * from sys.memory_global_total;
select host, current_count_used ccu, current_allocated,
current_avg_alloc, current_max_alloc, total_allocated
from sys.memory_by_host_by_current_bytes
where host is not null;
select * from memory_summary_global_by_event_name
where event_name = 'memory/sql/table'G
Much more at https://blogs.oracle.com/svetasmirnova/memory-summary-tables-in-performance-schema-in-mysql-57.
Performance_schema uses memory. Since 8.0 this query shows totals of RAM used in GB used by performance_schema ...
SELECT Round( Sum( sum_number_of_bytes_alloc)/1024/1024/1024, 2 ) as 'GB Allocatwd', Round( Sum( current_number_of_bytes_used) /1024/1024/1024, 2 ) As 'GB Used' FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/performance_schema/%';... for example on a small modest system ...
+--------------+---------+ | GB Allocatwd | GB Used | +--------------+---------+ | 0.27 | 0.27 | +--------------+---------+... and this reads out details of performance_schema memory use in 8.0 ...
SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/performance_schema/%';For more memory use queries see https://lefred.be/content/mysql-and-memory-a-love-story-part-2/.
Last updated 17 Sep 2019
