Split One WordPress Blog Into Two

Internet No Comments

When I started my Do It Scared! blog (later moved to Do The Void), I did not have in mind any goal beyond sharing technical knowledge and random thoughts. As I’ve written more content, I’ve come to realize that my posts split into two very different camps, technical how-to instructions and non-technical realizations about life. One of my non-technical friends told me a while ago, “I enjoy reading your blog except for the stuff that I have no idea what you are talking about.” My blog had become schizophrenic.

spideywebheadThe cure I’ve implemented is to split the blog into two different blogs, one technical and the other non-technical. Readers can focus on one or the other, without getting distracted. Because I’m certain that this is not a rare problem for a blog creator to have, I’ve documented what I’ve done to separate my blog into two.

A Domain By Any Other Name

Choosing a second domain name is probably the toughest step because a good domain name is hard to come up with and when you do, the chances of it being available are very low. However, there is hope because not all domain names are taken. You might just luck out or come up with something so unique that no one else has thought about it before (and had been willing to register it).

I suggest first deciding which content you want to keep under the old domain name and which content you wish to move to the new domain name. The nature of the latter’s content will help you to come up with an appropriate new domain name. I’ve decided to keep the non-technical content at the old domain and move the technical content to the new domain. Because I know the new domain is technical, I could try to come up with a nerdy domain name.

If you don’t have a preference for where each content should go after splitting the content into two, you may wish to keep the larger half at the old domain and move the smaller half to the new domain. This will reduce the effort required later to create redirects from the old domain to the new. Unfortunately, I’ve decided to move the technical content, the much larger half of my blog, to a new domain.

Being dissatisfied with all the potential, available new domain names that I came up with for my technical content, I’ve decided to put the technical content under an existing domain name that I had registered previously; this domain name is composed of my full name.

Attack of the WordPress Clones

To start the move, I’ve duplicated the WordPress content from the old domain to the new domain. This involved copying the Nginx site configuration file, WordPress source files and the WordPress MySQL database, and then making minor modifications. The instructions below were performed on my unmanaged virtual private server.

Note: To keep things consistent, I’ve always taken care to name the Nginx www directory, Nginx domain server configuration file, MySQL database name and username the same as the domain name. The instructions below will reflect this naming convention. Please adjust to match your own custom names accordingly.

Once the new domain name is registered and the DNS records are updated, we can configure Nginx to serve the new domain by doing the following:

# Copy the Nginx site config file
sudo cp /etc/nginx/sites-available/olddomain /etc/nginx/sites-available/newdomain

# Edit the new Nginx config file
sudo nano /etc/nginx/sites-available/newdomain
    # Update file location and server name
    root /var/www/newdomain;
    server_name newdomain.com www.newdomain.com

# Enable the new Nginx domain
sudo ln -s /etc/nginx/sites-available/newdomain /etc/nginx/sites-enabled/newdomain

# Copy WordPress code from the old domain to new domain
sudo mkdir /var/www/newdomain
sudo cp -r /var/www/olddomain /var/www/newdomain

# Adjust permissions for the new domain directory
sudo chown -R www-data:www-data /var/www/newdomain
sudo chmod -R g+w /var/www/newdomain

# Update the new domain WordPress configuration
sudo nano /var/www/newdomain/wp-config.php
    # Update database, user, and password variables.
    define('DB_NAME', 'newdomain');
    define('DB_USER', 'newdomain');
    define('DB_PASSWORD', 'newdomain_password');

# Reload the Nginx server to make the changes effective
sudo service nginx reload

Create the new domain’s WordPress MySQL database and user:

# Open a MySQL interactive command shell
mysql -u root -p

# Create MySQL WordPress database for new domain blog
mysql> create database newdomain;

# Create MySQL user and password
mysql> create user newdomain@localhost;
mysql> set password for newdomain@localhost = PASSWORD('newdomain_password');

# Grant the MySQL user full privileges on the WordPress database
mysql> grant all privileges on newdomain.* to newdomain@localhost identified by 'newdomain_password';

# Make the privilege changes effective
mysql> flush privileges;

