Home arrow Forum Mysql load
  Welcome, Guest. Please login or register.
Did you miss your activation email?
December 03, 2008, 01:44:21 AM
Home New Posts Search Calendar


Login with username, password and session length
+  Joomla Forum
|-+  Joomla Hacks
| |-+  Joomla-SMF Forum Support
| | |-+  Joomla-SMF 1.0.x (Moderators: -Wolverine, kai920)
| | | |-+  Mysql load		 0 Members and 1 Guest are viewing this topic. « previous next »
Pages: [1] 2 Go Down Print
Author Topic: Mysql load  (Read 4285 times)
Gandalf
Joomla Master
***

Karma: +4/-0
Offline Offline

Posts: 131



View Profile WWW
Mysql load
« on: October 08, 2005, 08:51:03 PM »
Hello,

when i was with Mambo, smf and mambo-smf i didn't experience any problem but when i switched to Joomla, SMF 1.1 and Joomla-SMF bridge, Mysql just hang at 100% i studied it closely and i've seen that always when it hangs, there's one slow query:
Code:
SELECT jos.name, jos.username, jos.email, jos.registerDate, jos.block, jos.password, jos.activation,
                smf.memberName, smf.realName, smf.passwd, smf.emailAddress, smf.dateRegistered, smf.is_activated, smf.validation_code
                FROM mos_users jos
                RIGHT JOIN smf_members smf ON jos.username = smf.memberName
                WHERE jos.username = 'XXXX'
                OR smf.memberName = 'XXXX'
                LIMIT 1                                           

and it needs around 5 - 10 mns to return to normal meanwhile the site is offline Shocked


anyone else having this?
Logged
Editor (cowboy)
Administrator
Joomla Guru
*****

Karma: +86/-23
Offline Offline

Posts: 1144



View Profile WWW
Re: Mysql load
« Reply #1 on: October 09, 2005, 01:46:58 AM »
How large is your user data? Did you try executing that sql manually?
Logged
Gandalf
Joomla Master
***

Karma: +4/-0
Offline Offline

Posts: 131



View Profile WWW
Re: Mysql load
« Reply #2 on: October 09, 2005, 01:49:28 AM »
I have 7153 users registered and no i didn't try to run it manually...
Logged
Editor (cowboy)
Administrator
Joomla Guru
*****

Karma: +86/-23
Offline Offline

Posts: 1144



View Profile WWW
Re: Mysql load
« Reply #3 on: October 09, 2005, 01:51:22 AM »
Can you try to see if it's fast or slow?
Logged
Gandalf
Joomla Master
***

Karma: +4/-0
Offline Offline

Posts: 131



View Profile WWW
Re: Mysql load
« Reply #4 on: October 09, 2005, 01:52:08 AM »
Ok give me a second...
Logged
Gandalf
Joomla Master
***

Karma: +4/-0
Offline Offline

Posts: 131



View Profile WWW
Re: Mysql load
« Reply #5 on: October 09, 2005, 02:01:34 AM »
Quote from: Editor (cowboy) on October 09, 2005, 01:51:22 AM
Can you try to see if it's fast or slow?
I replaced the XXXX (the username to search for) with the last member registered and the query took 6 mins 10 seconds to finish, meanwhile SQL was 100% server load was 1.5 / 2 and site inaccessible Shocked Shocked Shocked
« Last Edit: October 09, 2005, 02:05:09 AM by Gandalf » Logged
Editor (cowboy)
Administrator
Joomla Guru
*****

Karma: +86/-23
Offline Offline

Posts: 1144



View Profile WWW
Re: Mysql load
« Reply #6 on: October 09, 2005, 02:09:38 AM »
This site got 1300+ but it only takes couple of  milliseconds. I can't see 6mins for 7000+ users. You need to optimize your tables from SMF or maybe from CPANEL (not sure at CPANEL).
« Last Edit: October 09, 2005, 02:11:12 AM by Editor (cowboy) » Logged
Gandalf
Joomla Master
***

Karma: +4/-0
Offline Offline

Posts: 131



View Profile WWW
Re: Mysql load
« Reply #7 on: October 09, 2005, 02:11:02 AM »
I already optimised my tables from SMF and i don't have Cpanel, i have VHCS Sad
any other suggestion? maybe u can help on the server?

Thanks
Logged
Editor (cowboy)
Administrator
Joomla Guru
*****

Karma: +86/-23
Offline Offline

Posts: 1144



View Profile WWW
Re: Mysql load
« Reply #8 on: October 09, 2005, 02:17:59 AM »
I'm not sure how to optimize it the easy way other than creating an index on the table which is pretty much an in-depth SQL stuffs.

