I have a private website for the use of myself and a few friends. It has a database of stuff generated from XML files. For simplicity, every time something in the XML changes, I run a PHP script to regenerate the MySQL tables from scratch. There are 22 tables generated, and about 60,000 rows in total, most of which have at least one text field…
This used to run in about 10 seconds. Then about a month ago for reasons of general hygiene I reformatted my hard drive, reinstalling Windows and so on, and redownloaded and installed PHP and MySQL, restored my backups…
Now the PHP script takes something like 15-20 minutes to run. Or is it half an hour? There is never more than 1% CPU usage during this time, but the hard drive is basically sweating the whole time, clicking away. The parsing of the XML files is practically instantaneous. I took the opportunity to rewrite the queries in the script to use prepared statements, but I knew that wasn’t the problem (and it didn’t help). It’s clearly the database queries themselves that are being executed slowly.
I added innodb_file_per_table, thinking that somehow fragmentation of the ibdata0 file could be the problem. This didn’t help, and I did see the files growing so I know I enabled the option correctly. Then I removed innodb_file_per_table, and ran the script several times in a row, thinking that it might burrow itself a nice big hole in the ibdata1 file which could be reused without having to expanding the file any further, but this didn’t help performance at all either.
I don’t remember what version of MySQL I had installed earlier. There are also two versions of PHP (thread-safe and non-thread-safe), and possibly different ways of installing MySQL, but I also don’t remember how I had them set up earlier. Could something here be causing such a large problem?
What could it be? Perhaps there is something which wasn’t an error (or warning) in a previous version, but is now, and 60,000 warnings are being inefficiently written to a log file somewhere? I don’t know anything about how that works in MySQL, though. I don’t consider myself at all an expert concerning MySQL.
I’ve Googled all over the web for help with this problem, but nothing remotely relevant comes up. Apparently I’m the only one who does so much inserting. I could, of course, rewrite the insertion script to try to keep old stuff that doesn’t change, but the script is already 80kb and it would take a ton of work to get something more complex working smoothly. And besides, there is something ugly afoot here, something which should be fixable.
Any help is appreciated!