Import Large SQL Files

I've been in the process of server migration over the past few days. It's been easy and I've experienced no down time thanks to Cloudflare and the fact that DNS propagation takes a while.

One of the hardest parts of migration wasn't the site files, it was the databases! I had exported all of the databases into 1 SQL file via phpMyAdmin, but it was too large for phpMyAdmin to import so I had to find a way to get everything back where it belonged! Thankfully, MySQL makes this simpler than I'd imagined.

To import a SQL file:

  1. Login to the server via SSH
  2. Login to mysql with: mysql -u USER -p'PASSWORD'where USER is your MySQL user and PASSWORD is your MySQL password.
  3. Type: source FILENAME.SQL where FILENAME.SQL is the path to the SQL file you wish to import
  4. Hit enter, and watch it import!

Give Your Blog a Workout

After watching the Tumblr v 4Chan battle, it made me wonder how much my server could handle if put under enough stress.

The question, though, was how much stress?

Using the same tool that was used during Tumblr v. 4Chan, I attempted to DDOS my own server.

To be clear, a denial of service attack attempts to overload the server with network requests. So many, in fact, that it can't keep up. Usually this causes web pages to time out and it can really freak out a database!

Enter LOIC - that's Low Orbit Ion Canon. It's basically a DDOS tool. If you decide to use it, be careful. Don't do anything stupid. If you have shared hosting, don't go trying to DDOS your server. You will not only take down your site, but others' too. And trust me, they don't care if you were just running  a stress test.

If you have a dedicated server, or you own a server, there's only harm to you. If you're ok with that....then ok.

To use LOIC is simple, you either enter the URL or the IP address, select the type of attack, enter the number of users to emulate and hit the big button. Done.

I'd strongly encourage you to only use manual mode. Otherwise, the program is remote controlled via an IRC channel

It's really easy, and I learned a lot!

I've learned that any more than 20 constant, simultaneous HTTP requests will stop my server from serving web pages.

I've also learned that I can undergo up to 1000 pings and be ok.

Since this blog is relatively small, and the traffic never spikes that high at once, I can live with all of that.

In the future, I'd obviously need to load balance things to ensure that even under a DDOS I could survive. My personal goal would be 100 simultaneous attempts to connect.

Have you ever stress tested your blog? or even thought about it?

If so, what were your results?

Optimizing the Database

Optimizing your database is an increasingly popular way to speed up your blog. The concept is really simple: over time, your

database becomes overburdened with requests and thus creates overhead to compensate. The more a table is accessed, the more likely it is to have overhead. It's the same way a hard drag fragments itself.

The optimize process is the equivalent to de-fragging your hard drive.

Here's how you can check for overhead, and get rid of it!
1. First you need to login to PHPMyAdmin (this is the simplest way to do this).

  1. After logging in, open up your blog's database.

[singlepic id=3 w=320 h=240 float=]

  1. Under the list of your tables, click on "Check tables with overhead"...if you look at my database, you can see that the Options table has some overhead. That's because I've been working a lot on different aspects of this blog that all utilize the Options table. For you, it may be the Posts table, or the user table if you have a lot of people signing up. It really depends on your setup as to which tables are going to have the most overhead.

  2. Click the dropdown, and select Optimize table.

*Note: This is usually a non-invasive task, but you should always have database backups ready to go. I have mine in 3 places!

[singlepic id=6 w=320 h=240  float=]

  1. Click go!

You just optimized your Wordpress database!

It's a really simple process that can greatly effect your blog's overall performance. I have this blog set to Optimize daily. Any increment is good, but the busier the blog, the more often you should optimize.

You learn more here.