All we need is an easy explanation of the problem, so here it is.
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
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).
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.
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?
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?
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.
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_size=0. This is not necessary in MySQL 8.0, because the query cache has been removed altogether.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