12

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?

Improve this question

2 Answers 2

Reset to default
7

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.

Improve this answer
6
  • This site is about questions asked by professional system administrators. Commented Apr 26, 2012 at 2:30
  • @gparent - was uhh that meant to be on the question, not Shane's perfectly valid answer? Commented Apr 26, 2012 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". Commented Apr 26, 2012 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. Commented Apr 26, 2012 at 3:08
  • 1
    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.. Commented Apr 26, 2012 at 3:14
18

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."

Improve this answer
3
  • 2
    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. Commented Dec 27, 2012 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. Commented Sep 23, 2013 at 2:52
  • Fiasco, in centos at least, the command should be mysqldump -u username -p db_name > dbname.sql Commented Mar 20, 2014 at 19:08

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.