MySQL Forums
Forum List  »  Performance

Re: Mysql 5.5 memory issues.
Posted by: Peter Brawley
Date: October 02, 2020 10:24PM

This MySQL installation is overloaded with extremely high inserts/sec and queries/sec rates, and by way too many inefficient queries requiring full table scans and temp tables, find them with the slow query log and optimise them with covering indexes.

I'm having a hard time imagining up to 1,000 simultaneous connections against a single-user storage engine like MyISAM.

Key_buffer_size is 80GB, It can be up to about 25% of available RAM. Your installation is configued to use up to 675.1GB, 200GB more than you have. A big part of that is ...

max_connections * ( thread_stack + max( max_allowed_packet, net_buffer_length ) + 
                    net_buffer_length + read_buffer_size + read_rnd_buffer_size + 
                    sort_buffer_size + join_buffer_size

So far as I know, MySQL can use nothing like the huge values assigned to max_binlog_cache_size, 18446744073709547520,
max_binlog_stmt_cache_size, 18446744073709547520.

Details ...

MySQL version                          5.5.62-log MySQL version is 10 years old, past time to upgrade
Slow queries/sec                       0.2035     Find & optimise those queries
Queries not using indexes              0          
Queries needing full table scans/sec   171.9981   Find & optimise those queries
MyISAM key buffer size                 81920MB    Min pre-8.0=40MB, max(64-bit) depends on OS and memory
MyISAM buffer hit ratio                176.37     If < 10, grow key_buffer_size
MyISAM key write ratio                 2.74       Much higher than 1 suggests a disk write bottleneck
MyISAM key read rate                   1047.81    
Query cache hits/inserts               0          
Query cache spills / sec               0          
Query cache hits / sec                 0          
Temp tables created/sec                296.79     If > 20, find the queries responsible and optimise them, 
Temp tables created on disk/Questions  0.0007     If > .05, try growing join_buffer_size, optimise responsible queries, 
Temp tables created on disk/sec        2.25       If high, grow tmp_table_size, max_heap_size. 
                                                  Avoid Blobs, use HEAP tables in queries
Database changes / connection          1.95       Use db.table syntax
Processes/connection                   0          If high, grow thread_cache_size
Files opened/sec                       331.95     If > 5, increase table_open_cache
Queries/sec                            3595.64    Thousands is busy: you may need better hardware
Writes/sec                             1067.82    If, over 50/sec, better disk hardware may be needed
Insert statements/sec                  477.26     
Inserts/sec                            84176.47   Can be up to 20K/sec with efficient memory use
Next table row requests/sec            1830533    If high, queries need optimising esp. with covering indexes
Sampling time                          9:29:34    If < 2h in a typical use period, re-do
Max MySQL RAM use                      675.1GB    203GB more than available!
Available RAM                          472GB      More memory needed

Options: ReplyQuote

Written By
October 02, 2020 11:26AM
October 02, 2020 12:21PM
October 02, 2020 01:16PM
Re: Mysql 5.5 memory issues.
October 02, 2020 10:24PM

Sorry, only registered users may post in this forum.

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.