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?

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 | |
  • 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
  • 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.. – gparent Apr 26 '12 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 | |
  • 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. – 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 '14 at 19:08

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