MySQL Optimization Request - my.cnf Tuning for Quad Core Processor - 6GB Ram

mdp · Mar 21, 2014

Good Day!

Looking forward to getting guidance on optimizing my mysql server to keep the load down and function efficiently.

Below posted is the information that would help base suggestions. Thanks for your time in advance.

Server Details:

Code:

Dual Xeon 5620 Quad core Processors
6 GB RAM
500 GB SATA x 2
RAID 1 Controller
Redhat Enterprise 5.1

Code:

my.cnf settings
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
group_concat_max_len=10000000
key_buffer_size = 512M
thread_cache_size = 25
query_cache_size = 64M
query_cache_limit = 2M
#tmp_table_size = 24M
#max_heap_table_size = 24M
skip-innodb
default-storage-engine = myisam
table_open_cache  = 500
#table_definition_cache = 20000
#open_files_limit = 15000
#low_priority_updates=1
#concurrent_insert=ALWAYS
log-slow-queries=/var/lib/mysql/slow.log

Code:

mysqltuner.pl output
perl mysqltuner.pl

 >>  MySQLTuner 1.3.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
[OK] Currently running supported MySQL version 5.5.36-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED -InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 12G (Tables: 59790)
[!!] Total fragmented tables: 30

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned



-------- Performance Metrics -------------------------------------------------
[--] Up for: 16h 16m 25s (1M q [20.720 qps], 78K conn, TX: 2B, RX: 247M)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 592.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 997.8M (16% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 46% (70/151)
[OK] Key buffer size / total MyISAM indexes: 512.0M/1.5G
[OK] Key buffer hit rate: 97.3% (56M cached / 1M reads)
[!!] Query cache efficiency: 17.3% (148K cached / 855K selects)
[!!] Query cache prunes per day: 1021830
[OK] Sorts requiring temporary tables: 0% (197 temp sorts / 167K sorts)
[!!] Temporary tables created on disk: 39% (135K on disk / 348K total)
[OK] Thread cache hit rate: 99% (71 created / 78K connections)
[!!] Table cache hit rate: 0% (500 open / 96K opened)
[OK] Open file limit used: 85% (998/1K)
[OK] Table locks acquired immediately: 100% (707K immediate / 707K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_limit (> 2M, or use smaller result sets)
    query_cache_size (> 64M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 500)

"top" Command Output

top - 22:44:54 up 602 days, 23:50, 1 user, load average: 1.41, 1.20, 1.44
Tasks: 255 total, 1 running, 254 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.3%us, 0.1%sy, 0.0%ni, 98.6%id, 1.1%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 6220724k total, 6030940k used, 189784k free, 226260k buffers
Swap: 9438176k total, 84k used, 9438092k free, 4678788k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
30382 mysql 15 0 1021m 576m 4496 S 122.5 9.5 803:49.24 mysqld
16544 apache 15 0 124m 50m 31m S 4.0 0.8 0:04.25 httpd

"free" command output

free
total used free shared buffers cached
Mem: 6220724 6025900 194824 0 225648 4675496
-/+ buffers/cache: 1124756 5095968
Swap: 9438176 84 9438092

cPanelMichael · Mar 21, 2014

Hello

16 hours should be sufficient, but ideally you want to let MySQL run for at least 24 hours to get the most accurate results from a tuner.

Thank you.

mdp · Mar 21, 2014

Thanks for the heads up. I will post back the results after about 8 hours

mdp · Mar 21, 2014

As suggested, below is the latest output after almost 24 hours of mysql running. Request optimization tips please!

Server Details

Code:

Dual Xeon 5620 Quad core Processors
6 GB RAM
500 GB SATA x 2
RAID 1 Controller
Redhat Enterprise 5.1

my.conf settings

Code:

my.cnf settings
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
group_concat_max_len=10000000
key_buffer_size = 512M
thread_cache_size = 25
query_cache_size = 64M
query_cache_limit = 2M
#tmp_table_size = 24M
#max_heap_table_size = 24M
skip-innodb
default-storage-engine = myisam
table_open_cache  = 500
#table_definition_cache = 20000
#open_files_limit = 15000
#low_priority_updates=1
#concurrent_insert=ALWAYS
log-slow-queries=/var/lib/mysql/slow.log

Code:

perl mysqltuner.pl

 >>  MySQLTuner 1.3.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
 >>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.36-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED -InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 15G (Tables: 70038)
[!!] Total fragmented tables: 43

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned


-------- Performance Metrics -------------------------------------------------
[--] Up for: 23h 36m 4s (1M q [20.884 qps], 120K conn, TX: 2B, RX: 358M)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 592.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 997.8M (16% of installed RAM)
[OK] Slow queries: 0% (8/1M)
[OK] Highest usage of available connections: 46% (70/151)
[OK] Key buffer size / total MyISAM indexes: 512.0M/2.2G
[OK] Key buffer hit rate: 97.1% (79M cached / 2M reads)
[!!] Query cache efficiency: 16.5% (203K cached / 1M selects)
[!!] Query cache prunes per day: 1027183
[OK] Sorts requiring temporary tables: 0% (267 temp sorts / 236K sorts)
[!!] Temporary tables created on disk: 38% (198K on disk / 510K total)
[OK] Thread cache hit rate: 99% (74 created / 120K connections)
[!!] Table cache hit rate: 0% (500 open / 438K opened)
[OK] Open file limit used: 85% (997/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: [url=http://bit.ly/1mi7c4C]table_cache negative scalability - MySQL Performance Blog[/url]
Variables to adjust:
    query_cache_limit (> 2M, or use smaller result sets)
    query_cache_size (> 64M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 500)

"top" command output

Code:

 top
top - 05:54:18 up 603 days,  7:00,  1 user,  load average: 2.56, 3.21, 2.96
Tasks: 256 total,   1 running, 255 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.3%us,  0.1%sy,  0.0%ni, 98.6%id,  1.1%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   6220724k total,  5497128k used,   723596k free,   180300k buffers
Swap:  9438176k total,       84k used,  9438092k free,  4141436k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
30382 mysql     15   0 1085m 655m 4516 S 193.4 10.8   1173:23 mysqld

"free" command output

Code:

free
             total       used       free     shared    buffers     cached
Mem:       6220724    5644120     576604          0     193020    4274768
-/+ buffers/cache:    1176332    5044392
Swap:      9438176         84    9438092

mdp · Mar 22, 2014

And here is a latest mysqltuner report

Code:

perl mysqltuner.pl

 >>  MySQLTuner 1.3.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
 >>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.36-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED -InnoDB +MRG_MYISAM



[--] Data in MyISAM tables: 15G (Tables: 70038)
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 17h 28m 51s (3M q [23.480 qps], 235K conn, TX: 4B, RX: 691M)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 592.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 997.8M (16% of installed RAM)
[OK] Slow queries: 0% (17/3M)
[OK] Highest usage of available connections: 46% (70/151)
[OK] Key buffer size / total MyISAM indexes: 512.0M/2.1G
[OK] Key buffer hit rate: 95.9% (127M cached / 5M reads)
[!!] Query cache efficiency: 14.9% (365K cached / 2M selects)
[!!] Query cache prunes per day: 1198010
[OK] Sorts requiring temporary tables: 0% (429 temp sorts / 461K sorts)
[!!] Temporary tables created on disk: 38% (397K on disk / 1M total)
[OK] Thread cache hit rate: 99% (101 created / 235K connections)
[!!] Table cache hit rate: 0% (500 open / 874K opened)
[!!] Open file limit used: 86% (1K/1K)
[OK] Table locks acquired immediately: 100% (2M immediate / 2M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: /http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/ table_cache negative scalability - MySQL Performance Blog
Variables to adjust:
    query_cache_limit (> 2M, or use smaller result sets)
    query_cache_size (> 64M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 500)
    open_files_limit (> 1161)

mdp · Mar 23, 2014

Hi, Can anyone help me out! Thanks.

euro-space · Mar 24, 2014

Enabling large page support in memory, significantly helped me with the mysql optimization and dropped CPU usage from ~30% to ~10%-15%.

You can read all memory related stuff at - MySQL :: MySQL 5.0 Reference Manual :: 14.4 The MEMORY (HEAP) Storage Engine and understand how it works here - MySQL :: MySQL 5.5 Reference Manual :: 8.11.4.1 How MySQL Uses Memory .
After that you can follow these steps - MySQL :: MySQL 5.5 Reference Manual :: 8.11.4.2 Enabling Large Page Support

Thanks to gelleby for that info at Stackoverflow.

thinkbot · Mar 24, 2014

Code:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
myisam_use_mmap=1

group_concat_max_len=10000000
key_buffer_size = 4G
thread_cache_size = 50
query_cache_size = 100M
query_cache_limit = 2M
tmp_table_size = 64M
max_heap_table_size = 64M
skip-innodb
default-storage-engine = myisam
table_open_cache  = 7500
table_definition_cache = 5000
open_files_limit = 25000
#low_priority_updates=1
#concurrent_insert=ALWAYS
slow_query_log=1
slow_query_log_file=/var/lib/mysql/mysql-slow.log
long_query_time=0.1

key_buffer_size should be always kept higher than used MyiSAM Indexes size,
[OK] Key buffer size / total MyISAM indexes: 512.0M/2.1G

so if all MyISAM indexes are used, higher than 2.1G

mdp · Mar 25, 2014

@euro-space - Thanks, will check it out. But I guess its for innodb, I am using MyIsam

@thinkbot - Thanks for the inputs. I made the changes and after restarting the server, started getting the following errors:

Error Number: 5 Out of memory (Needed 1245304 bytes) for many queries/page loads.
Out of memory (Needed 16391 bytes)

So I reverted back to the original my.cnf file, but increased the value of key_buffer_size = 4G. The current below is the my.cnf file

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
group_concat_max_len=10000000
key_buffer_size = 4GB
thread_cache_size = 25
query_cache_size = 64M
query_cache_limit = 2M
#tmp_table_size = 24M
#max_heap_table_size = 24M
skip-innodb
default-storage-engine = myisam
table_open_cache = 500
#table_definition_cache = 20000
#open_files_limit = 15000
#low_priority_updates=1
#concurrent_insert=ALWAYS
log-slow-queries=/var/lib/mysql/slow.log

I also recollect that earlier when I had made changes (increased the values of open_files_limit, table_open_cache, table_definition_cache, I got that same error and therefore you will notice that they have been commented.)

Which variable could be the reason for out of memory error? -- open_files_limit, table_open_cache, table_definition_cache, tmp_table_size, query_cache_size ??

Thread starter	Similar threads	Forum	Replies	Date
A	MySQL Optimization Request	Workarounds and Optimization	3	Dec 7, 2015
S	High MySQL and server load. Optimization Request	Workarounds and Optimization	5	Oct 14, 2015
F	vBulletin + MySQL + cPanel / WHM dedicated server optimization request	Workarounds and Optimization	8	Mar 24, 2014
O	MySQL optimization request	Workarounds and Optimization	14	Feb 1, 2014
H	Mysql and Server optimization request	Workarounds and Optimization	0	Dec 7, 2010

Search

Search

MySQL Optimization Request - my.cnf Tuning for Quad Core Processor - 6GB Ram

mdp

Member

cPanelMichael

Administrator

mdp

Member

mdp

Member

mdp

Member

mdp

Member

euro-space

Member

thinkbot

Well-Known Member

mdp

Member