The slave is set for 1,000 connections? It needs that many?
Okeh, we can start by running this sproc ...
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 ;
Call mysqlmem(0);
Then please ...
(i) post the result of ...
select engine,data,indexes,total
from (
select
ifnull(engine,'TOTALS') as engine,
concat(data,' GB') as data,
concat(indexes,' GB') as indexes,
concat(tot,' GB') as total,
if(engine is null,-1,tot) as ord
from (
select
engine,
round( sum(data_length)/1024/1024/1024, 2 ) as data,
round( sum(index_length)/1024/1024/1024, 2 ) as indexes,
round( sum(data_length+index_length)/1024/1024/1024, 2 ) as tot
from information_schema.tables
where engine is not null and engine not in('information_schema','performance_schema')
group by engine with rollup
) sums
) list
order by list.ord desc;
(ii) post the results of running these queries in the mysql client program ...
show variables;
show global status;