Re: Open Table Cache (1024 limit vs. 100 mil. opened tables)
Posted by: Peter Brawley
Date: March 18, 2019 02:10PM

MySQL version                           5.5.56-log      MySQL version is 9 years old, past time to upgrade
Slow queries/sec                        0.0177          Find & optimise those queries
Queries not using indexes               0               
Queries needing full table scans/sec    13.7894         Find & optimise those queries
InnoDB buffer pool inefficiency         0.0027          Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB page reads and buffer pool       0.0032          Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB log file thrashing               0.125           Best is near 0.5; grow innodb_log_file_size if well under 0.5, shrink if much larger
Mins between InnoDB log rotation        48.2672         Grow log size if < 30 min, shrink if > 60 min, but see here.
InnoDB buffer pool reads / sec          64.0224         If high, increase Innodb_buffer_pool_size up to 70% of RAM
Est. best InnoDB log file size          64MB            Average hour of writes rounded up to 32MB boundary
MyISAM key buffer size                  384MB           Pre-8.0 MyISAM system tables need about 40MB
MyISAM query cache spills / sec         3.7             If high, grow query_cache_size up to 50
MyISAM query cache hits / sec           0.71            If < 5, try turning query cache off
MyISAM cache miss ratio                 46.03           If > 10, grow key_buffer_size
MyISAM key write ratio                  5.53            Much higher than 1 suggests a disk write bottleneck
MyISAM key read rate                    3.29            Should be high when the buffer is full
MyISAM query cache hits/inserts         0               If low, consider disabling the cache
Temp tables created/sec                 11.05           If > 20, find the queries responsible and optimise them, 
Temp tables created on disk/Questions   0.0328          If > .05, try growing join_buffer_size, optimise responsible queries, 
Temp tables created on disk/sec         6.47            If high, grow tmp_table_size, max_heap_size. Avoid Blobs, use HEAP tables in queries
Database changes / connection           0.85            Use db.table syntax
Processes/connection                    0.09            If high, grow thread_cache_size
Files opened/sec                        54.15           If > 5, increase table_open_cache
Queries/sec                             198.83          Thousands is busy: you may need better hardware
Writes/sec                              11.04           If, over 50/sec, better disk hardware may be needed
Insert statements/sec                   6.17            
Inserts/sec                             329.03          Can be up to 20K/sec with efficient memory use, see here
Next table row requests/sec             13610           If high, queries need optimising esp. with covering indexes
Sampling time                           14:26:44        If less than 2h in a typical use period, re-do
Max MySQL RAM use                       48.7GB          Must be less than RAM available to MySQL
Available RAM                           unknown         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 %  buffpoolfree % 
    1024MB        71         1162788     16384       131470             19          6906        87.1           0

Several major problems ...

MySQL is configured to use 49GB RAM. We absolutely need to know how much RAM is available and how much of that is used by other processes. I'll bet lunch it's swapping big time and that's killing performance.

Queries requiring table scans: 14/sec. Table row requests 13.6k/sec, yikes. Optimise those queries with covering indexes and/or major rewrites.

Too many db changes, use db.table syntax

Opening 54 files/sec, need a bigger table_open cache

The query cache is probably not helping, it's deprecated in 5,7 and removed in 8.0, turn it off---optimising queries is a much better solution.

Huge number of rollbacks, 87% of transactions are rolling back, terrible, got to fix that.

Very busy system is wildly under-resourced.

Edited 1 time(s). Last edit at 03/18/2019 02:13PM by Peter Brawley.

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.