WCF

History, politics, and culture articles and forum discussions.

technology

- By

How to reduce a massively-bloated WordPress database

I recently got in trouble with my hosting company for being a resource hog.  I get hit by a lot of spammers at WCF to begin with, but I didn’t realize that the size of my database made it such that it became taxing on the server.  I had noticed things were sluggish on this site for quite some time, but I chalked it up to my site’s use of some  couple dozen plugins or so running with a bbPress forum.  So how big was my database?  Almost 700 mb.  Yikes!

The culprit: wp_options

Before I spoke with my hosting company’s tech support, I assumed that there was little I could do if my site was frequently targeted by spammers or even legitimate spiders.  But after speaking with tech support, I learned that this seemed to be problematic only because the size of my database was excessively large.  When visitors would visit my site, it was more taxing on the server than it would have been had the same number of visitors visited a site with a leaner database.  Tech support mentioned that wp_options in my WordPress database was bloated, and said I might need to get in touch with a developer to remedy it.

How bloated was wp_options?  Bloated wasn’t really the right word.  Bloated is when you drink too much water so it’s sitting in your stomach.  My wp_options table was morbidly obese.  It was more like Jabba the Hut; so large and cumbersome that all it could do was pretty much sit in the same place and flap its flappers around and laugh with that deep laugh.  It was time to go Luke Skywalker on my own database.

The solution

I spent some time figuring out what had to be done.  I disabled and deleted a few plugins that I thought I could live without.  I then started researching how to reduce the size of my WordPress database even more, and I found a post discussing how to do this using a plugin called Advanced Database Cleaner.  The free version of the plugin really limits what you can do (you can’t really hunt down orphans all that well), but I followed the instructions, cleaned parts of my database here and there, and optimized the database.

Still no luck.  As you can see, after doing all of this, I noticed that my database was still abnormally large at 684.57 mb.

I then came across the Young Cow site which explained how to specifically focus on wp_options to clean out the mess.  There is one simple SQL query that needs to be run on the database.  Young Cow mentioned that it worked for his database, removing around 220,000 rows!  He then said that removing the rows didn’t change the table size, so a repair and optimize command had to be run afterwards.

Anyway, I ran the SQL query, and after what seemed like forever (I was worried it stalled out), I got this return:

Wowzer – are you kidding me?  That’s 3.9 million rows deleted!  I refreshed the view of my database stats.  Although the total options had shrunk by a massive amount, the database size hadn’t really changed at all:

The next step was to run the database optimizer included in the Advanced Database Cleaner plugin.  After doing so, I refreshed the database stats once more and got the news I was hoping for:

Bingo!  After optimization, it reduced the database size to less than 10% of its former size (from 685 mb down to 62 mb).  I was actually worried at first that doing this had to have broken my site somehow, so I clicked through my site a bit to make sure everything was in working order.  So far, everything looks to be good and operating as normal.  Better still, my site finally seems to be working faster!

This is great news for me and my site.  Western Civ Forum has been around for about 10 years now and has evoluved from a Phpbb site to an SMF site to a WordPress site, so I can understand why there would be some extra baggage hanging on along the way.  I’m just glad it got cleaned out and was so easy to do.  I’m imagining (“wishfully thinking of”) a kind of WCF renaissance of sorts now.  Faster page loads, more Googlebot visits, better indexing.  The world is my (site’s) oyster!

Update 6/19/2017

It’s been almost two weeks since I cleaned my database, and my site has been working properly.  However, I noticed something else strange going on with the database as the screenshot of the analysis shows.

The size of the database has grown by about 11 megabytes, and the number of “Total options” has ballooned from 634 to 52,357.  Something clearly is afoot – the same something that must have caused the nearly four million options in my database earlier this month.  I took a peek at wp_options in my database using PhpMyAdmin and noticed these kinds of rows being generated:

More transients, and they were being generated by Jetpack.  Why these are being generated is unclear to me.  I might ask around in the support board for Jetpack on WordPress.org, but at this point I just wanted to fix the problem.  Fortunately, there are a few different transient deletion plugins in the WordPress repository, so I don’t need to run SQL queries every time I want to clean my database.  The plugin I chose was called Transients Manager.

After deleting all expired transients, I got a timeout error because the process took over 30 seconds.  Refreshing my page, I saw that the number of transients left was down to about 3400.  I deleted expired transients again, and this time it successfully completed and left only 40 (unexpired) transients remaining.  I then ran the database optimizer, leaving me with this:

So my database is back where it should be – with only around 650 options and a database that is under 65 mb.

In the future, I may decide to make transient cleaning a regular activity.  I don’t know how to stop the increase of Jetpack transients, so I can only hope to contain them.  I noticed that another WordPress plugin, Transient Cleaner, gives the option for daily cleaning of transients, so I may give that a try.

- By

Is the Yahoo data breach cause for concern?

I received the following email in my Yahoo mail account this evening.  I don’t use my Yahoo mail account at all, but the icon flashes once in a while when using other Yahoo services, so I took a look at it.  Apparently, this breach hit about, oh, one billion users (did anyone know that Yahoo had a billion users?).  Here was the notice I got:

NOTICE OF DATA BREACH

Dear ***,
We are writing to inform you about a data security issue that may involve your Yahoo account information. We have taken steps to secure your account and are working closely with law enforcement.

What Happened?

Law enforcement provided Yahoo in November 2016 with data files that a third party claimed was Yahoo user data. We analyzed this data with the assistance of outside forensic experts and found that it appears to be Yahoo user data. Based on further analysis of this data by the forensic experts, we believe an unauthorized third party, in August 2013, stole data associated with a broader set of user accounts, including yours. We have not been able to identify the intrusion associated with this theft. We believe this incident is likely distinct from the incident we disclosed on September 22, 2016.

What Information Was Involved?

The stolen user account information may have included names, email addresses, telephone numbers, dates of birth, hashed passwords (using MD5) and, in some cases, encrypted or unencrypted security questions and answers. Not all of these data elements may have been present for your account. The investigation indicates that the stolen information did not include passwords in clear text, payment card data, or bank account information. Payment card data and bank account information are not stored in the system we believe was affected.

What We Are Doing

We are taking action to protect our users:

We are requiring potentially affected users to change their passwords.
We invalidated unencrypted security questions and answers so that they cannot be used to access an account.
We continuously enhance our safeguards and systems that detect and prevent unauthorized access to user accounts.
What You Can Do

We encourage you to follow these security recommendations:

Change your passwords and security questions and answers for any other accounts on which you used the same or similar information used for your Yahoo account.
Review all of your accounts for suspicious activity.
Be cautious of any unsolicited communications that ask for your personal information or refer you to a web page asking for personal information.
Avoid clicking on links or downloading attachments from suspicious emails.
Additionally, please consider using Yahoo Account Key, a simple authentication tool that eliminates the need to use a password on Yahoo altogether.
For More Information

For more information about this issue and our security resources, please visit the Yahoo Security Issues FAQs page available at https://yahoo.com/security-update.

Protecting your information is important to us and we work continuously to strengthen our defenses.

Sincerely,

Bob Lord
Chief Information Security Officer
Yahoo

After reading this long email, my reaction was to shrug it off.  This was a breach that occurred over three years ago, and some of the information taken (“…names, email addresses, telephone numbers, dates of birth…”) isn’t what I consider to be overly private.  What could be more alarming is the theft of “encrypted or unencrypted security questions and answers”.  Armed with this information, fraudsters could potentially gain access to sensitive accounts.

I typically try to avoid using the same security questions when I need to fill them out on secure sites, but this may be relatively difficult when dealing with services that require users to answer questions from a specific list (e.g. “what is your mother’s maiden name?”).

I think that a better approach for security questions may be to allow users to generate both the question and the answer.  Using this, I might generate my own question like “What is your favorite restaurant in the city of New York?”  I would then generate my own answer, and chances are it would be a question that I would not again repeat on a different site.  I think I have seen this kind of approach offered in the past, but it is by no means widespread.

