mysql tunning with larga database

Question

soo.. I have a server in a VM with a large database in MySQL 5.7 (centos), and I was trying to tune a little bit the server (32gb RAM) and, so far I'm running this my.ch I did use the perl script mysqltuner.pl

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
key_buffer_size=4096M
# query_cache_type=0
query_cache_type=1
# query_cache_size=0
query_cache_size=704M

# agregados 28/8/21
#table_cache=128
#sort_buffer_size=31M
#read_buffer_size=31M
#read_rnd_buffer_size=31M
#join_buffer_size=31M
#thread_cache_size=128
#thread_concurrency=16

#innodb_buffer_pool_size = 16G
innodb_buffer_pool_size = 28.5G
#innodb_log_file_size = 1G
innodb_log_file_size = 3G
innodb_log_buffer_size=4M
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency=0
innodb_write_io_threads = 64
innodb_flush_log_at_trx_commit=1
#innodb_buffer_pool_instances=23
innodb_buffer_pool_instances=17

#tmp_table_size=200M
tmp_table_size=318M
max_heap_table_size=318M
#table_definition_cache=2200
table_definition_cache=2800
#join_buffer_size = 512K
join_buffer_size = 1024K

# agregados 28/8/21
table_open_cache=2000
wait_timeout=25200
interactive_timeout=25200

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

character-set-server = utf8
default_authentication_plugin= mysql_native_password
bind_address=0.0.0.0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#max_connections = 150
max_connections=250

lower_case_table_names=1
show_compatibility_56=1
secure_file_priv=""
skip-name-resolve

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# Disabling symbolic-links is recommended to prevent assorted security risks

#slow-query-log=1
#long_query_time=60
#slow-query-log-file=/var/log/mysql/log-slow-queries.log

But when I uncomment some lines, like "#table_cache=128" sometimes mysql doesnt start, but sometime it does... why is this happening

Answer 1

You have overshot RAM already:

innodb_buffer_pool_size + key_buffer_size is greater than > RAM

Don't do that.

You should not be using ENGINE=MyISAM for any of your tables, so change key_buffer_size to 10M.

innodb_buffer_pool_size should be about 70% of RAM, as you seem to have set it.

Turn off the query cache on production machines: query_cache_type=0 query_cache_size=0. It has been removed in the next version (8.0).

In 5.1 (2005), table_cache was deprecated in favor of table_open_cache 5.7 (2015) made it an error. Where did you get the old name from??

It is usually safer to take the defaults instead of jacking up various interesting-looking settings.