How to optimize mysqld process

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Common Man
Registered User
Posts: 40
Joined: Thu Nov 15, 2018 3:01 pm

How to optimize mysqld process

Post by Common Man »

recently i upgraded my site to vps
every thing was ok but, the pages are loading very slowly
my subdomains which contain another script loading normally
they told me, you need to optimize mysqld usage.

how to do that, version 3.2.2
User avatar
Mick
Support Team Member
Support Team Member
Posts: 26520
Joined: Fri Aug 29, 2008 9:49 am

Re: How to optimize mysqld process

Post by Mick »

Unfortunately server setup is beyond the scope of these forums, you should seek help on a support site that specifically deals with whatever software you’re using on your server. Also see What if I need help installing/configuring PHP, MySQL, Apache, etc.?
  • "The more connected we get the more alone we become" - Kyle Broflovski©
  • "The good news is hell is just the product of a morbid human imagination.
    The bad news is, whatever humans can imagine, they can usually create.
    " - Harmony Cobel
User avatar
janus_zonstraal
Registered User
Posts: 6418
Joined: Sat Aug 30, 2014 1:30 pm

Re: How to optimize mysqld process

Post by janus_zonstraal »

A link to your board can help.
Sorry! My English is bat ;) !!!
User avatar
Darth Apple
Registered User
Posts: 19
Joined: Mon Jan 07, 2013 1:25 am

Re: How to optimize mysqld process

Post by Darth Apple »

I host my websites on a VPS as well. It's absolutely worth the extra effort (so much more flexibility), but the setup does take a little longer and there are a lot of things that need to be configured. There could be any number of reasons for your performance issues.

Just out of curiousity, how large is your forum? What host are you using (and what plan, as in RAM and CPU does it have), and what does "top" or "htop" output for server load in the command line? Look for the "server load" line that will look something like this:
1.33 0.99 1.5
Those numbers represent, on average, how many cores the server is trying to use over the last 1 minute, 5 minutes, and 15 minutes. For example, if you have a dual-core VPS, you never want those numbers to exceed 2.0. If you're seeing very high numbers, it indicates your VPS is heavily overloaded. This does not necessarily mean it's your fault. Sometimes neighboring VPSs aren't behaving and they cause your load numbers to shoot up, so it just depends. Are your performance issues intermittant? Do they come and go or is it pretty much consistently slow?

My guess is that MySQL likely isn't the culprit unless you're running a large forum (the defaults are usually sufficient for small to medium forums). I'm curious as to whether the server is having IO thrashing or if it has run out of RAM. It could also be a configuration issue with Apache and PHP (try using php-fpm, it's much easier on RAM).

If you're running a larger/busier board or if MySQL is the culprit, I've found this guide to be extremely helpful for performance-tuning MySQL (I use this on every server I set up). Pay very special attention to the caches (both for MyISAM and InnoDB). Those control how much data MySQL stores in RAM. Larger databases require much more cache in order to avoid slow queries. (It's generally good practice to dedicate about 50% of your server's RAM to MySQL in most cases).

In any case, it's not something that can't be fixed. Could be any number of things, but we will narrow it down and find the culprit. :)

Best regards,
-Darth Apple
Common Man
Registered User
Posts: 40
Joined: Thu Nov 15, 2018 3:01 pm

Re: How to optimize mysqld process

Post by Common Man »

this was the first time i moved my site to shared hosting to vps
so, i really don't know specifications i need

my site and my subdomains loaded fast in shared hosting
but now only my subdomains are loading fast
my main site phpbb was loading slow

also when site ste users exceeded more than 1000 { for 45 minutes ]
site was slowing down taking more than 25 seconds to load a single page

and when it exceeds more than 1300 it showing
general error 1040 please contact the board administrator if the problem... etc
also something in phpbb consuming my whole ram.
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 5876
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: How to optimize mysqld process

Post by thecoalman »

If you have VPS you may want to look into adjusting my.cnf which is the configuration file for MySQL/MariaDB.

The problem is there is n o one size fits all and the optimized settings vary by application. If you open phpMyadmin, switch to status tab and then click the link for advisor it will give you some suggestions.
“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
stevemaury
Support Team Member
Support Team Member
Posts: 52768
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: How to optimize mysqld process

Post by stevemaury »

One way to increase performance is to upgrade to phpBB 3.3.1 so you can use php 7.4.
I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)
User avatar
Darth Apple
Registered User
Posts: 19
Joined: Mon Jan 07, 2013 1:25 am

Re: How to optimize mysqld process

Post by Darth Apple »

Ah, you're running a very high traffic forum. The problem is likely a couple different things, but luckily there are several steps we can take that can massively improve performance here.


- Are you using Apache or Nginx? If you're using Apache, I'd strongly recommend switching over to Nginx/PHP-fpm. This alone will probably resolve most of your issues! Nginx is designed specifically for high performance on higher traffic websites (and generally requires much less RAM as well).

- You'll also have to switch to PFP-fpm (an Nginx compatible version of PHP), and then optimize both as needed. PHP-fpm is also great. The FPM versions are likewise faster than the standard PHP installs.

- After installing Nginx, make sure to tune a few things, and check the worker processes/threads (according to the guide linked below) as well. Raise the number of worker processes to the number of cores on your server, and raise the worker threads to at least ~1024 or so.

- For MySQL, it's generally recommended (on higher traffic websites) to dedicate at least 50% of your server's RAM to MySQL. Although the default settings are very sufficient for lower and medium traffic forums, it sounds like your forum is highly successful (congratulations by the way) and manages to keep MySQL quite busy indeed. Are you using InnoDB or MyISAM for the tables? If you want to make the most of the RAM on your server, you can dedicate the majority of your caches to the engine that is used by phpBB.

Guide on installing Nginx/PHP-fpm (Ubuntu): https://www.digitalocean.com/community/ ... untu-20-04
Optimizing PHP-fpm: https://geekflare.com/php-fpm-optimization/
Optimizing Nginx: https://www.digitalocean.com/community/ ... figuration
Installing phpBB on Nginx: https://area51.phpbb.com/phpBB/viewtopi ... 81&t=49426

It's daunting at first. It's definitely quite a few things we need to tweak along the way, but it's worth it in the end. I've set up about two dozen of these on AWS, so reach out with any questions you have along the way, I'm more than happy to help! :)
Paul
Infrastructure Team Leader
Infrastructure Team Leader
Posts: 28651
Joined: Sat Dec 04, 2004 3:44 pm
Location: The netherlands.
Name: Paul Sohier
Contact:

Re: How to optimize mysqld process

Post by Paul »

1000 users online is not really a high traffic board ;).
Switching to nginx is not something I directly would suggest, especially if you have no idea what the actual cause is of the slowndown. If it is pure a mysql issue, then switching wouldn't help at all. Besides that, apache should be able to handle such amount of users with no problem, as long as it is configured properly.
Also, you should not try this kind of stuff on your production board, as it will take time to find the proper configuration, so you don't want to bother your users with you trying out stuff.
Common Man
Registered User
Posts: 40
Joined: Thu Nov 15, 2018 3:01 pm

Re: How to optimize mysqld process

Post by Common Man »

i don't think my site is a high traffic board
in previous, it handled more than 1500 usrs very smoothly
I think changing some configurations can fix my problem
but I'm new to this VPS environment
i found some suggestions in PHPMyAdmin
i have Cpanel and whm panel
don't know where is my.cnf file located
please guide me to solve this problem
Attachments
Capture.JPG
Paul
Infrastructure Team Leader
Infrastructure Team Leader
Posts: 28651
Joined: Sat Dec 04, 2004 3:44 pm
Location: The netherlands.
Name: Paul Sohier
Contact:

