Mysql tuning parameters

Question

I have a MySql server (using innodb) running on a server that hosting several more process that does the main work on the DB.

I dont have a lot of experience with MySql tuning but here are the current values of some of the parameters that to my understanding are more relevant:

query_cache_size = 16M
query_cache_limit = 1M
#table_cache = 64 (currently commented out)

what values do you think these parameters should have? are there any other parameters i should change (my current parameters settings is the defaulted one)?

Thanks!

This is a very complicated issue. It depends on too many factors. First of all, do you use MyISAM engine or InnoDB (or both)? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Aug 12, 2011 at 9:50
For InnoDB, check this article: mysqlperformanceblog.com/2007/11/01/… — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Aug 12, 2011 at 9:53
@Ran Please post your complete report from MySQLTuner.pl for additional meaningful suggestions. — Wilson Hauck
– Wilson Hauck, Commented Nov 16, 2019 at 22:40

Madan · Accepted Answer · 2012-12-06 13:17:49Z

3

The tuning parameters which are used commonly most frequently.

key_buffer_size: 
query_cache_size  
tmp_table_size 
innodb_buffer_pool_size 
innodb_additional_mem_pool_size 
innodb_log_buffer_size 
max_connections 
sort_buffer_size 
read_buffer_size 
read_rnd_buffer_size 
join_buffer_size

answered Dec 6, 2012 at 13:17

Madan

6905 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Packet Tracer · Accepted Answer · 2011-08-12 11:56:21Z

1

it depends on your database and the queries you throw against it. you could use mysql_tuner to get a brief check on what your database needs.

https://github.com/rackerhacker/MySQLTuner-perl

answered Aug 12, 2011 at 11:56

Packet Tracer

3,9244 gold badges28 silver badges37 bronze badges

Comments

Wotchin · Accepted Answer · 2019-09-03 11:48:44Z

0

I always tune the following parameters:

query_cache_size  
tmp_table_size 
innodb_buffer_pool_size 
max_connections 
back_log

How to set your parameters? This depends on your workload and server. If your server had abundant memory, you should set innodb_buffer_pool_size more. And if you had high concurrency, you should give max_connections more, too. As the same time, there are some formulas, such as:

back_log = max_connections / 5 + 50

If you have no experience, I am here to suggest you read the following documents or use auto-tools:

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
https://github.com/yahoo/mysql_perf_analyzer
https://github.com/major/MySQLTuner-perl

Finally, tuning is very risky, maybe you can choose RDS(relational database service).

answered Sep 3, 2019 at 11:48

Wotchin

3002 silver badges7 bronze badges

1 Comment

Wilson Hauck Over a year ago

Please view profile, Network profile for Utility Scripts with FREE downloadable scripts to find areas to improve instance performance. Such as finding fragmented tables or redundant indexes. What is NOT risky?

DrupalAdmin · Accepted Answer · 2021-09-27 08:24:40Z

0

The optimal values of these parameters depend on your data and application. The "query_cache*" settings have been removed in the latest version of MySQL.

To improve the performance of MySQL, you can use the following tools:

https://github.com/major/MySQLTuner-perl to analyze your current settings, get recommendations and calculate settings for your server manually.
MySQL Performance Tuning as a Service to get recommended configuration automatically.

answered Sep 27, 2021 at 8:24

DrupalAdmin

256 bronze badges

Collectives™ on Stack Overflow

Mysql tuning parameters

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related