Hello!
I'm trying to optimize SQL performance, so I need a little help to do the task. The server is virtualized and it has 6 Cores CPU, 8GB RAM. Server is running on SAS disks (there are no disk ques since server has deployed) and /var/lib/mysql directory size is about 28GB.
The server has the following MySQL configuration:
pastebin.com/FWTNU80A
After running mysqltuner.pl I got the following results:
pastebin.com/XgVY45qb
From report we can see that max_connections needs to be adjusted (seems that value 85 will be ok because average value is around 40).
So I have questions:
1) mysqltuner suggests:
a) innodb_buffer_pool_size (>= 13G) if possible. Now it has 3.5GB configured. According to mysqlcalculator.com maximal memory usage will be too large if I increase the value. Do I need to decrease innodb_buffer_pool_instances?
b) suggest the following parameters increase:
so the question: how to determine the best compromise for these parameters? Do I need to reduce innodb_buffer_pool_size and increase some of the parameters from (point 1.b)
2) Seems the parameter wait_timeout and interactive_timeout has wrongly set up. How to determine the best value?
3) innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=1G) if possible. How much impact on performance will give the change of the log file size?
Question: is the procedure bellow procedure correct for Cpanel server?
percona.com/blog/2011/07/09/how-to-change-innodb_log_file_size-safely/
Do you have other comments / suggestions?
Than you in advance.
P.S. - seems the topic more covers Database Discussions category so please move it to the Forum's Databases Discussions category.
I'm trying to optimize SQL performance, so I need a little help to do the task. The server is virtualized and it has 6 Cores CPU, 8GB RAM. Server is running on SAS disks (there are no disk ques since server has deployed) and /var/lib/mysql directory size is about 28GB.
The server has the following MySQL configuration:
pastebin.com/FWTNU80A
After running mysqltuner.pl I got the following results:
pastebin.com/XgVY45qb
From report we can see that max_connections needs to be adjusted (seems that value 85 will be ok because average value is around 40).
So I have questions:
1) mysqltuner suggests:
a) innodb_buffer_pool_size (>= 13G) if possible. Now it has 3.5GB configured. According to mysqlcalculator.com maximal memory usage will be too large if I increase the value. Do I need to decrease innodb_buffer_pool_instances?
b) suggest the following parameters increase:
join_buffer_size (> 20.0M, or always use indexes with joins)
tmp_table_size (> 96M)
max_heap_table_size (> 50M)
table_open_cache (> 3800)
According mysqlcalculator maximal memory usage will be too large if I increase the value.tmp_table_size (> 96M)
max_heap_table_size (> 50M)
table_open_cache (> 3800)
so the question: how to determine the best compromise for these parameters? Do I need to reduce innodb_buffer_pool_size and increase some of the parameters from (point 1.b)
2) Seems the parameter wait_timeout and interactive_timeout has wrongly set up. How to determine the best value?
3) innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=1G) if possible. How much impact on performance will give the change of the log file size?
Question: is the procedure bellow procedure correct for Cpanel server?
percona.com/blog/2011/07/09/how-to-change-innodb_log_file_size-safely/
Do you have other comments / suggestions?
Than you in advance.
P.S. - seems the topic more covers Database Discussions category so please move it to the Forum's Databases Discussions category.
Last edited:
