1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.1 mysql optimization

Discussion in 'XenForo Questions and Support' started by xmlxp, Feb 25, 2013.

  1. xmlxp

    xmlxp Active Member

    my server hard disk was replaced and i had to move all my data to the new one and its like moving to a new server so i had to do the tweaks and optimizations all over again.

    OS redhat 5.9 ( 32 bits )
    dual processor
    4GB ram
    cpanel with mysql 5.1.66 & php 5.3.21 and ( i use MyISAM tables )

    xcache is installed and running perfectly

    i did some my.cnf tweaks as follwing:

    max_connections = 250
    thread_cache_size = 256
    table_open_cache = 8000
    table_definition_cache = 8000
    tmp_table_size = 512M
    max_heap_table_size = 512M
    query_cache_limit = 4M
    query_cache_size = 64M
    query_cache_type = 1
    key_buffer_size = 1024M
    join_buffer_size = 1M
    read_buffer_size = 1M
    connect_timeout = 10
    max_connect_errors = 10
    max_allowed_packet = 128M
    tmp_table_size = 64M
    myisam_sort_buffer_size = 64M

    i run mysqltuner.pl and the out put is:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.66-cll
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 4G (Tables: 364)
    [--] Data in MEMORY tables: 3M (Tables: 7)
    [!!] Total fragmented tables: 17
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 17h 58m 23s (838K q [12.961 qps], 95K conn, TX: 3B, RX: 186M)
    [--] Reads / Writes: 48% / 52%
    [--] Total buffers: 1.1G global + 4.4M per thread (250 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 2.2G (56% of installed RAM)
    [OK] Slow queries: 0% (13/838K)
    [OK] Highest usage of available connections: 16% (40/250)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/832.7M
    [OK] Key buffer hit rate: 99.9% (74M cached / 74K reads)
    [OK] Query cache efficiency: 67.5% (275K cached / 407K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 1% (630 temp sorts / 40K sorts)
    [OK] Temporary tables created on disk: 13% (130 on disk / 976 total)
    [OK] Thread cache hit rate: 99% (40 created / 95K connections)
    [OK] Table cache hit rate: 28% (514 open / 1K opened)
    [OK] Open file limit used: 5% (890/16K)
    [OK] Table locks acquired immediately: 99% (487K immediate / 487K locks)
    -------- 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
    I want to know a bout the [!!] lines in the mysqltuner report, what action i need to do?
  2. MattW

    MattW Well-Known Member

    First thing is you need to move to a server is a 64bit OS, as mysql can't make use of the RAM available to it.
  3. xmlxp

    xmlxp Active Member

    Hello Matt

    moving to a 64bit OS server is not an option for me now so i have to live with current hardware and software i got.

    from output of tuning-primer i got
    Max Memory Ever Allocated : 1.23 G
    Configured Max Per-thread Buffers : 1.08 G
    Configured Max Global Buffers : 1.06 G
    Configured Max Memory Limit : 2.14 G
    Physical Memory : 3.90 G
    Max memory limit seem to be within acceptable norms
    since mysql is limitted to 2 GB usage because of my server 32-bit OS, i would be in the safe side as long as Max Memory Ever Allocated is withen the range of 2 GB, its currently 1.23 GB, if you have comment on this please post it.

    what about this line
    [!!] Total fragmented tables: 17

    what varialbe is related to this line and whats the best value for it?
  4. MattW

    MattW Well-Known Member

    If you have phpMyAdmin installed, you can look for tables in the database which have an overhead on them, and Optimize them, which will reduce fragmentation.

    As for the rest, I was in the same situation until I moved to a 64bit OS, so I had to live with the current settings I had until I could tell MySQL to use more.

    Does you XenForo install have MyISAM tables, or are they InnoDB?
  5. xmlxp

    xmlxp Active Member

    MyISAM tables

    done table optimization from phpMyAdmin , I am amazed about phpMyAdim stability, it optimized xf_post table of size 2.2 GB and didnt crash !!

    the only table with overhead and there was no optimize option was xf_session_activity , is that normal?

Share This Page