# Exit the MySQL interactive shell
mysql> quit

The old domain’s WordPress database may contain URL references (to the old domain) and directory references (to the old domain directory). Because I’ve named the old domain directory the same as the old domain name (and likewise for the new domain), modifying the old WordPress database for the new domain requires a simple text replacement. A global search and replacement of the old domain name with the new domain name fixes all the URL and directory references.

# Export the old domain's WordPress database
mysqldump -u[olddomain] -p[mypassword] olddomain > /tmp/olddomain_modified.sql

# Use your favorite editor to search and replace all olddomain matches with newdomain
# Below is an example using the vi editor to do a global string replace
sudo vi /tmp/olddomain_modified.sql
:%s/olddomain/newdomain/g

# Import the modified WordPress database into the new domain's WordPress database
mysql -u newdomain -p[mypassword] newdomain < /tmp/olddomain_modified.sql

At this point, you should be able to browse to the new domain address to see an identical copy of your old domain’s blog.

Note: If your WordPress uses embedded code (like Google Analytics) or WordPress plugins that contain references to the old domain name (like Google FeedBurner), you will want to update them manually to use the new domain name.

Posts Not Here!

To ensure that external links to my old domain’s blog will continue to work, I need to create redirects from the old domain to the new domain for the posts that have been moved. To do so, I recommend using the WordPress Redirection plugin. It is a simple plugin that supports regular expression matching and keeps a history of the number of redirects that occur (very useful to record which URLs are being redirected).

My old website’s permalink format looks like the below. Many variations of it are also allowed.

# Permalink format
/olddomain.com/post_id/post_title/

# Allowed variations include just the post_id
/olddomain.com/post_id
/olddomain.com/post_id/

# Also allowed is removal of the ending forward-slash
/olddomain.com/post_id/post_title

In addition, the post title doesn’t matter because the title could be incorrect and WordPress will pull up the correct post using the post identifier. Because we need to redirect any of these variations, regular expression matching is required.

An example regular expression matching a URL like “/olddomain.com/314/the-value-of-pie/” and its variations would be:

^/314(|/.*)$

Note: The “olddomain.com” is dropped when doing URL matching, so the actual match is against “/314/the-value-of-pie/”.

Here’s a very brief explanation of the regular expression above.

  • The first caret “^” character says to match starting with the beginning of the string. The last dollar sign “$” character says to match to the end of the string.
  • The “/314” means that the beginning of the string should match that sequence exactly.
  • The parenthesis and vertical bar combination “( | )” creates a logical OR construct.
  • The first alternative “(|” means that nothing follows ‘/314’. This alternative would match a URL like “/domain.com/314” exactly.
  • The second alternative “|/.*)$” means that there should be a forward-slash “/” followed by zero or more number of any characters “.*” until the end “$”. This would match URLs like “/domain.com/314/”, “/domain/314/blah”, “/domain/314/blah/”, “/domain/314/blah/blah/”, and of course, “/domain.com/314/the-value-of-pie/”.

Create a URL redirect using the Redirection plugin.

  1. Install and activate the WordPress Redirection plugin on the old domain.
  2. Go to the Redirection plugin’s Settings (also found under menu Tools->Redirection).
  3. Click on the “Redirects” section.
  4. In the “Add new redirection” form at the bottom, input the matching regular expression into the “Source URL”, check the “Regular expression” checkbox, and input the new domain target in the “Target URL”.
  5. Click the “Add Redirection” button when done.
  6. Repeat the steps for each post that will be moved to the new domain.

redirection_plugin

The Redirection plugin will create a 301 permanent redirect. When encountering 301 redirects, browsers will cache the resulting redirected URLs. Search engines may also update their records accordingly. If you are just experimenting, I recommend editing the redirect (under the Redirection Settings, click on the redirect item, select Edit, and click on the empty square under “Source URL” to expand the set of available options) and choosing “307 – Temporary Redirect” in the “HTTP Code” field.

