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