Fine-tune MYSQL buffer settings (my.cnf) - Expert needed!

Hello,

Given are the following specs for a server:

50 CPU
128 GB RAM + 16 gb ram swap
2.5 TB SSD

Websites on the server are running on Wordpress + LScache.

We currently have this following my.cnf which needs improvements:

[mysqld]
performance-schema=0
#
# 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%.
innodb_buffer_pool_size = 80G
innodb_buffer_pool_instances = 64
innodb_buffer_pool_chunk_size = 128M
#
# 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.
# join_buffer_size = 1280M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

performance-schema=0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

performance-schema=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_file_per_table=1
default-storage-engine=MyISAM
max_allowed_packet=268435456
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


query_cache_type = 1
query_cache_size = 2048M
query_cache_limit = 40M

max_connections = 4096

wait_timeout = 60
#wait_timeout = 240

table_open_cache = 1024
sort_buffer_size = 40M
read_buffer_size = 40M
read_rnd_buffer_size = 40M
myisam_sort_buffer_size = 1024M
thread_cache_size = 8
tmp_table_size=2048M

tmpdir=/dev/shm/
open_files_limit=50000


We need you to provide us a better my.cnf file which would improve and deliver better performance for the server. Currently server runs stable but we want better performance.

This job could be on-going collaboration job if you are a sysadmin expert.

Looking forward!
About the recuiter
Member since Nov 11, 2022
Soumen Chowdhury
from Pennsylvania, United States

---

---

Offer to work on this project closes in 6 days!
Are you interested in this Opportunity?

Looking for help? Checkout our video tutorial
How to search and apply for jobs

How to apply? Do you have more questions about the Job?
See frequently asked questions