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.

Fast, and slow site!

Discussion in 'Workarounds and Optimization' started by Cedis, Dec 16, 2012.

  1. Cedis

    Cedis Member

    Joined:
    Jun 18, 2012
    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Slow MySQL import

    (take note that the thread title has changed)

    Hi! I have a new dedicated server with cPanel, latest version. Running PHP 5.3 and MySQL 5.5.

    From start importing an SQL file either through phpmyadmin or from ssh using source, it's really really slow. A 5 Mb file will take minutes. On my own computer, locally, it's dead fast. Less than a second.

    Using the export function, it's fast. No problems there. It's just the import that is painfully slow and almost unusable. Anthing bigger than 5 mb, say 10+ It always timeout and I have to resend the file for import. And sometimes it just won't complete.

    This must be a config issue. My my.cnf file us from another server I use that is fast as hell.

    What could be causing such slow imports?
     
    #1 Cedis, Dec 16, 2012
    Last edited: Dec 16, 2012
  2. Travis

    Travis Active Member
    Staff Member

    Joined:
    Apr 24, 2002
    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    1
    Location:
    cPanel Main Office
    cPanel Access Level:
    Root Administrator
    Re: Slow MySQL import

    Have you run some speed tests on the server to ensure your download and upload speeds are where you want them?
     
  3. Cedis

    Cedis Member

    Joined:
    Jun 18, 2012
    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Re: Slow MySQL import

    That cannot be the issue as I'm doing the import from the server.
     
  4. Cedis

    Cedis Member

    Joined:
    Jun 18, 2012
    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Can someone please help me?

    I just uploaded a 60 kb file (phpmyadmin) to drop and insert new data. It took over a minute.

    Why is the performance so slow? I can only think it have to do with limits to inserting / updatering data in databases.

    Btw, websites are loading fine at normal speeds.
     
  5. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,446
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
  6. Cedis

    Cedis Member

    Joined:
    Jun 18, 2012
    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I've been playing with mysqltuner.pl already, but I also installed mysqlmymonlite and it basically says the same. Here's the output of the mysqltuner version.

    Code:
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    mysqltuner.pl [found]
    
     >>  MySQLTuner 1.2.1 mod - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Modified by George Liu (eva2000) at http://vbtechsupport.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.5.28-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 5M (Tables: 88)
    [--] Data in InnoDB tables: 39M (Tables: 511)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 2)
    [!!] Total fragmented tables: 9
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3h 8m 55s (21K q [1.872 qps], 891 conn, TX: 59M, RX: 2M)
    [--] Reads / Writes: 85% / 15%
    [--] Total buffers: 335.0M global + 8.0M per thread (200 max threads)
    [OK] Maximum possible memory usage: 1.9G (24% of installed RAM)
    [OK] Slow queries: 0% (0/21K)
    [OK] Highest usage of available connections: 3% (6/200)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/722.0K
    [OK] Key buffer hit rate: 97.1% (4K cached / 116 reads)
    [OK] Query cache efficiency: 80.4% (14K cached / 17K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 264 sorts)
    [OK] Temporary tables created on disk: 19% (95 on disk / 500 total)
    [OK] Thread cache hit rate: 99% (6 created / 891 connections)
    [OK] Table cache hit rate: 88% (644 open / 726 opened)
    [OK] Open file limit used: 5% (224/4K)
    [OK] Table locks acquired immediately: 100% (4K immediate / 4K locks)
    [OK] InnoDB data size / buffer pool: 39.5M/128.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.
        Do not forget to disable slow query logging after troubleshooting
       - For MySQL 5.0 http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
       - For MySQL 5.1 http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
       - For MySQL 5.5 http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
       - For MySQL 5.6 http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
    
    
    Report Complete:
    Tue Feb 19 14:35:23 CET 2013
    ----------------------------
    
    There is no hint here as to why SQL import dumps are so painfully slow...?
     
    #6 Cedis, Feb 19, 2013
    Last edited: Feb 19, 2013
  7. arunsv84

    arunsv84 Well-Known Member

    Joined:
    Oct 20, 2008
    Messages:
    373
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    127.0.0.1
    cPanel Access Level:
    Root Administrator
    Is there any upload limit set? Have you checked the phpmyadmin upload limit ?
     
  8. Cedis

    Cedis Member

    Joined:
    Jun 18, 2012
    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Even via the command line using mysql and the source command it takes forever.

    The hosting company that gives support for my server says its due to the i/o of the harddrives being very slow.

    Can slow i/o really make it so different in read vs write mode?
     
Loading...

Share This Page