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.

mysqldump (Backup using ssh) not completed, & please advice my.cnf

Discussion in 'Workarounds and Optimization' started by basketmen, Nov 12, 2010.

  1. basketmen

    basketmen Well-Known Member

    Joined:
    Sep 9, 2010
    Messages:
    118
    Likes Received:
    0
    Trophy Points:
    16
    Hi guys i just move to new server


    in previous server i can running mysql backup using mysqldump with no problem

    but in this new server, its running not completed, stopped in the middle

    the mysql size total are 2.2Gb, but its only get 650Mb



    please advice what should we do, current server's specs are bigger from the previous servers





    here is current server's specs






    here is the my.cnf content, from /http://www.pctipsbox.com/a-real-production-mycnf-file-for-mysql-server/, and please advice do you think below configuration are good for above server's specs, the server are running vbulletin big enough

     
  2. RCraft

    RCraft Well-Known Member

    Joined:
    Nov 7, 2010
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    6
    It really doesn't help you to use someone else's mysql config file since your server is going to be completely different. Every server needs to be individually tuned.

    Copy and paste this:

    wget mysqltuner.pl
    chmod755 mysqltuner.pl
    ./mysqltuner.pl

    Copy the entire output and paste it here.

    Also, how is the CPU load on this server? There's really nothing in your MySQL config file that would prevent you from doing a dump.

    You might want to try running the MySQL dump at a lower priority to reduce I/O. Try adding the following in front of the command:

    ionice -c3 nice -n19
     
    #2 RCraft, Nov 12, 2010
    Last edited: Nov 12, 2010
  3. basketmen

    basketmen Well-Known Member

    Joined:
    Sep 9, 2010
    Messages:
    118
    Likes Received:
    0
    Trophy Points:
    16
    yes i just trying his my.cnf configuration since he said in that blog its running good in some server that using vbulletin, i am still dont know how to define the my.cnf configuration, my server's specs are above in first post, please give advice





    ok here is the output MySQLTuner


    >> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at MySQLTuner
    >> 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.91-community
    [OK] Operating on 64-bit architecture

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

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 13m 44s (326K q [73.884 qps], 17K conn, TX: 12B, RX: 77M)
    [--] Reads / Writes: 73% / 27%
    [--] Total buffers: 74.0M global + 4.5M per thread (500 max threads)
    [OK] Maximum possible memory usage: 2.3G (29% of installed RAM)
    [OK] Slow queries: 0% (0/326K)
    [OK] Highest usage of available connections: 2% (12/500)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/492.0M
    [OK] Key buffer hit rate: 97.6% (25M cached / 608K reads)
    [OK] Query cache efficiency: 55.0% (129K cached / 234K selects)
    [!!] Query cache prunes per day: 128154
    [OK] Sorts requiring temporary tables: 2% (608 temp sorts / 27K sorts)
    [!!] Joins performed without indexes: 106
    [OK] Temporary tables created on disk: 8% (551 on disk / 6K total)
    [OK] Thread cache hit rate: 99% (12 created / 17K connections)
    [OK] Table cache hit rate: 97% (683 open / 697 opened)
    [OK] Open file limit used: 51% (1K/2K)
    [OK] Table locks acquired immediately: 99% (248K immediate / 248K 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
    Adjust your join queries to always utilize indexes
    Variables to adjust:
    query_cache_size (> 32M)
    join_buffer_size (> 1.0M, or always use indexes with joins)








    here is top result in little peak time

    [​IMG]
     
  4. RCraft

    RCraft Well-Known Member

    Joined:
    Nov 7, 2010
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    6
    Leave your MySQL service up for 24 hours and then run it again as per the scripts recommendation. It needs those 24 hours to get an accurate reading.
     

Share This Page