MySQL Forums
Forum List  »  Newbie

Re: Solution for slow MySQL database performance
Posted by: Peter Brawley
Date: February 11, 2015 12:32PM

Best execute it in the mysql client program, which has no problem executing multi-statement scripts, and puts out results that paste readably into an environment like these fora.

The phpMyAdmin query interface has bugs. If you must execute it there, do it in two steps, first this part ...

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 ;

and after that executes, click on "Show Query Box" and paste in this part ...

set @timescale='uptime';  
select   
  'Slow queries/sec' as Item,   
  round( gsintval('slow_queries') / gsintval(@timescale), 2 ) as Rate, 
  'If > 0, find & optimise slow queries' as Suggestions 
union  
select   
  'Unindexed queries/sec',   
  round( gsintval('select_range_check') / gsintval(@timescale), 2 ),  
  'If > 0, find & optimise slow queries' 
union  
select   
  'Full table scans/sec',   
  round( gsintval('select_scan')/gsintval(@timescale), 2 ),  
  'If > 0, 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' 
union  
select   
  '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   
  'Query cache spills/sec',   
  round( gsintval('Qcache_lowmem_prunes') /   
         gsintval(@timescale), 2 ), 
  'If < 5, try turning query cache off' 
union  
select   
  'Query cache hits/inserts',   
  round( gsintval('Qcache_hits')/gsintval('Qcache_inserts'), 2 ), 
  'If < 5, consider disabling the cache' 
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   
  '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  
  'Writes/sec', 
   round( ( gsintval('Com_insert') +  
            gsintval('Com_delete') +  
            gsintval('Com_delete_multi') +       
            gsintval('Com_replace') +  
            gsintval('Com_update') +  
            gsintval('Com_update_multi') 
          ) / gsintval(@timescale), 2 ), 
   'More than 40 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
Written By
Posted
Re: Solution for slow MySQL database performance
February 11, 2015 12:32PM


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.