[HELP] 2.5.6 is putting a load in my server

[juanchi]

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?

[James T Brock]

I'm also having server load problems since upgrading but I didn't think it could possibly be the arcade. For me it's probably just a coincidence.

What are you using to show you your MySQL processes that are causing trouble? Is it an SSH command? Let me know, I'd like to try it myself and maybe we can verify if it's our arcades that's doing it.

I can't believe it, I turned off the arcade and my server load went back to normal. The server stopped crashing every twenty minutes.

How can I fix this? My arcade was working fine before I updated from ibproarcade made by Chris to the newer version.

Is it possible that the arcades database is corrupt and I just need to fix them? Or is this just a bug in the arcade period?

[juanchi]

What are you using to show you your MySQL processes that are causing trouble? Is it an SSH command? Let me know, I'd like to try it myself and maybe we can verify if it's our arcades that's doing it.

I can't believe it, I turned off the arcade and my server load went back to normal. The server stopped crashing every twenty minutes.

How can I fix this? My arcade was working fine before I updated from ibproarcade made by Chris to the newer version.

Is it possible that the arcades database is corrupt and I just need to fix them? Or is this just a bug in the arcade period?

Yes, you need to use ssh and log into mysql and at the mysql> prompt type: show processlist; .

In the last days I being tryng to figure out a solution, maybe it is my imagination since I did this yesterday and weekends at my server are not very busy, but since I enter the score tools section of the Arcade Control panel and run all the updates there my server have being kind of stable running the arcade without high loads, but as I told you, I did this yesterday and I don't know for sure if this is the fix.

[James T Brock]

Yes, you need to use ssh and log into mysql and at the mysql> prompt type: show processlist; .

In the last days I being tryng to figure out a solution, maybe it is my imagination since I did this yesterday and weekends at my server are not very busy, but since I enter the score tools section of the Arcade Control panel and run all the updates there my server have being kind of stable running the arcade without high loads, but as I told you, I did this yesterday and I don't know for sure if this is the fix.

Thanks. You don't know how much your thread helped me out. You saved me a lot of time, money, and grief. My server was going haywire. MySQL was crashing my server every 45 minutes and I had no idea it was the arcade because the previous arcade never had a single problem.

Ever since closing the arcade everything is fine but now I have a few hundred daily arcade users emailing me asking when the arcade is coming back. :cry:

It's too risky right now to open it back up.

[juanchi]

Thanks. You don't know how much your thread helped me out. You saved me a lot of time, money, and grief. My server was going haywire. MySQL was crashing my server every 45 minutes and I had no idea it was the arcade because the previous arcade never had a single problem.

Ever since closing the arcade everything is fine but now I have a few hundred daily arcade users emailing me asking when the arcade is coming back. :cry:

It's too risky right now to open it back up.

The worst thing here my friend is that no one seems to pay attention to this problem, , maybe I'm wrong and the developers are looking for a solution, my server never have any problem in the past with the arcade but after upgrading to the latest version all the problems started, I think the developers fix something related to the tournament system but this fix puts a high load on busy sites, I hope they come with a solution soon.

[Gemma]

Out of curiousity have you set the arcade scores to auto-prune?

I haven't upgraded to the latest version of the arcade yet but I did notice once before that the server loads were very high before I disabled auto-pruning

[James T Brock]

Out of curiousity have you set the arcade scores to auto-prune?

I haven't upgraded to the latest version of the arcade yet but I did notice once before that the server loads were very high before I disabled auto-pruning

No I have not. I only save my users top scores so there wasn't any reason to use the auto prune feature.

I should also point out that when I first upgraded the arcade it was loading REALLY slow. And I am on very high speed broadband and everything usually loads fast for me. I reduced the amount of games per page from 50 to 20 and it loaded a bit faster bit it was still really slow.

[juanchi]

Out of curiousity have you set the arcade scores to auto-prune?I

No, I don't have auto-prune active on my arcade.

[MrZeropage]

If you don't have autopruning active, make sure to save only users top score per game, not every score !

That is written in the AdminCP-Settings and somehow important, as by saving every score and never pruning it, scores-table grows too large and will slow everything down

[James T Brock]

I only save the users top score. That's the one I've always done it and it's the reason why I don't use autopruning.

[firstrebel]

If you don't have autopruning active, make sure to save only users top score per game, not every score !

