*** 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.