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 comments… read them below or add one }
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???
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!
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.
If you can get the host to install mtop you can take a look at what query exactly is taking so long and perhaps to to rewrite it… Also how are you producing the sql files, are they made using mysqldump or phpMyAdmin? Also, are both the producer and the consumer of the sql file using the same version of mysql or phpmyadmin?
If you have a bunch of indexes on the tables that can slow inserts down. It could be though that server is just really overloaded.
Rather than use a .sql file to do inserts bulk import could be faster?
I was going to guess network speed, but you said “I’ve tried command line mysql directly on the server, with the same range of very slow times.” which I take to mean that you uploaded your file to the server and then used something like mysql -p db_name < myfile.sql
Other than that…you might be able to use some of the “show” statements to see what variables are set on the box to determine the problem. Or, ask the hosting provider. It’s a managed box after all – they are paid to make it work, right?
sami – I’ll see if they can install mtop. The .sql files come from a couple sources – some are created by phpMyAdmin from databases on that server, and some are “stock” .sql files like the one that comes with Drupal.
MySQL is actually running on a separate box that I can only access from our hosted server, and the stuff that’s installed on the hosted server is a bit limited.
Chris – this is for new databases, too. create database… import from .sql file… sloooooooowness. I think we need to find a way to make .sql imports work, since that’s how the web apps package up their default data.
Greggles – it might be network speed. mysql is on a different physical server, but it’s on the same LAN, as our hosted server. I can’t log in via SSH to the actual MySQL server, so I can’t try command-line MySQL from localhost, but running command-line MySQL from our hosted server on the same LAN as the MySQL server shows the same slowness.
I’ve taken a look at the server variables (via phpMyAdmin’s UI) and can’t see anything that would be causing the slowness. The dba provided to us hasn’t found anything either. I keep asking them, and sending suggestions/questions, but they’re being rather unresponsive so I’m getting desperate. We have to use this provider for political reasons – I’d much rather just buy a new XServe and manage the box myself, but we’re not allowed to do that.