0

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.

From phpmyadmin:

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%

my.cnf config:

[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

Pastebins:

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

SHOW GLOBAL VARIABLES: https://pastebin.com/Vt1L1Mc8

SHOW FULL PROCESSLIST: https://pastebin.com/a2pive5G

STATUS: https://pastebin.com/w1TJ2i30

MySQLTuner: https://pastebin.com/a5Jhxcb4

TOP: https://pastebin.com/RrZnuepZ

ulimit -a: https://pastebin.com/egCHFDEh

iostat -xm 5 3: https://pastebin.com/AV1mXnti

/proc/meminfo: https://pastebin.com/52e3FKrE

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.

UPDATE (8/16/21):

enter image description here

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):

https://pastebin.com/9sM07pPv

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:

https://pastebin.com/xw8xezEq

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.

5
  • Do you know how to reach me? Aug 16 at 14:05
  • View profile, please. Aug 16 at 15:20
  • Did the MySQL version change?
    – Rick James
    Aug 16 at 18:08
  • @Wilson, I just sent you an email. Rick, I don't actually think MySQL was updated. I found a way to view what was actually installed on August 14, and I'm not seeing any MySQL. I will add the list of installs in the post above.
    – peppy
    Aug 16 at 21:09
  • Set up the slowlog if it is not already producing data. Use long_query_time=1
    – Rick James
    Aug 18 at 19:15
1

This may not Answer the specific question, but there are some issues with the settings.

Analysis of GLOBAL STATUS and VARIABLES:
 

Observations:

  • Version: 8.0.26-0ubuntu0.20.04.2
  • 3.8 GB of RAM
  • Uptime = 01:19:38; Please rerun SHOW GLOBAL STATUS after several hours.
  • You are not running on Windows.
  • Running 64-bit version
  • You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

With only 3.6GB of RAM, some settings should be shrunk:

table_open_cache = 200
innodb_buffer_pool_size = 1500M
thread_cache_size = 40
innodb_open_files = 5000
innodb_page_cleaners = 2
innodb_read_io_threads = 32
innodb_write_io_threads = 32

There seems to be a log of TRUNCATEs; what is going on?

Details and other observations:

( table_open_cache ) = 10,000 -- Number of table descriptors to cache -- Several hundred is usually good.

( innodb_buffer_pool_size ) = 2,048 / 4080218931.2 = 52.6% -- % of RAM used for InnoDB buffer_pool -- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 31,174,656 / (4778 / 3600) / 2 / 120M = 0.0933 -- Ratio -- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 4,778 / 60 * 120M / 31174656 = 321 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 125829120). (Cannot change in AWS.)

( innodb_flush_method ) = innodb_flush_method = fsync -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT

( Innodb_row_lock_time_avg ) = 1,353 -- Avg time to lock a row (millisec) -- Possibly conflicting queries; possibly table scans.

( Innodb_row_lock_time_max ) = 18,387 -- Max time to lock a row (millisec) -- Possibly conflicting queries; possibly table scans.

( Innodb_row_lock_waits/Innodb_rows_inserted ) = 33/1964 = 1.7% -- Frequency of having to wait for a row.

( innodb_strict_mode ) = innodb_strict_mode = OFF -- Catches some subtle errors earlier. -- OFF leaves some warnings as warnings; ON makes them errors.

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster. 0 is OK for Galera.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Usually should be ON. -- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (now 8) (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890). 10.5.0 decided to default OFF.

( innodb_ft_result_cache_limit ) = 2,000,000,000 / 4080218931.2 = 49.0% -- Byte limit on FULLTEXT resultset. (Possibly not preallocated, but grows?) -- Lower the setting.

( character_set_server ) = character_set_server = latin1 -- Charset problems may be helped by setting character_set_server (now latin1) to utf8mb4. That is the future default.

( local_infile ) = local_infile = ON -- local_infile (now ON) = ON is a potential security issue

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( log_slow_slave_statements ) = log_slow_slave_statements = OFF -- (5.6.11, 5.7.1) By default, replicated statements won't show up in the slowlog; this causes them to show. -- It can be helpful in the slowlog to see writes that could be interfering with Replica reads.

( back_log ) = 100 -- (Autosized as of 5.6.6; based on max_connections) -- Raising to min(150, max_connections (now 100)) may help when doing lots of connections.

