3

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.

2
  • 1
    Are you sure you’re using MySQL 7.0.33? With regards to the main question, though, there’s no honest way for anyone to answer this without knowing a lot about how you’re using the database. How many concurrent queries? What sorts of queries? Types of data? Ad hoc or stored procedures? Triggers? User-defined functions? Views? Heavy writes? Heavy reads? Index width? Key length? The only person who can answer your question is the people using your system. Is it “fast enough”? If they’re happy, you can look for optimizations. If they’re not, you can look at bottlenecks – matigo May 9 at 14:29
  • Thanks for your comment @matigo! In general the page loads ok, I'm just trying to get better results for time to first byte for performance reasons related to Google's Core Web Vitals. About the MySQL version, I'm positive, as I've checked it at the server panel. We are talking about a small website that has about 4000 visits per day. Most data is varchar, and the most complicated, maybe bigger queries -ie. with joins- have indexes in place. – Rosamunda May 9 at 14:46
6

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.

4
  • 1
    BIG THANKS @Bill Karwin! Before posting I've been doing a lot of googling. Your very good answer allows me to start digging deeper (and hopefully next time do post a better, more informed question) Thank you! – Rosamunda May 9 at 17:39
  • 2
    I'm curious, what version are you really using? – Bill Karwin May 9 at 17:45
  • I just realize a silly mistake I did! 7.0.33 is my PHP version! My MySQL version is 5.6.51. Veeery silly mistake, I know!! – Rosamunda May 10 at 2:14
  • About the rest of your great reply, I'm taking each and every suggestion that you've made to google about it, so I make inform changes instead of just tweak things without knowing. – Rosamunda May 10 at 2:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.