That is written in the AdminCP-Settings and somehow important, as by saving every score and never pruning it, scores-table grows too large and will slow everything down
Good point. My vb_games_scores table has nearly 9000 entries. If I enable autopruning say after 5 days I presume only score over 5 days old get zapped?

Bob

[juanchi]

I only save the users top score. That's the one I've always done it and it's the reason why I don't use autopruning.

I only save top scores also, sorry but there have to be a BUG with the latest version, and I think the problem is related to something that have to do with this table vbb_games_league, what is this table for? it keeps tracks of the arcade statistics? I can see the record of this table increase by the second, if this is related to tournaments I have to say that I'm not running tournaments on my site and I had never created a torunament in my arcade.

Can I roll back to 2.5.5.

[James T Brock]

if this is related to tournaments I have to say that I'm not running tournaments on my site and I had never created a torunament in my arcade.

Same here. I edited all references to tournaments from my arcades skin.

MrZeropage doesn't seem to care about this problem since it only affects people with a lot of traffic.

[Gemma]

Hmmm....the games_league table stores of the information for the Leaderboard.

It sounds like the 'bug fix' to make the League Table auto-update may be causing a strain on server loads

MrZeropage doesn't seem to care about this problem since it only affects people with a lot of traffic.

There is no need for quotes like that!

[juanchi]

There is no need for quotes like that!

Well there is definitley no need for a quote like that and I apreciate all the hard work done by MrZeropage but I understand also the frustation of James T Brock since I'm also expierincing this problem and maybe many others that don't have the time to look into this forums. When this problem started on my server the last thing I will think that could be causing the problem will be the arcade.

[James T Brock]

There is no need for quotes like that!

Well I don't mean to be rude but he pretty much ignored us even though it's a major problem that crashes an entire dedicated server. No one is even answering juanchi's question about whether or not it's possible to go back to a previous version.

[MrZeropage]

MrZeropage doesn't seem to care about this problem since it only affects people with a lot of traffic.
Thanks :hurt:

I was on vacation until 23rd May so don't blame me for not being here within a second ;)


even though it's a major problem that crashes an entire dedicated server. No one is even answering juanchi's question about whether or not it's possible to go back to a previous version.ibProArcade is running on several dedicated Servers, even some I personally administrate. No problem there at all.

If you want to switch back to v2.5.5+ it is no problem, just overwrite all .php-files and you're done


For further investigation on your problem I'll need access to your FTP and ACP so I can trace that bug ...

[Kacela]

Not for this issue, but I had another perplexing issue that I needed MrZeropage's assistance with. I gave him AdminCP access and FTP access, and he sorted my problem quickly. I guess what I'm saying is that in addition to being trustworthy and capable, he will not "ignore" a users problem.

He has spent many hours recoding a superior arcade for us to use, and gives us outstanding suppport, all for free (although, I have sent him thanks via paypal in the past;) ).

I think it unfair to bash him like that. Just PM MrZeropage the info for AdminCP access and FTP access (he has his own accounts on my board), and he can probably sort your problem quickly.

[Neo_obs]

I agree he is a great guy and even when I may be annoying he never ignores me it just might take him awhile and i understand he is a busy man

[James T Brock]

Anyone know where I can download the older version of ibproarcade? I just ran a repair on my database but when I reopened the arcade it almost brought my server down again. And the only person playing games was me.

[James T Brock]

*bump*

[Gemma]

I'll PM you a link :)

[James T Brock]

Thank you Gemma.

[juanchi]

I'll PM you a link :)

Can I have also that link, thanks.

[James T Brock]

Going back to 2.5.5 actually worked. So yes whatever new changes were implemented in 2.5.6 are what is causing the server issues. I hope this is corrected for the next upgrade.

[Gemma]

Can I have also that link, thanks.

Okey :)

PM Sent

[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

[James T Brock]

Thanks MrZeropage. I'm currently using version 2.5.5 without any problems but it would be nice to have some of the new features of 2.5.6 such as guest being allowed to play games. I was looking forward to that feature.

But since my current arcade works and isn't crashing my server I'm fine with waiting however long it takes.

edit: I do find this odd that it completely crashes my dedicated server (3.2 pentium with 2 gigs of ram) and yet a lot of people on shared hosts use this arcade without any problems? Just my luck.

[MrZeropage]

I think you have alot of members and posts in your forum, that is why the load is so high with v2.5.6+

If you want, I can give you my idea of the fix (just one single line has to be changed) and you test this after upgrading to v2.5.6+ again - I bet that it will be good then :)

