Page 1 of 1

Efficiency change after mysql upgrade - code issue?

Posted: Thu Oct 26, 2006 11:55 am
by OffbeatAdam
Alright... I realize that when you look at this post you may want some code, and I'll see what I can do, but the major issue would be the code is rather difficult to tie together in its girthly form.

However, hopefully I can get some insight as to whats going on.

I have a system for forum signature generation that has stemmed out into a rather large community (growth is actually quite a bit larger than I originall expected ^.^)

Initially the system ran on a 6-hour regeneration period where it would regenerate all images after 6 hours. Of course, this was a slop job and it wasnt made to take care of the 8000+ users I now take care of.

I fixed that... that was simple, I just had to actually work the project instead of make a little pet.

However, recently I upgraded my dedicated server that I had it running on to MySQL 4.1. I rewrote the database class and played around with the backend code afterwords. However, in running my normal tests, both the old and the new way represent a stunning comparison to mysql 4.0.

In mysql 4.0, I was able to parse through and update users from an XML file of abut 40000+ users (I only need about 8000+ of those users but, unfortunately, the producer of it doesnt make it that easy for me :D) in roughly 30-40 seconds. After the change to 4.1, it takes roughly 3300-4500 seconds.

I am unaware of where the inefficiency may lie, or if its even related to 4.1. In the process of updating to 4.1 I did also update to the latest version of PHP. However I am unaware of any XML lib changes that have occured since then that could have caused this to occur on the XML parsing side. The system itself is a hefty system, Athlon 64 3800+ with 2GB of ram, so I'm thinking this is out of the question as well.

Now, with the new DB class that I wrote, I did run into a couple issues, but those are related to the SQL statements and me being slightly rushed :) Even after properly correcting these issues, it continued to take a very long time. With the new DB class, I only knock off about 5-10 seconds, maximum.

Any suggestions?

And, if you would like to see a block of code, I dont mind posting it but again the issue seems to be covering a pretty large block and I wanted to keep the post from being a few pages long so that the question was legible at first. So, if you think it would help, just tell me which part would be most helpful.

The actual issue is with updating user data for the signatures, not creating the signatures as that is done seperately and I've got that going great.

Posted: Thu Oct 26, 2006 12:18 pm
by RobertGonzalez
This sounds a lot more like a database optimization issue. Have you defined your indexes properly? Have you optimized the table and set up appropriate keys? What type of table is it (MyISAM, InnoDB, HEAP)?

Posted: Thu Oct 26, 2006 12:41 pm
by OffbeatAdam
You know, thats funny. I didnt even think to look at my indexes. lol.

It would seem that in the upgrade I lost the indexes on the tables :P

Wow, thanks for pointing that out for me lol.

Knocked it back down to 31seconds :D

Posted: Thu Oct 26, 2006 1:15 pm
by RobertGonzalez
Way to go man. Glad you got it working.

PS This thread was moved to Databases as this was entirely a database topic.

Posted: Thu Oct 26, 2006 1:22 pm
by volka
If you let your script start with

Code: Select all

error_reporting(E_ALL);
ini_set('display_errors', true);
ini_set('mysql.trace_mode', 1);
some errors and caveats might be displayed, see http://de2.php.net/manual/en/ref.mysql. ... trace-mode
(Don't forget to at least remove mysql.trace_mode when you are done with debugging)

You might also want to look into prepared statements, http://dev.mysql.com/tech-resources/art ... ments.html

Posted: Thu Oct 26, 2006 1:51 pm
by OffbeatAdam
Yea, after writing my new mysqli db class I've begun looking at prepared statements more. Its just something I hadnt really played with yet because of the insane increase in execution time.

However..

I definitely did not know about the trace mode, that is awesome and will prove very useful in the future :)

Thank you for that information.