Re: mysql out of memory
Posted by: Peter Brawley
Date: November 22, 2019 12:08PM

I ran your results through an analysis script. The system is extremely busy, needs reconfiguring as indicated by suggestions in upper case. Many queries urgently need to be optimised.

After that, you'll probably still need better hardware. If it's my system with this load, I want enough memory to run everything in RAM, ie 60GB with the present data load, presumably more soon as the system is probably growing.

And why aren't you running 8.0?

You're running MySQL Enterprise, use the 1-on-1 support you paid for.

DB sizes
InnoDB | 31.55 GB | 5.15 GB | 36.70 GB |
MyISAM | 25.36 GB | 4.22 GB | 29.58 GB |
CSV    | 0.00 GB  | 0.00 GB | 0.00 GB  |
TOTALS | 56.91 GB | 9.36 GB | 66.28 GB |

MySQL version                           5.6.31-
Slow queries/sec                        unknown         ENABLE THE SLOW QUERY LOG
Queries not using indexes               0               
Queries needing full table scans/sec    164.4673        FIND & OPTIMISE THOSE QUERIES
InnoDB buffer pool inefficiency         0.0006          Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB page reads and buffer pool       0.0026          Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB log file thrashing               0.0867          BEST IS NEAR 0.5; GROW INNODB_LOG_FILE_SIZE IF WELL UNDER 0.5, shrink if much larger
Time between InnoDB log rotation        0:13:17         Grow log size if < 30 min, shrink if > 60 min, but see here.
InnoDB buffer pool reads / sec          33.9585         If high, INCREASE INNODB_BUFFER_POOL_SIZE UP TO 70% OF RAM
Est. best InnoDB log file size          160MB           Average hour of writes rounded up to 32MB boundary
MyISAM key buffer size                  0.02MB          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                 1.82            If > 10, grow key_buffer_size
MyISAM key write ratio                  1.06            Much higher than 1 suggests a disk write bottleneck
MyISAM key read rate                    239.72          Should be high when the buffer is full
MyISAM query cache hits/inserts         0               If low, consider disabling the cache
Temp tables created/sec                 132.94          If > 20, FIND THE QUERIES RESPONSIBLE AND OPTIMISE THEM, 
Temp tables created on disk/Questions   0               If > .05, try growing join_buffer_size, optimise responsible queries, 
Temp tables created on disk/sec         0.04            If high, grow tmp_table_size, max_heap_size. Avoid Blobs, use HEAP tables in queries
Database changes / connection           0               Use db.table syntax
Processes/connection                    0               If high, grow thread_cache_size
Files opened/sec                        367.7           If > 5, INCREASE TABLE_OPEN_CACHE
Queries/sec                             1228.23         Thousands is busy: YOU MAY NEED BETTER HARDWARE
Writes/sec                              84.07           If, over 50/sec, BETTER DISK HARDWARE MAY BE NEEDED
Insert statements/sec                   28.86           
Inserts/sec                             300573.07       SYSTEM IS OVERLOADED.
Next table row requests/sec             3376672         If high, QUERIES NEED OPTIMISING ESP. WITH COVERING INDEXES
Sampling time                           19:58:54        If less than 2h in a typical use period, re-do
Max MySQL RAM use                       5.1GB           Must be less than RAM available to MySQL
Available RAM                           11.89GB         If less than Max MySQL memory use, more memory needed
MySQL manual page on memory use

InnoDB basic stats
  innobuffpool  bytes read/sec  reads/sec     bytes/read  bytes written/sec  writes/sec   bytes/write    rollback %  buffpool free %
      1500MB            78          31056          396          49557           52           951           31.4          0.9      

Options: ReplyQuote

Written By
November 21, 2019 01:20AM
November 21, 2019 10:49AM
Re: mysql out of memory
November 22, 2019 12:08PM

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.