> Occasion swapping? Never heard. Probably not. Where can I find documentation about this?
All serious OSes need to be able to swap. But you need to minimise swapping because it kills performance. 40 VMs will consume a lot of RAM. You don't want them to be swapping.
Nor do you want MySQL swapping. You need to optimise MySQL mem use also.
What OS is this running on?
What is the MySQL version? How much RAM is available to MySQL?
Please ...
(i) post the result of running this in the mysql client program ...
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;
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 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 ) > 9 Then
Set global_sum = global_sum +
( select @@max_binlog_cache_size ) +
(select @@max_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,
@@max_binlog_cache_size, @@max_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
set @sum5 = 0;
set @sql = "Set @sum5 = ( Select @@query_cache_size + @@innodb_additional_mem_pool_size )";
Prepare stmt From @sql;
Execute stmt;
Drop Prepare stmt;
If detail Then
Set global_sum = global_sum + @sum5;
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);
(iii) if under Linux post the result of this terminal cmd ...
free -m
... or if in windows, this cmdline cmd ...
systeminfo
> What about the idea of retrieving e. g. 10 tasks at a time.
Ome call, especially through a stupidly slow gate like ODBC, is much quicker than 10 or 20.
You'll need to benchmark ODBC, app and MySQL contributions to slowness.
Re the whole-table lock, locking whole tables is for single-user single-task contexts. Period. If I understand your requirement correctly, you need a transactional storage engine.
Edited 1 time(s). Last edit at 12/15/2018 06:02PM by Peter Brawley.