Mysql configuration file fine tuning

Hi all,
I was requested by the customer to look their Mysql performance issues, I havent started with anything yet but I do requested from them the configuration file. Before I suggest  something to them, could anyone expert here review the setting and gives some advise ?
Mysql_config_file.txt
motioneyeAsked:
Who is Participating?
 
theGhost_k8Connect With a Mentor Database ConsultantCommented:
Hi,
There are questions before tuning a config:
What's the data size?
What is total system memory you're having?
Is this dedicated server for mysql?
All your tables are innodb - if not what's the data size engine wise?

But to generalize, I have few points:
1. If there is one variable you want to tune with to get great performance improvement then it is: innodb_buffer_pool_size

Set this to --> innodb_buffer_pool_size = <2/3rd of available memory>

2. Then depending on how much writes you're doing, you might need to improve --> innodb_log_file_size
3. Why innodb_thread_concurrency is set to 41? If the reason is not known, set it to 0, you might want to <a href="https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_thread_concurrency">read this</a>

Also, did you note there are default my.cnfs shipped along with the installation? See if you can borrow the configuration from them. Eg. my-big.cnf.
0
 
Alessandro LionConnect With a Mentor System and Support EngineerCommented:
Tuning a configuration file without a view on DB needs or issues is impossible.
Can you attach the output o MySQLTuner ?
Basically it is a Perl script which inspect the engine running statistics to give you general advises about the first setting.
I often use it for the first tuning, and only if the situation is not satisfactory I will go for further consideration on estimated size of the table for the period, checking slow queries explain plan and identify, based on software architecture and DB design, which are the most recurrent operations.

Cheers
0
 
motioneyeAuthor Commented:
Thanks.
I will go the customer office and trying to collect as much information as I can. About the  current setting and etc, I was not aware what has been done, someone that has left the company do the blind installation and configuration. Its works fine until recently they started to notice some performance degradation time to time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.