Sanjeev Sabhlok's notes on technology, hardware, gardening

Dreamhost’s MySQL VPS and VPS – some notes

[RELATED POST]

NOTE TO SELF: I DON’T NEED MYSQL VPS. I’VE TESTED THIS TWICE ALREADY AND THE PROBLEMS NEVER GO AWAY THROUGH THE UPGRADE.

In August 2017:

I was having massive surge in errors in the error.log and got SICK of things and decided to try an upgrade to MySQL VPS. Turned out that NOT A SINGLE ERROR GOT REDUCED.

This proved (once again) that the database was NOT causing problems. It was the use of CPU and RAM in the server.

The solution to that is different: a) clear out plugins, etc. and b) if that doesn’t work then upgrade to VPS.

I used this website – found it extremely useful.

Earlier, in 2015:

Switched back to shared hosting since memory issues were more chronic on VPS mySQL

WHAT ABOUT PAYMENT?
My question to Dreamhost:
I had paid a 3-year subscription for shared server ($284.14 till September 2019). If I decide to switch my entire account to VPS will the balance amount be automatically applied towards my VPS subscription?

Their response:

If you were to only purchase the web VPS service and not Mysql VPS as well. The shared plan you have would need to stay active to manage the shared database services. [In other words I’d be paying $7.89+ $15 per month (= $22.89 minimum) per month].

VPS and MySql VPS are independent services and purchased separately. Accordingly, if you were to purchase VPS and MySql VPS. The shared hosting plan could be closed since it won’t be used. The remaining unused payment would be applied on the VPS and MySql VPS services. [In other words I’d be paying $30 per month minimum].

SO FAR I’VE NOT HAD TO UPGRADE SINCE ALL PROBLEMS HAVE BEEN AT MY END (PLUGINS, ETC). Theoretically, Apache can serve up to 1M requests easily. ALL problems are therefore on my server side (basically, BAD PLUGINS).

BACKGROUND NOTES

FROM MY PREVIOUS CORREPSONDENCE WITH DREAMHOST, THIS IS WHAT THEY SAID

[My username] went over the allowed memory limit on the server. When an FTP user uses up excessive memory we have a script in place to stop those processes. Procwatch (Process Watcher) is a daemon that runs constantly on shared servers to monitor the usage of RAM/CPU and execution time so that no single user can use an  inappropriately high percentage of the shared resources and impact the overall health of the server or the server’s ability to serve all users’ pages. This is done to preserve quality of service and not adversely affect the stability of the shared environment.

[They then provided me with an] excerpt from the log kept by our Process Watcher, the daemon that is killing your troublesome php5.cgi processes:

[THEIR SUGGESTIONS]

Now, it’s important to get these errors fixed to keep your sites running with optimum speed, as well as keep our servers happy. Unfortunately it can often be a bit of a trial-and-error procedure, but I will provide as much information as I can to aide you in this process.

Firstly, it should be noted that any sites running under your user can contribute to the problems you are encountering. For example, if site A is  running processes that are taking up 85% of your individual limit, and site B tries to run a concurrent process that takes up 20% of the memory  cap, site B will have it’s process killed since it is the one that took you above your limits, even though site A’s script is the obvious memory  hog… For this reason, it’s important to keep all of your user’s sites optimized and running smoothly.

There are quite a few things that can contribute to high memory consumption on any given site, but I will mention the most common causes…

Plugins – Especially third-party plugins can often be poorly written and run up the memory consumption. I often recommend disabling all non-critical plugins and see if the problem gets better, then enabling them one-by-one until you are able to identify one that causes problems. Generally, if any plugins are not in use, remove them and reinstall them at a later time if they are needed. If there are any plugins that are currently in use, you will want to check for any recent updates available for the plugins and proceed with updating plugins to the latest stable version.”

Database related overhead, or an unnecessary amount of database queries – If you are trying to query your database for thousands of results at a time, this can also cause issues… Try to keep all database sizes and queries to a minimum by deleting irrelevant or old information from your database. You might also try optimizing tables in your databases by visiting your database through PHPMyAdmin. Simply use the “Check tables having overhead” link located directly underneath your tables and then “With Selected:”, choose “Optimize Table”. This might reduce overhead in your database, which is basically a lot of empty and redundant space that can slow your queries down.

It is also possible that a spike in traffic, or a heavy run by a site indexer (such as the GoogleBot), or even abusive hits by a given IP address, could cause this on a short term basis, so you might want to inspect your access logs for such activity (you can often manage this problem by implementing a robots.txt or by blocking abusive IP addresses with .htaccess).

The following articles on improving WordPress performance may help you with more optimization tasks to perform:

These links may help as well for further information/troubleshooting: https://help.dreamhost.com/hc/en-us/articles/216349808-Common-reasons-for-poor-website-performance

https://help.dreamhost.com/hc/en-us/articles/215036388-How-do-I-optimize-my-database

https://help.dreamhost.com/hc/en-us/articles/215836488-What-about-my-slow-site-can-I-check-

https://help.dreamhost.com/hc/en-us/sections/203242637-Optimization

You might as well want to try looking over the suggestions offered at our wiki pages on this matter:

http://wiki.dreamhost.com/Poor_performance

http://wiki.dreamhost.com/Optimize_database

http://wiki.dreamhost.com/WordPress_Optimization

http://wiki.dreamhost.com/Database_Maintenance