Anyway, it seems like we see major breaches like what happened to Yahoo on a semi-regular basis these days, and there will probably not be an end to these going forward.

- By

Is Optical Disk Drive Products Antitrust Litigation email legit?

This past week I have received two emails at different addresses that I use that addressed a class action lawsuit over “optical disk drives”.  The first email was dated August 24, and the second was dated August 26.  After reading through both, it seems that they are identical:

Dear Class Member:

Our records indicate that you have purchased a new computer or laptop with an internal Optical Disk Drive (ODD) or a stand-alone ODD designed for internal or external use between April 1, 2003 and December 31, 2008. You are eligible for up to $10/drive from settlements reached in antitrust litigation currently pending in Federal court. This is an estimate of the settlement amount which could increase or decrease.

ODD refers to a DVD-RW, DVD-ROM, or COMBO drive sold by a number of manufacturers including Panasonic, Sony, Hitachi-LG Data Storage, Toshiba Samsung Storage Technology, Lite-On, and Pioneer.

Submitting a claim is as easy as clicking on the link below and confirming the number of drives purchased during the settlement period.

While I was mildly curious whether the case was legit when I received the first a few days ago, I was even more curious when I looked at the second one moments ago.  Is this a legitimate email?  A phishing scam?  I recall buying a laptop during the time in question, but I have no idea whether it had an “ODD”.  The email didn’t name the federal court in which the case is pending, and that scales-303388_640seems like an important thing to leave out, but perhaps not a smoking gun.  With a payout of only $10 per drive, the email does not sound like the typical scammer who would likely be dangling a much higher dollar payout to entice victims.  Thus far, it’s really unclear one way or the other whether this email is a scam or not (I should point out that Google placed the email in my spam box, though this isn’t conclusive, either).  I know that I wasn’t the only one confused about this email.

I checked the domain name registration for the domain included in the email, and it was registered to “Sipree Devops”, a company which does advertise legal settlement marketing like this.

Next, since I don’t like clicking on links from unknown email sources, I went to Google’s cached version of the site for the claimed litigation settlement.  That site has a case number, and the name of the federal court in which the case is proceeding, and I was able to use it to find a site which has more information on the class action.  Notice the news blurb on the bottom of that site which reads, “UPDATE July 25, 2016: A judge approved a $124.5 million settlement for indirect purchasers in 23 states and the District of Columbia.”

Yes, the case seems legit

So the bottom line is that yes, this appears to be a legitimate case.  Whether you received a legit email, or one from a scammer who is capitalizing on the real deal, is another issue.  As always, be careful with what you click on.

You can check out the case for yourself: In Re: Optical Disk Drive Antitrust Litigation, Case No. 3:10-md-02143 RS (U.S. District Court for the Northern District of California).

Update – June 16, 2017

This morning, I looked in the spam folder of my Gmail account and found a new message from sender “Optical Disk Drive Antitrust News”.  A prominent blue button was near the top of the message, along with these words:

Claim Registrations Now Open
Click Below to Make Your Claim

Well, I inadvertently clicked the button, and it led me to the following url: https://opticaldiskdriveantitrust.com/#two

I then went back to the site for the U.S. District Court for the Northern District of California to search for updates on In Re Optical Disk Drive Antitrust Litigation, but that site does not appear to allow searches for case filings (they outsource it to another site that requires registration to search).  So I then went to Leagle.com, which does not require registration.  Searching specifically for the url that was provided in the email I received – opticaldiskdriveantittrust.com – I came to a filing dated April 17, 2017.  Under Section 8(a), it states the following (my emphasis in bold):

No later than 30 days from the signing of this order for preliminary approval, the Settlement Administrators shall cause the full versions of the Settlement Agreements, the Preliminary Approval Order, and the claim form, in both an electronic fillable form and in a format that may be downloaded and/or printed, to be published on a public website, located at www.OpticalDiskDriveAntitrust.com;

So there you have it.  You can read more about the background to this case over at CNET.

WCF