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 used mysql tuner and here are the statistics. The recommendation says to optimize tables. I have 96 GB of memory on the machine. Is there anyway I can speed up the optimization process? The tables are as big as 1TB. Do you have any recommendations on how to speed up optimize table for large tables?

>>  MySQLTuner 1.2.0 - Major Hayden 
 >>  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.5.11
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 10270G (Tables: 1263)
[--] Data in MRG_MYISAM tables: 5G (Tables: 2)
[--] Data in InnoDB tables: 39G (Tables: 278)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 277

-------- Performance Metrics -------------------------------------------------
[--] Up for: 11h 49m 35s (46K q [1.090 qps], 9K conn, TX: 141M, RX: 8B)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 38.0G global + 272.4M per thread (151 max threads)
[OK] Maximum possible memory usage: 78.2G (81% of installed RAM)
[OK] Slow queries: 2% (1K/46K)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Key buffer size / total MyISAM indexes: 36.0G/645.0G
[OK] Key buffer hit rate: 99.7% (1B cached / 3M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (3 temp sorts / 7K sorts)
[OK] Temporary tables created on disk: 0% (10 on disk / 6K total)
[OK] Thread cache hit rate: 99% (13 created / 9K connections)
[OK] Table cache hit rate: 96% (224 open / 231 opened)
[OK] Open file limit used: 7% (290/4K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[!!] InnoDB data size / buffer pool: 39.6G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
Variables to adjust:
    query_cache_size (>= 8M)
    innodb_buffer_pool_size (>= 39G)

Thank you - Bala

share|improve this question

closed as not constructive by Ladadadada, mgorven, John Gardeniers, Antoine Benkemoun, TheCleaner Feb 15 '13 at 16:37

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Oh, you're not using the --make-it-go-faster flag? –  mgorven Feb 14 '13 at 19:34
    
I didnt get you –  Boolean Feb 15 '13 at 5:53

1 Answer 1

If you are InnoDB and also if you have indexes than OPTIMIZE table will take time , drop indexes and than run OPTIMIZE table and then add indexes back.

share|improve this answer
    
Most of the tables are myisam –  Boolean Feb 15 '13 at 5:53

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