MyISAM tables, InnoDB engine and my.cnf

MySQL & MariaDB Other MySQL® Questions
1

My webhost is migrating clients including me to a newer server and as part of that I have discovered that they have taken us from mysql 5.6 to mariadb 10.3
Websites appear to be still functioning. My concern is that the on the old server running mysql 5.6 the application uses MyISAM tables.
Now on the new server it appears that InnoDB is the default engine, however the tables are still MyISAM (as per https://i.imgur.com/fqoKnSC.png)

Not in a position to convert the tables to InnoDB as legacy application.

According to MariaDB Memory Allocation - MariaDB Knowledge Base
“If only using MyISAM, set key_buffer_size to 20% of available RAM. (Plus innodb_buffer_pool_size=0)”

My question therefore is, should I be doing the recommendation as per maridb’s website as I’m unclear.
I seem to be using MyISAM tables and the InnoDB engine but I don’t know if mariadb are referring to MyISAM tables or engine for the key buffer size recommendation.

cPanel says

"Memory Information
[    0.000000] Memory: 4097684k/5515264k available (7796k kernel code, 1049112k absent, 368468k reserved, 5947k data, 1984k init)"

Current Memory Usage

                          total            used              free       shared    buff/cache    available
Mem:        4146400     1067516     1208488      128156     1870396     2676096
Swap:       1048572                   0     1048572
Total:         5194972     1067516     2257060

Any recommendations for changes to the my.cnf the webhost implemented, given I only have MyISAM tables, InnoDB engine? I see three mentions of key buffer size!

# Baseline 4GB Configuration
# Modified 8/6/2014

[mysql]

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

[mysqld]
key_buffer_size=201326592
table_open_cache=1024
innodb_strict_mode="OFF"
sql_mode="NO_ENGINE_SUBSTITUTION"
open_files_limit = 50000

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

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

# SAFETY #
skip-external-locking
max-allowed-packet             = 16M
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               = 0
max-connections                = 300
thread-cache-size              = 25
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        = 2G
# LOGGING #
log_error                      = /var/lib/mysql/mysql.log
# log-queries-not-using-indexes  = 1
# slow-query-log                 = 1
# slow-query-log-file            = /var/lib/mysql/mysql-slow.log

[mysqldump]
quick
max_allowed_packet             = 16M

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

[mysqlhotcopy]
interactive-timeout
1 Like
2

Your my.cnf as conflicting parameters. You have key_buffer_size (size of memory caching for MyISAM indexes) in there twice. Remove the 32M line. Since you are not using InnoDB, lower InnoDB buffer pool to 256M.

However, you really should just migrate the tables to InnoDB. There is no feature in MyISAM that will not work in InnoDB. Plus MyISAM is old, dead, and hasn’t been improved in 9+ years.