Re: How to optimize mysqld process

Post by Paul »

As per Mick,
Mick wrote: Sun Oct 18, 2020 9:15 am Unfortunately server setup is beyond the scope of these forums, you should seek help on a support site that specifically deals with whatever software you’re using on your server.
We can make some suggestions, however if you have no idea how to run a VPS, I highly suggest you don't use a VPS, as it will require basic knowledge on how to setup a server.
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 5876
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: How to optimize mysqld process

Post by thecoalman »

Before trying to tweak my.cnf open the WHM control panel, type tweak settings in search box and click the result below. Find the SQL tab. Set all option for "Allow cPanel & WHM to determine the best value for your MySQL......" to yes.

You said you had RAM maxed, How much RAM do you have and is it completely maxed or near max?

I don't like to discourage people but there is significant learning curve going from shared to VPS even if it's managed. The WHM panel can help a lot but that itself can be a bit daunting. Last host I had offered a "shared" hosting package on steroids for people that needed a lot of resources but didn't want the hassle of managing a VPS. The cost structure was same as VPS. You may want to look and see if you can find something like that.
“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
Darth Apple
Registered User
Posts: 19
Joined: Mon Jan 07, 2013 1:25 am

Re: How to optimize mysqld process

Post by Darth Apple »

TheCoalMan is correct. (Running out of RAM is specifically why I had suggested nginx as well. Unfortunately nginx is quite a bit harder to set up, and it might not be something that could easily be done on WHM. )

I'm not sure if we are allowed to link to specific hosts here or not, but my recommendation is to look into hosts that offer services similar to what thecoalman suggested (or research "managed VPS servers" as well). These services are specifically intended for use cases like yours, and the host takes care of all of the server management/maintenance.

You could also ask for help on a community that specializes in servers or see if someone you know is willing to help you set it up. The only downside is that server management is an ongoing process. There will be problems that arise, updates that need to applied, configuration changes that need to be made, and so forth. These can be daunting and can result in downtime if you aren't quite sure already how to use a command line or how to resolve them quickly.

Don't be discouraged by any of this! Server management is a daunting task. There are people who dedicate their entire careers to it (and it's a huge field). :)

Regards,
-Darth Apple
Common Man
Registered User
Posts: 40
Joined: Thu Nov 15, 2018 3:01 pm

Re: How to optimize mysqld process

Post by Common Man »

i'm upgrading my site from 3.2.2 to 3.3.1
to check if my problem will solve or not

i'm little confused about this knowledge base
https://www.phpbb.com/support/docs/en/3 ... upgrade32/

what should i do to my styles foler and ext folder
it says do not delete the ext folder, should i merge with the ext folder on package of 3.3.1
and what about styles folder
User avatar
warmweer
Jr. Extension Validator
Posts: 11242
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium
Contact:

Re: How to optimize mysqld process

Post by warmweer »

Common Man wrote: Tue Oct 27, 2020 5:44 pm what should i do to my styles foler and ext folder
it says do not delete the ext folder, should i merge with the ext folder on package of 3.3.1
and what about styles folder
You do not need to delete the /ext folder but you should check whether the extensions in there are compatible with phpBB 3.3. In most cases (from 3.2 to 3.3 they will not be), but usually updated versions are available. So disable (not delete data) all extensions before upgrading, and replace with updated versions after upgrading.
Same thing with styles; all except for prosilver should be disabled, and replaced by compatible versions after upgrading.
Basically it's the same with languages (but if there's a compatible version available, there's no harm in uploading that before upgrading - but upgrade with British English as only active language).
Extra: for an upgrade, use only the Full Package or the Changed Files package.

edited: concerning languages I wrote style instead of language: (corrected that now)
second edit: I need to follow a typing course :oops:
Last edited by warmweer on Tue Oct 27, 2020 8:49 pm, edited 2 times in total.
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
Post Reply

Return to “[3.2.x] Support Forum”