MyBB Community Forums

A great sql table makes the server overheat, and it also makes a board slower. Its side effects are felt when your board has more than 1.000.000 posts, and it is visited by thousands of guests and members within an hour. I know that enormous sql table equals mybb_posts, so I hope developers of MyBB optimize this table. Though I'm not a programmer, I'll just recommend a few things about this matter:

Is it possible to divide mybb_posts table into a few tables? For example in every 200.000 posts, a new post table can be automatically created such as mybb_posts1, mybb_posts2 and so on?

Or another way, is it possible to divide mybb_posts into three tables such as mybb_posts1, mybb_posts2 and mybb_posts3 in which the posts will be recorded into these three tables in row. For example, 1st post into mybb_posts1, 2nd to mybb_posts2, 3rd to mybb_posts3, 4th to mybb_posts1, 5th to mybb_posts2 and so on....

My board's mybb_posts table is bigger than 400 MB. It is very slow while viewing the threads though I always rent and use the best servers. The problem is also not related to any other plugins. And there is no problem of slowing while viewing other pages. So, for enormous boards, mybb_posts table can be optimized in different ways you develop. I just recommend it, but I don't know the best way to achieve this as I'm not a programmer.

This has nothing to do with MyBB 2.0. However, have you considered joining the "Big Board Owners" group (if your board meets the group requirements)? You can view the group requirements here http://community.mybb.com/usercp.php?action=usergroups

The largest MyBB forum is running 12 million posts just fine. Doing something like this would cause more problems than solutions imo.

Most of the time you can optimize your forum without changing the schema of the database. What database engine are you using? Are you using any pre-parser plugins? How many queries per second are you doing?

This would do little for optimization and instead add more overhead. It sounds like your server is struggling with something; I'd check with your system administrator.

In fact, I don't know whether those ways might be useful or not. Anyway, there are many ways to optimize tables; for example, some unimportant columns might be excluded from posts table and be included into a secondary posts table with another name.

Spencer, thanks for your invitation to Big Board Owners. I generally clean old threads every month, and delete more than 100.000 posts monthly. There aren't 1.000.000 posts yet. If I don't delete old threads and posts, be sure our board will be slower and slower; and I'll have to close it. We don't have one million posts, but our posts generally includes articles, stories (I mean long texts) as well as short ones etc.

Malcolm., I checked your website and saw there are a few guests in your online list whereas our board generally takes hundreds of visitors within 15 minutes and most of them check and reply all the new topics and that situation makes the board slower. Your board has much more posts. Anyway, what's your server's features? Do you use dedicated server as I do?

Steven, I agree with you that this might cause worse results; however, we can't be sure without trying such methods. Beta testers can try such methods; I can give my database to MyBB developers: no matter. It is only a prediction for optimization. In fact, it is a serious problem for me that I have to use expensive dedicated servers; otherwise our board has trouble. I'm not rich, and I don't earn any money from administrating a board.

As I said, I'm not a php programmer. Our MyBB developers know the best way to optimize mybb_posts table for big boards. Maybe they can exclude some unnecessary columns from posts table and include them into a secondary 'posts' table. If a table is mostly used and it is the largest one, the server has difficulty when the board has a great vast of traffic.

Please check:

While viewing a thread with two posts:
Generated in 2.7790620 seconds (6.11% PHP / 93.89% MySQL)
SQL Queries: 31 / Global Parsing Time: 0.5508971 / Memory Usage: 14.83 MB
PHP version: 5.1.6 / Server Load: 1.89 / GZip Compression: Enabled

At the moment, it's very good. Sometimes generated in 4-5 seconds

Wile viewing a private message:
Generated in 0.1188228 seconds (62.13% PHP / 37.87% MySQL)
SQL Queries: 18 / Global Parsing Time: 0.0648279 / Memory Usage: 14.77 MB
PHP version: 5.1.6 / Server Load: 1.90 / GZip Compression: Enabled

Posts table is greater than private messages table. So viewing a post takes more time than viewing a private message.

Please check the database (though I deleted 100 MB from mybb_posts last week; it was 480 MB and members would have difficulty in viewing the threads/posts):



So I believe that our MyBB developers should give importance to optimization in 2.0. I witnessed that MyBB 1.2 was faster than any version we have had so far. However I don't forget it would give several errors of mysql collapse as the board had great traffic.

