SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Optimisation suggestions

    We've had some suggestions from our server hosts for optimisations to our database. Thought I'd see if could get a second opinion here. Should I make the suggested changes?

    * Storage Engine Statistics
    [--] Status: -Archive +BDB -Federated +InnoDB +ISAM -NDBCluster
    [--] Data in MyISAM tables: 2G (Tables: 296)
    [--] Data in InnoDB tables: 3M (Tables: 124)
    [!!] BDB is enabled but isn't being used
    [!!] ISAM is enabled but isn't being used
    [!!] Total fragmented tables: 10

    * Performance Metrics
    [--] Up for: 32d 15h 26m 11s (55M q [19.511 qps], 6M conn, TX: 658M, RX: 2B)
    [--] Reads / Writes: 83% / 17%
    [--] Total buffers: 81.7M global + 2.7M per thread (250 max threads)
    [OK] Maximum possible memory usage: 753.6M (37% of installed RAM)
    [OK] Slow queries: 0% (1K/55M)
    [OK] Highest usage of available connections: 40% (101/250)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/1.2G
    [OK] Key buffer hit rate: 99.9% (58B cached / 44M reads)
    [!!] Query cache efficiency: 0.1% (29K cached / 32M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (915 temp sorts / 15M sorts)
    [!!] Joins performed without indexes: 1613974
    [OK] Temporary tables created on disk: 23% (5M on disk / 21M total)
    [!!] Thread cache hit rate: 0% (6M created / 6M connections)
    [!!] Table cache hit rate: 0% (96 open / 1M opened)
    [OK] Open file limit used: 14% (148/1K)
    [OK] Table locks acquired immediately: 99% (73M immediate / 73M locks)
    [OK] InnoDB data size / buffer pool: 3.0M/8.0M

    * Recommendations
    General recommendations:
    Add skip-bdb to MySQL configuration to disable BDB
    Add skip-isam to MySQL configuration to disable ISAM (MySQL > 4.1.0)
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_limit (> 4B, or use smaller result sets)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    thread_cache_size (> 250)
    table_cache (> 96)

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    Yorkshire, UK
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you're not using ISAM and BDB table types, and have no intention of using them, it makes sense to disable them. I'm not sure what impact it will have on performance, however.

    It definitely makes sense to run your JOIN queries on Indexed columns - this can speed up the queries considerably. It also makes sense to OPTIMIZE your tables regularly, especially if you have deleted many rows from a table. MySQL makes a note of deleted rows and uses these positions to insert new rows. OPTIMIZE will defragment the tables, removing unused rows.

    Regarding the cache, without knowing anything about your Performance Metrics, it is hard to say what the impacts will be. Caching usually helps if the same query is being run many times in a short period - you can save the result and use it many times rather than re-running the query. If any tables are changed, the cached results are removed. The variable "query_cache_limit" needs to be large enough to store the query results - try setting it to a large value (a few MB) to see what happens.
    There's more to me than meets the eye, but not much more.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •