MyBB Community Forums

Dear friends,

first of all, i have to apologize for my terrible english, i hope that this message will be easy to understand even if this is not my native language.

i am using mybboard 1.4.2 for www.angelmule.com, with success.
i have upgrade the forum from 1.2 to 1.4 almost without problem.

I am experience some performance trouble, the server load is quite high, and mysql cpu usage is growing very high.

the forum actually have 23K users registered, with about 6K visitors per day.

The forum is hosted on one of my server (dedicated one) but he have to "live" with other application that aren't so happy of high load and high mysql usage.

Using a bin log analyzer, i have take a look to some of mysql log, to know wich query are the most executed, to try to optimize my applications, and that's is what coming out.

________________ 001 ___
Count : 60.78k (15.24%)
Query abstract:
SET timestamp=N; UPDATE edk_sessions SET uid='S', time='S', location='S', useragent='S', location1='S', location2='S', nopermission='S' WHERE sid='S' LIMIT N;

________________ 002 ___
Count : 42.39k (10.63%)
Query abstract:
SET timestamp=N; UPDATE edk_users SET lastactive='S', timeonline=timeonline+N WHERE uid='S';


________________ 003 ___
Count : 29.94k (7.51%)
Query abstract:
SET timestamp=N; SET @@session.character_set_client=N,@@session.collation_connection=N,@@session.collation_server=N; UPDATE edk_sessions SET uid='S', time='S', location='S', useragent='S', location1='S', location2='S', nopermission='S' WHERE sid='S' LIMIT N;


________________ 004 ___
Count : 28.56k (7.16%)
Query abstract:
SET timestamp=N; REPLACE INTO edk_threadsread (tid, uid, dateline) VALUES('S', 'S', 'S')1;



reading this table, seems quite obvius that the most common query are issued by www.angelmule.com (i can say that by looking at the prefix edk_)

For that reason i am here to ask for suggest and consult, to understand if i can reduce a bit the number of query, and if yes, how

tnx for help!

Can you also try going into your debug page (the "Advanced details" link at the bottom right of the forum) and see which queries are taking the longest?

hi dennis, tnx for your help,

the query that took more time is
#4 - Write Query
UPDATE edk_sessions <blabla>
Query Time: 0.00236034393310552

probably you will argue that 0.00023603439331055 isn't a lot
but...

Count : 60.78k (15.24%)
Count : 29.94k (7.51%)
(that is this query)
90K more or less

for this reason, suppose that each query require this time
0.00236034393310552*90000 = 212,4306

anyway, the point for me is not to reduce the time that this query is used to, but to reduce the number of query...
is possible to cache this query using eaccelerator shm?
is possible to store this information on php_session (and, will it be faster?)
what's happen if i put a rand(0,1)==0 before execute this query? (will i broke everything probably)

and so on

tnx for your help guys

The query is run to update a user session (ie mark that they're logged in).
As you can see, it's very fast. One thing to note is that the reported time is actually more than it takes the query to run.

MyBB 1.2 had the same query, and it hasn't really changed much in 1.4.

How often are you getting these 90,000 queries? Every day?

every 5hours more or less (easy to check, because rotate when they are 100MB, checking the access time of 2 near log, will do the trick)

-rw-rw---- 1 mysql adm 101M 2008-10-18 06:25 mysql-bin.000006
-rw-rw---- 1 mysql adm 101M 2008-10-18 11:46 mysql-bin.000007

seems that this kind of query is executed not just when user login or logout, but each time that navigate through the forum.

probably i haven't noticed that this was present on 1.2 because i simply haven't check for it

Yes, that query is performed every time someone navigates anywhere in your forum. Is it really an issue?

You mentioned caching the query somewhere to a memory cache - the sessions table isn't critical in terms of the data it stores - why not change the table type to a HEAP/MEMORY table?
See if that helps.

(2008-10-18, 12:10 PM)ZiNgA BuRgA Wrote: [ -> ]Yes, that query is performed every time someone navigates anywhere in your forum. Is it really an issue?

You mentioned caching the query somewhere to a memory cache - the sessions table isn't critical in terms of the data it stores - why not change the table type to a HEAP/MEMORY table?
See if that helps.

I've been wondering about that myself.