Mysql – Configure MySQL to minimize file locks

*** EDIT ***
8GB RAM

SHOW VARIABLES: https://pastebin.com/ANAiq1E2

SHOW GLOBAL STATUS: https://pastebin.com/pwWKRiie

*** END EDIT ***

To start off, I'm not a DBA, so I may have overlooked something simple.

Backstory, we currently host all our client accounts off of a VPS. VPS has 42 cores available, 8GB of ram, and SSD storage. Most of our databases are from CMS (Joomla or WordPress) with a dozen Symfony applications. Current table count is just over 15K with ~4.2GB of data. The initial problem started with pages becoming unresponsive for a few seconds at a time. After digging into the logs I found multiple errors like these

2020-11-27T19:50:35.218190Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5187ms. The settings might not be optimal. (flushed=8 and evicted=0, during the time.)

2020-11-27T20:17:32.699558Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4027ms. The settings might not be optimal. (flushed=14 and evicted=0, during the time.) 

The flush times were often as high as 20 seconds. I started researching fixes for it and eventually moved all tables to InnoDB, increased innodb buffer pool, increased table cache size, increased table open cache, and increased file limits. That resulted in file lock errors.

2020-11-27T16:49:38.736243Z 378 [ERROR] InnoDB: Unable to lock ./sedashoa_feb2718/zys4l_easyblog_featured.ibd error: 37
2020-11-27 10:49:38 0x7f6009133700  InnoDB: Assertion failure in thread 140050445842176 in file fil0fil.cc line 906

I wasted a lot of time tweaking the settings back down, since the first time I contacted my hosting provider about the issues, they seemed to think it was MySQL memory usage. After tweaking everything down to where the max MySQL memory usage was under 3GB and raising table open cache back, I started getting the same error: 37 crashes. This time, when I contacted hosting support, they informed me I was hitting the file ops limit. So I lowered table open cache back down to 1000.

My question is how should I configure MySQL to make the most out of the resources available? I already know we need to either move on to a dedicated host or to a managed SQL server like Digital Ocean offers. In the meantime, what should I tweak to make the most out of the ram available (figure 4GB just for MySQL) without running into the file lock limits. This is the current config.

# Default options are read from the following files in the given order:
# /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

#[mysql]
#port                            = 3306
#socket                          = /var/run/mysqld/mysqld.sock

[mysqld]
# Required Settings
basedir                         = /usr
bind_address                    = 127.0.0.1 # Change to 0.0.0.0 to allow remote connections
datadir                         = /var/lib/mysql
default-time-zone               = '-5:00'
max_allowed_packet              = 32M       # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet
max_connect_errors              = 1000000
performance-schema              = 1     # turn performance schema on
pid_file                        = /var/run/mysqld/mysqld.pid
port                            = 3306
skip_external_locking
#skip_name_resolve
#socket                          = /var/run/mysqld/mysqld.sock

# Enable for b/c with databases created in older MySQL/MariaDB versions (e.g. when using null dates)
sql_mode                        = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES

tmpdir                          = /tmp
user                            = mysql

# InnoDB Settings
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 2     # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size         = 2G    # Use up to 70-80% of RAM (working off 4GB or half VM RAM)
innodb_lru_scan_depth           = 256   # Default 1024 https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_lru_scan_depth
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 1         # https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
innodb_flush_method             = O_DIRECT  # https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method
innodb_log_buffer_size          = 16M       # https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_buffer_size
innodb_log_file_size            = 256M
innodb_stats_on_metadata        = 0         # https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata
innodb_use_native_aio           = 0     # MySQL will not start with native asynchronous file access enabled

#innodb_temp_data_file_path     = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file
innodb_thread_concurrency       = 16     # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better
                                        # contain CPU usage. E.g. if your system has 8 CPUs, try 6 or 7 and check
                                        # the overall load produced by MySQL/MariaDB.
innodb_read_io_threads          = 8
innodb_write_io_threads         = 8

# MyISAM Settings - disable cache
# query_cache_limit               = 4M    # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
query_cache_size                = 0     # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
query_cache_type                = 0     # Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x

key_buffer_size                 = 10M   # UPD

low_priority_updates            = 1         # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_low_priority_updates
concurrent_insert               = 2         # Same as auto https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_concurrent_insert

# Connection Settings
max_connections                 = 25   # UPD
max_user_connections            = 24

back_log                        = 60
thread_cache_size               = 100       # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_thread_cache_size
thread_stack                    = 256K      # Default value for 64 bit platforms    See: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_thread_stack

interactive_timeout             = 180
wait_timeout                    = 60

# For MySQL 5.7+ only (disabled by default)
#max_execution_time             = 30000 # Set a timeout limit for SELECT statements (value in milliseconds).
                                        # This option may be useful to address aggressive crawling on large sites,
                                        # but it can also cause issues (e.g. with backups). So use with extreme caution and test!
                                        # More info at: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time

# For MariaDB 10.1.1+ only (disabled by default)
#max_statement_time             = 30    # The equivalent of "max_execution_time" in MySQL 5.7+ (set above)
                                        # The variable is of type double, thus you can use subsecond timeout.
                                        # For example you can use value 0.01 for 10 milliseconds timeout.
                                        # More info at: https://mariadb.com/kb/en/aborting-statements/

# Buffer Settings
join_buffer_size                = 512K  # UPD
read_buffer_size                = 1M    # UPD
read_rnd_buffer_size            = 512K  # UPD
sort_buffer_size                = 2M    # UPD

# Table Settings
# In systemd managed systems like Ubuntu 16.04+ or CentOS 7+, you need to perform an extra action for table_open_cache & open_files_limit
# to be overriden (also see comment next to open_files_limit).
# E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
# and for MariaDB check: https://mariadb.com/kb/en/library/systemd/

# ***Non crashing, but with slow cache flush ***
table_definition_cache          = 20000 # UPD   https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_table_definition_cache
table_open_cache                = 1000  # UPD Total tables on 2020-11-24=15,617  https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_table_open_cache
open_files_limit                = 2000  # UPD - This can be 2x to 3x the table_open_cache value or match the system's
                                        # open files limit usually set in /etc/sysctl.conf or /etc/security/limits.conf
                                        # In systemd managed systems this limit must also be set in:
                                        # /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+) and
                                        # /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB)
# ***Crashes because of VPS op limit ***
#table_definition_cache          = 20000
#table_open_cache                = 20000
#open_files_limit                = 40000

# max_heap_table_size and tmp_table_size should be changed together and kept equal
max_heap_table_size             = 16M
tmp_table_size                  = 16M

# Search Settings
ft_min_word_len                 = 3     # Minimum length of words to be indexed for search results

# Logging
log_error                       = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes   = 1
long_query_time                 = 5
slow_query_log                  = 0     # Disabled for production
slow_query_log_file             = /var/lib/mysql/mysql_slow.log

[mysqldump]
# Variable reference
# For MySQL 5.7: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
# For MariaDB:   https://mariadb.com/kb/en/library/mysqldump/
quick
quote_names
max_allowed_packet              = 64M

Any ideas or suggestions would be appreciated.