Register Members List Search Today's Posts Mark Forums Read

 
 
Mod Options
  #31  
Old 04 Sep 2006, 04:31
juanchi juanchi is offline
 
Join Date: Apr 2004
Thumbs up Please don't forget us

Hi MrZeropage, I decided to stick with ibproarcade but I'm still running 2.5.5+ , I know you tweaked my settings on my server to solve this issue but the problem never go away so I rolled back to 2.5.5+, I hope we can safely upgrade to 2.5.7+ and forget this issue.
  #32  
Old 11 Sep 2006, 21:17
Barabaika Barabaika is offline
 
Join Date: May 2006
There are no appropriate indexes, that is the source of the problem.
What about adding an index to the "avatarid" field in the "user" table?

Also, why do you need this garbage: " AND trim(password) = '' "
trim is a function, it must be calculated every time causing some load.
  #33  
Old 13 Sep 2006, 10:10
MrZeropage's Avatar
MrZeropage MrZeropage is offline
 
Join Date: Nov 2003
Real name: Marcel
Originally Posted by Barabaika
There are no appropriate indexes, that is the source of the problem.
What about adding an index to the "avatarid" field in the "user" table?
why index the avatarid ? please be more detailed thx
__________________
Get the most installed modification for your vBulletin (more than 8400 installations and 148.000 downloads!):
ibProArcade 2.7.6+ download here | Click here to enter the ibProArcade-Support-Section
Don't miss the new Google Analytics Integration (GDPR/DSGVO) for vB4.x
  #34  
Old 14 Sep 2006, 06:59
Barabaika Barabaika is offline
 
Join Date: May 2006
I just gave a quick look at the mysql.log file for an arcade.php call.
The user table has thousands of entries and full scan can take a long time.
I found this SELECT in the log:
SELECT user.userid, user.avatarid, user.avatarrevision, avatar.avatarpath, NOT ISNULL(filedata) AS hascustom, customavatar.dateline FROM user AS user LEFT JOIN avatar AS avatar ON avatar.avatarid = user.avatarid LEFT JOIN customavatar AS customavatar ON customavatar.userid = user.userid WHERE customavatar.userid=1 OR user.userid=1

It includes user.avatarid that should be indexed.

I will try to check the queries thoroughly to find bottlenecks later.
EXPLAIN SELECT command can help.
  #35  
Old 14 Sep 2006, 12:38
MrZeropage's Avatar
MrZeropage MrZeropage is offline
 
Join Date: Nov 2003
Real name: Marcel
any optimization is welcome
__________________
Get the most installed modification for your vBulletin (more than 8400 installations and 148.000 downloads!):
ibProArcade 2.7.6+ download here | Click here to enter the ibProArcade-Support-Section
Don't miss the new Google Analytics Integration (GDPR/DSGVO) for vB4.x
  #36  
Old 27 Sep 2006, 00:33
Barabaika Barabaika is offline
 
Join Date: May 2006
Ok. I've found the main problem.

Now it creates a query like this:
function/dbclass.php, line 85
SELECT user.userid,.... WHERE customavatar.userid=111111 OR user.userid=111111;

We don't need this at all, as it makes the query on all user table entries:
customavatar.userid=111111 OR

So, the WHERE statement in the line 85 should be:
WHERE user.userid=".$idneeded;

If it's difficult to understand, I'll create a diff file.

There is another "bad" query, I'll examine it later.

Last edited by Barabaika; 27 Sep 2006 at 00:36.
  #37  
Old 27 Sep 2006, 00:45
juanchi juanchi is offline
 
Join Date: Apr 2004
Thanks for your hard Work Barabaika but are you sure this is the problem? I'm just asking, I don't know anything about coding but do you have the chance to look at versions 2.5.5 and 2.5.6 files and compare them, cause if this code is on both versions it might not be the problem, this problem apeared with version 2.5.6.

Originally Posted by Barabaika
Ok. I've found the main problem.

Now it creates a query like this:
function/dbclass.php, line 85
SELECT user.userid,.... WHERE customavatar.userid=111111 OR user.userid=111111;
  #38  
Old 27 Sep 2006, 07:02
Barabaika Barabaika is offline
 
Join Date: May 2006
At least, it's much faster for me now.
I've created indexes and fixed several queries.

When I had looked at the server load, it was mainly MySQL that created it by getting the data for the arcade page.

The easiest way is to log slow queries in MySQL and then investigate them.
http://dev.mysql.com/doc/refman/5.0/...query-log.html


There can be other problems as well.
  #39  
Old 27 Sep 2006, 08:39
fsw fsw is offline
 