[James T Brock]

I think you have alot of members and posts in your forum, that is why the load is so high with v2.5.6+

If you want, I can give you my idea of the fix (just one single line has to be changed) and you test this after upgrading to v2.5.6+ again - I bet that it will be good then :)

One million posts and fifteen thousand members. Though I never have more than 15 users in the arcade at one time. I noticed that it would crash with only three or four users playing games so I assumed that the size of my forum has nothing to do with it.

I'll take that "fix" if you don't mind, but like I said...since my current arcade now works I'm more than fine with waiting however long it takes for an official release fixing this.

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

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.

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/en/slow-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.

[Barabaika]

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

[MrZeropage]

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 ? :)

[scdurwood]

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

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/en/slow-query-log.html


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

[MrZeropage]

I am also waiting for response on the improvements he did ... :)

[ArchangelX]

Oh man...I just upgraded to 2.5.6...is this a problem for everyone? Quickfix please, just in case!

[MrZeropage]

just update to v2.5.7+ now

[gazzak]

Just as a side note. I had a big load on my server at all times when the arcade was running. Turning off the option to show high scores in the postbit totally cut the load. I've not seen that mentioned in this thread so give it a try.

[MrZeropage]

did you use v2.5.7+ ?

[TonyLuong]

I just installed ibProArcade 2.5.7+ for 2 days on a dedicated 3.2 Dual Xeon 2GB RAM. My server crashes so often right after having ibProArcade there.

My site is busy on forums 1000+ current online but not many people on arcade (around 10 at once max) my board having about 500,000 members and about 457,000 posts.

I don't have "Show Highscores in Postbit" set
I don't have "Show Tournaments won in Postbit" set
I don't have any tournament created

Just the arcade home itself take very long time to load. My forumhome takes about 1 sec to load for me where arcade home takes about 20 sec.

Anyone running 2.5.7+ and experience this slow and high server load? Any fix here?

[MrZeropage]

wwhat are your settings about score-saving ? should be "save users top score per game" ...

Also make sure not to display 100 games per page

[bbunlock]

well I tried to install the arcade for the first time yeterday on my forum (on dedicated server) did everything I was supposed to but as soon as I got to the "Enter the path to the Product-XML-File: ./includes/xml/product-ibproarcade.xml and click IMPORT" the server went balistic and finaly crashed, at first I wasnt sure if it was the isntall that did it so rebooted server and waited a while, all ok untill I ran that command again and server crashed again.

I then decided to login to ssh and run top, then I ran the above command again from admincp and would check what was happening in top, well as soon as I clicked import the top screen went mad, every process was mysql and the server load went to 74.? and finaly had to reboot server again becuase it was totaly unresponsive.

in total tried to install this 4 times and 4 times it blew my server away and needed to reboot.

some info for you just in case

mod (latest version 2.5.7)
vbul 3.5.3
PHP Version 4.3.10
mysql 3.23.49
server p4 1.8

any ideas would be great

regards

[TonyLuong]

wwhat are your settings about score-saving ? should be "save users top score per game" ...

Also make sure not to display 100 games per page


I have "Method of recording a user's score" set to "Only save users top score". The display 20 games per page.

Some more info:
+ I go ahead and do a test install it on my vertual host account I got from other company and see the differences. (Notes: this also fresh install of vBulletin 3.6.3). The installation went much faster than it did not my server (it takes 10 minutes to import the product-ibproarcade.xml on my server where about 20 sec on this virtual server).
+ The game homepage here load alot faster than the game homepage on my dedicate server (10 times faster).
+ Is this because my board has too many users? to many posts? too many concurrent online? Is there any code in there that makes ibProAcade goes crazy on busy site and big board?

[TonyLuong]

Hello MrZeropage,
Are you looking into this heavy server load issue? I really want to run Arcade on my board that already have lots of users already. But Arcade gives heavy server load problem there. Arcade works on empty vbulletin board for me but that's not where I want to run it. Please help!

[James T Brock]

Can anyone with a big forum who were previously having problems verify whether or not the newest 2.5.8+ fixed their issues?