Strange problem: increase in the number of db row reads

Get help with installation and running phpBB 3.3.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
User avatar
RoboYonk
Registered User
Posts: 5
Joined: Sat Sep 26, 2020 6:26 am
Name: Thomas

Strange problem: increase in the number of db row reads

Post by RoboYonk »

Hi!

Nice to be here :)

Last weekend I upgrade one of the largest literary forums in Poland (almost 249 k posts, more than 4 thousand registered users - of course not all are active user type...) - https://weryfikatorium.pl/forum.
The previous phpBB engine was v.3.1.8. Now is v.3.3.1

I have installed and adapted new style (Ravaio from Gramziu, themeforest). I'm frontend developer, so I add some little changes ;)

The forum use several extensions, such as:
  • Advanced BBCode Box (v.3.2.4)
  • Auto Groups (v.2.0.3)
  • Board Announcements (v.1.1.0)
  • Board Rules (v.2.1.2)
  • Collapsible Forum Categories (v.2.0.0)
  • Display Last Post (v.1.2.0)
  • Genders (v.1.1.1)
  • Google Analytics (v.1.0.5)
  • mChat (v.2.1.4)
  • Posts merging (v.2.1.0-1 - strange version because I had to modify some core elements, so I added my version number to distinguish from the original)
  • QuickReply Reloaded (v.2.0.1-beta4)
  • RH Topic Tags (v.3.0.0)
  • Robo Configuration 2 (v.2.3.4) - it's a Ravaio Configuration ext, but I modified the original enough that they are not completely compatible anymore :]
  • Search user topics (v.1.0.2)
I have added my own styles to most of them for compatibility with Ravaio style. Of course...

Language pack - polish.

I tried not to change the configuration in the admin panel to keep the settings from the previous version.
I've been working on the modifications for over two months on the forum clone, on separate hosting and when everything (or the vast majority) was ready, I upgraded the forum on last weekend. And it stared... :P
One more think, searching didn't work on prev version, so I cleared search_wordmatch and search_wordlist tables, and turned on reindexing. The database has grown from less than 1 GB to 2 GB.
The search engine used is phpBB Native Fulltext.

Everything works fine in general, except for one thing: Maybe I was missing something, but suddenly the number of rows read in the database exploded!
Below I am putting links to pictures with server load statistics from my hosting.

CPU time used by scripts: X - the day of mont, Y - time in hh:mm
Image

Time of using the system on the database server during the day: X - date, Y - time
Image

The number of rows readed from databases per server per day: X - date, Y - in milions reads (i'm not sure if I translated it correctly)
Image
this red line is a limit on hosting for single account. As you can see, the limit has been significantly exceeded. The upgrade started on September 18 in the evening, there weren't that many readings before.

Interestingly, the number of database connections has not increased.
Number of database queries:
Image

Please help me! :) What I'm doing wrong. Or maybe nothing and this is normal behav for v.3.3.1 of phpBB? Or maybe I should change some setting/settings?

Thanks from the mountain!*

Thomas

*it's a joke - it's mean thank you in advance
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 5871
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: Strange problem: increase in the number of db row reads

Post by thecoalman »

Not sure what version it changed but at some point the way way text is stored for posts, signatures and other things changed. Each post needs to be reparsed and that is handled by cron.php. There will be a lot of database activity if that is what is occurring and it will stop once they are reparsed.

You can test this by going into the ACP and under server settings set "Run periodic tasks from system cron:" to yes which will stop cron.php from executing, it's just an "image" file in footer and used as work around for real cron. If the database activity drops off you'll know that is what it is. It will reparse X amount of posts each time it is requested, it does other things as well.

You can set it back to no if the activity is not problem, once again it will stop after a while. If your host is complaining leave it set to yes and you can set up regular cron job through hosting control panel to limit it's activity.
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
RoboYonk
Registered User
Posts: 5
Joined: Sat Sep 26, 2020 6:26 am
Name: Thomas

Re: Strange problem: increase in the number of db row reads

Post by RoboYonk »

Thanks for quick reply! :o

I reparsed it by hand, because some bbcodes was changed (not displayed correctly), but I didn't turn off this switch in ACP.
Is phpbb cron not responsible for sending e-mail notifications too? If I disable native phpbb and don't set real cron, will it stop getting notifications to users?
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 5871
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: Strange problem: increase in the number of db row reads

Post by thecoalman »

I edited my previous post, "it does other things as well." You either need to use built in cron.php or use regular system cron. If you use sytem cron you have control over how often it's executed.
RoboYonk wrote: Sat Sep 26, 2020 8:46 am I reparsed it by hand,


Presumably you mean using CLI?
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
RoboYonk
Registered User
Posts: 5
Joined: Sat Sep 26, 2020 6:26 am
Name: Thomas

Re: Strange problem: increase in the number of db row reads

Post by RoboYonk »

thecoalman wrote: Sat Sep 26, 2020 8:54 am [...] Presumably you mean using CLI?
Yes, yes! Of course :)
I followed this guide, or something similar...

Thanks for the tips, I'll be testing.
User avatar
RoboYonk
Registered User
Posts: 5
Joined: Sat Sep 26, 2020 6:26 am
Name: Thomas

Re: Strange problem: increase in the number of db row reads

Post by RoboYonk »

Summary:

The bots turned out to be the main problem.
Three things I took into account: bots, cron and just cleaning the cache, after every minor change in settings, or in ext or style files. It turned out that the biggest problem for the host was the third option. It is true that the bots generally burdened the server, but the sudden increase in the number of row reads from the database is a matter of cleaning the cache.

Cron is not as heavy as it seems.

The bots have been rendered harmless. I created a robots.txt file and adjusted the entries in .htaccess.
By the way - based on 7G Firewall, I used their sets, combined with the standard ones from phpbb. The created htaccess and robots files are available on GitHub. Maybe someone else will benefit.
NOTICE: Files "7G_log.*" are not necessary, they are great for testing the rules. Everything is in the 7G Firewall documentation.

Thanks for help :)
Post Reply

Return to “[3.3.x] Support Forum”