Join Date: Sep 2005
I have not had time to even glance at the code of this - but I know one place that there is a query that is doing much more than it should be.

When a game score gets submitted - ones that have thousands of scores take time - I have seen it take up to ten seconds.

I am running a 3.2 Dual Zeon with basically no load on the server - my site the only thing on it - and almost a gig of ram dedicated to MySql - and if its in ram on my server - its in ram - I have no swap.

I know all the scores are kept in one table, no biggy - it might be up to 100,000 scores by now - my server should be able to handle anything thrown at that instantly, but it doesnt.

I am going to guess that after every game played its running quries for the overall scoring. I need to find time to look

.. anyway - if thats the case - I would recommend splitting that off (optionally) and allowing it to be ran by cron if the user prefers. I am one that does not care how much resources it needs - it isnt going to put a dent on me - but I do not like the end user to have to wait for anything (well - actually - I do not like having to read the end user +++++ about waiting
  #40  
Old 27 Sep 2006, 11:46
juanchi juanchi is offline
 
Join Date: Apr 2004
Well guys I hope MrZeropage have a chance to look at your comments before he release the new version, I know for a fact that he has been looking/working on this issue.
  #41  
Old 27 Sep 2006, 17:56
Barabaika Barabaika is offline
 
Join Date: May 2006
You should put this in your my.cnf:

long_query_time=1
log-slow-query=/var/log/mysql/mysql-slow.log

1 second is the lowest setting for now, but that's good enough to find the slowest queries.
If the log says that to find 1 entry it scanned 50000 entries, that means something is wrong with the query.

You can analyze the slow query:
EXPLAIN SELECT .... WHERE user.avatarid=avatar.userid ...
If it says ALL entries, that's bad.
For example, in the former statement there must be indexes ON
user.avatarid
avatar.userid
  #42  
Old 02 Oct 2006, 12:35
MrZeropage's Avatar
MrZeropage MrZeropage is offline
 
Join Date: Nov 2003
Real name: Marcel
Originally Posted by Barabaika
At least, it's much faster for me now.
I've created indexes and fixed several queries.

When I had looked at the server load, it was mainly MySQL that created it by getting the data for the arcade page.
Maybe you can hand those changes to me for integrating in v2.5.7 ?
__________________
Get the most installed modification for your vBulletin (more than 8400 installations and 148.000 downloads!):
ibProArcade 2.7.6+ download here | Click here to enter the ibProArcade-Support-Section
Don't miss the new Google Analytics Integration (GDPR/DSGVO) for vB4.x
  #43  
Old 24 Oct 2006, 05:34
scdurwood scdurwood is offline
 
Join Date: Aug 2006
Originally Posted by MrZeropage
I think I found the code that causes a higher serverload, just waiting for confirmation of that.

Well, "it's not a bug, it's a feature"
It is a feature I implemented and if that was the reason I will do a switch in AdminCP to turn it on/off ... Until v2.5.7+ is out I will provide a QuickFix for that here
Have you released the quickfix?

Server loads via the arcade are an issue on my site as well...

Originally Posted by Barabaika
At least, it's much faster for me now.
I've created indexes and fixed several queries.

When I had looked at the server load, it was mainly MySQL that created it by getting the data for the arcade page.

The easiest way is to log slow queries in MySQL and then investigate them.
http://dev.mysql.com/doc/refman/5.0/...query-log.html


There can be other problems as well.
Please explain how you indexed each table and the other query fixes you implemented...

Last edited by scdurwood; 24 Oct 2006 at 05:41. Reason: Automerged Doublepost
  #44  
Old 24 Oct 2006, 11:09
MrZeropage's Avatar
MrZeropage MrZeropage is offline
 
Join Date: Nov 2003
Real name: Marcel
I am also waiting for response on the improvements he did ...
__________________
Get the most installed modification for your vBulletin (more than 8400 installations and 148.000 downloads!):
ibProArcade 2.7.6+ download here | Click here to enter the ibProArcade-Support-Section
Don't miss the new Google Analytics Integration (GDPR/DSGVO) for vB4.x
  #45  
Old 27 Oct 2006, 00:29
ArchangelX ArchangelX is offline
 
Join Date: Feb 2006
Real name: Michael Kitchens
Oh man...I just upgraded to 2.5.6...is this a problem for everyone? Quickfix please, just in case!
 

Similar Mod
Mod Developer Type Replies Last Post
[Linux] loadsock (retrieve server load from remote server) fastforward vBulletin 2.x Full Releases 5 18 Mar 2002 11:55



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Mod Options

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 17:46.

Layout Options | Width: Wide Color: