Help optimizing mysql, please.

S

Secmas

Well-Known Member
Feb 18, 2005
388
21
168
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:
skip-external-locking
query_cache_size = 150M
join_buffer_size = 5M
tmp_table_size = 175M
max_heap_table_size = 175M
table_open_cache = 50K
open_files_limit = 35K
key_buffer_size = 500M
max_allowed_packet = 32M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
innodb_buffer_pool_size = 200M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 12
max_connections = 400
max_user_connections = 75
slow_query_log
local-infile = 0
Click to expand...
I have run mysqltuner.pl and this is what is showing for more than 48 hours:
>> MySQLTuner 1.1.2 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.61-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1021M (Tables: 24080)
[--] Data in InnoDB tables: 184M (Tables: 2665)
[--] Data in MEMORY tables: 1M (Tables: 64)
[!!] Total fragmented tables: 2879

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 19h 53m 59s (6M q [27.391 qps], 192K conn, TX: 17B, RX: 1B)
[--] Reads / Writes: 69% / 31%
[--] Total buffers: 1.0G global + 17.2M per thread (400 max threads)
[OK] Maximum possible memory usage: 7.7G (66% of installed RAM)
[OK] Slow queries: 0% (28/6M)
[OK] Highest usage of available connections: 10% (40/400)
[OK] Key buffer size / total MyISAM indexes: 500.0M/207.7M
[OK] Key buffer hit rate: 98.2% (22M cached / 396K reads)
[OK] Query cache efficiency: 76.3% (3M cached / 4M selects)
[!!] Query cache prunes per day: 38407
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 179K sorts)
[!!] Joins performed without indexes: 17449
[!!] Temporary tables created on disk: 34% (335K on disk / 979K total)
[OK] Thread cache hit rate: 98% (2K created / 192K connections)
[OK] Table cache hit rate: 51% (41K open / 80K opened)
[OK] Open file limit used: 58% (60K/102K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 184.9M/200.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_size (> 150M) [see warning above]
join_buffer_size (> 5.0M, or always use indexes with joins)
tmp_table_size (> 175M)
max_heap_table_size (> 175M)
Click to expand...
Thanks in advance.

Sergio
 
S

Secmas

Well-Known Member
Feb 18, 2005
388
21
168
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:

innodb_buffer_pool_size = 200M
join_buffer_size = 15M
key_buffer_size = 500M
local-infile = 0
max_allowed_packet = 32M
max_connections = 250
max_heap_table_size = 250M
max_user_connections = 75
myisam_sort_buffer_size = 64M
open_files_limit = 35K
query_cache_size = 150M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
skip-external-locking
slow_query_log
sort_buffer_size = 2M
table_open_cache = 100K
thread_cache_size = 8
tmp_table_size = 250M
Click to expand...
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:

>> MySQLTuner 1.1.2 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.61-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 24144)
[--] Data in InnoDB tables: 185M (Tables: 2666)
[--] Data in MEMORY tables: 1M (Tables: 64)
[!!] Total fragmented tables: 2887

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 0h 1m 2s (2M q [34.435 qps], 86K conn, TX: 8B, RX: 464M)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 1.1G global + 27.2M per thread (250 max threads)
[OK] Maximum possible memory usage: 7.7G (66% of installed RAM)
[OK] Slow queries: 0% (19/2M)
[OK] Highest usage of available connections: 17% (44/250)
[OK] Key buffer size / total MyISAM indexes: 500.0M/209.5M
[OK] Key buffer hit rate: 99.2% (11M cached / 93K reads)
[OK] Query cache efficiency: 69.5% (1M cached / 1M selects)
[!!] Query cache prunes per day: 32783
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 77K sorts)
[!!] Joins performed without indexes: 8579
[!!] Temporary tables created on disk: 35% (196K on disk / 559K total)
[OK] Thread cache hit rate: 98% (1K created / 86K connections)
[OK] Table cache hit rate: 99% (28K open / 28K opened)
[OK] Open file limit used: 24% (49K/205K)
[OK] Table locks acquired immediately: 99% (770K immediate / 771K locks)
[OK] InnoDB data size / buffer pool: 186.0M/200.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_size (> 150M) [see warning above]
join_buffer_size (> 15.0M, or always use indexes with joins)
tmp_table_size (> 250M)
max_heap_table_size (> 250M)
Click to expand...
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
 