Besides the permalink format, WordPress accepts this default query format, “/olddomain.com/?p=post_id”. Unfortunately, the Redirection plugin does not support this format for redirects. I looked at some other redirect plugins but they require that the original posts be kept on the old domain’s WordPress because they use an extended post property to perform the redirect. Because I wish to delete the moved posts, I cannot use any of the existing WordPress redirect plugins to redirect the query formatted URLs. However, because external websites should only use the permalink format when referencing my old blog, I don’t actually need to redirect the query format.

Nginx Rewrites

As an alternative to the WordPress Redirection plugin, Nginx rewrite directives can be used. The advantage is speed because Nginx will redirect before WordPress is even involved. The disadvantage is that there isn’t an easy way to track the redirects that occur.

To perform a redirect using Nginx, edit the Nginx configuration file “/etc/nginx/sites-available/olddomain” and insert the following rewrite statement immediately beneath the “server_name” directive:

# 301 permanent redirect
rewrite ^/314(|/.*)$ http://newdomain.com/314/the-value-of-pie/ permanent;

# Or 307 temporary redirect
rewrite ^/314(|/.*)$ http://newdomain.com/314/the-value-of-pie/ redirect;

You will need to create a rewrite directive for each post to be moved. The rewrite directives will take effect when the Nginx server is reloaded.

Unlike the WordPress redirect plugins, Nginx supports redirecting post identifier query formatted URLs. In the old domain’s Nginx configuration file, immediately beneath the “server_name” directive, insert the if-return statement below:

# 301 permanent redirect
if ($arg_p = 314) {
    return 301 http://newdomain.com/314/the-value-of-pie/;
}

# Or 307 temporary redirect
if ($arg_p = 314) {
    return 307 http://newdomain.com/314/the-value-of-pie/;
}

Note: You will need an if-return statement for each post which you wish to redirect. If there are many posts to redirect, the Nginx configuration file may become bloated with if-return statements. It may be possible to use the Nginx map function to replace the if-return statements; unfortunately, I haven’t figured out how to use the map function yet.

Batch Import For Redirection

Because I have over a hundred technical URLs to redirect and do not wish to manually input them, I’ve looked into ways to batch import them into the Redirection plugin. The most direct method is to insert the redirects directly into the MySQL database.

Here is an example MySQL insert statement:

# Log into MySQL interface
mysql -u root -p

# Use the old domain's WordPress database
mysql> use olddomain;

# Insert the redirect into Redirection's items table
mysql> INSERT INTO wp_redirection_items VALUES (NULL,'^/314(|/.*)$',1,0,0,
'0000-00-0000:00:00',1,'enabled','url',301,'http://newdomain.com/314/the-value-of-pie/','url',NULL);

# Exit MySQL interface
mysql> quit

Note: Before executing the MySQL insert statement above, view the Redirection plugin’s Settings page at least once to trigger the Redirection plugin to create its MySQL tables, including “wp_redirection_items”.

Because I am lazy, I wrote a PHP script to generate a file containing the MySQL insert statements for all my moved posts, and then executed that file against the old domain’s WordPress database.

The Redirection plugin’s user interface exposes support for importing a comma-separated file containing the redirects. Unfortunately, there is no documentation on the import format and I could not get it to work based upon the few related forum posts which I found. Even had I gotten import to work, it looks like a MySQL update statement is required to enable the regular expression type check on all the imported redirects. Since MySQL would be required in any case, I am satisfied with the MySQL insert solution above.

Very Slow Cleanup

Once the redirects are working, you can start moving the moved posts from the old domain’s blog to the trash. Also, move the posts in the new domain’s blog that remain in the old domain to the trash. I recommend waiting a few weeks to make sure everything is working fine before emptying the trash. Once you have emptied the trash, don’t forget to delete any images referenced by the deleted posts.

In a few months, I plan to look at the redirect statistics to figure out which URLs are being redirected. I plan to delete the redirects that are not being used. Once I’ve reduced the number of redirects as much as I can, I intend to convert them into permanent Nginx rewrite statements and disable the Redirection plugin.

Update: The latest version of the Redirection plugin has a bulk action “Reset Hits” which resets the redirect counts, so we wouldn’t need to use the MySQL commands below.

Because I want to do several rounds of checking which URLs are being redirected, I needed a way to reset the redirection counts to zero. Unfortunately, there is no user interface option to reset the counts, so I had to use these MySQL statements:

# Log into MySQL interface
mysql -u root -p

# Use the old domain's WordPress database
mysql> use olddomain;

# Reset the redirection counts to zero
mysql> update wp_redirection_items set last_count=0;

# Exit MySQL interface
mysql> quit

If you decide to split your blog, I hope the instructions above will help.

No Comments

DigitalOcean After A Year: Still Good

Linux No Comments

Two weekends ago, on a Friday, my droplet was automatically upgraded to DigitalOcean‘s new cloud. I had received an email about the upgrade but had ignored it, believing that the upgrade would go smoothly. I was on a trip that Friday and the weekend, so did not check my website until Monday morning. Unfortunately, my website was unreachable and had been so since Friday.

Droplet Up, Website Down

050DragonSlayerI logged into DigitalOcean and the web interface said that my droplet was up and working fine. However, I could not ping it or secure shell to it. DigitalOcean’s Console Access web interface did worked and showed the necessary processes running on my droplet. The droplet was working fine but network access to it appeared to be broken.

I contacted support and was eventually routed to second level support (an engineer) who told me that I had to manually power off (run “sudo poweroff” on the command line) and then power on the droplet (using DigitalOcean’s web interface). This fixed the network connection issue and my website was back online. Note that doing a “shutdown -r” command or a “Power Cycle” (using the web interface) did not fix the connectivity problem.

DigitalOcean support was very responsive. Of the three support cases I’ve opened in the year that I’ve been with them, first line support had responded promptly. Of course, support did make use of canned responses (which I didn’t object to because it made sense to filter out beginners). Though I was vexed by the network connectivity issue (my website was offline for 3 days) and my irritation showed in my communications, the support staff always remained very polite and gracious.

Doing such a system-wide upgrade without checking network connectivity to affected droplets concerns me. Checking that upgraded droplets are up and reachable would have been my first validation test after the upgrade, instead of putting the burden on the customer to make sure everything is okay. Then again, this expectation might be acceptable for an unmanaged VPS; though I think it is a grey area because the upgrade was initiated by DigitalOcean. For full disclosure, DigitalOcean did provide a manual upgrade process; which in hindsight, I should have taken advantage of. Lesson learned.

Slow and Slowerer

When I configured my droplet a year ago, I was very impressed by the performance. My website loaded pages within 1 second, as opposed to the 2-4 seconds on my previous shared web hosting service. Recently, I would have been very glad to get back my 2-4 seconds page load time.

Since the past few months, I’ve noticed my website getting slower and slower. Even a simple PHP application I had running (also on the droplet) took longer and longer to process. Like a frog slowly being boiled, I got used to a 4-6 seconds page load time as being “normal”.

Worse, after my droplet was upgraded, the page load time dropped to 8-9 seconds. I installed the “WP Super Cache” WordPress plugin in a quick fix attempt to increase performance and it worked. Once WP Super Cache was activated, page load times moved back to 4-6 seconds.

You know what they say about quick fixes. A week later, the page load times increased to 8-15 seconds. 15 seconds! I disabled WP Super Cache and page load times dropped to 4-6 seconds. I didn’t understand why but at least, the crisis was averted.

Bottleneck? What Bottleneck?

The performance of any VPS (or shared web hosting) is determined by the allocated CPU power, amount of memory, disk access speed, software stack (programs running), and network traffic. The first three can be collectively known as the hardware or virtual hardware. In my website’s case, the software stack is composed of the Ubuntu operating system, LEMP infrastructure, WordPress and its plugins. And though I would love to say that the slowdown was due to increased network traffic to my website, it wasn’t.

When optimizing for performance, it pays to determine where the bottleneck is. For example, you could waste time optimizing the LEMP (by adding Varnish) or WordPress (by adding the WP Super Cache plugin) when the bottleneck is that you are out of memory (Varnish won’t help) or free disk space (WP Super Cache could actually make this worse with its caching mechanism). Having said that, there are ways to optimize LEMP (and WordPress to a lesser extent) to reduce memory usage; but then, it is usually at the cost of performance.

I contacted DigitalOcean support for help. I got a mostly canned reply back. They stated that the fault wasn’t because of network connectivity, hardware, or over-subscription (where there are too many droplets running on the same physical hardware). They had tested loading a couple of static images from my website, which only took 100-200ms each and proved that the problem was not on their end. The canned reply suggested using sysstat to figure out the problem with the droplet.

Sysstat is a collection of utilities to monitor performance under Linux. Here’s how to install and use sysstat:

# Install sysstat
sudo apt-get install sysstat

# Enable sysstat system metrics collection
sudo vi /etc/default/sysstat
  # Change ENABLE="false" to "true"

# Start sysstat
/etc/init.d/sysstat start

# Check CPU usage
sar -u

# Check memory usage
sar -r

Because we have just started the sysstat process, the check CPU and memory usage will only return the current CPU and memory usage. Sysstat will collect system metrics every 10 minutes; so in the future, the “sar” commands above would return the CPU and memory usage collected every 10 minutes in the past. Sysstat has a lot more functionality which I have yet to explore.

My favorite performance monitoring tool is the “top” command. It displays a real-time summary of the CPU, memory, and swap usage with a list of the processes consuming the most CPU. (Note that the Ubuntu image from DigitalOcean has swap disabled by default.) The top command allows me to see what is happening on the system as I load a page.

linux_top

Right off the bat, I noticed that my CPU usage was around 90% constantly, which was a big red flag. After a day of recording, sysstat returned the same average 90% CPU usage. This might explain why the WP Super Cache plugin, which required more CPU and disk access to do the page caching, made the website performance worse. I didn’t recall seeing the CPU that high when I first configured the droplet a year (it would have concerned me very much then).

Memory also looked alarming with a 98% usage (493424 used / 501808 total); however, it was a false alarm. Evidently, Linux operating systems like Ubuntu will allocate all the free memory for disk caching. Then, when applications need more memory, they get it from the disk cache. So, the important data to look for is the cache size. Here, the cache size was 28% of total memory (144132 cached Mem / 501808 total), which means only about 2/3 of memory was actually used by applications.

Note: The Linux command to display free memory, “free -m”, supports the same conclusion. Look for the reported “cached” number.

What Is Eating My Hard Drive?

Running the Linux command to report file system disk space usage, “df -h”, indicated that 93% of my 20GB quota was used. I remembered that my droplet used much less than 50% of the 20GB a year ago.

Find the space hogs:

cd /
sudo du -h --max-depth=1
16G    ./var

cd var
sudo du -h --max-depth=1
6.0G   ./www
7.4G   ./lib
2.0G   ./log

Note: If your system’s estimate file usage “du” command does not support the “max-depth” flag, then you will need to run this command on each directory one by one like so:

sudo du -sh /var/www
sudo du -sh /var/lib
sudo du -sh /var/log

The “/var/www” directory contained my website content so that was a keeper. The “/var/lib” directory contained important system and application files, so we could not just delete anything in it without a lot of caution. The “/var/lib” directory’s large size was caused primarily by the MySQL database file, “/var/lib/mysql/ibdata1”, which we will examine in detail later. I was certain that I could safely delete the archived log files from the “/var/log” directory though.

# Manually rotate log files (basically create new active log files)
sudo /etc/cron.daily/logrotate

# Delete all gzipped archive files
sudo find /var/log -type f -name "*.gz" -delete

# Delete all next-to-be-archived files
sudo find /var/log -type f -name "*.1" -delete

# Double-check size again
sudo du -sh /var/log
563M   /var/log

Strangely, I found the mail directory, “/var/mail”, taking up 150MB. There were a lot of non-delivery notification emails sent to the website address. (I don’t know why but plan to investigate it at a later time.) I was sure that it is also safe to delete those emails.

