有没有办法弄清楚为什么我的生产mysql这么慢？

I have a php file which parses a txt file and writes the data to a Mysql table. The xml file is quite big, with over 6 million lines. I did this on my home computer, and it took about six hours for the whole process. Now I'm trying to do the exact same thing on my beefed-up dedicated server (32GB ram), and 12 hours later, it barely got through 10% of the records.

I don't know if it's connected, but I also imported a large sql file through phpmyadmin several days ago, and I thought it took much longer than it should.

What could be the problem?

TIA!

Well, I ended up implementing all the changes to the DB settings as advised here: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

And now the db is roaring along! I'm not sure exactly which setting was the one that made the difference, but it's working now, so that the main thing! In any case all of you also gave me great advice which I'll be following up on, so thanks!

Unless you do profiling and stuff like EXPLAIN queries, it's hard to say.

There are some possibilities that may be worth investigating though:

  • Lots of indexes: If you're doing INSERTS, then every index associated with the table you're INSERTING into will need to be updated. If there's a lot of indexes, then a single insert can trigger a lot of writes. You can solve this by dropping the indexes before you start and reinstating them afterward
  • MyISAM versus InnoDB: The former tends to be faster as it sacrifices features for speed. Writing to an InnoDB table tends to be slower. NOTE: I'm merely pointing out that this is a potential cause of an application running slower, I'm not recommending that you change an InnoDB table to MyISAM!
  • No transaction: If using InnoDB, you can speed up bulk operations by doing them inside a transaction. If you're not using a transaction, then there's an implicit transaction around every INSERT you do.
  • Connection between the PHP machine and the SQL server: In testing you were probably running both PHP and the SQL server on the same box. You may have been connecting through a named pipe or over a TCP/IP connection (which has more overhead), but in either case the bandwidth is effectively unlimited. If the SQL server isn't the same machine as the one running the PHP script then it will be restricted to whatever bandwidth exists in the connection between the two.
  • Concurrent users: You were the only user at any given time of your test SQL database. The live system may and will have any number of additional users connected and running queries at a given time. That's going to take time away from your script, adding to its run time. You should run big SQL jobs at night so as not to inconvenience other users, but also so they can't take performance away from you too. There are other reasons too, but the ones above are worth investigating first.

Of course the problem may be on the PHP side, you can't be sure that it's on the database until you investigate exactly where it's slowing down and why.

Check if php memory_limit setting or Mysql buffer settings is lower on server than local.