Forum suddenly slow after db restore

[Soldf]

Why is the Forum slow after a DB restore?

We've had performance issues on our P.Board installation for 1,5 months.

Our host had some kind of problem with the db server on 22 Oct and the ibf_posts got corrupt, ending in a db restore.

After re-cache and letting it sit for a few days it was still slow.

We're on a shared hosting plan, as we want to spend our time serving the members in our community rather than patching os, installing apache and MySQL and fiddling with backups on a VPS.

Even with Performance mode on, it was slow, so we upgraded our plan to be allowed to use more CPU load and allocate PHP memory to 256 MB, (we only had 64 MB).

It became temporary faster and we activated full mode. Also we disabled Reputation system as ibf_reputation_totals is 737 000 rows.

WE USED TO HAVE MORE TRAFFIC A FEW YEARS AGO WITHOUT PROBLEMS

We've been on IPB since 2002 and it has worked quite fine. We now have about half as many visitors as back in 2007 and only about 1/10 of the daily post, maybe 1/20 of daily PMs and much fewer logged in members than a few years ago.

Our peak is about 100 users (80-90% visitors non members) which is about half compared a couple of years ago which worked fine on 64 MB and 100 visitors was no problem on 64 MB until Oct 22.

So in theory the board should be much easier to manage while the hosting environment has been upgraded to faster disks, cpu:s, bandwidth, etc.

The problem seems to be our db rucksack of 700 000+ posts as they all reside in one table ibf_posts. (It is now 600 000 post as we are archiving 33% of all topics)

INNODB?

Our host recommended converting to InnoDB. I have searched your forum and usually Bfarber has been skeptical to InnoDB in some posts, the latest seems to be from 2011.
http://community.inv...nd-myisam-type/
http://community.inv...-both-or-other/

Full text search and read performance seems to be the big issues with InnoDB and Sphinx is supported in IP.Board. Too bad our web host does not offer Sphinx support and I don't think it's something we can install our selves. But the above threads are rather old and MySQL it seems InnoDB has been improved since then.

The current MySQL engine at our host i 5.5.30 and I've read some test about full text search with InnoDB in MySQL 5.6 http://www.mysqlperf...sql-5-6-part-3/
It seems at least there is such a function now to FTS search n 5.6, but how about in 5.5.30?

SUMMARY OF WHAT WE HAVE TRIED

• Disable reputation system
• Archive 30% of all topics
• Upgraded PHP memory from 64 -> 256 MB and more CPU
• ibf_session from Myisam to memory as recommended in some threads.
• ibf_content_cache_posts to InnoDB
• Crawl-Delay: 5 in robots.txt to limit search engines to crawl too fast.
• Disable prefetching =Yes
• re-cached, optimized all tables

OUR OPTIONS?!
As I see it, we have three options given the fact that we cannot have Sphinx on my current web host and we have MySQL 5.5.30

1. Use Innodb on selected tables.
Which tables do you recommend to have MyISAM vs InnoDB for us?

We tried to convert the ibf_content_cache_posts to InnoDB but that does not help at all.

2. Find a new Web host.
Invision hosting is going to be too expensive for us, we're not a company and banner ads have to pay for our hosting.
Also our traffic is 95% from Sweden so hosting in Sweden would be preferred.

3. Use the archive function to ease the burden of the post table and buy some time

Since 1,5 week we have started to archive post each 10 minutes for all off topic forum posts without a reply in 4 years -> 30% of all topics.

Ibf_posts has gone from 700 000 posts and 512MB to 600 000 and 434MB. Still no improvement.

We would really appreciate any input from you who knows how IP.Board behaves and scales.

We submitted a ticked but Invision does not support performance issues or DB optimization. I was told to ask my question in this forum.

Thanks.

[3DKiwi]

One thing that would help would be to delete inactive members. I do this regularly and delete anyone who hasn't visited in the last 12 months. This will reduce your db size. I used to but don't anymore was to delete old topics. My current server can cope with lots of topics whereas early on the shared hosting plans we had couldn't.

Regulary have a look at the IP addresses in the online user list. It's not uncommon for me to have over 100 connections from 1 IP. Look the IP up here and ban it or the range it's in. Often these are spambots or a Chinese IP. Also worth doing is slowing Google down in your Google Webmaster account. Get one if you haven't.

Where you're hosted doesn't really matter. I'm in New Zeland by have my hosting in the USA since it's cheaper. You may have to bite the bullet and get yourself a dedicated server as I had to do a long time ago.

[Rhett]

Your hosting seems to be your issue, if our hosting is not in your budget, perhaps look at other hosting companies in your area etc.

Your forum should run fine however with most hosing providers, keep in mind you do get what you pay for however.

