Is there a way to find out why my mysql product is so slow?
I have a php file that parses a txt file and writes data to a Mysql table. The xml file is quite large with over 6 million lines. I did this on my home computer and the whole process took about six hours. Now I am trying to do the same on my dedicated dedicated server (32GB RAM) and after 12 hours it barely got through 10% of the writes.
I don't know if this is related, but I also imported a large sql file via phpmyadmin a few days ago and I thought it took a lot longer than it should.
What could be the problem?
TIA!
source to share
Well, I ended up implementing all the changes to the DB settings as mentioned here: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
And now dB is roaring! I'm not sure which setting was the one that made the difference, but it works now, so the main thing! Anyway, all of you also gave me great advice that I will follow, so thanks!
source to share
Unless you're profiling and making EXPLAIN queries like this, it's hard to tell.
There are some possibilities that might be worth exploring:
- Lots of indexes: if you are doing INSERTS, then every index associated with the table you insert into must be updated. If there are many indexes, then one insert can cause many records. You can fix this problem by dropping the indexes before you start and then rebuilding them
- MyISAM vs. 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 just pointing out that this is a potential reason for slowing down the application. I do not recommend that you modify the InnoDB table in MyISAM!
- No transactions: When using InnoDB, you can speed up bulk operations by executing them inside a transaction. If you are not using a transaction, then there is an implicit transaction around every INSERT you do.
- Communication between PHP machine and SQL server: When testing, you probably ran both PHP and SQL server in the same box. You may have been connecting over a named pipe or over a TCP / IP connection (which has more overhead), but bandwidth is effectively unlimited anyway. If the SQL server is not the same machine as the PHP script server, then it will be limited to any bandwidth in the connection between them.
- Concurrent Users: You were the only user at any time on your test SQL database. A live system can and will have any number of additional users connected and executing queries at a given time. It will take some time from your script adding to its runtime. You should run large SQL jobs at night in order not to inconvenience other users, but they also cannot distract you from you. There are other reasons, but the above are worth exploring.
Of course, the problem could be on the PHP side, you cannot be sure that it is in the database until you research where exactly it is slowing down and why.
source to share