Skip to main content

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

mdp
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: Dual Xeon 5620 Quad core Processors 6 GB RAM 500 GB SATA x 2 RAID 1 Controller Redhat Enterprise 5.1
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
mysqltuner.pl output perl mysqltuner.pl >> MySQLTuner 1.3.0 - Major Hayden >> 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 [quote]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 [QUOTE]free total used free shared buffers cached Mem: 6220724 6025900 194824 0 225648 4675496 -/+ buffers/cache: 1124756 5095968 Swap: 9438176 84 9438092

Comments

8 comments

Date Votes
  • cPanelMichael
    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.
    0
  • mdp
    Thanks for the heads up. I will post back the results after about 8 hours :)
    0
  • mdp
    As suggested, below is the latest output after almost 24 hours of mysql running. Request optimization tips please! Server Details Dual Xeon 5620 Quad core Processors 6 GB RAM 500 GB SATA x 2 RAID 1 Controller Redhat Enterprise 5.1
    my.conf settings 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
    perl mysqltuner.pl >> MySQLTuner 1.3.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> 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 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 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 free total used free shared buffers cached Mem: 6220724 5644120 576604 0 193020 4274768 -/+ buffers/cache: 1176332 5044392 Swap: 9438176 84 9438092
    0
  • mdp
    And here is a latest mysqltuner report perl mysqltuner.pl >> MySQLTuner 1.3.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> 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)
    0
  • mdp
    Hi, Can anyone help me out! Thanks.
    0
  • euro-space
    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 - [url=http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html]MySQL :: MySQL 5.0 Reference Manual :: 14.4 The MEMORY (HEAP) Storage Engine and understand how it works here - [url=http://dev.mysql.com/doc/refman/5.5/en/memory-use.html]MySQL :: MySQL 5.5 Reference Manual :: 8.11.4.1 How MySQL Uses Memory . After that you can follow these steps - [url=http://dev.mysql.com/doc/refman/5.5/en/large-page-support.html]MySQL :: MySQL 5.5 Reference Manual :: 8.11.4.2 Enabling Large Page Support Thanks to gelleby for that info at Stackoverflow.
    0
  • thinkbot
    [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
    0
  • mdp
    @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 [QUOTE][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 ??
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post