Putting the information for posts in separate tables wouldn't really change much. You would still be retrieving the same amount of information you would just be request it across multiple tables. And, if your having issues before reaching 1,000,000 posts there are things you can as others have said.

While yes things can always be optimized your just saying they should be without really giving a practical way of doing so. There are site that are much larger than yours that are running fine so there is something you can do.

Also maybe go through your plugins because your showthread.php is loading about 10 more SQL Queries than a base install of MyBB.

Edit: You may also just need a better server because depending on how many cores your sever has it looks like it's stressing the processor.

(2011-08-15, 11:19 PM)FirefoX Wrote: [ -> ]While viewing a thread with two posts:
Generated in 2.7790620 seconds (6.11% PHP / 93.89% MySQL)
SQL Queries: 31 / Global Parsing Time: 0.5508971 / Memory Usage: 14.83 MB
PHP version: 5.1.6 / Server Load: 1.89 / GZip Compression: Enabled

If this is a dedicated box, why is it still running PHP 5.1? Even Debian which the distro with a reputation for using old "stable" software instead of newer versions, is long past the PHP 5.1 age. If PHP is outdated, maybe MySQL is too? Add suboptimal configuration of services to that and there's a good chance the issue is on your end entirely.

In any case, you should investigate in more detail why MySQL takes up so much time here. This would be way more productive than blindly assigning blame or making suggestions that make no sense whatsoever - having three posts tables will only make things worse. Most likely it's a problem on your end or a plugin doing a very costly query. If this was MyBB's fault, everyone else would suffer in the same way.

(2011-08-15, 11:19 PM)FirefoX Wrote: [ -> ]Malcolm., I checked your website and saw there are a few guests in your online list whereas our board generally takes hundreds of visitors within 15 minutes and most of them check and reply all the new topics and that situation makes the board slower. Your board has much more posts. Anyway, what's your server's features? Do you use dedicated server as I do?

UnR runs on two servers, a web server and database server.

Few things you can try:

Upgrade PHP to the latest version, upgrade MySQL to the latest version, change your tables to InnoDB, install a cache such as xcache or eAccelerator, enable the mysql query cache... I could go on, but there's many things you can do.

To Alex Smith

If I removed all the plugins and custom templates, the board would be very pure. And I'm very sure that it's not due to plugins. I tried removing the plugins before. However, it was still the same. If you don't believe me about this, I can remove all the plugins and paintscreen the advanced debugs for you.

The problem is due to enourmous traffic.
Daily unique visits = around 10.000
Daily page views = around 40.000
Stats from Google Analytics

and when I search "site:www.blablabla.com" in Google, it gives 200.000 results.

Maybe other big boards don't have traffic like ours, so you shouldn't compare it with other larger boards without knowing the conditions.

I agree that we need a better server. Our old server (Xeon X3450 8x2.66 GHz) was perfect, but now we use two processors (Xeon E5530 2x2.4 Ghz -VDS) of a virtual server due to financial problems.

To frostschutz
Later I will update to last version of PHP, anyway it's not due to using the old version. Last month, I used PHP 5.3.6 in another server. I couldn't see any performance increase in version changes. :-/
In the meantime, I changed three servers last month.

I've been investigating the problem for years; now I focus on mybb_posts (which is the largest table and makes the server load increase, especially when the traffic is terrific.)

And I don't blame MyBB; I just request developers to focus on optimization of posts table for 2.0. I'm using MyBB since 2005, and I'll continue and support. I like MyBB, and I have more than ten MyBB boards. I always recommend using MyBB to my friends and help them.

This is the suggestion forums; and I'm suggesting a few things as someone who has a board with terrific traffic and large database. Don't behave me as if I'm blaming MyBB. :shy:

To Malcolm.
Thanks for your suggestions. I can try those ones.

Every now and then you should run OPTIMIZE TABLE on your various MySQL tables. This works a lot like defrag on files. It compacts records, reorders them based on primary key, and updates indexes. Since you seem to enter and delete a lot of records, you may need to run this even more often.

Proper indexes can increase your lookup speeds, but may slightly slow down inserts.

Look in your slow queries log from MySQL to find out what is really causing the slow down. From there you can ask MySQL to EXPLAIN or DESCRIBE the query and have it suggest indexes.