Optimizing large boards

Do not post support requests, bug reports or feature requests. Discuss phpBB here. Non-phpBB related discussion goes in General Discussion!
Scam Warning
Post Reply
lochness
Registered User
Posts: 54
Joined: Tue Aug 07, 2007 12:04 pm

Optimizing large boards

Post by lochness » Sat Mar 02, 2019 6:05 pm

Some years ago there was a topic on optimizing large boards to improve performance and I haven't found anything similar for 3.2. Besides, that topic has thousands of messages and it takes time to read.

Things I've read here and there are switching the most used tables to InnoDB (topics, posts, private messages, users, sessions. The latest phpBB versions already create the tables as InnoDB, but back in the days in which there was no fulltext available for InnoDB, some of us might still have some tables as MyISAM. In my case, the posts one, which has 4M entries and in my server takes a few hours in case you want to do anything other than checking and anything done with it has to be done over ssh.

Another one is creating an index join in the posts tables with topic_id, post_time and a third parameter I can't remember. If anyone does remember, please feel free to drop the name below. The sql command will also be appreciated.

And the third one is using sphinx as search engine for the board. The instructions to install and run can be found in https://wiki.phpbb.com/Sphinx_Fulltext_Search

Some tools that can be useful to manage if case of no access to ssh
  • AutoMySQLBackup. all kinds of options including encrypting the backup, incremental backups and FTP it
  • Big Dump Tool to import database backups. It has some limitations on the kinds of imports it can do and can run into timeouts depending on server resources and settings.
  • Adminer: database manager. I use this instead of phpMyAdmin.
In my case, I'm also trying to unify the table maintenance tasks either with cron or a script that checks and autorepairs tables at least once a week or once a fortnight and does the backups and removes the older ones automatically. I'm still deciding whether I want a separate backup just for the posts table and another for the rest and whether to zip them or not. I've found that doing them manually don't keep them as up to date as I would like.
The cron task right now looks like

Code: Select all