http://wiki.dreamhost.com/Slow_site_troubleshooting

http://wiki.dreamhost.com/Finding_Causes_of_Heavy_Usage

Implementing the above suggestions more often than not will fix the ProcWatch errors, but if you are still finding that your sites encounter the  same problems, you might want to consider moving to a VPS (http://wiki.dreamhost.com/DreamHost_PS) or dedicated server where you can reserve  sufficient RAM for your own processes to run!

FURTHER BACKGROUND

I agree with this commentator:

@Ipstenu-DH Yeap I can’t complain about the Shared Hosting, it worked really well. With some minor exceptions here and there… over all I can say DH’s Shared Hosting is quite good.
The site gets only 3000-5000 UV (unique visitors) per day on average. On Shared Hosting it worked well even with 30K per day. Actually I remember 2 years ago (2011) it even held up perfectly a record 75,000 in a single day + 70,000 the second day UV. It worked like a charm.

I barely get 1500+ visitors on all my sites, combined, so shared hosting should be perfectly fine.

KEY DIFFERENCE BETWEEN DREAMHOST AND OTHER VPSs

On Dreamhost, mysql databases are on a separate server (a typical shared MySQL allows up to 300 connections at one time – which should be fine in most cases).

Even with VPS (called “file VPS”) your mysql databases will still be on shared hosting (unless you install MySQL server on your VPS and move your databases to it). Shared MySQL databases are included for free with web (file) VPSes.

Question: Can I run my own mysql server on the same VPS server as my websites?

No. You can continue to use shared MySQL servers if you have VPS OR you can move your MySQL databases to a separate MySQL VPS.  [BOTH cost separately]

Dreamhost doesn’t support running a database server on your web VPS. You can install MySQL yourself using an admin user, but it won’t be available through the Panel.

VPS MYSQL

“Add a VPS MySQL Server” on dreamhost

DreamHost offers “MySQL VPS” that costs $15 a month (minimum). Source

http://wiki.dreamhost.com/DreamHost_PS_MySQL#Adding_MySQL_VPS

MySQL VPS – These databases are hosted on a MySQL service isolated from other customers. RAM is guaranteed for the customer. MySQL VPS is optimized with database query caching. About three quarters of VPS MySQL Dreamhost customers run at 300 MB.

[DreamHost PS MySQL uses Linux-VServer to offer an isolated database server.This is different from the standard shared MySQL server which an account uses by default, as the shared MySQL server shares other users databases and resources. With the PS MySQL, your databases and resources are protected from other users in the same was a web private server is.] VPS MySQL service starts at $15/month, which includes 300MB RAM and unlimited disk space. The most expensive plan is $200/month, which includes 4000MB RAM. When you activate VPS MySQL for the first time, you receive a one week trial. During the trial period, the amount of RAM is set at 2300MB RAM. When the free trial ends, the RAM adjusts to your site’s needs.  [Source]

My initial conclusion: Given the technical complexity and challenge of installing MySQL on one’s own VPS, it is not necessary to get a separate VPS. managed SQL VPS is good enough.

RUNNING MYSQL ON THE VPS SERVER ON DREAMHOST

If you get VPS you should create your own SQL server on the VPS, else you’ll continue to pay for shared SQL.

Source (for info below)

You can run your own MySQL server on DreamHost’s VPS.

  1. Create an admin user for your VPS that has sudo abilities, and log into your VPS with that through ssh.
  2. Tweak apt so you can install the mysql-server package. Part of installing packages through apt involves temporarily storing files in /tmp and then running them from there. Unfortunately, the /tmp directory is mounted on DreamHost’s VPS servers with the noexec option, which means that you can’t run files that are present in that directory. That basically prevents you from installing the mysql-server package until you tweak apt to temporarily stage files in /var/tmp instead. Do this by: Creating a file called apt.conf in the /etc/apt/directory, and edit it so the contents are the following:

APT:: ExtractTemplates::TempDir “/var/tmp”;

  1. Then, install the mysql-server package:
  2. sudo apt-get install mysql-server;

(When it asks to set a root password, make sure and set one.)

  1. Now, edit the file /etc/mysql/my.cnf and set the following options:

bind-address=psXXXXX.dreamhostps.com

(Replace psXXXXX with the name of your dreamhost VPS.)

  1. Restart your mysql service:

sudo service mysql restart

At this point, you should be able to log in to your new mysql server:

mysql -u root -p

and then perform what SQL functions you need to.

Install phpmyadmin using the tutorial here: http://wiki.phpmyadmin.net/pma/Quick_Install

First you’ll want to create a user (that isn’t your root user) to log into phpmyadmin:

mysql> CREATE USER ‘newusr’@’%’ IDENTIFIED BY ‘your_password’;

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘newusr’@’%’ WITH GRANT OPTION;

At this point, you can sync your old databases to your new mysql server using the built-in sync tool that’s in DreamHost’s installations of phpmyadmin.  Then, just edit the wp-config.php file in the folder of your WordPress installations, and change the line that says the following to your DreamHost VPS:

define(‘DB_HOST’, ‘psXXXXXX.dreamhostps.com’);

Print Friendly, PDF & Email

sabhlok

View more posts from this author
One thought on “Dreamhost’s MySQL VPS and VPS – some notes

Leave a Reply

Your email address will not be published. Required fields are marked *