MyBB Community Forums

So does is the query still processed?

BTW, Jesse, did you get a chance to confirm my Big Board verification link?

(2011-01-19, 05:03 PM)gimmezell Wrote: [ -> ]Today my site has been slower. I sent an email to Paul at KnownHost who is extremely helpful. Below is his reply and I have no idea how to answer his question. If any one could help I would appreciate it.

Quote:Hi Jeremy,

Thanks for your email. I checked your VPS and seeing quite high CPU and intensive disk I/O usage generated by the MySQL server. While looking at MySQL I'm seeing a bunch of slow queries like the following example:

| 4637516 | ********* | localhost | ******** | Query | 24 | Copying to tmp table | SELECT t.*, p.displaystyle AS threadprefix, (t.totalratings/t.numratings) AS averagerating, r.uid AS rated, t.username AS threadusername, u.username
FROM mybb_threads t
LEFT JOIN mybb_users u ON (u.uid = t.uid)
LEFT JOIN mybb_threadratings r ON(r.tid=t.tid AND r.uid='1922')
LEFT JOIN mybb_threadprefixes p ON (p.pid = t.prefix)
WHERE t.fid='14' AND t.visible='1'
ORDER BY t.sticky DESC, t.lastpost desc
LIMIT 0, 20 |


Any query that forces MySQL to copy data into the temporary table is bad and will affect performance no matter what kind of hardware you'll through at it.
Are you familiar with the query above? Do you know what software might be generating it?


Regards,
Paul

Just want to say I'm experiencing the same issue since Saturday (same day!?) which made the forums slowdown a lot. Currently I'm trying to see if I can improve the performance a bit by tweaking the my.cnf manually but so far it's not helping that much though...

My forum stats:
Our members have made a total of 151.421 posts in 18.210 threads.
We currently have 57.858 members registered.

Version: 1.6.2

Everything is good now. MattRogowski, was kind enough to go through my database tables and added about 20 missing indexes. For whatever reason they were missing even though they should have been there from the beginning.

(2011-02-23, 05:22 PM)gimmezell Wrote: [ -> ]Everything is good now. MattRogowski, was kind enough to go through my database tables and added about 20 missing indexes. For whatever reason they were missing even though they should have been there from the beginning.

Thanks for the update, that's what fixed it for me as well.

I added a index for mybb_threads (keyname fid, Field fid, type index) and in mybb_threadratings (keyname tid, Field tid, type index)

It would be nice to have a way to do a structure compare for the database. I know that in upgrades and other various changes the latest MyBB tables may not be 100% accurate to the latest MyBB.

(2011-02-23, 09:51 PM)labrocca Wrote: [ -> ]It would be nice to have a way to do a structure compare for the database. I know that in upgrades and other various changes the latest MyBB tables may not be 100% accurate to the latest MyBB.

Yea that would be nice, I actually did a fresh install of mybb in a different database and started to compare the tables.
It seems that I'm missing quite a lot indexes as well (~20). I just added them manually.

We have been running MyBB for quite a while (since v1.0 or earlier i think).

Yes I mainly see the issues from upgraded sites that have been around a while. Maybe having a nice MyBB default schematic posted in Wiki would be nice.

by looking at the debug info you can tell what queries are missing indexes given the actual query being run and thus you can go add them to the tables. they may not be MyBB default indexes, but they will work and help performance.