Welcome to all phpBB2 Refugees!This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area. |
|
Author |
Message |
Oliver2000 Board Member
Joined: 29 Jan 2009
Posts: 21
|
Posted: Thu Jan 29, 2009 3:34 pm Post subject: Heavy board and performance... |
|
|
Hi to all,
First of all thanks to the Team from REFUGEES for this nice idea and oportunity to still talk to some fans of the good old phpbb2. I use it since a couple of years and run a pretty big board i guess.
Around 2 million visits per month (70.000 day), 110.000 members who write around 5000 messages a day and around 1.8 million messages in board (well, i prune from time to time down but never less than 1.5 millions posts)
I run the entire system on 4 machines. 1 Database Server (quad core with 8gb memory), 2 dual core with webserver only and one dual core with adserver only but this webserver use the same mysql and server as the entire system.
The board is heavy modded by myself and some finished mods and dont has a lot with the original phpbb in common now.
So far things was fine but in the moment i have never less than 2000-2500 users around and 350 of them with login in the same time. Over the entire day around 3000 differant users login and register around 5-6k a day.
I write this that who ever read this has an idea about my traffik and system do understand me better.
Well... the problem is that i see over the last few days in my mytop some strange (for me) things going on and i dont know what it the reason for this.
Usually the db server has a load of around 2-3 (i made all kind of performance tweaks thanks to you guys here and a lot of other sites) but somehow every once a while pop up hundrets of simliar querys which i dont understand.
Here the query:
$sql = 'SELECT u.*
FROM ' . USERS_TABLE . ' u, ' . SESSIONS_KEYS_TABLE . ' k
WHERE u.user_id = ' . (int) $user_id . "
AND u.user_active = 1
AND k.user_id = u.user_id
AND k.key_id = '" . md5($sessiondata['autologinid']) . "'";
it is from the sessions.php
Now i dont know WHY i see them all the time like this becaise usually i see only some querys normal, some others.... normal, but suddenly appear like 200-300 of this once mixed with some others and the side gets slow for some seconds.... after they are gong everything continue fine.
I have to admit that I am not sure WHAT happens and i checked crons etc which do some things for me, i also cache everything like the forum indexes for guests etc.
Does somebody have a tip for me how i can find out what is going on... or maybe the db ist just on the limit and i see all this querys because the entire things gets slow until they are done.
Well, i am far not a pro and know some coding but i am really not a mysql expert.
thanks for some discussion and or tips of all kind for performance tweaks of large forums.
have a nice day,
Oliver |
|
Back to top |
|
|
Ptirhiik Board Member
Joined: 19 Nov 2008
Posts: 114
|
Posted: Thu Jan 29, 2009 5:22 pm Post subject: Re: Heavy board and performance... |
|
|
This one occurs when a new session is opened with a user using the "always log me in" feature. Maybe clearing the session_key table (with a truncate table) should solve the problem, then performing a survey to recatch it and fine tuning the auto login feature validity delay.
By my side, I have splited this process in two step: first get the session (through autologin or sid), then get the user. Also, having an index on users with user_active plus user_id as key can help (if you don't have it already). |
|
Back to top |
|
|
Oliver2000 Board Member
Joined: 29 Jan 2009
Posts: 21
|
Posted: Thu Jan 29, 2009 5:50 pm Post subject: Re: Heavy board and performance... |
|
|
Hi Ptirhiik,
I am honered to get a answer from you. I know your great mods and work since years and use several of your modifications.
About your suggestions: I made now (didnt had this befor) a index for phpbb_users.user_active as well phpbb_users.user_id plus i cleared the session tables. Lets see what happens now. I am going to watch it a little bit.
Do you have some code examples for me avalible to split the process as you mentioned? I dont understand this very well i have to confess.
Thank you very much for your tips, i apreciate it.
Oliver |
|
Back to top |
|
|
espicom Board Member
Joined: 24 Nov 2008
Posts: 55 Location: Woodstock, IL
|
Posted: Thu Jan 29, 2009 7:08 pm Post subject: Re: Heavy board and performance... |
|
|
By adding the index, you did what I was going to suggest - repair the sessions_key table. Sometimes, and index will be damaged, but not enough to generate an error. And sometimes, it's subtle enough that the only way to fix it is to tell MySQL to destroy the table's index, and build a new one.
If the problem persists, see Fixing MySQL 1016/1030/1034 errors for how to run the "make a new index" repair. |
|
Back to top |
|
|
Oliver2000 Board Member
Joined: 29 Jan 2009
Posts: 21
|
Posted: Fri Jan 30, 2009 7:38 am Post subject: Re: Heavy board and performance... |
|
|
Hi again,
I looks like my problem is solved thanks to the tips from you. I was most likely a screwed up session key table. Somehow far to much lines in table as well. But it seems now stable... monday,thuesday are my most heavy traffic days...lets see...
I also made the indexes like you suggested.
I guess i made all the common suggested tweaks etc. to speed my board up but with growing 2500-guests/400-users same time i am helpful for anything else worse mention.
I love my phpbb2 and because of the heavy modifications i made myself and some ready mods i guess i will also not update to phpbb3 so i stick with it and keep updating myself.
thanks to all,
have a nice day |
|
Back to top |
|
|
|
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0478 seconds using 16 queries. (SQL 0.0168 Parse 0.0006 Other 0.0304) |
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com
|
|