MySQL Forums
Forum List  »  Stored Procedures

Re: Proc Slow in Mysql Fast in MSSQL
Posted by: Peter Brawley
Date: June 25, 2014 10:53AM

Could be query design & indexing issues, also could be MySQL config issues.

Re query design & indexing: run EXPLAIN EXTENDED on the query, and post that result inside BBCode tags, plus the result of Show Create Table for each table referenced by the query.

Re config, how much RAM is there? To give us a config snapshot, run this script, and post the result inside BBCode tags..

set global log_bin_trust_function_creators=1; 

drop function if exists gsintval; 
delimiter go 
create function gsintval( pname varchar(128) ) returns bigint 
begin 
  declare ret bigint; 
  select variable_value into ret  
    from information_schema.global_status  
    where variable_name=pname COLLATE utf8_unicode_ci; 
  return ret; 
end; 
go 
delimiter ; 

drop function if exists gvintval; 
delimiter go 
create function gvintval( pname varchar(128) ) returns bigint 
begin 
  declare ret bigint; 
  select variable_value into ret  
    from information_schema.global_variables  
    where variable_name=pname COLLATE utf8_unicode_ci; 
  return ret; 
end; 
go 
delimiter ; 

set @timescale='uptime';  
select   
  'Slow queries/sec' as Item,   
  round( gsintval('slow_queries') / gsintval(@timescale), 2 ) as Rate, 
  'Find & optimise slow queries' as Suggestions 
union  
select   
  'Unindexed queries/sec',   
  round( gsintval('select_range_check') / gsintval(@timescale), 2 ),  
  'Find & optimise slow queries' 
union  
select   
  'Full table scans/sec',   
  round( gsintval('select_scan')/gsintval(@timescale), 2 ),  
  'Find & optimise slow queries' 
union  
select   
  'InnoDB buffer pool inefficiency',   
  round( gsintval('innodb_buffer_pool_reads') /  
         gsintval('innodb_buffer_pool_read_requests'), 2 ), 
  'Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM' 
union 
select   
  'InnoDB pages read and buffer pool',   
  round( gsintval('innodb_pages_read') /  
         gsintval('innodb_buffer_pool_read_requests'), 2 ), 
  'As above' 
union 
select   
  'InnoDB log thrashing',   
  round( gvintval('innodb_log_file_size') *  
         gvintval('innodb_log_files_in_group') /  
         gvintval('innodb_buffer_pool_size'), 2 ), 
  'Grow log size if < 30 min'          
union  
select   
  'Mins between InnoDB log rotation',   
  round( gsintval('uptime') / 60 *   
  gvintval('innodb_log_file_size') /   
  gsintval('Innodb_os_log_written'), 2 ), 
  'Grow log size if < 30 min, shrink if > 60 min, but see <a href="www.mysqlperformanceblog.com/2012/10/08/measuring-the-amount-of-writes-in-innodb-redo-logs/">here</a>.' 
union  
select   
  'MyISAM query cache hits/sec',   
  round( gsintval('qcache_hits') /   
         (gsintval('qcache_hits') + gsintval('com_select')), 2 ), 
  'If high, grow query_cache_size up to 50' 
union  
select   
  'MyISAM query cache spills/sec',   
  round( gsintval('Qcache_lowmem_prunes') /   
         gsintval(@timescale), 2 ), 
  'If < 5, try turning query cache off' 
union  
select   
  'MyISAM cache hit rate',   
  round( gsintval('key_read_requests')/gsintval('key_reads'), 2 ), 
  'If < 10, grow key_buffer_size' 
union  
select   
  'MyISAM key read rate',   
  round( gsintval('key_reads')/gsintval(@timescale), 2 ), 
  'Should be high when the buffer is full' 
union  
select   
  'MyISAM query cache hits/inserts',   
  round( gsintval('Qcache_hits')/gsintval('Qcache_inserts'), 2 ), 
  'If < 5, consider disabling the cache' 
union  
select   
  'Temp tables created/sec',   
  round( gsintval('Created_tmp_disk_tables')/gsintval(@timescale), 2 ),  
  'Grow tmp_table_size, max_heap_size. Use Heap tables in queries'   
union  
select   
  'Database changes/connection',   
  round( gsintval('com_change_db') / gsintval('connections'), 2 ), 
  'Use db.table syntax' 
union  
select   
  'Processes/connection',   
  round( gsintval('threads_created') / gsintval('connections'), 2 ), 
  'If high, grow thread_cache_size' 
union  
select   
  'Queries/sec',   
  round( gsintval('queries') / gsintval(@timescale), 2 ), 
  'Thousands is busy: you may need better hardware' 
union  
select   
  'InnoDB buffer reads/sec',   
  round( ( gsintval('innodb_buffer_pool_reads') +   
           gsintval('Innodb_buffer_pool_pages_flushed')) /   
           gsintval(@timescale), 2 ), 
  'Thousands is busy: you may need better hardware' 
union  
select   
  'Files opened/sec',   
  round( gsintval('opened_files') / gsintval(@timescale), 2 ), 
  'If > 5, increase table_open_cache' ; 

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Proc Slow in Mysql Fast in MSSQL
1252
June 25, 2014 10:53AM


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.