MyBB Community Forums

hi,

When users answer to posts on my freshly from XMB migrated forum, they often encounter a 15 sec lag.

the server is a dedicated one with 6 cores and 4GB RAM
we got 250 simultaneous users and + 3.000.000 posts

at the beginning i had locks on mybb_users table => resolved by putting this table in InnoDB

now the problem is when running this query :

Quote:mysql> SELECT pid FROM mybb_posts WHERE tid='143583' ORDER BY dateline ASC LIMIT 0, 1;
+---------+
| pid |
+---------+
| 3856104 |
+---------+
1 row in set (16.36 sec)

we obtain random duration on this request : from .01s to 15-16 sec...
when it takes 15 sec, one core goes 100% cpu.
during this time the server works normally : no lag at opening pages
nothing is shown in SHOW PROCESSLIST

i've optimized the database
checked the tables for repair

we never had such issues under XMB

I didn't find any reason

Thanks by advance for your advices

Do you use any of those heavy plugins? Some plugins decrease your performance, increase memory and server load so get rid of plugins that you don't really need. Sometimes you can do the stuff manually no plugin is required.

Please take a snapshot of you "debug information" section and post it here

Is this the only query you've seen is slow? Is it slow at totally random times or is there any sort of pattern to when it's slow?

Thank you for your answers.
I believe the problem can be solved by forcing the mysql optimizer to use the proper index

for example :

Quote:mysql> SELECT pid FROM mybb_posts use index(tid) WHERE tid='143583' ORDER BY dateline LIMIT 0,1;
+---------+
| pid |
+---------+
| 3856104 |
+---------+
1 row in set (0.01 sec)

mysql> SELECT pid FROM mybb_posts use index(dateline) WHERE tid='143583' ORDER BY dateline LIMIT 0,1;
+---------+
| pid |
+---------+
| 3856104 |
+---------+
1 row in set (25.14 sec)

mysql>

Here we can see that if I force the request to use the tid index : the response time is more than correct...

now the problem is... where could I find this request to modify it?
I didn't see it in newreply.php

i've found in datahandler/post.php and there are several lines that could be modified (for example by adding pid in the order by sequence : i.e to get ORDER BY dateline,pid)
worth a try?

another question would it be a good idea to delete the dateline index ?
the cardinality of this index equals the number of lines of the file... is it really useful ?

I know these questions aren't so easy and even not so usual...
sorry to bother you with these interrogations, but I really need to find a way to get a well-running forum..

thanks

Hi,

I know this problem doesn't affect many users (it seems we're not so many to have more than 3 millions posts...)
but I wanna share how the problem was fixed by adding fields in the order statement

patches :
* inc/datahandlers/post.php

"order_by" => "dateline",
=>
"order_by" => "pid,dateline",

* inc/functions.php

ORDER BY p.dateline ASC
=>
ORDER BY p.dateline,p.pid ASC

* editpost.php

$query = $db->simple_select("posts", "pid", "tid='{$tid}'", array("limit" => 1, 
"order_by" => "dateline", "order_dir" => "asc"));
=>
$query = $db->simple_select("posts", "pid", "tid='{$tid}'", array("limit" => 1, "order_by" => "pid,dateline", "order_dir" => "asc"));