Help optimizing mysql, please.

Hi all,
I am having a very high load in my server, I have manage to tweak mysql configuration but want to know if there is something else that I should improve and hope you can help me.

My server has REHL 6 / 64 bits with 12GB RAM and WHM says it has 16 processors (physically two Intel(R) Xeon(R) CPU E5520 @ 2.27GHz), what it is weird is that WHM shows the legend "Total processors: 10" but it shows 16, so, go figure.

Any way, here is what I have in my.cnf:

I have run mysqltuner.pl and this is what is showing for more than 48 hours:

Thanks in advance.

Sergio

 

BUMP :D
no help at all?

 

Sorry for the delay in answering back, but as you know we have to wait 24 hours for any changes to see how it works.

Ok, here is the last my.cfn configuration that I did, using all your inputs, thank you for that:

I used to have a maximum 400 connections but after reviewing all the tests that I did and that no more of the 10% (40 connections) were made, I drop the connections to 250, and that helped me a lot, the load in my server has dropped considerably.

This is the last info that mysqltuner shows now:

For the Total fragmented tables: 2887, I have ran so many times the optimaztion and repair command and everything shows OK, only some tables that can't be optimized or repaired because are olders. So, what I have to do there?

What do you do with the query_cache_size? it says it needs more than 150MB but at the same time is says that the size over 128M may reduce performance.

Why tmp_table_size and max_heap_table_size always requires more? is there a size that I could set in order for this not to be adjusted every 24 hours?

Once again, thanks.


Sergio

 

Well, finally I manage to set my.cnf at a really nice configuration, here is my last mysqltuner:

It seems that the "join_buffer_size" will never be fixed; so, I will left it as it is now, "tmp_table_size and max_head_table_size" will be gradually adjust as needed.

Sergio

 

Thank you, Tank.

I will watch the video.

Sergio

 

I have seen the tutorial but it is kind of old, it is from 2010, I assume a lot of thing has changed since then. The only thing that I have take from that video is the variable "thread_concurrency" that was set in my.cnf, so i deleted it and I will be checking until tomorrow how it goes.

About what storminternet said, yes, I did that. But, since the first post that I wrote to this day, I have learned a few things and made a lot of changes in my.cnf and I really liked the performance that my server has now. I have been using another tool to check my.cnf and used a mysql memory calculator to see if the variables and sizes were right.

 

Well, I have learned that there are some variables that could take all your memory if you don't tweak it right. All of this variables will be multiplied for each connection made to your MySQL, so, be careful on how you set them:

max_allowed_packet
sort_buffer_size
read_rnd_buffer_size
join_buffer_size <<<<<<== be carefull with this variable, just use as it says 128KB if you set this to high you can get rid of all your memory.

The following variables can bet set accordingly to your MySQL usage and are not multiplied by all the accounts, they are global variables:

query_cache_size
innodb_buffer_pool_size
key_buffer_size

tmp_table_size
max_heap_table_size
table_open_cache
open_files_limit

read_buffer_size
myisam_sort_buffer_size
thread_cache_size

To check how mucho memory have you set to your mysql, use the following online calculator MySQL &mdash; How much memory do I need?

Sergio