MySQL Forums
Forum List  »  Connector/ODBC

Re: Optimization of application to reduce db workload and improve performance
Posted by: Peter Brawley
Date: December 15, 2018 06:00PM

> 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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.