List:General Discussion« Previous MessageNext Message »
From:Joey L Date:October 24 2011 4:27pm
Subject:Re: large temp files created by mysql
View as plain text  
>>  MySQLTuner 1.2.0 - Major Hayden <major@stripped>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-3-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 9G (Tables: 2512)
[--] Data in InnoDB tables: 1M (Tables: 40)
[!!] Total fragmented tables: 58

-------- Performance Metrics -------------------------------------------------
[--] Up for: 10s (2K q [237.300 qps], 591 conn, TX: 947K, RX: 261K)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 2.3G global + 34.6M per thread (200 max threads)
[OK] Maximum possible memory usage: 9.1G (41% of installed RAM)
[OK] Slow queries: 0% (0/2K)
[OK] Highest usage of available connections: 3% (6/200)
[!!] Key buffer size / total MyISAM indexes: 2.0G/12.0G
[!!] Key buffer hit rate: 89.9% (5K cached / 524 reads)
[!!] Query cache efficiency: 17.9% (298 cached / 1K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 45 sorts)
[!!] Joins performed without indexes: 25
[!!] Temporary tables created on disk: 29% (93 on disk / 316 total)
[OK] Thread cache hit rate: 98% (6 created / 591 connections)
[!!] Table cache hit rate: 3% (407 open / 13K opened)
[OK] Open file limit used: 78% (808/1K)
[OK] Table locks acquired immediately: 100% (997 immediate / 997 locks)
[OK] InnoDB data size / buffer pool: 1.3M/8.0M

-------- 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
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    key_buffer_size (> 12.0G)
    query_cache_limit (> 2M, or use smaller result sets)
    join_buffer_size (> 32.0M, or always use indexes with joins)
    table_cache (> 407)

I just did some adjustments and used mysqltuner.pl ---any advice ??
I have 22gigs of ram on the server - so if i need to allocate - i can.
thanks
mjh
Thread
large temp files created by mysqlJoey L24 Oct
  • Re: large temp files created by mysqlJoey L24 Oct
    • Re: large temp files created by mysqlReindl Harald24 Oct
      • Re: large temp files created by mysqlmos24 Oct
        • Re: large temp files created by mysqlJoey L24 Oct
          • Re: large temp files created by mysqlReindl Harald24 Oct
        • Re: large temp files created by mysqlReindl Harald24 Oct
          • Re: large temp files created by mysqlmos24 Oct
            • Re: large temp files created by mysqlReindl Harald25 Oct
              • Re: large temp files created by mysqlmos25 Oct
                • Re: large temp files created by mysqlReindl Harald25 Oct
                  • Re: large temp files created by mysqlmos26 Oct
                    • Re: large temp files created by mysqlReindl Harald26 Oct
Re: large temp files created by mysqlAntony T Curtis24 Oct
  • Re: large temp files created by mysqlJoey L24 Oct
    • Re: large temp files created by mysqlJohan De Meersman24 Oct
Re: large temp files created by mysqlJan Steinman31 Oct
  • Re: large temp files created by mysqlJan Steinman31 Oct
    • Re: large temp files created by mysqlLuis Daniel Lucio Quiroz24 May
      • RE: large temp files created by mysqlRick James24 May
        • RE: large temp files created by mysqlLuis Daniel Lucio Quiroz24 May