The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Mysql optimization for moodle my.cnf empty

Discussion in 'Workarounds and Optimization' started by goober21, Feb 20, 2012.

  1. goober21

    goober21 Registered

    Joined:
    Feb 20, 2012
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I'm in the process of optimizing my moodle installation/server it has about 7 separate sites of varying usage. Reports and other tasks seem to be timing. looked at my.cnf and noticed it was empty. /etc/my.cnf

    I did a locate and all others i've found haven't had the standard or much info either.

    server specs:
    12gb memory
    x2 xeon 3.47 ghz
    raid 5 10k drives

    I realize my mysqltuner is only for a couple days. i'll certainly post another at the end of this week if it helps get a better baseline of what kind of config would make sense to better utilize the servers possible performance from a sql standpoint.
    thanks much!


    here are my mysqltuner.pl results.
    >> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
    >> 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.0.92-community
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 1831)
    [--] Data in InnoDB tables: 4M (Tables: 33)
    [!!] Total fragmented tables: 231

    -------- Security Recommendations -------------------------------------------
    [OK] All database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 20h 40m 21s (4M q [14.949 qps], 206K conn, TX: 13B, RX: 536M)
    [--] Reads / Writes: 81% / 19%
    [--] Total buffers: 34.0M global + 2.7M per thread (100 max threads)
    [OK] Maximum possible memory usage: 309.0M (2% of installed RAM)
    [OK] Slow queries: 0% (11/4M)
    [OK] Highest usage of available connections: 20% (20/100)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/1.0G
    [OK] Key buffer hit rate: 98.9% (221M cached / 2M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 192K sorts)
    [!!] Joins performed without indexes: 5120
    [!!] Temporary tables created on disk: 45% (475K on disk / 1M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 173K opened)
    [OK] Open file limit used: 12% (124/1K)
    [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
    [OK] InnoDB data size / buffer pool: 4.6M/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
     
  2. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Well,

    First start off with some reasonable settings, and than re-run another report after 24 hours of running. Just make sure to restart mysql after making changes to the my.cnf

    Try something like
     
    #2 srpurdy, Feb 20, 2012
    Last edited: Feb 20, 2012
  3. goober21

    goober21 Registered

    Joined:
    Feb 20, 2012
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I inputted the config you provided exactly into /etc/my.cnf and restarted mysql service.. wouldn't come up.

    Sorry for my lack of knowledge on this front. Would there be a place i can check to see why it didnt' start?
    I did create the /var/log/mysql-slow-queries.log #Make sure this file exists and is owned by mysql.
    and chown mysql:mysql to confirm it is owned by mysql user.

    thanks so much for your help with this. so strange the config files are all empty i have 3 cpanel servers on centos and all have the empty config.
     
  4. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,474
    Likes Received:
    202
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    You could use one of the Preconfigured Option Files as needed for a base configuration.

    These are not set or configured by the system, it's up to the Server Administrator.
     
  5. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    They start out empty mysql has default settings. So it's not so unusual. :)

    are you getting any errors?

    Make sure that this line

    slow-query-log-file = /var/log/mysql-slow-queries.log #Make sure this file exists and is owned by mysql.

    is 1 line and not two lines.

    Shawn
     
Loading...

Share This Page