Truemag

  • Categories
    • Tips And Tricks
    • Internet
    • PHP
    • Javascript
    • CSharp
    • SQL Server
    • Linux
  • Lastest Videos
  • Our Demos
  • About
  • Contact
  • Home
  • Write With Us
  • Job Request
Home MySQL How To Tune And Optimize MySQL Performance With MySQLTuner

How To Tune And Optimize MySQL Performance With MySQLTuner

MySQLTuner is a Perl script that helps to analyzes your current MySQL configuration and give some recommendations to adjust your settings to increase the performance and stability.

The MySQLTuner output is separated into 5 parts as below:

  • General Statistics
  • Storage Engine Statistics
  • Security Recommendations
  • Performance Metrics
  • Recommendations

You should go through all parts especially on the Recommendations. It’s the main part which summarizes everything you need to step by step review your MySQL installation or make changes on some settings in the configuration file (usually located in /etc/my.cnf or /etc/mysql/my.cnf).

1. Get and run MySQLTuner on your server

  • Login to your server with root account vis SSH then go to the tmp directory:

    [text] cd /tmp
    [/text]
  • Download the MySQLTuner script: [text] wget http://mysqltuner.pl/mysqltuner.pl
    [/text]
  • Make it executable

    [text] chmod +x mysqltuner.pl
    [/text]
  • Run it:

    [text] ./mysqltuner.pl
    [/text]

    Note: you may be asked to enter the MySQL root password, if yes, please provide.

2. Example of the MySQLTuner output

[text] >> MySQLTuner 1.2.0 – 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.0.77
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[–] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 6G (Tables: 137)
[–] Data in InnoDB tables: 2M (Tables: 139)
[!!] BDB is enabled but isn’t being used
[!!] Total fragmented tables: 19

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

——– Performance Metrics ————————————————-
[–] Up for: 43d 6h 14m 17s (25M q [6.849 qps], 1M conn, TX: 78B, RX: 3B)
[–] Reads / Writes: 96% / 4%
[–] Total buffers: 34.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 309.0M (3% of installed RAM)
[OK] Slow queries: 0% (0/25M)
[OK] Highest usage of available connections: 42% (42/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.4G
[OK] Key buffer hit rate: 99.5% (2B cached / 12M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (16 temp sorts / 6M sorts)
[!!] Temporary tables created on disk: 49% (5M on disk / 10M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 43K opened)
[OK] Open file limit used: 10% (104/1K)
[OK] Table locks acquired immediately: 99% (32M immediate / 32M locks)
[OK] InnoDB data size / buffer pool: 2.3M/8.0M

——– Recommendations —————————————————–
General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)

[/text]

Please leave your opinion in the comment or share your solution.

Jun 24, 2011Hoan Huynh
Google Toolbal is disabled and disappeared on Firefox 5Tips For Tuning And Optimizing Mysql On Low Memory Dedicated Server Or VPS
You Might Also Like:
  • Tips For Tuning And Optimizing Mysql On Low Memory Dedicated Server Or VPS
  • Schedule Backup And Zip MySQL Database In Windows
  • Mysql Create FullText Index and Load Index Into Cache
  • MySql backup database with gzip compression
  • Mysql restore database from dump file with GZIP compression
  • MySQL dump table
  • Change or reset MySQL Root Account Password
  • Where MySQL Data Dir And Innodb Data Home Dir Location
  • Install LAMP (Apache, MySQL and PHP in Linux) on CentOS
  • Shell script backup all Mysql Databases in Linux
Hoan Huynh

Hoan Huynh is the founder and head of 4rapiddev.com. Reach him at [email protected]

9 years ago MySQLMySQLTuner, optimize, performance143
0
GooglePlus
0
Facebook
0
Twitter
0
Digg
0
Delicious
0
Stumbleupon
0
Linkedin
0
Pinterest
Most Viewed
PHP Download Image Or File From URL
22,164 views
Notepad Plus Plus Compare Plugin
How To Install Compare Text Plugin In Notepad Plus Plus
20,041 views
Microsoft SQL Server 2008 Attach Remove Log
Delete, Shrink, Eliminate Transaction Log .LDF File
15,821 views
JQuery Allow only numeric characters or only alphabet characters in textbox
13,296 views
C# Read Json From URL And Parse/Deserialize Json
9,790 views
4 Rapid Development is a central page that is targeted at newbie and professional programmers, database administrators, system admin, web masters and bloggers.
Recent Posts
  • Installment Loans – Making Sense of Online Software
  • Apple Pay Casino Canada
  • Casinos austria
  • Essay For Sale – How To Write A Superb One That Will Sell
  • Free Online Photo Editor
Categories
  • CSharp (45)
  • Facebook Graph API (19)
  • Google API (7)
  • Internet (87)
  • iPhone XCode (8)
  • Javascript (35)
  • Linux (28)
  • MySQL (16)
  • PHP (84)
  • Problem Issue Error (29)
  • Resources (32)
  • SQL Server (25)
  • Timeline (5)
  • Tips And Tricks (141)
  • Uncategorized (103)
Recommended
  • Custom Software Development Company
  • Online Useful Tools
  • Premium Themes
  • VPS
2014 © 4 Rapid Development