1

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

2
  • 2
    if it doesn't start it will show in the mysql erro log why and you can fix whatg it reports. join buffer size should be 50 % iof the free ram. and all other seem also very low
    – nbk
    Aug 30, 2021 at 20:11
  • 1
    @nbk - No, join_buffer_size size is not that important; it should not be more than 1% of RAM.
    – Rick James
    Aug 31, 2021 at 0:15

1 Answer 1

2

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.

2
  • HI, so: #table_cache=128 #--- deprecated I wont us it innodb_buffer_pool_size = 26G #about 80% give or take Why you tell me to not use cache on production? with these a stress test it is give me results 10 times faster than production key_buffer_size=4096M # query_cache_type=0 query_cache_type=1 # query_cache_size=0 query_cache_size=704M These lines that I have currently commented out: #sort_buffer_size=31M #read_buffer_size=31M #read_rnd_buffer_size=31M #join_buffer_size=31M #thread_cache_size=128 #thread_concurrency=16 It would be better than default? thanks Sep 1, 2021 at 2:17
  • Be wary of stress tests. For example, if you run the identical Select repeatedly, the Query Cache will make it run extremely fast. But as soon as any row is changed anywhere in the table, the Select will be slow again.
    – Rick James
    Sep 1, 2021 at 2:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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