Fast, and slow site!

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?

 

Re: Slow MySQL import

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

 

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.

 

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...?

 

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?