MySQL Forums
Forum List  »  Optimizer & Parser

Re: Startup was very slow
Posted by: Peter Brawley
Date: March 02, 2022 10:58AM

my.cnf
~~~~~~
Re shared memory see https://dev.mysql.com/blog-archive/improving-the-performance-of-mysql-on-windows/

Re innodb_file_per_table: Please see https://bugs.mysql.com/bug.php?id=96340 re this & partitioning

cheatsheet
~~~~~~~~~~
An extremely busy system.

It says your demand may be 8TB RAM, which you likely don't have. Re max mysql RAM use, the formula is ...

innodb_ft_total_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size + tmp_table_size + key_buffer_size + 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 )

Is there significant swapping? Possibly your value is distorted by your MyISAM settings, obviously you don't have 8TB RAM, you'll need to investigate that. Otherwise you need more and/or better hardware, but first you will need to address the startup table scan issue mentioned above

Other issues needing attention in caps ...

     
ENGINE  DATA       INDEXES    total       
------  ---------  ---------  ------------
InnoDB  891.73 GB  420.84 GB  1312.57 GB  
MyISAM  1.53 GB    1.24 GB    2.76 GB     
CSV     0.00 GB    0.00 GB    0.00 GB     
TOTALS  (NULL)     (NULL)     (NULL)      
TOTALS  893.26 GB  422.07 GB  1315.33 GB 
 
 
Item                                  Rate      Suggestions                                                                         
------------------------------------  --------  --------------------------------------------------------------------------------
SLOW QUERIES/SEC                      0.46      FIND & OPTIMISE SLOW QUERIES                                                        
UNINDEXED QUERIES/SEC                 2.38      FIND & OPTIMISE UNINDEXED QUERIES                                                   
FULL TABLE SCANS/SEC                  112.39    FIND & OPTIMISE QUERIES THAT DO FULL TABLE SCANS                                    
Total InnoDB load / buffer pool size  1.74      Increasing innodb_buffer_pool_size may improve performance                          
InnoDB buffer pool inefficiency       0.00      Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM     
InnoDB pages read and buffer pool     0.01      As above                                                                            
InnoDB log thrashing                  0.04      Best near 0.5; if well under 0.5, grow innodb_log_file_size, 
                                                shrink if much larger  
Est. best InnoDB log file size        32MB      Average hour of writes rounded up to 32MB boundary                                  
Mins between InnoDB log rotation      34.48     Grow log size if < 30 min, shrink if > 60 min**                                     
Query cache hits/sec                  (NULL)    If high, grow query_cache_size up to 50                                             
Query cache spills/sec                0.00      If < 5, try turning query cache off                                                 
MyISAM buffer hit ratio               49.69     If < 10, grow key_buffer_size                                                       
MyISAM key read rate                  219.55    Should be high when the buffer is full                                              
MyISAM key write ratio                2.4       Much higher than 1 suggests a disk write bottleneck                                 
MyISAM query cache hits/inserts       (NULL)    If < 5, consider disabling the cache                                                
Temp tables created/sec               14.60     Grow tmp_table_size, max_heap_size. Use Heap tables in queries                      
Database changes/connection           (NULL)    Use db.table syntax                                                                 
Processes/connection                  0.00      If high, grow thread_cache_size                                                     
QUERIES/SEC                           29289.68  OVER 1K IS BUSY: YOU MAY NEED BETTER HARDWARE                                       
Inserts/sec                           (NULL)    Over 10K is busy, you may need better hardware                                      
TABLE SCAN NEXT ROW SEEKS/SEC         86453.90  IF HIGH, THERE ARE QUERIES THAT NEED OPTIMISING, ESP. WITH COVERING INDEXES         
INNODB BUFFER READS/SEC               1294.02   OVER 1K IS BUSY: YOU MAY NEED BETTER HARDWARE                                       
FILES OPENED/SEC                      206.52    IF > 5, INCREASE TABLE_OPEN_CACHE                                                   
CREATED TMP DISK TABLES/SEC           14.60     IF > 1, CHECK TMP_TABLE_SIZE, MAX_HEAP_TABLE_SIZE, 
                                                TEXT/BLOB QUERY RETRIEVALS       
Created_tmp_disk_tables/Queries       0.00      If > 4%, check tmp_table_size, max_heap_table_size, 
                                                Text/Blob query retrievals      
MAX MYSQL RAM USE                     8073.23   MUST NOT BE GREATER THAN RAM AVAILABLE TO MYSQL                                     
 
 
InnoDB IOPS  BuffPoolSize  BufferPoolPctFree  BytesRead/s  Reads/s  Bytes/Read  BytesWritten/s  Writes/s  Bytes/Write  
-----------  ------------  -----------------  -----------  -------  ----------  --------------  --------  -------------
2602.7       98.0GB        0.1                5            1157.8   214         711             278.4     3      

Options: ReplyQuote


Subject
Views
Written By
Posted
758
January 27, 2022 07:53AM
217
February 28, 2022 11:14AM
Re: Startup was very slow
290
March 02, 2022 10:58AM
113
October 09, 2022 09:24AM


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.