[wimg]

Do check whether all indexes were restored.

Since everything was working ok before the database restore, anything to do with the database needs to be checked. Also check

you didn't get a default my.cnf rather than the one you used to have.

Warm regards, Wim

[Rod.]

Don't delete inactive members, it won't make a difference. Our db is several GB in size, with 13k+ members. Like Rhett said, the problem is your hosting. Your entire site seems slow. I just did a reverse IP lookup on your domain, and there are 452 other websites being hosted on the same box. Your hosting provider is overselling.

[Soldf]

Thanks for your input.

The thing that troubles me is the sudden slowness. Is there something I can do with the DB?

Something that might not be right because of the restore?

Stupid question, but how do I "Check the indexes"?

I have optimized all un optimized tables and the post table that was very large is shrunken with at least 20%

Is there a list of what tables to have as myisam, innodb, memory and what thresholds for number or rows/size.

There are some threads here and there about MYISAM vs INNODB from 2011 and earlier, but no summary of recommendations and best practices.

As I said, it used to work fine for years with much more user activity, is 3.4 such a resource hog compared to earlier IPB versions?

[Soldf]

Don't delete inactive members, it won't make a difference. Our db is several GB in size, with 13k+ members. Like Rhett said, the problem is your hosting. Your entire site seems slow. I just did a reverse IP lookup on your domain, and there are 452 other websites being hosted on the same box. Your hosting provider is overselling.

Btw how could you see the 452?

I used some reverse IP-tools and if I user www.soldf.com I get 9

If I use forum.soldf.com I only get 1. As we have a SSL certificate it's probably better to use www in order to get the machine.

The web host has a large load balanced environment with storage on NetApp Clustered Data ONTAP.

[Ae9803]

Unfortunately shared hosting is a gimmick for sites that use forums, once you go to vps you dont look back :)

[Rod.]

Btw how could you see the 452?

I used some reverse IP-tools and if I user www.soldf.com I get 9

If I use forum.soldf.com I only get 1. As we have a SSL certificate it's probably better to use www in order to get the machine.

The web host has a large load balanced environment with storage on NetApp Clustered Data ONTAP.

I used this site to check who else is hosted under your IP: http://www.yougetsignal.com

Not sure how accurate it is, but it returned 452 results.

Either way, doesn't really matter, as there are other things that affect performance. You can have 1,000 other websites on the same box, however if they're all very tiny, static html sites that receive no traffic, the affect it will have on your site is minimal.

Load balanced w/ netapp means nothing. Shared hosting companies oversell, period. How can your host guarantee you 256 MB RAM in a shared environment? They can't. Do you have shell access? If so, you can run some commands on the server to see resource usage. Bottom line is you're most likely hitting your limits. 256 MB RAM for a forum is nothing. I have over 5 GB allocated to my forum, and even at that, I hit the limits.

[wimg]

Thanks for your input.

The thing that troubles me is the sudden slowness. Is there something I can do with the DB?

Something that might not be right because of the restore?

Stupid question, but how do I "Check the indexes"?

I have optimized all un optimized tables and the post table that was very large is shrunken with at least 20%

Is there a list of what tables to have as myisam, innodb, memory and what thresholds for number or rows/size.

There are some threads here and there about MYISAM vs INNODB from 2011 and earlier, but no summary of recommendations and best practices.

As I said, it used to work fine for years with much more user activity, is 3.4 such a resource hog compared to earlier IPB versions?

Optimization just propels changes into the database (sorry, a little tired, can't think of the correct terms right now and English is not my mother tongue).

To check indexes (and tables) you could use the tools in the ACP:

ACP -> Support -> Diagnostics -> Database Index Checker

HTH, warm regards, Wim

[GreenLinks]

It is your hosting , there is total of 1476 other sites hosted on the same server http://whois.domaintools.com/soldf.com

99.9% your host is overselling their servers. Your best bet is moving to a better host.

Also you should consider converting your DB tables to InnoDB however when the host is overselling even converting to InnoDB is not going to help much.

Your issue is 99.9% related to MySQL table locks , InnoDB may assist on that

But keep in mind site loading speed is an extremely important factor regarding SE rankings , if you are targetting to improve your forum , you should act soon. You can also try to use a CDN service to minimise apache calls on your server and reduce the load levels a bit.

[Makoto]

Unfortunately shared hosting is a gimmick for sites that use forums, once you start renting your own servers you dont look back :smile:

I fixed that for you :P

[IveLeft...]

Unfortunately shared hosting is a gimmick for sites that use forums, once you own your own servers you don't look back :tongue:

Fixed Again :thumbsup:

[Ae9803]

Rofl does wording really matter? I don't know if it was being nice or being respectful.