[HELP] 2.5.6 is putting a load in my server

I already made a post about this but there seems to be no interest on this and I'm not sure but I think it is a bug.

My site is a busy site but the latest version, 2.5.6, is putting a high load to my server, sometimes bringing my server to not working condition.

All the mysql processes show something like these:

| 4315 | xxxxxx | localhost | xxxxxxx | Query | 0 | Locked | UPDATE vbb_games_league SET mid='2122',gid='16',position='1560',points='0',cat ='1' WHERE gid=16 AND |
| 4411 | xxxxxx | localhost | xxxxxxx | Query | 0 | Locked | UPDATE vbb_games_league SET mid='1831',gid='16',position='1476',points='0',cat ='1' WHERE gid=16 AND |

And this processes become so intense that the load of the server go to the top. If I shut down the arcade the server behaves without a problem.

Right now, if there is no answer for this could someone tolde me if it is safe to roll back to 2.5.5 and if so, I will only need to upload the files from that version and overwrite the product, right?

[juanchi]

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.

[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?

Also, why do you need this garbage: " AND trim(password) = '' "
trim is a function, it must be calculated every time causing some load.

[MrZeropage]

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

[Barabaika]

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.

[MrZeropage]

any optimization is welcome

[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;

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.

[juanchi]

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;

[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.

[fsw]

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

[juanchi]

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.