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?

Mark Henderson
  • 68,823
  • 31
  • 180
  • 259
Barkat Ullah
  • 145
  • 1
  • 3
  • 9

2 Answers2

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

PJ Brunet
  • 586
  • 1
  • 5
  • 15
  • 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 02: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
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.

Shane Madden
  • 114,520
  • 13
  • 181
  • 251
  • This site is about questions asked by professional system administrators. – gparent Apr 26 '12 at 02:30
  • @gparent - was uhh that meant to be on the question, not Shane's perfectly valid answer? – Mark Henderson Apr 26 '12 at 02: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 02: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 03: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 03:14
  • I disagree with you. There is no need to draw a line and anyway, where do you draw it. Questions are both easily vetted by user score and up votes. I have a hard time seeing how someone's "simple" question is getting in your way. In any case, four people found this question useful enough to up vote it and many more so the answers. Your "should be" is, unfortunately, entirely subjective. – dmgig Jan 30 '15 at 18:20