MySQL Forums
Forum List  »  Performance

Re: High memory used by MySQL
Posted by: Peter Brawley
Date: February 12, 2020 11:41AM

Most significant issues ...

As configured, MySQL can try to use more RAM than is available; that will get you out-of-memory crashes.

Too many queries require table scans---turn on the slow query log to find them, optimise them by creating covering indexes for them.

Huge Insert rate, see the cited url.

Details---items in caps need attention ...

DB sizes
InnoDB | 2.99 GB | 1.84 GB | 4.82 GB |
MyISAM | 0.12 GB | 0.04 GB | 0.16 GB |
CSV    | 0.00 GB | 0.00 GB | 0.00 GB |
TOTALS | 3.10 GB | 1.88 GB | 4.98 GB |

MySQL version                           5.7.29-0
                                        ubuntu0.18.04.1
Slow queries/sec                        unknown         ENABLE THE SLOW QUERY LOG
Queries not using indexes               0               
Queries needing full table scans/sec    7.6352          FIND & OPTIMISE THOSE QUERIES
InnoDB buffer pool inefficiency         0               Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB page reads and buffer pool       0               Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB log file thrashing               0.2116          Best is near 0.5; grow innodb_log_file_size if well under 0.5, shrink if much larger
Time between InnoDB log rotation        13:26:21        Grow log size if < 30 min, shrink if > 60 min, but see here.
InnoDB buffer pool reads / sec          15.2335         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                  100MB           Pre-8.0 MyISAM system tables need about 40MB
MyISAM query cache spills / sec         0               
MyISAM query cache hits / sec           0               
MyISAM cache hit ratio                  364.18          IF < 10, GROW KEY_BUFFER_SIZE
MyISAM key write ratio                  1               Much higher than 1 suggests a disk write bottleneck
MyISAM key read rate                    0.01            Should be high when the buffer is full
MyISAM query cache hits/inserts         0               Query cache deprecated, turn it off
Temp tables created/sec                 5.55            If > 20, find the queries responsible and optimise them, 
Temp tables created on disk/Questions   0.0222          If > .05, try growing join_buffer_size, optimise responsible queries, 
Temp tables created on disk/sec         1.43            If high, grow tmp_table_size, max_heap_size. Avoid Blobs, use HEAP tables in queries
Database changes / connection           0.02            Use db.table syntax
Processes/connection                    0               If high, grow thread_cache_size
Files opened/sec                        0.04            If > 5, increase table_open_cache
Queries/sec                             66.51           Thousands is busy: you may need better hardware
Writes/sec                              10.77           If, over 50/sec, better disk hardware may be needed
Insert statements/sec                   2.94            
Inserts/sec                             12583.62        Can be up to 20K/sec with efficient memory use, see 
                                                        https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.html
Next table row requests/sec             86011           IF HIGH, QUERIES NEED OPTIMISING ESP. WITH COVERING INDEXES
Sampling time                           0:20:45         If less than 2h in a typical use period, re-do
Max MySQL RAM use                       27.5GB          MUST BE LESS THAN RAM AVAILABLE TO MYSQL
Available RAM                           16GB            IF LESS THAN MAX MYSQL MEMORY USE, MORE MEMORY NEEDED
MySQL manual page on memory use

InnoDB basic stats
innobuffpool:       6144MB
bytes read/sec      37576
reads/sec           2
bytes/read          16341
writes/sec          18
bytes/write         24789
bytes written/sec   445945
rollback %          2.8
buffpool free       40.3%



Edited 1 time(s). Last edit at 02/13/2020 05:54PM by Peter Brawley.

Options: ReplyQuote


Subject
Views
Written By
Posted
710
February 12, 2020 02:45AM
Re: High memory used by MySQL
428
February 12, 2020 11:41AM
355
February 13, 2020 09:24AM
336
February 13, 2020 11:49AM
321
February 14, 2020 09:05AM
306
February 14, 2020 11:06AM
304
March 02, 2020 04:12AM
260
March 02, 2020 04:17PM
247
March 02, 2020 08:35PM
280
March 02, 2020 08:36PM
263
March 03, 2020 06:12AM
235
March 03, 2020 11:51AM


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.