Suddenly Very Slow Performance – SLOOOOOW Database Performace - The Jamroom Network

Clay Gordon
@claygordon
2 weeks ago
733 posts

I got a notice from Uptime Robot this morning that my JR site (hosted on Contabo) was unreachable for nearly ten minutes.

We ran a server check and it seems to be running smoothly.
I did a database integrity check and it came back okay.

I am doing an optimize tables and the following appear to be taking a very long time each – I am wondering if any of the following message points to a source of the problem? And if yes, what to do about them.

optimizing table: jr_jrcore_log_201901 .... optimizing table: jr_jrcore_log_202401
optimizing table: jr_jrcore_log_debug (VERY long time, like forever).
optimizing table: jr_jruser_item_key
^It looks like I have sufficient free disk space.

After optimizing tables I am still getting a DB Performance check rating of 58.76 with a baseline of 6.5.

This was working fine the last time I checked – a couple of days ago.

Do I need to repair tables?

@brian
2 weeks ago
10,144 posts

No - repair tables or optimize tables are not going to help. You really don't ever need to run optimize/repair, since that mainly affects MyISAM tables, which JR has not use in a long time (some really old installs may still be using them however). My guess is that your MySQL server is not actually ON your server - since you're only using ~1G RAM, that would mean your InnoDB settings would be set for a very, very small server - I have a feeling your database server is not on the same physical server as your webhosting, and that the DB server is over loaded. Maybe give it some time and see if it improves?

--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

Clay Gordon
@claygordon
2 weeks ago
733 posts

Brian -

Thanks. We will take a look at that. Is there a clue in the attached screenshot?

@brian
2 weeks ago
10,144 posts

It's hard to tell - what you want to look at is the value for "jrCore_db_host" in your data/config/config.php file.

Also - I would _highly_ recommend you get APCu installed on your server - it's almost a requirement in Jamroom 7 and will make your site faster.

I know I probably already asked you this but have you thought about coming back to JR hosting?

. We moved to new servers that are really, really fast.

--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
updated by @brian: 04/26/24 09:43:51AM

Clay Gordon
@claygordon
2 weeks ago
733 posts

Brian –

This is the first performance anxiety issue on Contabo. If this persists then I will definitely consider moving back.

Clay Gordon
@claygordon
2 weeks ago
733 posts

Brian -

db_host is 127.0.0.1 – localhost, not remote. I am on a VPS.

@brian
2 weeks ago
10,144 posts

OK yeah - then I'm not sure why that would be so slow - the system load appears fine (based on your screenshot). It could just be that the HOST your VM is on has a high load due to other VM's on the same server. Did your integrity check when you did the table optimization finish successfully?

--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

Clay Gordon
@claygordon
2 weeks ago
733 posts

Brian -

The tech guy I work with – who also handles my Ghost development and hosting – is looking into local network/VM issues that may be affecting performance.

I don’t see an obvious system status dashboard so I am going to have to create a support ticket.

While it took a loooooooooooong time., table optimization was completed and I exited properly. One suggested upgrade to all of the checks would be to add an elapsed time in the completion dialog. Could be useful in diagnosing issues. The regular integrity check took about the amount of time I remember it taking.

@michael
one week ago
7,697 posts

If optimizing your debug table took a long time, see whats in it. on my sites its almost always empty. If its filling up you might have left over debugging code in place.

Wipe out the activity log too if its big. Only really useful to help you understand whats going on at the moment. Pretty sure I've never gone back more than a month or so to see what was happening last month in the activity log.

Clay Gordon
@claygordon
one week ago
733 posts

Michael:

I have a lot of log tables. I just took a look at the Activity Log tab System Core - Global Config.

I have Developer Mode on. Not sure why.
Just before I took this screenshot (attached) PURGE was set to never.

I am going to set the purge to 1 day to force a purge ASAP then reset to 10 days and turn developer mode off.

What else should I consider?

Clay Gordon
@claygordon
one week ago
733 posts

Michael -

Right now, all of the Database Options are set to off. (Global Config-Database Options)

Would it help to turn these on for a couple of days to see what we might find?

@michael
one week ago
7,697 posts

Developer mode is sure to be one of the issues causing your site to run slow. Turn that off for production. It causes caching to be switched off so developers can see whats happening when they change stuff without having to click RESET CACHES each time.

Clay Gordon
@claygordon
one week ago
733 posts

After making a bunch of changes, system performance feels much more responsive even though running the Performance Check still returns an absurdly big result (greater than 50). Looking at the activity log I see a lot of the following entries – most of them from the same IP addresses:

developer: slow query took 276ms (associated with AWS)
developer: slow query took 450ms (associated with Google)

Slow db query logging is turned off in Global Config

I am also getting the following when trying to take a look at the Error Log from the Dashboard:

Unable to process your request
The system has encountered an unrecoverable error - please try again shortly

AND I found a typo in a help message in Global Config • Developer Settings in the Developer Settings tab, Developer is misspelled:

If this option is enabled, additional tools for rebasing and resetting the system will appear in the Develope Tools -- Tools menu
Default: off