Help – Slow MySQL Insert?

I’ve been struggling with this for what feels like months. On a project, we’re using a third party hosting provider, who offers us space on a managed server, complete with everything we need to run Drupal in a shared hosting environment. We’re running a copy of Provisionator on the server to help us deploy lots of Drupal sites easily.

Here’s where it gets messy. We can create new databases just fine, but importing a .sql file takes for freaking ever. Imports that take 3 seconds on my Powerbook can take 90 – 300 seconds on this server. Running the import on a dual G5 XServe with the same version of MySQL finishes the job in about a second.

I’ve tried removing variables from the equation. Using full-on Provisionator. Using a separate custom .php script. Using phpMyAdmin. Each take so long that browsers often time out before completing the import (leaving partially imported databases). I’ve tried command line mysql directly on the server, with the same range of very slow times.

I can’t seem to find anything that might make imports of a smallish .sql (~400K) file take so long. Unfortunately, it’s making the process of deploying new sites essentially useless for now.

The curious thing is that once a database has (eventually) been populated, the select queries seem to run at normalish speed.

Any ideas on how to get the server back up to speed? It’s running MySQL 4.1.20 with MyISAM tables on a RedHat Linux box. I don’t have access to tools like top, ps, netstat, or even env, so exact details of the box’s config are shrouded in a veil of mystery and obscurity.

I’ve been struggling with this for what feels like months. On a project, we’re using a third party hosting provider, who offers us space on a managed server, complete with everything we need to run Drupal in a shared hosting environment. We’re running a copy of Provisionator on the server to help us deploy lots of Drupal sites easily.

Here’s where it gets messy. We can create new databases just fine, but importing a .sql file takes for freaking ever. Imports that take 3 seconds on my Powerbook can take 90 – 300 seconds on this server. Running the import on a dual G5 XServe with the same version of MySQL finishes the job in about a second.

I’ve tried removing variables from the equation. Using full-on Provisionator. Using a separate custom .php script. Using phpMyAdmin. Each take so long that browsers often time out before completing the import (leaving partially imported databases). I’ve tried command line mysql directly on the server, with the same range of very slow times.

I can’t seem to find anything that might make imports of a smallish .sql (~400K) file take so long. Unfortunately, it’s making the process of deploying new sites essentially useless for now.

The curious thing is that once a database has (eventually) been populated, the select queries seem to run at normalish speed.

Any ideas on how to get the server back up to speed? It’s running MySQL 4.1.20 with MyISAM tables on a RedHat Linux box. I don’t have access to tools like top, ps, netstat, or even env, so exact details of the box’s config are shrouded in a veil of mystery and obscurity.

7 thoughts on “Help – Slow MySQL Insert?”

  1. I can bulk copy from csv’s into a staging db on Mysql. That process take about 2-3 minutes. I then, make some changes and insert into production – which take almost 2 hours for the same tables. It is painful. I am set to INNDB, have modified pool buffers, removed keys, added keys, flushed tables …
    Nothing seems to help. Its not network as staging and production are on the same server!

    Why is MySQL soo slow with inserts???

    1. I had the same problem today. Import of 1.3Mb of SQL code took 30 minutes. Usually it makes a minute or two. I work with InnoDB tables. I tried different parameters changing, but only one helped me. It is actual only for UNIX systems.
      innodb_flush_method = O_DSYNC
      Try it! 😉

  2. One of the little bugs we found with inserts lately is running 64 bit software on NON-64 bit cpus. The reads seem to work normally as you described but the inserts are nightmarishly slow. 64 bit software deals with buffers differently and while its not technically a bug it is annoying. So if you have CPU’s that are not dual core and are running 64bit software this will explain your results.

Comments are closed.