MySQL table keeps crashing on high load

I have a database on about 6GB and one table takes up almost all that space and when the server get's hit with high load the table keeps crashing so that I have to stop the MySQL server and repair it, then start it again. Any ideas why and what would be causing this and how I could mitigate this? This ONLY happens when the server suddenly gets a big spike in visitors. See this example image of when the last crash happened during a spike: image of last crash

My hosting provider said the following when I asked them to investigate:

On checking, we have found that the database size is higher so when the website undergoes a high load, a lot of database queries will be existing so the chance of the database crashing will be very high.

FREE -M

              total        used        free      shared  buff/cache   available
Mem:           8342        1451         586         451        6304        6180

THE TABLE INFO

Table - Rows - Engine - encoding - Size 
Stats: 22 020 753   MyISAM  utf8_unicode_ci 6,0 GB

SERVER

Server: Localhost via UNIX socket
Servertyp: MariaDB
Server connection: SSL is not being used Dokumentation
Serverversion: 10.3.27-MariaDB - MariaDB Server
Protokollversion: 10

MY.CNF

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]
log-error=/var/lib/mysql/server.err
performance-schema=0
table_open_cache=2000
innodb_strict_mode="ON"
sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
open_files_limit=40000

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 64M
myisam-sort-buffer-size        = 64M
myisam-recover-options         = FORCE

# SAFETY #
skip-external-locking
max-allowed-packet             = 128M
max-connect-errors             = 1000000
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 32
max-connections                = 500
thread-cache-size              = 286
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 512
group-concat-max-len           = 1048576

# INNODB #
#innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-log-buffer-size         = 16M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 4G

# LOGGING #
# log-queries-not-using-indexes  = 1
# slow-query-log                 = 1
# slow-query-log-file            = /var/lib/mysql/mysql-slow.log

max_allowed_packet=268435456
innodb_file_per_table=1
[mysqldump]
quick
max_allowed_packet             = 128M

[myisamchk]
key_buffer_size                = 256M
sort_buffer_size               = 256M
read_buffer                    = 2M
write_buffer                   = 2M

[mysqlhotcopy]
interactive-timeout

Solution 1:

For now, increase key_buffer_size to 1G.

In the long run, migrate from MyISAM to InnoDB (and change the cache sizes). That will at least get rid of the need to "repair".

What is the naughty query that is running when the issue occurs? Or do you think it is "too many users" stumbling over each other? I have seen such. The quick fix is to decrease max_connections to, say, 200 and also decrease the number of "children" the web server maintains -- this will prevent getting too many connections made in the first place.

If those don't help, provide more info by following the instructions here: http://mysql.rjweb.org/doc.php/mysql_analysis

Make the change (or addition) under [mysqld]. That is the name of the server where it matters. myisamchk is a separate utility.

This changes one table: