Efficiency change after mysql upgrade - code issue?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
OffbeatAdam
Forum Newbie
Posts: 3
Joined: Mon Oct 09, 2006 10:58 pm

Efficiency change after mysql upgrade - code issue?

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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)?
OffbeatAdam
Forum Newbie
Posts: 3
Joined: Mon Oct 09, 2006 10:58 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
OffbeatAdam
Forum Newbie
Posts: 3
Joined: Mon Oct 09, 2006 10:58 pm

Post 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.
Post Reply