S

Secmas

Well-Known Member
Feb 18, 2005
388
21
168
Well, finally I manage to set my.cnf at a really nice configuration, here is my last mysqltuner:

>> MySQLTuner 1.1.2 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.61-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 24232)
[--] Data in InnoDB tables: 191M (Tables: 2673)
[--] Data in MEMORY tables: 1M (Tables: 64)
[!!] Total fragmented tables: 2945

-------- Performance Metrics -------------------------------------------------
[--] Up for: 9h 0m 37s (598K q [18.455 qps], 18K conn, TX: 1B, RX: 99M)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 902.0M global + 18.2M per thread (200 max threads)
[OK] Maximum possible memory usage: 4.4G (38% of installed RAM)
[OK] Slow queries: 0% (3/598K)
[OK] Highest usage of available connections: 18% (36/200)
[OK] Key buffer size / total MyISAM indexes: 250.0M/215.4M
[OK] Key buffer hit rate: 99.2% (2M cached / 18K reads)
[OK] Query cache efficiency: 70.7% (259K cached / 367K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 23K sorts)
[!!] Joins performed without indexes: 1663
[!!] Temporary tables created on disk: 34% (34K on disk / 98K total)
[OK] Thread cache hit rate: 98% (223 created / 18K connections)
[OK] Table cache hit rate: 99% (31K open / 31K opened)
[OK] Open file limit used: 25% (51K/205K)
[OK] Table locks acquired immediately: 99% (197K immediate / 197K locks)
[OK] InnoDB data size / buffer pool: 191.5M/200.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
join_buffer_size (> 10.0M, or always use indexes with joins)
tmp_table_size (> 255M)
max_heap_table_size (> 255M)
Click to expand...
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
 
T

tank

Well-Known Member
Apr 12, 2011
255
5
68
Chicago, IL
cPanel Access Level
Root Administrator
Last edited:
S

Secmas

Well-Known Member
Feb 18, 2005
388
21
168
tank said:
So I actually read your post. Please wait a full 24 hours before running mysqltuner. Since you last restarted mysql 9 hours ago as of that post, you have not had enough time to properly analyze. However that being said did you do as storminternet said?

Another good article that alpha wolf posted to read.

What to tune in MySQL Server after installation - MySQL Performance Blog
Click to expand...
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.
 
T

tank

Well-Known Member
Apr 12, 2011
255
5
68
Chicago, IL
cPanel Access Level
Root Administrator
That video is only a year and half old not too bad. Do you mind sharing your changes so that all of us may learn what worked in your instance? What kind of calculator are you referring too?
 
S

Secmas

Well-Known Member
Feb 18, 2005
388
21
168
tank said:
That video is only a year and half old not too bad. Do you mind sharing your changes so that all of us may learn what worked in your instance? What kind of calculator are you referring too?
Click to expand...
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
 
You must log in or register to reply here.
Thread starter Similar threads Forum Replies Date
S I need help optimizing MySQL Workarounds and Optimization 5
E Help Optimizing MySQL Workarounds and Optimization 2
johnburk Help optimizing MySQL for a 32GB system Workarounds and Optimization 1
M Need help optimizing mysql config Workarounds and Optimization 7
C Help with optimizing MySQL - already ran MySQlTuner Workarounds and Optimization 4
Similar threads
I need help optimizing MySQL
Help Optimizing MySQL
Help optimizing MySQL for a 32GB system
Need help optimizing mysql config
Help with optimizing MySQL - already ran MySQlTuner
Top Bottom