# Check usage
sudo du -sh /var/mail
156M   /var/mail

# Truncate all mail files to zero size
sudo find /var/mail -type f -exec truncate {} --size 0 \;

# Double-check usage
sudo du -sh /var/mail
4.0K   /var/mail

Note: I did read a recommendation to enable swap on Ubuntu to guard against out of memory errors (because swap allows disk space to be used as additional memory at the expense of performance); however, because I have 1/3 free memory and a performance problem, I don’t think enabling swap is the appropriate solution for my case.

Die, WordPress Plugin, You Die!

I strongly believed that the bottleneck was the CPU; the top five most CPU-intensive processes were the “php5-fpm” processes (responsible for executing PHP scripts). So, optimizing LEMP by adding Varnish (an additional HTTP accelerator process) would probably not help, and might even harm the performance further. What could be causing so much CPU usage?

According to Google Analytics, the traffic to my website had not changed significantly this past year. Even if it had, the now roughly 200 visitors per day should not cause such a high CPU usage. I had not changed my website in any way (for example, by adding new plugins). The only changes had been software updates to Ubuntu, WordPress and its plugins.

For LEMP infrastructure issues, the recommended step is to check the log files for errors.

# Linux system log
sudo tail /var/log/dmesg

# Nginx log
sudo tail /var/log/nginx/error.log

# PHP log
sudo tail /var/log/php5-fpm.log

# MySQL log
sudo tail /var/log/mysql/error.log

Looking at the Nginx log was eye-opening because I could see hacking attempts against my website using invalid URLs. However, that could not be the cause of the high CPU usage. There were no other errors or clues in the log files.

For WordPress performance issues, the universally-recommended first step is to disable the plugins and see if that fixes the issue. Rather than disabling all the plugins and re-enabling them one by one, my gut told me that the culprit plugin might be the “WordPress SEO”. When I get daily-in-row or even twice-a-day updates to a piece of software, I know that the software is very buggy. WordPress SEO was guilty of that behavior. Disabling the WordPress SEO plugin resulted in an immediate drop in CPU usage to the 30-50% range. Page load times dropped to 2-3 seconds.

Unfortunately, when I checked a few days later, the CPU was back up to 90% and page load times had increased back to 8-10 seconds. The WordPress SEO plugin was a contributor, but it was not the primary cause of my droplet’s performance issue.

MySQL, What Big Eyes You Have

In addition, the “/var/lib” directory had grown another 1.5GB in size and at a total 9GB, had consumed almost half of my 20GB allocation. Digging further, I found that it was the “/var/lib/mysql/ibdata1” file that had grown to over 6GB. The “ibdata1” file was where MySQL (specifically the InnoDB storage engine) stored the database data and while it can grow, unfortunately it can never decrease in size.

A MySQL query on the database size was necessary to investigate further. Log into MySQL as the root user and run this query to show the sizes of the existing databases:

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema;

I found that my MediaWiki database was over 6GB in size. I had a MediaWiki for personal use. Access to it was restricted by a password-protected directory. I hadn’t used it in a long time (over half a year) so hadn’t paid any attention to it. When I logged into it, I found the main page was blank with a link to an unknown website. A check of the history indicated that multiple unknown revisions had been made to it since February of this year. My MediaWiki had been hacked.

Evidently, someone had gotten past the password-protection and was using the MediaWiki to store 6GB of data. Worse, that someone may have hacked MediaWiki to run their own PHP code (very unlikely but not impossible as I had a very old version of MediaWiki running). This explained the high CPU usage and the low free disk space.

I savaged my personal info from the MediaWiki (using the history to view old page revisions). I then deleted the MediaWiki database and directory containing the MediaWiki PHP code. The CPU usage immediately went down to a few percentages. Page load time dropped to around one second. Hurrah! (I also changed all my passwords just in case.)

MySQL Database Surgery

To reclaim the 6GB in space used by MySQL’s “ibdata1” file required major surgery. I needed to delete the “ibdata1” file which required deleting and re-creating the WordPress database (and my other personal databases).