Can you try the SQL without the JOIN. Just make a query for Joomla and SMF seperately.

Like this:
Quote
SELECT jos.name, jos.username, jos.email, jos.registerDate, jos.block, jos.password, jos.activation,
FROM mos_users jos
WHERE jos.username = 'XXXX'
LIMIT 1

Quote
SELECT smf.memberName, smf.realName, smf.passwd, smf.emailAddress, smf.dateRegistered, smf.is_activated, smf.validation_code
FROM smf_members smf
WHERE jsmf.memberName = 'XXXX'
LIMIT 1 

Tell me how long it took. Get back to you tomorrow. Ill just take some rest.
Logged
Gandalf
Joomla Master
***

Karma: +4/-0
Offline Offline

Posts: 131



View Profile WWW
Re: Mysql load
« Reply #9 on: October 09, 2005, 02:23:54 AM »
Quote from: Editor (cowboy) on October 09, 2005, 02:17:59 AM
I'm not sure how to optimize it the easy way other than creating an index on the table which is pretty much an in-depth SQL stuffs.

Can you try the SQL without the JOIN. Just make a query for Joomla and SMF seperately.

Like this:
Tell me how long it took. Get back to you tomorrow. Ill just take some rest.
I tried both queries it took me a few millisecond for both of them... so i guess the Join is the problem Shocked Shocked Shocked
weird...

ok man, no problem, i'll take some rest too,
Bye Cheesy
« Last Edit: October 09, 2005, 02:26:15 AM by Gandalf » Logged
Editor (cowboy)
Administrator
Joomla Guru
*****

Karma: +86/-23
Offline Offline

Posts: 1144



View Profile WWW
Re: Mysql load
« Reply #10 on: October 09, 2005, 10:37:44 AM »
but still, i don't see 6min on a 7k records while it's just milliseconds here.

Try these 3 and post how long it takes:

SELECT jos.name, jos.username, jos.email, jos.registerDate, jos.block, jos.password, jos.activation,
smf.memberName, smf.realName, smf.passwd, smf.emailAddress, smf.dateRegistered, smf.is_activated, smf.validation_code
FROM mos_users jos, smf_members smf
WHERE jos.username = smf.memberName
AND jos.username = "xxx"
LIMIT 1

SELECT jos.name, jos.username, jos.email, jos.registerDate, jos.block, jos.password, jos.activation,
smf.memberName, smf.realName, smf.passwd, smf.emailAddress, smf.dateRegistered, smf.is_activated, smf.validation_code
FROM mos_users jos, smf_members smf
WHERE jos.username = smf.memberName
AND smf.memberName = "xxx"
LIMIT 1

SELECT jos.name, jos.username, jos.email, jos.registerDate, jos.block, jos.password, jos.activation,
smf.memberName, smf.realName, smf.passwd, smf.emailAddress, smf.dateRegistered, smf.is_activated, smf.validation_code
FROM mos_users jos
RIGHT JOIN smf_members smf ON jos.username = smf.memberName
WHERE (pls try jos.username or smf.memberName here) = 'XXXX'
LIMIT 1         
Logged
Gandalf
Joomla Master
***

Karma: +4/-0
Offline Offline

Posts: 131



View Profile WWW
Re: Mysql load
« Reply #11 on: October 09, 2005, 01:19:07 PM »
Quote from: Editor (cowboy) on October 09, 2005, 10:37:44 AM
but still, i don't see 6min on a 7k records while it's just milliseconds here.

Try these 3 and post how long it takes:

SELECT jos.name, jos.username, jos.email, jos.registerDate, jos.block, jos.password, jos.activation,
smf.memberName, smf.realName, smf.passwd, smf.emailAddress, smf.dateRegistered, smf.is_activated, smf.validation_code
FROM mos_users jos, smf_members smf
WHERE jos.username = smf.memberName
AND jos.username = "xxx"
LIMIT 1
ok this one took a milliseconds

Quote from: Editor (cowboy) on October 09, 2005, 10:37:44 AM
SELECT jos.name, jos.username, jos.email, jos.registerDate, jos.block, jos.password, jos.activation,
smf.memberName, smf.realName, smf.passwd, smf.emailAddress, smf.dateRegistered, smf.is_activated, smf.validation_code
FROM mos_users jos, smf_members smf
WHERE jos.username = smf.memberName
AND smf.memberName = "xxx"
LIMIT 1
This one took a milliseconds too