30 3 1,15 * * mysqlcheck -u root -pXXX --auto-repair --o --all-databases 2>&1 | mail -s "MySQL Database Optimization" user@domain.com
And the one to do the backup removal (commented out until I automate the backup creation.

Code: Select all

0 3 * * * find /path/to/backup/folder -type f -name 'backup*' -atime +3 -delete
What kind of optimizations do you run in your boards? Any other ideas on how to tweak a large board?
Last edited by lochness on Tue Mar 05, 2019 5:53 pm, edited 1 time in total.

User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 2985
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: Optimizing large boards

Post by thecoalman » Sat Mar 02, 2019 8:18 pm

It's easy, just throw money at it. :D Sphinx is outstanding but it should be noted you need root access and it needs RAM allocated. I haven't done anything to tweak phpBB itself but do run opcache, all my tables are innodb, SSD drives and various other tweaks server side.

I'm also using Cloudflare, not for the performance but for the DDOS protection. Haven't got around to it yet but I'll be leveraging that for caching output from file.php.

viewtopic.php?f=641&t=2502946
lochness wrote:
Sat Mar 02, 2019 6:05 pm
I've found that doing them manually don't keep them as up to date as I would like.
Have a look at AutoMySQLBackup. all kinds of options including encrypting the backup, incremental backups and FTP it. The basic backup is performed once daily, it rotates out the old ones over 7 days, weekly backup and it rotates them out every 4 weeks, monthly backup I do not rotate. I have script for WinSCP that is scheduled at night and syncs with the backup folders. I also use it to sync the entire contents of public_html thus I have complete copy of everything. If i had to restore the site it's just a matter of how fast I can upload it.

https://sourceforge.net/projects/automysqlbackup/

lochness
Registered User
Posts: 54
Joined: Tue Aug 07, 2007 12:04 pm

Re: Optimizing large boards

Post by lochness » Sun Mar 03, 2019 9:25 am

thecoalman wrote:
Sat Mar 02, 2019 8:18 pm
It's easy, just throw money at it. :D
:lol: :lol:

I believe opcache is standard and enabled in lates php versions, but I haven't played with it since I'm still trying to fine tune the prefork and mysql conf files to my server and load.
thecoalman wrote:
Sat Mar 02, 2019 8:18 pm
and various other tweaks server side
Any that can be useful regardless server specs and loads?

By the ways, this reminds me that I have had to recently ask my host to switch me to another server since I was seing 25% steal and a lot of related time outs. It took me a while to think about steal CPU as the reason for the jump from ~25% CPU to ~50% CPU with the current board usage.
thecoalman wrote:
Sat Mar 02, 2019 8:18 pm
I'm also using Cloudflare, not for the performance but for the DDOS protection.
I'll investigate how to implement. Thanks for the tip. I always thought it was to cache files and images and that's it, but DDOS protection sounds nice.
thecoalman wrote:
Sat Mar 02, 2019 8:18 pm
Have a look at AutoMySQLBackup
Will do :mrgreen:
thecoalman wrote:
Sat Mar 02, 2019 8:18 pm
I have script for WinSCP that is scheduled at night and syncs with the backup folders.
¿lftp or rsync or something else? Another option would be Unison.

User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 2985
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: Optimizing large boards

Post by thecoalman » Sun Mar 03, 2019 9:08 pm

lochness wrote:
Sun Mar 03, 2019 9:25 am
I believe opcache is standard and enabled in lates php versions,
If you are using WHM/Cpanel you can select it in EasyApache but you still need to configure and enable it.

Any that can be useful regardless server specs and loads?
Nothing in particular.
I'll investigate how to implement. Thanks for the tip. I always thought it was to cache files and images and that's it, but DDOS protection sounds nice.
There is all kinds of things it does, it's a little bit work to fully utilize it. Quick tip, you can firewall off ports 80 and 443 to everything but Cloudflare IP's . This is actually important step if you are concerned about DDOS. If they know the IP range they will try and fingerprint the IP by making requests across the range for a file that is unique to your site. This can also be a bit helpful in slowing down an attack on the IP if they find it, any http requests are dropped.

¿lftp or rsync or something else? Another option would be Unison.
WinSCP has it's own sync utility but you need script to use it with Windows Scheduler.

User avatar
warmweer
Registered User
Posts: 2011
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: Optimizing large boards

Post by warmweer » Tue Mar 05, 2019 4:35 pm

lochness wrote:
Sat Mar 02, 2019 6:05 pm
Some tools that can be useful to manage if case of no access to ssh
  • Big Dump Tool to import database backups. It has some limitations on the kinds of imports it can do and can run into timeouts depending on server resources and settings.
    ...
BigDump does not work with php 7.
If bigdump times out (and you have acceptable settings in the bigdump.php) then you'll most probably have worse timeout problems with other "dumpers"
A bug is a feature that hasn't made it to the manual (yet)

lochness
Registered User
Posts: 54
Joined: Tue Aug 07, 2007 12:04 pm

Re: Optimizing large boards

Post by lochness » Tue Mar 05, 2019 5:52 pm

I didn't know Big dump isn't working in the latest php versions. Btw, I've added Thecoalman suggestion regarding Automysql to the first message.

User avatar
Dog Cow
Registered User
Posts: 2493
Joined: Fri Jan 28, 2005 12:14 am
Contact:

Re: Optimizing large boards

Post by Dog Cow » Thu Mar 07, 2019 4:59 pm

lochness wrote:
Sat Mar 02, 2019 6:05 pm
Some years ago there was a topic on optimizing large boards to improve performance and I haven't found anything similar for 3.2.
That topic is about a decade old by now, and times have changed. PhpBB 3.x incorporated a lot of the optimizations and queries suggested in that topic, as well as other optimizations. CPU performance has increased dramatically, especially with multiple cores and gigabytes of RAM, and now SSDs are very common for low-latency storage.
Moof!
Mac GUI Vault: Retro Apple II & Macintosh computing archive.
Inside Allerton bookMac GUIMac 512K Blog

User avatar
</Solidjeuh>
Registered User
Posts: 1325
Joined: Tue Mar 29, 2016 3:45 am
Location: Aalst (Belgium)
Name: Andy Dm
Contact:

Re: Optimizing large boards

Post by </Solidjeuh> » Thu Mar 07, 2019 5:16 pm

Mine: Fast server, Opcache, Pagespeed and a good .htaccess.
https://www.pixelemu.com/documentation/ ... ion-part-1

and forum is a rocket 8-)
We offer fun HTML5 games for young and old.
Register a free account & enjoy all functions!
Save your score, challenge other members or play along with our tournaments.


~~~ https://www.solidjeuh.be ~~~

Image

Post Reply

Return to “phpBB Discussion”

Who is online

Users browsing this forum: koraldon and 22 guests