Before starting, I recommend configuring MySQL to store each InnoDB table in its own separate file, instead of in the “ibdata1” file, to allow more options to manage drive space usage. Doing this will support the MySQL “Optimize Table” command, which can reduce the table’s file size.

sudo nano /etc/mysql/my.cnf
  # Add "innodb_file_per_table" to the [mysqld] section
  [mysqld]
  innodb_file_per_table

The change above won’t take effect until we restart MySQL.

We need to do some steps before and after deleting the “ibdata1” file:

# Dump a backup of "wordpress" database (and any other personal database)
mysqldump -u[username] -p[password] wordpress > /tmp/wordpress.sql

# Delete "wordpress" database (and any other database except "mysql" and "performance_schema")
mysql -u root -p
mysql> drop database wordpress;
mysql> quit

# Stop MySQL server
sudo service mysql stop

# Log into root user (necessary to access "/var/lib/mysql" directory)
su

# Delete subdirectories and files ("ibdata1") under "/var/lib/mysql" except for "/var/lib/mysql/mysql"
cd /var/lib/mysql
ls -I "mysql" | xargs rm -r -f

# Exit root user
exit

# Start MySQL server
sudo service mysql start

# Create "wordpress" database (and any other database)
mysql -u root -p
mysql> create database wordpress;
mysql> quit

# Restore "wordpress" database (and any other database)
mysql -u [username] -p[password] wordpress < /tmp/wordpress.sql

Viewing the “/var/lib/mysql” directory showed a much smaller “ibdata1” file (about 18M). Strangely, my WordPress database was configured to use MyISAM (an alternative storage engine to InnoDB) by default, so it didn’t use the “ibdata1” file. The “/var/lib/mysql/wordpress” directory contained MyISAM .myd storage files. However, my other personal database did use InnoDB and its directory, “/var/lib/mysql/personal_database”, did contain individual InnoDB .ibd storage files (per table).

WordPress On A Diet

While I was poking around WordPress, I decided to optimize the MySQL database by deleting unnecessary data such as previous versions of posts. Rather than manually truncating database tables myself (a very dangerous, though oddly satisfying pastime), I decided to use the “Optimize Database after Deleting Revisions” plugin, which did exactly what its name said it did.

Before running the “Optimize Database after Deleting Revisions” plugin, backup your WordPress MySQL database. Then do the following to manually optimize your database:
wordpress_optimize_db

  1. Go to “Settings/Optimize Database” in the WordPress administration.
  2. Configured the options. I checked all the “Delete…” options except for “Delete pingbacks and trackbacks”. I did not enable the Scheduler because I only wish to run this plugin manually when I decide to.
  3. Click the “Save Settings” button.
  4. Click the “Go To Optimizer” button.
  5. Click the “Start Optimization” button.

Thoughts on Hardware Upgrade

Had I not fixed the high CPU usage issue (and it had been a valid issue), the next step would have been to look at options to upgrade the hardware. This would mean upgrading to DigitalOcean’s higher-priced plans or even another VPS provider (I have heard that Linode has better performance overall).

Because my bottleneck was the CPU, I would have had to upgrade to DigitalOcean’s $20/month plan which includes a 2 core processor. Upgrading to the $10/month plan, which only included a 1 core processor (the DigitalOcean website didn’t say whether it is a faster processor than the 1 core processor in the $5/month plan), would not have fixed my CPU issue. Had my bottleneck been a memory limitation, I would chose the $10/month plan, which would have doubled the memory size (1GB versus 512MB).

Thinking outside the box, a cheaper option than the $20/month plan would be to get a second $5/month droplet to run a dedicated MySQL server (hosting the WordPress database). The original droplet would run only the WordPress application and talk to the second droplet’s database. This $10/month option with two $5/month droplets would have two 1 core processors, which might be better than a single 2 core processor! Alas, the MySQL process used only 10-15% of the CPU so removing it from the original droplet would not have made much of a difference.

Hopefully documenting my trials and tribulations above will help you to have an easier time with the performance of your unmanaged VPS.

Some info above taken from:

No Comments