MySQL Forums
Forum List  »  Performance

Re: High memory usage on mysql slave server
Posted by: Peter Brawley
Date: April 15, 2019 08:31AM

MySQL version                           5.6.37          
Slow queries/sec                        unknown         Enable the slow query log
Queries not using indexes               0               
Queries needing full table scans/sec    0.042           Find & optimise those queries
InnoDB buffer pool inefficiency         0.001           Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB page reads and buffer pool       0.0088          Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB log file thrashing               1.5             Best is near 0.5; grow innodb_log_file_size if well under 0.5, shrink if much larger
Time between InnoDB log rotation        21:49:15        Grow log size if < 30 min, shrink if > 60 min, but see here.
InnoDB buffer pool reads / sec          8.3748          If high, increase Innodb_buffer_pool_size up to 70% of RAM
Est. best InnoDB log file size          32MB            Average hour of writes rounded up to 32MB boundary
MyISAM key buffer size                  8MB             Pre-8.0 MyISAM system tables need about 40MB
MyISAM query cache spills / sec         0               If high, grow query_cache_size <= 50
MyISAM query cache hits / sec           0               If < 5, try turning query cache off
MyISAM cache miss ratio                 587.58          If > 10, grow key_buffer_size
MyISAM key write ratio                  1.85            Much higher than 1 suggests a disk write bottleneck
MyISAM key read rate                    0.04            Should be high when the buffer is full
MyISAM query cache hits/inserts         0               If low, consider disabling the cache
Temp tables created/sec                 0.05            If > 20, find the queries responsible and optimise them, 
Temp tables created on disk/Questions   0.1039          If > .05, try growing join_buffer_size, optimise responsible queries, 
Temp tables created on disk/sec         0.01            If high, grow tmp_table_size, max_heap_size. Avoid Blobs, use HEAP tables in queries
Database changes / connection           0.39            Use db.table syntax
Processes/connection                    0               If high, grow thread_cache_size
Files opened/sec                        0.09            If > 5, increase table_open_cache
Queries/sec                             38.66           Thousands is busy: you may need better hardware
Writes/sec                              16.22           If, over 50/sec, better disk hardware may be needed
Insert statements/sec                   9.42            
Inserts/sec                             29.26           Can be up to 20K/sec with efficient memory use, see here
Next table row requests/sec             3632            If high, queries need optimising esp. with covering indexes
Sampling time                           17:27:56        If less than 2h in a typical use period, re-do
Max MySQL RAM use                       12.8GB          Must be less than RAM available to MySQL
Available RAM                           16GB            If less than Max MySQL memory use, more memory needed

InnoDB basic stats
  innobuffpool  bytes read/sec  reads/sec     bytes/read  bytes written/sec  writes/sec   bytes/write    rollback %  buffpool free %
     12288MB            43          708337        16384         129934          18           7391          0.1           8.3

Next row requests/sec is 3632, too high. Find those queries with the slow query log and fix them.

Pre-8.0 MyISAM system tables need key_buffer_size ~ 40MB.

I don't see signs of major stress. As noted, it's configured to use near 16GB and is doing so. If the DB is growing, it will soon need more RAM.

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.