Issues with DB Load since upgrading to 3.2

Software Support
1

Hi all, we’ve been struggling with our database load since our upgrade to OJS 3.2.1-1 in August. This version of OJS appears to be creating considerably more connections to backend databases than it has in the past, which isn’t a problem until usage spikes, then slow database response leads to pages not loading. This happens most obviously when the server is being crawled by multiple bots, typically Google, Microsoft and Baidu.

We’ve switched the web server over to apache’s mpm-event multiprocessing module, as well as running php in fastCGI (php-fpm) mode hoping that this performance improvement might help. Although the web server performs better now, the load on the database is still high during crawling. One additional setting that we will try to change is switching mysql to use persistent connections.

We’ve also updated all robots.txt to add a crawl delay.

Does anyone have any other suggestions to decrease our DB load? Is anyone else experiencing this issue?

1 Like
2

Hi @Sonya_Betz,

A couple of users have reported improvements with added indexes; see: Add indexes for search performance · Issue #6301 · pkp/pkp-lib · GitHub

Regards,
Alec Smecher
Public Knowledge Project Team

3

Thanks Alec - We’ve moved all of our journals to InnoDB and added those missing indexes. We’ve also disallowed access to several bots that looked like they were not respecting our crawl delay settings, or that looked like they were solely collecting seo data to sell and not really providing much benefit, including:

MJ12Bot
PetalBot or AspiegelBot
AhrefsBot
SEMrushBot
DotBot
MauiBot

In the robots.txt file:

User-agent: bot_name
Disallow: /

We’ll see if those combined changes do the trick!

2 Likes
4

(Writing from the phone…)

Hi Sonya,

Are you using mariadb or mysql?

Some benchmarks show that recent versions of mariadb degrade quite easily.

Please, keep posting about your research.

I’m moving to 3.2 and I’m really concerned about DB performance.

BTW @asmecher . Won’t some caching help to decrease the DB calls?

Take care,
m.