Quote from: Editor (cowboy) on October 09, 2005, 10:37:44 AM
SELECT jos.name, jos.username, jos.email, jos.registerDate, jos.block, jos.password, jos.activation,
smf.memberName, smf.realName, smf.passwd, smf.emailAddress, smf.dateRegistered, smf.is_activated, smf.validation_code
FROM mos_users jos
RIGHT JOIN smf_members smf ON jos.username = smf.memberName
WHERE (pls try jos.username or smf.memberName here) = 'XXXX'
LIMIT 1         
hmmmmm :roll: with both jos.username & smf.memberName it took a milliseconds too :roll:
Logged
Gandalf
Joomla Master
***

Karma: +4/-0
Offline Offline

Posts: 131



View Profile WWW
Re: Mysql load
« Reply #12 on: October 10, 2005, 01:54:36 AM »
I can see that it's the isValidUser function in functions.smf.php

Code:
function isValidUser(&$row, $username, $password) {

global $database, $smf_prefix;



//try first with MOS

$sql = "

SELECT jos.name, jos.username, jos.email, jos.registerDate, jos.block, jos.password, jos.activation,

smf.memberName, smf.realName, smf.passwd, smf.emailAddress, smf.dateRegistered, smf.is_activated, smf.validation_code

FROM #__users jos

LEFT JOIN {$smf_prefix}members smf ON jos.username = smf.memberName

WHERE jos.username = '$username'

OR smf.memberName = '$username'

LIMIT 1

";



$database->setQuery($sql);

$database->loadObject( $row );

if ($row != null) {

$mos = ($row->password == md5($password));

$smf = ($row->passwd == sha1(strtolower($username) . $password));

if (!$mos && !$smf)

return 0;

if ($mos && $smf) {

if (!$row->block && $row->is_activated) return 1;

if ($row->block && !$row->is_activated) return 4;

return 5;

}

if ($mos)

return 2;

if ($smf)

return 3;



}



//try with SMF

$sql = "

SELECT jos.name, jos.username, jos.email, jos.registerDate, jos.block, jos.password, jos.activation,

smf.memberName, smf.realName, smf.passwd, smf.emailAddress, smf.dateRegistered, smf.is_activated, smf.validation_code

FROM #__users jos

RIGHT JOIN {$smf_prefix}members smf ON jos.username = smf.memberName

WHERE jos.username = '$username'

OR smf.memberName = '$username'

LIMIT 1

";

$database->setQuery($sql);

$database->loadObject( $row );

if ($row != null) {

$mos = ($row->password == md5($password));

$smf = ($row->passwd == sha1(strtolower($username) . $password));

if (!$mos && !$smf)

return 0;

if ($mos && $smf) {

if (!$row->block && $row->is_activated) return 1;

if ($row->block && !$row->is_activated) return 4;

return 5;

}

if ($mos)

return 2;

if ($smf)

return 3;

}

}



can you please tell me how to rewrite it to avoide that problem? look above your 3 forms of the query worked but not the one you used in this function i guess it's better to rewrite it...

Thanks again for your help i appreciate it because my site is 70% down.. whenever a user login the site goes down for 6 mins....
Logged
Editor (cowboy)
Administrator
Joomla Guru
*****

Karma: +86/-23
Offline Offline

Posts: 1144



View Profile WWW
Re: Mysql load
« Reply #13 on: October 10, 2005, 02:14:52 PM »
This probably got something to do with your table index.

I'm really not sure how to explain it to you. It will take me more time telling you rather done modifying it myself.

But basically, the first SQL gets the Joomla and SMF user data base on Joomla. If not found, the 2nd SQL is tried based on SMF. And both are done through JOIN to save SQL call. Which should be an improvement. I really don't know what happened in your case.

If you want you can seperate the calls by not doing JOIN. But store the Joomla and SMF data in $row.
Logged
Gandalf
Joomla Master
***

Karma: +4/-0
Offline Offline

Posts: 131



View Profile WWW
Re: Mysql load
« Reply #14 on: October 10, 2005, 02:16:42 PM »
Can you write the code for me please? i know mysql but i've never understod how join works so i donno what to do with the above code...
Logged
Pages: [1] 2 Go Up Print 
« previous next »
Jump to:  



Login with username, password and session length

Powered by MySQL Powered by PHP Joomla Forum | Powered by SMF 1.1 RC1.
© 2001-2005, Lewis Media. All Rights Reserved.
Joomla Bridge by JoomlaHacks.com 		Valid XHTML 1.0! Valid CSS!

Joomla Hacks is a Joomla Components, Joomla Modules, Joomla Templates, & Joomla Mambots resource portal. None of the text or images in this public website may be copied without the expressed written consent of the authors. Copyright 2005 by JoomlaHacks.com. Powered by Joomla. All rights reserved.
Terms of Use
Joomla Hacks



Joomla Hacks
 German Lang French Lang Italian Lang Spanish Lang Japanese Lang Chinese Lang
Search Contact About Advertise Blogs Topsites Submit News Register Login