InnoDB tables in an 8GB RAM server: Are these settings optimized?

I've been reading about how to optimize my database (MySQL 5.6.51) settings, and I've found an article suggesting to increase the key_buffer as much as possible.
Here's my my.cnf file:

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1
performance-schema=0
max_allowed_packet=32M
open_files_limit=10000
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_file_per_table=1
key_buffer_size = 32M
default-storage-engine=MyISAM

query_cache_type=2
query_cache_size=128M
query_cache_limit=2M

Should I keep these settings as they are? I'm administrating my own website database, but I'm not a proper Database Admin.

Thanks for your help.

EDIT: Sorry, by mistake I indicated my MySQL version as being 7.0.33, but that is my PHP version! (very silly mistake, I know). I corrected that in the question.

Take a look at the date on the article you linked to. It's from 2006, which is fifteen years ago as we write this. The latest version of MySQL at that time was 5.0.30.

The key_buffer_size configuration option only applies to MyISAM tables. It's the size of RAM allocated for caching MyISAM indexes. InnoDB tables do not use the key buffer.

I notice one of your settings is default-storage-engine=MyISAM which suggests you have at least some MyISAM tables, unless you have specified each of your tables is InnoDB. You can check what engine is used for each of your tables:

SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

The default storage engine in MySQL was changed to InnoDB in MySQL 5.5, circa 2010. There are many reasons to prefer to use InnoDB over MyISAM in modern versions of MySQL. You should use InnoDB unless you have a specific reason not to, and have proven with measurements that you need to use MyISAM.

You asked if your settings are optimal for a server with 8GB RAM. We don't have enough information about your usage of the database to know that.

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1

Why do you set this? Do you ever use the slow query log for diagnosing performance problems? Do you rotate the slow.log periodically so it doesn't continue to fill up your storage (IMO, no log should be allowed to exist on a production server unless you have automated log rotation).

performance-schema=0

Why do you disable the performance schema? In some old versions of MySQL, it was slow, but that has been fixed and there's no reason to disable it. I do not disable it in databases I operate, even those that are very sensitive to performance.

max_allowed_packet=32M

Is this large enough? It means you cannot have any SQL query longer than 32MB, or a result set of a query that returns a row larger than 32MB. Do you use BLOB/TEXT columns?

open_files_limit=10000

Why do you set this? Did you measure anything that suggested the default open_files_limit is not enough? How many tables do you have? What is your typical number of Threads_connected? Have you read https://dev.mysql.com/doc/refman/8.0/en/table-cache.html

innodb_buffer_pool_size = 512M

This is half of one GB, but you said you have a server with 8GB RAM. Why not use more? On the other hand, 512MB might be more than enough, if your data is small. You haven't said anything about how much data you store in InnoDB tables.

Note that the innodb config options have no effect on MyISAM tables, so if you do still use MyISAM tables, then there's no benefit to tuning innodb options.

innodb_log_file_size = 128M

Is this enough? The appropriate log file size is determined mostly by your rate of write traffic. Also it must be at least 10x the size of your largest BLOB/TEXT data. Do you have any large BLOB/TEXT columns?

innodb_file_per_table=1

This is the default in current versions of MySQL. So why specify it in the options file if it's the default anyway?

key_buffer_size = 32M

There's no point tuning key_buffer_size if you use InnoDB tables. There are still a few MyISAM tables in the mysql system schema up to version 8.0, but these tables are small and cached in other ways. The key buffer is probably not important to tune.

default-storage-engine=MyISAM

I recommend against setting the default storage engine to MyISAM. It's slower than InnoDB in most benchmarks, and doesn't support ACID properties of an RDBMS.

query_cache_type=2
query_cache_size=128M
query_cache_limit=2M

The query cache is deprecated, as it has been discovered it causes a bottleneck of performance. I recommend setting query_cache_type=0 and query_cache_size=0. This is not necessary in MySQL 8.0, because the query cache has been removed altogether.

StackExchange Database Administrators Q#291262, answer score: 6