Yesterday, I noticed in my web logs that most traffic disappeared for 2 hours. I decided to do a routine Ubuntu 20.04 upgrade yesterday that I haven't done in 1-2 months, and it seems like a lot of major problems started up because of it. The website is freezing up and sometimes not loading up for 20 minutes. I can see in the traffic logs that most of the traffic goes away for an hour or two. It comes back and runs just fine for awhile, and then the slowness and hanging returns again. I also keep getting disconnected from my SSH shell unexpectedly (on the MySQL server only). When the website hanged for a long time, I logged into MySQL from SSH and "SHOW PROCESSLIST" had almost no processes running at all, which seemed weird.
So on my database server, I am not seeing any errors. OOM killer is not running, there is plenty of memory and disk space available. On my web/php/apache server, same thing: plenty of memory and disk space. The most intensive process on the web/apache server is Sphinx Search, which is an indexer that indexes some of our MySQL tables. The indexer does a full index of most of our big tables once a day, and then runs the smaller 5 minute indexer to index whatever updated throughout the day. I suspect this might be related to the problem, as our php7.4-fpm children processes go up when this runs. One of our site has a max_children of 40 php-fpm processes and I've seen them all being used when the site hangs. When I restart php7.4-fpm and kill all those processes, the website still continues to hang, so I don't get it. I still suspect it's a MySQL problem or probably something to do with Ubuntu's update with MySQL.
Basic details of our database server: MySQL 8.0 - Total size of tables is 14.3G with the largest table being 3.2G, the memory on the server is 4G. Virtually all tables are InnoDB, and a couple Memory tables - No MyISAM tables at all. It's basically a MySQL-only server, with just MySQL, fail2ban and other basic system files running.
Network traffic since startup: 3.0 GiB This MySQL server has been running for 0 days, 1 hours, 10 minutes and 27 seconds. It started up on Aug 16, 2021 at 04:45 AM. Traffic # ø per hour Received 58.7 MiB 50.0 MiB Sent 3.0 GiB 2.5 GiB Total 3.0 GiB 2.6 GiB Connections # ø per hour % Max. concurrent connections 72 --- --- Failed attempts 28 23.85 0.11% Aborted 43 36.62 0.16% Total 27 k 22.7 k 100.00%
[mysqld] local_infile=ON # upload data to website occassionally. skip-name-resolve character_set_server=latin1 collation_server=latin1_swedish_ci default_authentication_plugin = mysql_native_password bind-address = *.*.*.* #IP address of my MySQL server binlog_expire_logs_seconds = 604800 # 7 days instead of 30 - takes up huge space port = 3306 sql_mode = "NO_ENGINE_SUBSTITUTION" innodb_buffer_pool_size = 2000M innodb_strict_mode = OFF join_buffer_size = 1M key_buffer_size = 8M # Wilson said 8M. Changed to 10M after all tables went innodb. max_connect_errors = 10000 myisam_recover_options = "BACKUP,FORCE" performance_schema = 0 read_buffer_size = 1M slow_query_log = ON sort_buffer_size = 1M sync_binlog = 0 thread_stack = 262144 wait_timeout = 14400 table_open_cache = 10000 # from 2000 to support 1M+ opened in 2 days table_definition_cache = 2500 # from default to support 2000+ opened in 2 days open_files_limit = 35000 # from 5000 to support 900,000 + opened in 2 days max_connections = 100 # from 151 to support 17 max_used_connections read_rnd_buffer_size = 128K # from 256k default to reduce RD RPS innodb_change_buffer_max_size = 10 # from 25% of innodb_buffer_pool_size 1% used innodb_log_buffer_size = 12M # from 2M to cover 30 minutes of log innodb_log_file_size = 120M # from ~ 20M to cover a few days innodb_buffer_pool_instances = 8 # from 1 to minimize mutex contention innodb_lru_scan_depth = 128 # from 1024 which is causing page_cleaner warnings innodb_page_cleaners = 64 # from 1 to auto follow = innodb_buffer_pool_instances thread_cache_size = 50 # from 8 default to support 17 max_used with room for growth. max_heap_table_size=24M # from 16M to reduce created_tmp_disk_tables tmp_table_size=24M # must be same as max_heap_table_size thread_cache_size=100 # from 50 v5.7 5.1.5 CAP at 100 innodb_io_capacity=800 # from 200 to reduce time to hard drive read_buffer_size=128K # from 1M to reduce RD for your MyISAM predominate table storage read_rnd_buffer_size=64K # from 128K to reduce RPS for random data eq_range_index_dive_limit=32 # from 200, if you have not found it in 32 dives, you will not, move on symbolic-links=0 # from YES to protect your server from the RANSOM WARE dudes and other bad people key_cache_age_threshold=64800 # from 300 seconds, why discard a good key & force RD before 18 hours key_cache_division_limit=50 # from 100 for a HOT/WARM division when discarding an old block key_cache_block_size=32K # from 1024 get enough room to survive for a while innodb_buffer_pool_dump_pct=90 # from 25%, you will likely need the same rows first thing, keep the pointers. innodb_print_all_deadlocks=ON # from OFF and check your error log daily, try to correct one a day, time permitting innodb_read_ahead_threshold=8 # from 56 do not wait so long before reading the next EXTENT to be ready innodb_read_io_threads=64 # from 4 turn the delivery up for RD innodb_write_io_threads=64 # from 4 no need to wait around, get it done max_allowed_packet=32M # from 16M when you need more than 1M because LOCAL INFILE is more than 1M data, in your SESSION max_seeks_for_key=32 # from a huge number if you can not find in 32 seeks, why waste cpu? max_write_lock_count=16 # from a huge number allow RD after nn locks myisam_repair_threads=4 # from 1 for more concurrency dealing with MyISAM repairs open_files_limit=30000 # from 40000 leave your linux at 40000 so the other apps can open files query_alloc_block_size=32K # from 8K to avoid RAM allocations query_prealloc_size=32K # from 8K to avoid RAM allocations for parsing every minute sort_buffer_size=2M # from 1M be ready for larger sort volume updatable_views_with_limit=NO # from YES DYN variable, may have to go back to YES, probably not see REFMA innodb_parallel_read_threads=0 temptable_max_mmap=32M temptable_max_ram=32M innodb_adaptive_max_sleep_delay=2000 net_buffer_length=64K general_log_file=/var/log/mysql/general.log # from /var/lib/mysql/database.log slow_query_log_file=/var/log/mysql/slow-query.log # from /var/lib/mysql/database-slow.log log_error=/var/log/mysql/error.log
SHOW GLOBAL STATUS: https://pastebin.com/r7p0QyD3
SHOW GLOBAL VARIABLES: https://pastebin.com/Vt1L1Mc8
SHOW FULL PROCESSLIST: https://pastebin.com/a2pive5G
ulimit -a: https://pastebin.com/egCHFDEh
iostat -xm 5 3: https://pastebin.com/AV1mXnti
Any tuning advice is appreciated. Unfortunately, the server hasn't been running very long, maybe 1.5 hours or so as the situation is a bit urgent with the websites now going down for long periods of time.
This screenshot suggests that it was probably something to do with the Ubuntu 20.04 update that screwed up MySQL, all the problems started afterwards. However, I updated my web/php/apache Ubuntu 20.04 server at the same time too.
Using the sudo less /var/log/apt/history.log command, this was what was actually updated on August 4th and August 14 (August 14 is when everything went bad):
For my web/php/apache server, I ran the same "sudo less /var/log/apt/history.log" command, which shows a list of updates starting from August 4th through August 14th, and also yesterday when I deleted what looked like "junk" stuff that I didn't need. I needed a pastebin for that:
Also, I do use a lot of TRUNCATE queries, usually in cronjobs. These are usually on small tables. For example, one table keeps track of scheduled jobs that users put in, deleting images from 3rd-party object storage, etc. The cronjob runs through the list, and then truncates the table if no more jobs are available. It will truncate an empty table if nothing is found. Typically, there might only be 1-100 rows or even 5000 rows sometimes, tiny maintenance tables. I'll just truncate the whole table instead of one-at-a-time deletes.