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
Have you upgraded to 1.6.1? Because I think the query in the update added a group by statement to the query. And yes that query on a large active forum is a problem but it's one of the most important on the site.
I personally altered mine but I had to remove some features like thread prefixes. If you don't use those you can alter your forumdisplay.php to at least remove one join.
I believe the temp table is caused because of the ORDER BY t.sticky. I'm still working on various ways to optimize my own site. I'm curious to know if grabbing the query without the t.sticky and using php to parse so sticky is top of list (instead of MySQL) would be more beneficial.
I do know that MyBB is becoming increasingly aware that performance is important.
There are also other query problems in 1.6 with the new features that if disabled by core edits might give you a performance boost. Do you have a big board yet (100k posts)? If so please join the big board group to have additional discussion about large forum optimizations in our private forum.
Thanks for you reply labrocca. I did upgrade to 1.6.1. Unfortunately, you are talking to someone that doesn't have as much knowledge as you appear too. I don't use thread prefixes so I could alter the forumdisplay.php but I do not know what part(s) need to be edited.
I have no idea what the ORDER BY t.sticky is, sorry.
What are these core edits that will yield a performance boost and how do I perform these edits?
Yes, I do have a big board (250K posts) and I will join the big board group as you suggested.
I can tell you that the query is not 1.6.1 though. You need to make sure you properly upgraded.
What are specs on your VPS? And who is provider? With 250k posts you may have to up your usage requirements. Do you mind saying what are you current hosting fees? If you're on some $20 account it's almost surely time to move to a more stable host that will offer you higher CPU and RAM.
You only have 16,000 threads but apparently it's enough to cause these problems.
Interesting, so the query in question is not 1.6.1? I ran the upgrade script what could have gone wrong?
I don't mind saying what my fees are I pay KnownHost $35 a month for the below VPS specs.
RAM - 768 MB
Total Processors: 4
Vendor: AuthenticAMD
Name: Dual-Core AMD Opteron™ Processor 2212
Speed: 1995.051 MHz
Cache: 1024 KB
Did you upload the new files from the changed files package?
On your host is there guaranteed CPU and RAM usage? Because I'm sure they don't give you all the processing power. Also is the MySQL server on the same VPS or is it a different box besides localhost?
Do you have the power to upgrade your MYSQL to 5.5? I did that this week and I saw an immediate 200-300% performance boost. You can go with MySQL 5.5 and convert all your MyBB tables to Innodb from MyISAM with little work.
A quick rundown on INNODB. MySQL has different engine types (think V6 vs V8 on a car). MYISAM used to be default and it's very old. InnoDB supports row level locking and some other jargon which I won't bore you with. But basically it's kick lime. It's stable too. I'm running HF with 8 million posts pretty well. I'm even considering reverting my performance changes just to see how well 5.5 is handling it all with MyBB 1.6.1 default.
So you do have some options here. If all this is scaring you don't worry about it. It's not that bad. If your host won't do the MySQL upgrade LMK and maybe I'll discuss with you an option.
(2011-01-19, 05:46 PM)labrocca Wrote: [ -> ]Did you upload the new files from the changed files package?
As far as I know I did. I followed the upgrade Wiki and uploaded the new files but something must not have gone right. How can I fix it? Run the upgrade again?
labrocca Wrote:On your host is there guaranteed CPU and RAM usage? Because I'm sure they don't give you all the processing power. Also is the MySQL server on the same VPS or is it a different box besides localhost?
I do not know. I will send an email asking these questions.
labrocca Wrote:Do you have the power to upgrade your MYSQL to 5.5? I did that this week and I saw an immediate 200-300% performance boost. You can go with MySQL 5.5 and convert all your MyBB tables to Innodb from MyISAM with little work.
Wow, that is quite the boost. I will if I can upgrade to MYSQL 5.5. If not ask them to upgrade. A little work for you sounds like a lot of work to me. haha. If I could upgrade MYSQL myself would doing so mess anything up?
labrocca Wrote:A quick rundown on INNODB. MySQL has different engine types (think V6 vs V8 on a car). MYISAM used to be default and it's very old. InnoDB supports row level locking and some other jargon which I won't bore you with. But basically it's kick lime. It's stable too. I'm running HF with 8 million posts pretty well. I'm even considering reverting my performance changes just to see how well 5.5 is handling it all with MyBB 1.6.1 default.
So you do have some options here. If all this is scaring you don't worry about it. It's not that bad. If your host won't do the MySQL upgrade LMK and maybe I'll discuss with you an option.
Yeah, this sounds kind of intimidating but I am willing to learn.
For the upgrade simply grab the Changed Files package and upload them all again. Just make sure not to upload the install folder. I'm going to assume that you did indeed run the upgrade script but just messed up the files uploading. This is common enough.
Quote:If I could upgrade MYSQL myself would doing so mess anything up?
Realistically a forum sql backup is simple enough and it's nearly impossible to permanently cause a problem you can't fix without a site backup. The only question is downtime. Do you do this with an hour downtime or 2 days? lol
This stuff can be intimidating. Trust me when I say it often baffles me too. But I have little choice but to figure it out and do my best. I'm certainly not going to pay MySQL $3000 for a support package.
I will grab the Changed Files package and upload them again. That is all I would have to do to get rid of that query that isn't part of 1.6.1? How can I verify that uploading them again fixed it?
Unfortunately the query remains but a bug is fixed and so are many other bugs. It's just best to make sure you on latest package. These are separate issues btw. Just upgrade properly. Having the latest version ensures that you have bugs fixed and security updates.