( max_connect_errors ) = 10,000 -- A small protection against hackers. -- Perhaps no more than 200.

( Connections ) = 30,726 / 4778 = 6.4 /sec -- Connections -- Increase wait_timeout (now 14400); use pooling?

( thread_cache_size / Max_used_connections ) = 100 / 72 = 138.9% -- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.

Abnormally small:

Key_blocks_unused = 505
gtid_executed_compression_period = 0
innodb_adaptive_max_sleep_delay = 2,000
innodb_lru_scan_depth / innodb_io_capacity = 0.16
innodb_parallel_read_threads = 1
innodb_read_ahead_threshold = 8
key_cache_division_limit = 50

Abnormally large:

Binlog_stmt_cache_use = 0.19 /sec
Com_create_db = 0.75 /HR
Com_show_master_status = 8.3 /HR
Com_truncate = 25 /HR
Innodb_buffer_pool_bytes_data = 437100 /sec
Innodb_data_pending_fsyncs = 3
Innodb_system_rows_deleted = 50 /HR
Innodb_system_rows_inserted = 99 /HR
Innodb_system_rows_read = 15 /sec
Innodb_system_rows_updated = 0.17 /sec
Select_range / Com_select = 27.5%
Ssl_accepts = 145
Ssl_finished_accepts = 145
innodb_open_files = 10,000
innodb_page_cleaners = 8
innodb_read_io_threads = 64
innodb_write_io_threads = 64
key_cache_age_threshold = 64,800
key_cache_block_size = 16,384
max_error_count = 1,024
max_length_for_sort_data = 4,096
myisam_repair_threads = 4
net_buffer_length = 65,536
performance_schema_error_size = 4,946
query_alloc_block_size = 32,768
query_prealloc_size = 32,768
table_open_cache / max_connections = 100

Abnormal strings:

character_set_system = utf8mb3
event_scheduler = ON
have_query_cache = NO
innodb_fast_shutdown = 1
mysqlx_compression_algorithms = DEFLATE_STREAM,LZ4_MESSAGE,ZSTD_STREAM
opt_tf__dynamic_range = ...
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on,range_optimizer=on,dynamic_range=...
protocol_compression_algorithms = zlib,zstd,uncompressed
slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
updatable_views_with_limit = NO
1
  • Thanks for looking into this and providing optimization tips, I greatly appreciate it.
    – peppy
    Aug 19 at 22:27
0

I was able to resolve this issue. I spent the entire day yesterday and day before trying to investigate PHP, Apache and everything else, learning various new things. After viewing the PHP processes and how they were interacting with MySQL, it seemed that most of the problems were happening at the mysql connection, and with mundane queries. I thought perhaps it was a rouge PHP script or a bunch of arcane queries, but that wasn't the case. After many hours, I completely powered off the web/php/apache server and the mysql server and restarted just the mysql server. I found that even with no requests being sent to MySQL from the web server, there were still significant slowdowns, network timeouts and the server kicking me out of the SSH at random, which was totally bizarre.

I just concluded that something was broken with MySQL itself. I uninstalled and reinstalled MySQL, used the exact same configuration Wilson Hauck gave me in the past, and now everything is working perfectly fine again - no slowdown in the shell, nor on the websites. I didn't have enough time, nor experience to figure out exactly what in the MySQL system could have caused this. The only thing I suspect is that during every Ubuntu update, I used "sudo reboot" without shutting down MySQL first. Perhaps this did something weird with MySQL or corrupted something if MySQL was ungracefully shut down during a reboot.

When all other explanation failed, this finally worked for me:

sudo apt-get purge mysql-server
sudo apt-get install mysql-server

IMPORTANT FOR ANYONE ELSE READING THIS: These commands did not delete my tables and databases themselves on MySQL 8.0. But these might delete data in earlier MySQL versions, so back up your data just to be safe if you plan on using these.

Some of the highly experience MySQL Admins might find my problem's description/solution very familiar with other cases they've dealt with in the past. If you have any insights about what exactly in the MySQL system may have been the real cause of the problem, I'd greatly appreciate the insight. Uninstalling and reinstalling MySQL is a bit extreme, so it would be nice knowing what the real cause was.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.