Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I am running Linux 64-Bit OS. My mysql tuner is saying the things below:

-------- 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: 1914)
[--] Data in InnoDB tables: 4M (Tables: 199)
[!!] Total fragmented tables: 296

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6s (7K q [1K qps], 142 conn, TX: 8M, RX: 701K)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 1.7G global + 13.2M per thread (384 max threads)
[OK] Maximum possible memory usage: 6.6G (42% of installed RAM)
[OK] Slow queries: 0% (0/7K)
[OK] Highest usage of available connections: 5% (20/384)
[OK] Key buffer size / total MyISAM indexes: 1.5G/2.7G
[OK] Key buffer hit rate: 97.4% (553K cached / 14K reads)
[OK] Query cache efficiency: 64.6% (4K cached / 6K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 337 sorts)
[OK] Temporary tables created on disk: 8% (17 on disk / 198 total)
[OK] Thread cache hit rate: 85% (20 created / 142 connections)
[OK] Table cache hit rate: 98% (438 open / 445 opened)
[OK] Open file limit used: 10% (856/8K)
[OK] Table locks acquired immediately: 99% (2K immediate / 2K locks)
[OK] InnoDB data size / buffer pool: 4.2M/8.0M


-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate

Their recommendations is to run OPTIMIZE TABLE to defragment tables for better performance; But I don't know how to run it via Putty.

Is there anyone who can give me the code how do I run it via Putty?

share|improve this question

2 Answers 2

up vote 5 down vote accepted

OPTIMIZE TABLE is a SQL statement - run it in a MySQL client.

On the command line of the server, you should have the command line client installed; you can connect to your database in the client with mysql -u username -p.

See the documentation on OPTIMIZE TABLE for more information.

share|improve this answer
    
This site is about questions asked by professional system administrators. –  gparent Apr 26 '12 at 2:30
    
@gparent - was uhh that meant to be on the question, not Shane's perfectly valid answer? –  Mark Henderson Apr 26 '12 at 2:46
2  
@gparent Right - but I don't think it's fair to judge someone's professionalism based on knowing a SQL statement when they see it. Research would have quickly turned up the answer, but.. I don't like the idea that this community turns away questions for being too "obvious". –  Shane Madden Apr 26 '12 at 2:51
    
You're right, my comment doesn't belong here at all, I think the author posted a comment on Shane's question and then removed it, either that or I really meant to post it on Barkat's question but I would usually add more info if that was the case. –  gparent Apr 26 '12 at 3:08
    
I disagree with you though, there's a place where you have to draw the line. I think it's fair to say that a professional (faq: in a professional capacity) currently working with MySQL databases and trying to optimize them should be proficient enough to google the term and figure out it is a MySQL query from the first search result. Maybe I'm expecting too much in any employee.. –  gparent Apr 26 '12 at 3:14

You can also try mysqlcheck -u username -p --auto-repair --optimize --all-databases in the terminal.

As far as running "optimize table" you can start MySQL like this mysql -u username -p and from there you can execute commands. Typically the first command I use is show databases; but you should really find a MySQL tutorial first. The first one that came up in Google: http://downloads.mysql.com/docs/mysql-tutorial-excerpt-5.1-en.pdf

Whether you're using putty or not is irrelevant. You might want to say "from the command-line" instead of "via Putty."

share|improve this answer
1  
This is a much better solution than the one that was accepted. No need to enumerate all databases and tables prior to running the commands, plus auto-repair. –  Sammitch Dec 27 '12 at 22:46
1  
Also do yourself a favor and do a database backup before running any of this. mysqldump -u username -p dbname > dbname.sql and gzip dbname.sql should do it nicely. –  Fiasco Labs Sep 23 '13 at 2:52
    
Fiasco, in centos at least, the command should be mysqldump -u username -p db_name > dbname.sql –  yitwail Mar 20 at 19:08

protected by Michael Hampton Sep 23 '13 at 2:31

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.