BlogmyQuery – BMQ

Imagine that your biggest client calls because they are having trouble retrieving their email. Or they want to know what their best-selling item is right now. Or their most popular blog post. Perhaps their website has suddenly gone down. You can hardly reply, “No problem, I’ll get back to you in 24 to 48 hours.â€�

And yet DNS gets away with it! If you need to move a website or change the way a domain’s email is handled, you’ll be faced with a vague 24 to 48-hour delay. This is quite an anomaly in a world of ultra-convenience and super-fast everything. This article explains what DNS is, how it works, where that pesky delay comes from, and a couple of ways to work around it.

What Is DNS?

DNS is the “domain name system.â€� It translates human-friendly website addresses like www.cnn.com into computer-friendly IP addresses like 157.166.224.25. Try visiting http://157.166.224.25 if you’d like to verify this.

Every computer, Web server and networking device on the Internet has one of these numerical IP addresses. In some cases, through a process called “network address translation,� a whole house, office or building shares the same IP address. But the addresses are otherwise unique, and they allow computers to easily route information around the Internet.

DNS is a distributed service. No single computer out there translates domain names to addresses. Instead, the task is shared by millions of name servers (also spelt as one word, “nameserver�), which constantly refer to and update each other.

Your Local Name Server

Every computer connected to the Internet has a name server. When you attempt to visit a website like www.smashingmagazine.com, your computer asks its local name server to go off and find the corresponding IP address, 80.72.139.101 in this case. Your computer’s name server can’t make this translation by itself; it has to keep asking other name servers until something somewhere comes back with a definitive answer.

Your local name server is like the little address book that you kept near the telephone before mobiles were invented. If you hired A1 Triple Glazing to retrofit your windows, you might have copied their phone number into your address book. The next time you had to ring them, the number would be right there, immediately available. Or under the sofa.

Some Name Servers Are Special

The name servers just “know.â€� Every domain name like smashingmagazine.com has at least one name server that authoritatively and definitively knows the correct IP address. The authoritative name servers for smashingmagazine.com are flashily called a.regfish-ns.net, b.regfish-ns.net and c.regfish-ns.net.

This is like saying that A1 Triple Glazing’s phone number can definitely be found in the Northampton Yellow Pages. That particular phone book is the authoritative source of information on the whereabouts of A1 Triple Glazing.

Where the Delay Comes From

Say A1 Triple Glazing decides to change its phone number. It could take up to 12 months before the 2012 edition of the Northampton Yellow Pages comes out with the updated phone number. And it could take a further 12 to 36 months before you next go up to Northampton, check the Yellow Pages, and copy the new phone number into your personal address book. In the intervening 24 to 48 months, your address book would be out of date. And if you ever rang A1 Triple Glazing, you’d be disconnected instantly… or end up speaking to a hairdresser. Fortunately, new windows generally have a 10-year guarantee. But websites need to be a bit more responsive than that.

Creating A New Website

DNS becomes important whenever you need to create a new website or move an existing one. New websites are the simpler case, so we’ll discuss them first. With any new website, you need to do several things:

1. Buy the Domain Name

123-reg is a domain name registrar that does hosting on the side.

A company from which you buy and register a domain name is called a registrar. Registrars get a special license from ICANN that allows them to sell domain names. The license costs $2500 (US) to apply, plus $4000 per year. Some particularly large registrars are GoDaddy in the US and 123-reg in the UK.

After registering a new domain name, there may be a delay of a few minutes to a few hours before you can log into the registrar’s website to change the domain’s name servers (step 3 below) or point to an IP address (step 4 below). This delay is a result of the registrar processing your payment, adding you to the Whois database and updating its records. The delay applies only to brand new domains and so is not part of the DNS delay.

2. Find a Host for the Website

Some companies provide only hosting.

The hosting company puts your website on a big powerful server somewhere, provides you with an IP address and charges you monthly. Thousands of big and small companies offer hosting or resell another company’s. Most registrars also offer hosting, and if you buy the domain name and hosting space from the same company, you won’t need to worry at all about DNS.

3. Specify Name Servers for Your Domain

Almost all registrars allow you to change name servers. This screenshot is from Fasthosts.

This step is akin to specifying which Yellow Pages your domain name should appear in. Usually you can skip this step and just use the default name servers provide by the registrar.

You might want to change them if, for example, you registered the domain names (step 1 above) with several companies but wanted to manage the DNS (step 4 below) from one place. Or perhaps you used Really Cheap Registrar Plc to register the domain names, but you want to use Really Flexible DNS Plc to manage the DNS. Or perhaps your host (step 2) has a nice DNS interface that you’d like to use.

To change the name servers, log into your registrar (from step 1 above), navigate to the domain name in question, and look for a “Change name servers� option, as in the screenshot above. Really Flexible DNS Plc will tell you what to change them to.

4. Point the Domain Name at the IP Address

Using Fasthost’s advanced DNS to add the IP address of a website.

Now you need to log into whichever company is providing the name servers (either the registrar, the host or another) and point www.yournewdomainname.com to your new IP address. Usually there is an option for “DNS Settingsâ€� or “Advanced DNS,â€� often with a big warning asking whether you’re sure you know what you’re doing. Find the button to add an new “Aâ€� record (for “Addressâ€�). Enter www as the host name (i.e. the prefix for the domain name) and the IP address given by your host in step 2.

You can use the same process to create other address records, such as webmail.yournewdomainname.com. Sometimes you can enter * as the host name, and then everything.yournewdomainname.com will point to the IP address. And if you enter @ as the host name, then it will point yournewdomainname.com without any host name.

5. Wait For It to Happen

This is the cause of part of the DNS delay. Many companies will process your DNS request immediately. Others process requests only once or twice a day; so, if your company processes changes only at 4:00 am, and you request the change at 4:02 am, then you’ll need to wait almost 24 hours.

123-reg says this:

Not only do we give you the power to change your DNS settings to whatever you like, but we make those changes instantly! Unfortunately, we can’t make the Internet as efficient as we are?—?other web services may take longer to update. Your changes will go global just as soon as they catch up.

The next section discusses how DNS works in detail, and the final section covers the main part of the DNS delay.

How DNS Works

When you visit a website in the browser or ping or FTP or telnet or do any networking operation, your computer needs to convert the (fully qualified) domain name into an IP address. This section shows how that happens, with commands so that you can try it yourself.

For the commands, you’ll need to open up the terminal on Mac or Linux or the command prompt on Windows. To do this on a Mac, go to Applications ? Utilities ? Terminal. In Ubuntu Linux, go to Applications ? Accessories ? Terminal. On Windows, go to Start ? Programs ? Accessories ? Command Prompt.

Note that in DNS, both smashingmagazine.com and www.smashingmagazine.com can be called “domain names.â€� But the latter, www.smashingmagazine.com, could also be called a “host nameâ€� or a “fully qualified domain nameâ€� or just a website address. This article mostly uses the term “domain name.â€�

1. Ask Your Local Name Server

Let’s say you want to visit www.smashingmagazine.com. Within its many networking settings, your computer has stored the IP address of a local domain name server. Its first step is to ask this name server for the answer.

On Mac and Linux, you can run the following command to find out what your name server is:

cat /etc/resolv.conf

On Windows, the command is:

ipconfig /all

What the command looks like in Linux.

In this case, my computer sends a request to 192.168.1.1, something along the lines of, “Oi, 192.168.1.1! What’s the IP address for www.smashingmagazine.com?â€�

2. Your Local Name Server Doesn’t Know

Let’s say that the local domain name server, 192.168.1.1, is brand spanking new. It has never been asked anything before, let alone for the IP address of www.smashingmagazine.com. It knows very little. In fact, the only things it knows are the IP addresses of the root name servers. You can find out everything it knows using the NsLookup command.

On Mac, Linux and Windows, run the command shown below. The -type=ns tells NsLookup to only return information on name servers. And the dot at the end tells it to look up root name servers.

nslookup -type=ns .

This will return the names and IP addresses of a handful of root name servers. If you’d like to see what’s holding the Internet together, Wikipedia has a picture of one of these very important computers.

A list of the Internet’s very important root name servers.

3. So, It Asks a Top-level Domain Name Server…

Your local name server extracts the last part of the requested domain name, which is com in this case. This is called the top-level domain or TLD. Others are net, gov, uk, fr, ie and de.

Your local domain name server picks one of the root name servers listed above and asks it something like, “Excuse me, 193.0.14.129. If you don’t mind, where would I find information about .com domains?â€�

You can see the sort of answer it would receive by running this command:

nslookup -type=ns com 193.0.14.129

This is a list of TLD name servers for com domains.

4. … And Gets Redirected to a Lesser Domain Name Server

It’s nearly there. Your local name server now asks one of these TLD name servers something like, “Hi, 192.52.178.30. Do you know where I should go for stuff on smashingmagazine.com?â€�

You can see the answer to this question by running NsLookup again:

nslookup -type=ns smashingmagazine.com 192.52.178.30

This returns a list of name servers for the domain smashingmagazine.com. The word authoritative means that these name servers are the definitive place to go for DNS information on smashingmagazine.com.

Running the NsLookup command.

5. Get the IP Address

So, now your local name server goes to one of these name servers. It has arrived at the first part of the requested domain name, the www, so it no longer needs the name servers; it’s ready for the actual data. Now it can ask one of those name servers, “Hola, 79.140.49.11. Can you tell me the IP address of www.smashingmagazine.com? Cheers!â€�

Run the NsLookup command again, using the IP address of one of the domain name servers from above, but without the type=ns this time:

nslookup www.smashingmagazine.com 79.140.49.11

Bingo! Now your local name server knows that www.smashingmagazine.com translates into 80.72.139.101.

6. Remember It For Next Time

Your local domain name does not want to have to go through all that rigmarole again any time soon. So, it caches (i.e. stores) everything it has learned, including the IP addresses for TLD servers and the IP address of www.smashingmagazine.com.

So, the next time you ask for a com domain, such as www.google.com, it needs to repeat only steps four and five above. And the next time you (or anyone else using your local name server) asks for www.smashingmagazine.com, it doesn’t need to do anything; it just replies with the IP address that it has already remembered.

But it won’t remember that translation forever. Eventually, it will forget and have to repeat some or all of the steps above. You can use the dig command to find out how long it will remember.

On Mac and Linux, run this:

dig www.smashingmagzine.com

Windows users will need to use an online version of this tool, because Windows does not come with the dig command.

Digging Smashing Magazine.

In the Answer Section, is a line starting with www.smashingmagazine.com (the thing you asked about) and ending with the translated IP address. The number in the middle is the number of seconds before your local name server forgets about this domain and has to repeat steps four and five above. In this example, it is 238 seconds:

;; ANSWER SECTION:
www.smashingmagazine.com.  238  IN  A  80.72.139.101

This caching on your local name server is responsible for part of the DNS delay. In this case, even if Smashing Magazine changed its IP address right now, your computer wouldn’t know about it for at least 238 seconds, when the local name server would have to recheck its sources.

If you use the online tool, then you are not checking your personal local name server, but rather you’re checking that website’s local name server. You can run a slimmer version of this command:

dig +nocmd www.smashingmagazine.com +noall +answer

Also note that in all of the commands above, you could have provided the name of the name server rather than the IP address. NsLookup would have translated it for you.

7. Send the Answer Back to Your Computer

Finally, your local domain name server sends the answer back to you at 80.72.139.101. Your computer and/or browser might also cache this translation, so that the next time you ask for www.smashingmagazine.com, it doesn’t even need to ask the local name server.

Now your computer will embark on another amazing process to communicate with the computer at the address 80.72.139.101 and ask it for a Web page. Your computer will essentially send a request down its network cable (or over its wireless connection), and ask your broadband router something like, “Can you please ask 80.72.139.101 to send me the home page for www.smashingmagazine.com?â€�

Your broadband router will send the same request along its network cable to the next router. This process will keep repeating. At some point, some large networking device will have several cables connected to it and will follow a rule like, “Requests for any IP addresses starting with less than 100 should go down cable #1. Everything else down cable #2,â€� and so on, until the request finally gets to 80.72.139.101. And the reply will be sent back in the same way.

You can follow this journey using the traceroute command on Mac and Linux and tracert on Windows:

tracert 80.72.139.101

Time To Live

The caching in step six above is the main cause of the DNS delay. Any given translation (of a Web address into an IP address) has a property called “time to live� or TTL. This tells domain name servers how long they are allowed to cache the translation before having to look it up again.

You can find out what the TTL for a given (fully qualified) domain name is using the dig command, instructing the command to use the domain name’s name server, like so:

dig @a.regfish-ns.net www.smashingmagazine.com

Finding out the time to live, which in this case is 1800 seconds, or half an hour.

The Answer Section shows that www.smashingmagazine.com has a TTL of 1800 seconds:

;; ANSWER SECTION:
www.smashingmagazine.com. 1800	IN	A	80.72.139.101

That is, your local domain name server will remember this for 1800 seconds. If Smashing Magazine suddenly decided to change its IP address, your local domain name server could hang onto the old IP address for up to 30 minutes.

The command also specifies how long to remember that a.regfish-ns.net is a name server for smashingmagazine.com:

;; AUTHORITY SECTION:
smashingmagazine.com.	86400	IN	NS	a.regfish-ns.net.

If Smashing Magazine suddenly decides to change its name servers, your local domain name server would hang onto the old name server for up to 86,400 seconds, which is one whole day. Only then would it ask for the new name server, and only then would it ask the new name server for the new translation.

Moving A Website

And now for the grand finale! This section ties together all of the above to explain the delay. Three sections ago, we had an in-depth description of how to buy a domain name and set up the DNS. This section looks at what happens when you change the IP address of an existing address.

1. Find Out the Name Servers for the Domain

First, you need to know which name servers your domain uses. You can use the nslookup or whois command or an online networking tool. In this example, I will change the IP address of test.stockashop.co.uk.

nslookup -type=ns stockashop.co.uk
whois stockashop.co.uk

The name servers for this domain are listed as ns.rackspace.com and ns2.rackspace.com.

2. Change the IP Address

Changing the IP address for an existing address record at Rackspace.

To actually make the change, you will need to log into the website of the company that manages your name servers, as in the section far above. Then find the (fully qualified) domain name that you want to move, and click on something like “Edit DNS Settings� or “Advanced DNS.� Then find the address record you want to change, and edit and save it.

3. Check Whether the Change Has Been Processed

Your DNS change will be processed after a few minutes or hours, depending on the company. To check if and when the change has been processed, you can use the nslookup command to query the name server directly. This bypasses your own local name server and gets the information straight from the horse’s mouth. You can also use an online tool, submitting the domain (test.stockashop.co.uk in this case) and server (ns.rackspace.com).

nslookup test.stockashop.co.uk ns.rackspace.com

Keep running this command until it comes back with the new IP address. This particular change with Rackspace took 10 to 20 minutes. This is the first part of the DNS delay, and it could take anywhere from 0 to 24 hours.

4. Check How Long You Have to Wait

Eventually, the authoritative name servers for your domain will be changed, and it will return the new IP address. Then you can use the dig command to find out how long until your own name server reflects the change:

dig test.stockashop.co.uk

Look in the Answer Section. It will give you the IP address that it thinks is correct (ending in 33 in this case), and the number of seconds until this expires (91).

;; ANSWER SECTION:
test.stockashop.co.uk.    91    IN    A    92.52.106.33

After the 91 seconds have passed (which felt a lot longer than 91 seconds as I was actually doing it), the answer will suddenly change. The IP address will be the new one (ending in 34), and the number of seconds will reset back to about the time to live (1799 in this case, or 30 minutes).

;; ANSWER SECTION:
test.stockashop.co.uk.  1799    IN    A    92.52.106.34

Now you can restart your browser (to clear its internal cache) and visit the address. Your browser should go to the new IP address and the moved website.

You can also use an online dig to test this, although you will be using its name servers instead of your own; so even if it returns the correct IP address, you (or your client) may have to wait a bit longer.

Most DNS entries have a time to live of 86,400 seconds, which is 24 hours. This will add another 0 to 24 hours of delay, with an average of 12 hours. So, the total delay could be between 0 and 48 hours.

Note that the process is similar when changing the name servers for a domain. You can use nslookup or dig to keep track of the changes.

Minimizing The Delay When Moving A Website

There are a few techniques for shrinking the delay, or eliminating it entirely. Please comment if you have any other suggestions.

1. Make the Delay Immaterial

If the website is static and never changes, then having an exact copy on both the old and new hosts will be sufficient. Visitors won’t be able to tell whether they are seeing the old or new one. Or, if you are in a position to shut down dynamic content (such as turning off the comments on a blog for a weekend), then you can make your website static for the duration of the transfer period.

2. Update the Database Across the Internet

All big websites use a database that updates frequently based on user events, such as blog comments and items in shopping baskets. When moving this kind of website, it is possible to subject only the files (HTML, PHP, ASP, etc.) to the DNS delay, and not the data. As above, make an exact copy of the website’s files on the new host. Then configure the new host to access the database still residing on the old host (which may require some firewall configuration). Then make the DNS changes and wait out the delay. Then, at a convenient time, when few people are using the website, transfer the database.

3. Change the TTL

An alternative is to lower the time to live for the transfer. The TTL is usually set to a day to avoid a lot of unnecessary Internet traffic, and many registrars and hosting companies do not let you change the TTL. But some do, such as Rackspace (as seen above), and this alone could be the deciding factor for your choice of a DNS.

You can change the TTL from 86,400 seconds to 300 seconds (5 minutes), and then wait a day for all name servers around the world to learn about this change. Then copy the website and database across as quickly as possible, make the DNS change, and everyone should know about it within five minutes. Then change it back to 86,400 seconds. (Some hosts, like Rackspace, do this automatically after a few days.)

Email

If you have to transfer email accounts along with the website, the easiest way to do this is to set up the email addresses on the new mail server (i.e. the server that stores the emails, which is usually the same as the Web server), and then change the DNS MX record (which specifies which server handles the email for the domain) on a Friday afternoon. By Monday morning, everyone will know about the change, and you can download all of your email one last time from the old mail server, change your email preferences to reflect the new mail server (and your passwords, if they have changed), and then start checking your email on the new server.

This only applies to POP accounts on which no mail is left on the server. IMAP accounts are more difficult; you’ll have to copy all of your emails off the old server first, and then reupload them to the new server. There are other more immediate methods as well, such as changing the TTL or specifying MX records for both the old and new mail servers at the same time.

Conclusion

The 24 to 48-hour DNS delay is caused by two main factors:

1. The time it takes your registrar or host (or other company) to process your DNS request, which could be anywhere from a few minutes to 24 hours. Before this happens, nobody anywhere has any chance whatsoever of knowing about the change.
2. The time it takes for your personal name server to learn about the change, which can vary from instantly to the time to live (usually 24 hours). The delay from this will be different for everyone.

Hopefully this article has given you a solid understanding of the basics. Please feel free to comment if you have anything to add or suggest.

(al) (il)

© Paul Tero for Smashing Magazine, 2011. | Permalink | Post a comment | Smashing Shop | Smashing Network | About Us
Post tags: coding, domains

Website speed has always been a big issue, and it has become even more important since April 2010, when Google decided to use it in search rankings. However, the focus of the discussion is generally on minimizing file sizes, improving server settings and optimizing CSS and Javascript.

The discussion glosses over another important factor: the speed with which your pages are actually put together on your server. Most big modern websites store their information in a database and use a language such as PHP or ASP to extract it, turn it into HTML and send it to the Web browser.

So, even if you get your home page down to 1.5 seconds (Google’s threshold for being considered a “fast� website), you can still frustrate customers if your search page takes too much time to respond, or if the product pages load quickly but the “Customer reviews� delay for several seconds.

Google’s threshold for a fast-loading website is about 1.5 seconds. This screenshot comes from Google Webmaster Tools (go to [domain name] → Diagnostics → Site Performance).

This article looks at these sorts of issues and describes some simple ways to speed up your website by optimizing your database. It starts with common knowledge but includes more complex techniques at the end, with links to further reading throughout. The article is intended for fearless database beginners and designers who have been thrown in at the deep end.

What Is A Database? What Is SQL?

A database is basically a collection of tables of information, such as a list of customers and their orders. It could be a filing cabinet, a bunch of spreadsheets, a Microsoft Access file or Amazon’s 40 terabytes of book and customer data.

A typical database for a blog has tables for users, categories, posts and comments. WordPress includes these and a few other starter tables. A typical database for an e-commerce website has tables for customers, products, categories, orders and order items (for the contents of shopping baskets). The open-source e-commerce software Magento includes these and many others. Databases have many other uses — such as for content management, customer relations, accounts and invoicing, and events — but these two common types (i.e. for a blog and an e-commerce website) will be referenced throughout this article.

Some tables in a database are connected to other tables. For example, a blog post can have many comments, and a customer can make multiple orders (these are one-to-many relationships). The most complicated type of database relationship is a many-to-many relationship. One relationship is at the core of all e-commerce databases: an order can contain many products, and a single product can be added to many different orders. This is where the “order items� table comes in: it sits between the products and the orders, and it records every time a product is added to an order. This will be relevant later on in the article, when we look at why some database queries are slow.

The word database also refers to the software that contains all this data, as in “My database crashed while I was having breakfast,� or “I really need to upgrade my database.� Popular database software include Microsoft Access 2010, Microsoft SQL Server, MySQL, PostgreSQL and Oracle Database 11g.

The acronym SQL comes up a lot when dealing with databases. It stands for “structured query languageâ€� and is pronounced “sequelâ€� or “es-cue-el.â€� It’s the language used to ask and tell a database things — exciting things like SELECT lastname FROM customers WHERE city='Brighton'. This is called a database query because it queries the database for data. There are other types of database statements: INSERT for putting in new data, UPDATE for updating existing data, DELETE for deleting things, CREATE TABLE for creating tables, ALTER TABLE and many more.

How Can A Database Slow Down A Website?

A brand new empty website will run very fast, but as it grows and ages, you may notice some sluggishness on certain pages, particularly pages with complicated bits of functionality. Suppose you wanted to show “Customers who bought this product also bought…� at the bottom of a page of products. To extract this information from the database, you would need to do the following:

1. Start with the current product,
2. See how many times the product has recently been added to anyone’s shopping basket (the “order items� table from above),
3. Look at the orders related to those shopping baskets (for completed orders only),
4. Find the customers who made those orders,
5. Look at other orders made by those customers,
6. Look at the contents of those orders’ baskets (the “order items� again),
7. Look up the details of those products,
8. Identify the products that appear the most often and display them.

You could, in fact, do all of that in one massive database query, or you could split it up over several different queries. Either way, it might run very quickly when your database has 20 products, 12 customers, 18 orders and 67 order items (i.e. items in shopping baskets). But if it is not written and programmed efficiently, then it will be a lot slower with 500 products, 10,000 customers, 14,000 orders and 100,000 order items, and it will slow down the page.

This is a very complicated example, but it shows what kind of stuff goes on behind the scenes and why a seemingly innocuous bit of functionality can grind a website to a halt.

A website could slow down for many other reasons: the server running low on memory or disc space; another website on the same server consuming resources; the server sending out a lot of emails or churning away at some other task; a software, hardware or network fault; a misconfiguration. Or it may have suddenly become a popular website. The next two sections, therefore, will look at speed in more detail.

Is It My Database?

There are now several ways to analyze your website’s speed, including the Firebug plug-in for Firefox, the developer tools in Google Chrome (press Shift + Control + I, and then go to Resources → Enable Resource Tracking) and Yahoo YSlow. There are also websites such as WebPagetest, where you can enter a URL, and it will time it from your chosen location.

All of these tools will show you a diagram of all of the different resources (HTML, images, CSS and JavaScript files) used by your page, along with how long each took to load. They will also break down the time taken to perform a DNS lookup (i.e. to convert your domain name into an IP address), the time taken to connect to your server, the time spent waiting for your server to reply (aka “time to first byte�), and the time spent receiving (i.e. downloading) the data.

Many Web pages are constructed in their entirety by the Web server, including by PHP that accesses the database, and then sent to the browser all at once, so any database delays would lead to a long waiting time, and the receiving/downloading time would be proportional to the amount of data sent. So, if your 20 kB HTML page has a quick connection, a waiting time of 5 seconds and a download time of 0.05 seconds, then the delay would occur on the server, as the page is being built.

Not all Web pages are like this, though. The PHP flush function forces the server to send the HTML that it has already built to the browser right away. Any further delays would then be in the receiving time, rather than the waiting time.

Either way, you can compare the waiting/receiving time for your suspected slow and complicated Web page to the waiting time for a similarly sized HTML page (or image or other static resource) on the same server at the same time. This would rule out the possibility of a slow Internet connection or an overloaded server (both of which would cause delays) and allow you to compare the times taken to construct the pages. This is not an exact science, but it should give you some indication of where things are being held up.

The screenshots below show the analysis provide by Google Chrome’s Developer Tools of a 20 kB Web page versus a 20 kB image. The Web page waited 130 milliseconds (ms) and downloaded for 22 ms. The image waited for 51 ms and downloaded for 11 ms. The download/receiving times are about the same, as expected, but the server is spending about 80 ms extra on processing and constructing the Web page, which entails executing the PHP and calling the database.

When performing these tests, analyze the static resource by itself and click “Refresh,� so that you are not getting a quick cached version. Also, run each a few times to ensure that you’re not looking at a statistical anomaly. The third screenshot below shows that WebPagetest indicates almost double the time of Google for the same page at the same time, demonstrating that using the same environment for all tests is important.

Resource analysis using Google Chrome’s Developer Tools, showing a 130-ms wait time for a Web page.

The same tool, showing a 51-ms wait time for an image of about the same size.

Resource analysis of the same page from WebPagetest, with a 296-ms wait time and a 417-ms total time.

How To Time A Database Query In PHP And MySQL

The approach above was general; we can now get very specific. If you suspect that your database might be slowing down your website, then you need to figure out where the delay is coming from. I will define a couple of timing functions, and then use them to time every single database query that is run by a page. The code below is specific to PHP and MySQL, but the method could be used on any database-driven website:

function StartTimer ($what='') {
 global $MYTIMER; $MYTIMER=0; //global variable to store time
 //if ($_SERVER['REMOTE_ADDR'] != '127.0.0.1') return; //only show for my IP address

 echo '<p style="border:1px solid black; color: black; background: yellow;">';
 echo "About to run <i>$what</i>. "; flush(); //output this to the browser
 //$MYTIMER = microtime (true); //in PHP5 you need only this line to get the time

 list ($usec, $sec) = explode (' ', microtime());
 $MYTIMER = ((float) $usec + (float) $sec); //set the timer
}
function StopTimer() {
 global $MYTIMER; if (!$MYTIMER) return; //no timer has been started
 list ($usec, $sec) = explode (' ', microtime()); //get the current time
 $MYTIMER = ((float) $usec + (float) $sec) - $MYTIMER; //the time taken in milliseconds
 echo 'Took ' . number_format ($MYTIMER, 4) . ' seconds.</p>'; flush();
}

StartTimer starts the timer and also prints whatever you are trying to time. The second line is a check of your IP address. This is very useful if you are doing this (temporarily) on a live website and don’t want everyone in the world to see the timing messages. Uncomment the line by removing the initial //, and replace the 127.0.0.1 with your IP address. StopTimer stops the timer and displays the time taken.

Most modern websites (especially well-programmed open-source ones) have a lot of PHP files but query the database in only a handful of places. Search through all of the PHP files for your website for mysql_db_query or mysql_query. Many software development packages such as BBEdit have functions to perform searches like this; or, if you are familiar with the Linux command line, try this:
grep mysql_query `find . -name \*php`

You may find something like this:

mysql_query ($sql);

For WordPress 3.0.4, this is on line 1112 of the file wp-includes/wp-db.php. You can copy and paste the functions above into the top of this file (or into any PHP file that is included by every page), and then add the timer before and after the mysql_query line. It will look like this:

StartTimer ($query);
$this->result = @mysql_query( $query, $dbh );
StopTimer();

Below is a partial screenshot of this being done on a brand new WordPress installation. It is running about 15 database queries in total, each taking about 0.0003 seconds (0.3 ms); so, less than 5 ms in total, which is to be expected for an empty database.

This shows and times all of the database queries that WordPress runs.

If you have found this line in other commonly used systems, please share this information by adding to the comments for this article.

You can also do other interesting things with it: you can see how fast your computer is compared to mine. Counting to 10 million takes my computer 2.9420 seconds. My Web server is a bit faster at 2.0726 seconds:

StartTimer ('counting to 10000000');
for ($i=0; $i<10000000; $i++); //count to a high number
StopTimer();

Notes on the Results

This technique gives you only comparative results. If your server was very busy at that moment, then all of the queries would be slower than normal. But you should have at least been able to determine how long a fast query takes on your server (maybe 1 to 5 ms), and therefore identify the slow-ish ones (200+ ms) and the really slow ones (1+ second). You can run the test a few times over the course of an hour or day (but not immediately after — see the section below about the database cache) to make sure you’re not getting a fluke.

This will also most likely severely mess up the graphical presentation of the page. It may also give you PHP warnings like “Cannot modify header information. Headers already sent by…” This is because the timing messages are interfering with cookie and session headers. As long as the page still displays below the warnings, you can ignore them. If the page does not display at all, then you may need to put the StartTimer and StopTimer around specific blocks of code, rather than around mysql_query.

This technique is essentially a quick hack to show some rough results. It should not be left on a live website.

What Else Could It Be?

If your database queries are not particularly slow, but the construction of your Web page is, then you might just have poorly written code. You can put the timer statements above around bigger and bigger blocks of code to see if and where the delay is occurring. It could be that you are looping through 10,000 full rows of product information, even if you are displaying only 20 product names.

Profiling

If you are still baffled and/or want more complete and accurate information about what’s happening in your code, you could try a debugging and profiling tool such as Xdebug, which analyzes a local copy of your website. It can even visually show where bottlenecks are occurring.

Indexing Database Tables

The experiment above may have surprised you by showing just how many database queries a page on your website is running, and hopefully, it has helped you identify particularly slow queries.

Let’s look now at some simple improvements to speed things up. To do this, you’ll need a way to run database queries on your database. Many server administration packages (like cPanel or Plesk) provide phpMyAdmin for this task. Alternatively, you could upload something like phpMiniAdmin to your website; this single PHP file enables you to look at your database and run queries. You’ll need to enter your database name, user name and password. If you don’t know these, you can usually find them in your website’s configuration file, if it has one (in WordPress, it’s wp-config.php).

Among the database queries that your page runs, you probably saw a few WHERE conditions. This is SQL’s way of filtering out results. For instance, if you are looking at an “Account history” type of page on your website, there is probably a query like this to look up all of the orders someone has placed. Something like this:

SELECT * FROM orders WHERE customerid = 2;

This retrieves all orders placed by the customer with the database ID 2. On my computer, with 100,000 orders in the database, running this took 0.2158 seconds.

Columns like customerid — which deal with a lot of WHERE conditions with = or < or > and have many possible values, should be indexed. This is like the index at the back of a book: it helps the database quickly retrieve indexed data. This is one of the quickest ways to speed up database queries.

What to Index

In order to know which columns to index, you need to understand a bit about how your database is being used. For example, if your website is often used to look up categories by name or events by date, then these columns should be indexed.

SELECT * FROM categories WHERE name = 'Books';
SELECT * FROM events WHERE startdate >= '2011-02-07';

Each of your database tables should already have an ID column (often called id, but sometimes ID or articleid or the like) that is listed as a PRIMARY KEY, as in the wp_posts screenshot below. These PRIMARY KEYs are automatically indexed. But you should also index any columns that refer to ID numbers in other tables, such as customerid in the example above. These are sometimes referred to as FOREIGN KEYs.

SELECT * FROM orders WHERE customerid = 2;
SELECT * FROM orderitems WHERE orderid = 231;

If a lot of text searches are being done, perhaps for descriptions of products or article content, then you can add another type of index called a FULL TEXT index. Queries using a FULL TEXT index can be done over multiple columns and are initially configured to work only with words of four or more letters. They also exclude certain common words like about and words that appear in more than 50% of the rows being searched. However, to use this type of index, you will need to change your SQL queries. Here is a typical text search, the first without and the second with a FULL TEXT index:

SELECT * FROM products WHERE name LIKE '%shoe%' OR description LIKE '%shoe%';
SELECT * FROM products WHERE MATCH(name,description) AGAINST ('shoe');

It may seem that you should go ahead and index everything. However, while indexing speeds up SELECTs, it slows down INSERTs, UPDATEs and DELETEs. So, if you have a products table that hardly ever changes, you can be more liberal with your indexing. But your orders and order items tables are probably being modified constantly, so you should be more sparing with them.

There are also cases where indexing may not help; for example, if most of the entries in a column have the same value. If you have a stock_status column that stores a value of 1 for “in stock,â€� and 95% of your products are in stock, then an index wouldn’t help someone search for in-stock products. Imagine if the word the was indexed at the back of a reference book: the index would list almost every page in the book.

SELECT * FROM products WHERE stock_status = 1;

How to Index

Using phpMyAdmin or phpMiniAdmin, you can look at the structure of each database table and see whether the relevant columns are already indexed. In phpMyAdmin, click the name of the table and browse to the bottom where it lists “Indexes.â€� In phpMiniAdmin, click “Show tablesâ€� at the top, and then “sctâ€� for the table in question; this will show the database query needed to recreate the table, which will include any indices at the bottom — something like KEY 'orderidindex' ('orderid').

Using phpMiniAdmin to check for indices in the WordPress wp_posts table.

If the index does not exist, then you can add it. In phpMyAdmin, below the index, it says “Create an index on 1 columnsâ€�; click “Goâ€� here, enter a useful name for the index (like customeridindex), choose the column on the next page, and press “Save,â€� as seen in this screenshot:

Indexing a column using phpMyAdmin.

In phpMiniAdmin, you’ll have to run the following database statement directly in the large SQL query box at the top:

ALTER TABLE orders ADD INDEX customeridindex (customerid);

Running the query again after indexing takes only 0.0019 seconds on my computer, 113 times faster.

Adding a FULL TEXT index is a similar process. When you run searches against this index, you must list the same columns:

ALTER TABLE articles ADD FULLTEXT(title,author,articletext);
SELECT * FROM articles WHERE MATCH(title,author,articletext) AGAINST ('mysql');

Back-Ups and Security

Before altering your database tables in any way, make a back-up of the whole database. You can do this using phpMyAdmin or phpMiniAdmin by clicking “Export.â€� Especially if your database contains customer information, keep the back-ups in a safe place. You can also use the command mysqldump to back up a database via SSH:

mysqldump --user=myuser --password=mypassword
--single-transaction --add-drop-table mydatabase
> backup`date +%Y%e%d`.sql

These scripts also represent a security risk, because they make it much easier for someone to steal all of your data. While phpMyAdmin is often provided securely though your server management software, phpMiniAdmin is a single file that is very easy to upload and forget about. So, you may want to password-protect it or remove it after usage.

Optimizing Tables

MySQL and other kinds of database software have built-in tools for optimizing their data. If your tables get modified a lot, then you can run the tools regularly to make the database tables smaller and more efficient. But they take some time to run (from a few seconds to a few minutes or more, depending on the size of the tables), and they can block other queries from running on the table during optimization, so doing this at a non-busy time is best. There’s also some debate about how often to optimize, with opinions ranging from never to once in a while to weekly.

To optimize a table, run database statements such as the following in phpMyAdmin or phpMiniAdmin:

OPTIMIZE TABLE orders;

For example, before I optimized my orders table with 100,000 orders, it was 31.2 MB in size and took 0.2676 seconds to run SELECT * FROM orders. After its first ever optimization, it shrunk to 30.8 MB and took only 0.0595 seconds.

The PHP function below will optimize all of the tables in your database:

function OptimizeAllTables() {
 $tables = mysql_query ('SHOW TABLES'); //get all the tables
 while ($table = mysql_fetch_array ($tables))
 mysql_query ('OPTIMIZE TABLE ' . $table[0]); //optimize them
}

Before calling this function, you have to connect to your database. Most modern websites will connect for you, so you don’t need to worry about it, but the relevant MySQL calls are shown here for the sake of completeness:

mysql_connect (DB_HOST, DB_USER, DB_PASSWORD);
mysql_select_db (DB_NAME);
OptimizeAllTables();

Making Sure To Use The Cache

Just as a Web browser caches copies of pages you visit, database software caches popular queries. As above, the query below took 0.0019 seconds when I ran it the first time with an index:

SELECT * FROM orders WHERE customerid=2;

Running the same query again right away takes only 0.0004 seconds. This is because MySQL has remembered the results and can return them a second time without looking them up again.

However, many news websites and blogs might have queries like the following to ensure that articles are displayed only after their published date:

SELECT * FROM posts WHERE publisheddate <= CURDATE();
SELECT * FROM articles WHERE publisheddate <= NOW();

These queries cannot be cached because they depend on the current time or date. In a table with 100,000 rows, a query like the one above would take about 0.38 seconds every time I run it against an unindexed column on my computer.

If these queries are run on every page of your website, thousands of times per minute, it would speed things up considerably if they were cacheable. You can force queries to use the cache by replacing NOW or CURDATE with an actual time, like so:

SELECT * FROM articles WHERE publisheddate <= '2011-01-17 17:00';

You can use PHP to make sure the time changes every five minutes or so:

$time = time();
$currenttime = date ('Y-m-d H:i', $time - ($time % 300));
mysql_query (“SELECT * FROM articles WHERE publisheddate <= '$currenttime'�);

The percentage sign is the modulus operator. % 300 rounds the time down to the last 300 seconds or 5 minutes.

There are other uncacheable MySQL functions, too, like RAND.

Outgrowing Your Cache

Outgrowing your MySQL cache can also make your website appear to slow down. The more posts, pages, categories, products, articles and so on that you have on your website, the more related queries there will be. Take a look at this example:

SELECT * FROM articles WHERE publisheddate <= '2011-01-17 17:00' AND categoryid=12

It could be that when your website had 500 categories, queries like this one all fit in the cache together and all returned in milliseconds. But with 1000 regularly visited categories, they keep knocking each other out of the cache and returning much slower. In this case, increasing the size of the cache might help. But giving more server RAM to your cache means spending less on other tasks, so consider this carefully. Plenty of advice is available about turning on and improving the efficiency of your cache by setting server variables.

When Caching Doesn’t Help

A cache is invalidated whenever a table changes. When a row is inserted, updated or deleted, all queries relying on that table are effectively cleared from the cache. So, if your articles table is updated every time someone views an article (perhaps to count the number of views), then the improvement suggested above might not help much.

In such cases, you may want to investigate an application-level cacher, such as Memcached, or read the next section for ideas on making your own ad-hoc cache. Both require much bigger programming changes than discussed up to now.

Making Your Own Cache

If a particularly viscous database query takes ages but the results don’t change often, you can cache the results yourself.

Let's say you want to show the 20 most popular articles on your website in the last week, using an advanced formula that takes into account searches, views, saves and “Send to a friendâ€� hits. And you want to show these on your home page in an unordered (<ul>) HTML list.

It might be easiest to use PHP to run the database query once an hour or once a day and save the full list to a file somewhere, which you can then include on your home page.

Once you have written the PHP to create the include file, you could take one of a couple approaches to scheduling it. You could use your server’s scheduler (in Plesk 8, go to Server → Scheduled Tasks) to call a PHP page every hour, with a command like this:

wget -O /dev/null -q http://www.mywebsite.co.uk/runhourly.php

Alternatively, you could get PHP to check whether the file is at least an hour old before running the query — something like this, where 3600 is the number of seconds in an hour:

$filestat = stat ('includes/complicatedfile.html');
//look up information about the file
if ($filestat['mtime'] < time()-3600) RecreateComplicatedIncludeFile();
//over 1 hour
readfile ('includes/complicatedfile.html');
//include the file into the page

Returning to the involved example above for “Customers who bought this product also bought…,� you could also cache items in a new database column (or table). Once a week or so, you could run that long set of queries for each and every product, to figure out which other products customers are buying. You could then store the resulting product ID numbers in a new database column as a comma-separated list. Then, when you want to select the other products bought by customers who bought the product with the ID 12, you can run this query:

SELECT * FROM products WHERE FIND_IN_SET(12,otherproductids);

Reducing The Number Of Queries By Using JOINs

Somewhere in the management and control area of your e-commerce website is probably a list of your orders with the names of the customers who made them.

This page might have a query like the following to find all completed orders (with a status value indicating whether an order has been completed):

SELECT * FROM orders WHERE status>1;

And for each order it comes across, it might look up the customer’s details:

SELECT * FROM customers WHERE id=1;
SELECT * FROM customers WHERE id=2;
SELECT * FROM customers WHERE id=3;
etc

If this page shows 100 orders at a time, then it has to run 101 queries. And if each of those customers looks up their delivery address in a different table, or looks for the total charge for all of their orders, then the time delay will start to add up. You can make it much faster by combining the queries into one using a JOIN. Here’s what a JOIN looks like for the queries above:

SELECT * FROM orders INNER JOIN customers
ON orders.customerid = customers.id WHERE orders.status>=1;

Here is another way to write this, without the word JOIN:

SELECT * FROM orders, customers
WHERE orders.customerid = customers.id AND orders.status>=1;

Restructuring queries to use JOINs can get complicated because it involves changing the accompanying PHP code. But if your slow page runs thousands of database statements, then it may be worth a look. For further information, Wikipedia offers a good explanation of JOINs. The columns with which you use a JOIN (customerid in this case) are also prime candidates for being INDEXed.

You could also ask MySQL to EXPLAIN a database query. This tells you which tables it will use and provides an “execution plan.â€� Below is a screenshot showing the EXPLAIN statement being used on one of the more complex WordPress queries from above:

Using the EXPLAIN statement to explain how MySQL plans to deal with a complex query.

The screenshot shows which tables and indices are being used, the JOIN types, the number of rows analyzed, and a lot more information. A comprehensive page on the MySQL website explains what the EXPLAIN explains, and another much shorter page goes over how to use that information to optimize your queries (by adding indices, for instance).

…Or Just Cheat

Finally, returning again to the advanced example above for “Customers who bought this product also bought…,� you could also simply change the functionality to be something less complicated for starters. You could call it “Recommended products� and just return a few other products from the same category or return some hand-picked recommendation.

Conclusion

This article has shown a number of techniques for improving database performance, ranging from simple to quite complex. While all well-built websites should already incorporate most of these techniques (particularly the database indices and JOINs), the techniques do get overlooked.

There is also a lot of debate on forums around the Web about the effectiveness and reliability of some of these techniques (i.e. measuring speed, indexing, optimization, how best to use the cache, etc.), so the advice here is not definitive, but hopefully it gives you an overview of what’s available.

If your website starts to mysteriously slow down after a few months or years, you will at least have a starting point for figuring out what’s wrong.

(al)

© Paul Tero for Smashing Magazine, 2011. | Permalink | Post a comment | Smashing Shop | Smashing Network | About Us
Post tags: database, performance, SQL

Have you ever heard a colleague answer the phone like this: “Good afterno… Yes… What? Completely?… When did it go down?… Really, that long?… We’ll look into it right away… Yes, I understand… Of course… Okay, speak to you soon… Bye.” The call may have been followed by some cheesy ’80s rock ballad coming from the speaker phone, interrupted by “Thank you for holding. You are now caller number 126 in the queue.” That’s your boss calling the hosting company’s 24 hour “technical support” line.

An important website has gone down, and sooner or later, heads will turn to the Web development corner of the office, where you are sitting quietly, minding your own business, regretting that you ever mentioned “Linux” on your CV. You need to take action. Your company needs you. Your client needs you. Here’s what to do.

1. Check That It Has Actually Gone Down

Don’t take your client’s word for it. Visit the website yourself, and press Shift + Refresh to make sure you’re not seeing a cached version (hold down Shift while reloading or refreshing the page). If the website displays fine, then the problem is probably related to your client’s computer or broadband connection.

If it fails, then visit a robust website, such as google.com or bbc.co.uk. If they fail too, then there is at least an issue with your own broadband connection (or your broadband company’s DNS servers). Chances are that you and your client are located in the same building and the whole building has lost connectivity, or perhaps you have the same broadband company and its engineers have taken the day off. You will need to check the website on your mobile phone or phone a friend. To be doubly sure, ask your friend to check Where’s It Up? or Down for Everyone or Just Me?, which will confirm whether your website is down just for you or for everyone.

If the website is definitely down, then frown confusedly and keep reading. A soft yet audible sigh would also be appropriate. You might want to locate the documents or emails that your Internet hosting service sent you when you first signed up with it. It should have useful details such as your IP address, control panel location, log-in details and admin and root passwords; these will come in handy.

2. Figure Out What Has Gone Down

A website can appear to have gone down mainly for one of the following reasons:

• A programming error on the website,
• A DNS problem, or an expired domain,
• A networking problem,
• Something on the server has crashed,
• The whole server has crashed.

To see whether it’s a programming error, visit the website and check the status bar at the bottom of your browser. If it says “Done” or “Loaded,” rather than “Waiting…” or “Connecting…,” then the server and its software are performing correctly, but there is a programming error or misconfiguration. Check the Apache error log for clues.

Otherwise, you’ll need to run some commands to determine the cause. On a Mac with OS X or above, go to Applications → Utilities and run Terminal. On a PC with Windows, go to Start → All Programs → Accessories and choose “Command Prompt.â€� If you use Linux, you probably already know about the terminal; but just in case, on Ubuntu, it’s under Applications → Accessories.

The first command is ping, which sends a quick message to a server to check that it’s okay. Type the following, replacing the Web address with something meaningful to you, and press “Enter.â€� For all of the commands in this article, just type the stuff in the grey monospaced font. The preceding characters are the command prompt and are just there to let you know who and where you are.

C:\> ping www.stockashop.co.uk

If the server is alive and reachable, then the result will be something like this:

Reply from 92.52.106.33:
bytes=32 time=12ms TTL=53

Ping command from a Windows computer.

On Windows, it will repeat four times, as above. On Linux and Mac, each line will start with 64 bytes from and it will repeat indefinitely, and you’ll need to press Control + C to stop it.

The four-part number in the example above is your server’s IP address. Every computer on the Internet has one. At this stage, you can double-check that it is the correct one. You’ll need to have a very good memory, or refer to the documentation that your hosting company sent you when you first signed up with it. (This article does not deal with the newish eight-part IPv6 addresses.)

For instance, my broadband company is sneaky and tries to intercept all bad requests so that it can advertise to me when I misspell a domain name in the Web browser. In this case, the ping looks successful but the IP address is wrong:

64 bytes from advancedsearch.virginmedia.com
(81.200.64.50): icmp_seq=1 ttl=55 time=26.4 ms

Note that ping might also show the server name in front of the IP address (advancedsearch.virginmedia.com in this case). Don’t worry too much if it doesn’t match the website you are pinging — a server can have many names. The IP address is more important.

Assuming you’ve typed the domain name correctly, a bad IP address indicates that the domain name could have expired or that somebody has made a mistake with its DNS settings. If you receive something like unknown host, then it’s definitely a domain name issue:

ping: unknown host www.nosuchwebsite.fr

In this case, use a website such as Who.is to verify the domain registration details, or run the whois command from Linux or Mac. It will at least tell you when it expired, who owns it and where it is registered. The Linux and Mac commands host and nslookup are also useful for finding information about a domain. The nslookup command in particular has many different options for querying different aspects of a domain name:

paul@MyUbuntu:~$ whois stockashop.co.uk
paul@MyUbuntu:~$ host stockashop.co.uk
paul@MyUbuntu:~$ nslookup stockashop.co.uk
paul@MyUbuntu:~$ nslookup -type=soa stockashop.co.uk

If nothing happens when you ping, or you get something like request timed out, then you can deepen your frown and move on to step three.

What a non-responding server looks like in a Linux terminal.

Alternatively, if your server replied with the correct IP address, then you can exhale in relief and move on to step five.

Note that there are plenty of websites such as Network-Tools.com that allow you to ping websites. However, using the command line will impress your colleagues more, and it is good practice for the methods in the rest of this article.

3. How Bad Is It?

If your ping command has timed out, then chances are your whole server has crashed, or the network has broken down between you and the server.

If you enjoy grabbing at straws, then there is a small chance that the server is still alive and has blocked the ping request for security reasons — namely, to prevent hackers from finding out it exists. So, you can still proceed to the next step after running the commands below, but don’t hold your breath.

To find out if it is a networking issue, use traceroute on Mac or Linux and tracert on a PC, or use the trace option on a website such as Network-Tools.com. On Mac and Linux type:

paul@MyUbuntu:~$ traceroute www.stockashop.co.uk

On Windows:

C:\> tracert www.stockashop.co.uk

Traceroute traces a route across the Internet from your computer to your server, pinging each bit of networking equipment that it finds along the way. It should take 8 to 20 steps (technically known as “hops”) and then time out or show a few asterisks (*). The number of steps depends on how far away the server is and where the network has broken down.

The first couple of steps happens in your office or building (indicated by IP addresses starting with 192.68 or 10). The next few belong to your broadband provider or a big telecommunications company (you should be able to tell by the long name in front of the IP address). The last few belong to your hosting company. If your server is alive and well, then the very last step would be your server responding happily and healthily.

Traceroute on a Mac, through the broadband company and host to an unresponsive server.

Barring a major networking problem, like a city-wide power outage, traceroute will reach your hosting company. Now, you just need to determine whether only your server is ill or a whole rack or room has gone down.

You can’t tell this just from traceroute, but chances are the servers physically next to yours have similar IP addresses. So, you could vary the last number of your server’s IP address and check for any response. If your server’s IP address is 123.123.123.123, you could try:

C:\> ping 123.123.123.121
C:\> ping 123.123.123.122
C:\> ping 123.123.123.124
C:\> ping 123.123.123.125

If you discover that the server is in the middle of a range of 10 to 20 IP addresses that are all broken, then it could well indicate a wider networking issue deep within the air-conditioned, fireproof bunker that your server calls home. It is unlikely that the hosting company would leave so many IP addresses unused or that the addresses would have all crashed at the same time for different reasons. It is likely, though not definitive, that a whole rack or room has been disconnected or lost power… or burned down.

Alternatively, if nearby IP addresses do reply, then only your server is down. You can proceed to the next step anyway and hope that the cause is that your server is very secure and is blocking ping requests. Perhaps upgrade that deep frown to a pronounced grimace.

Otherwise, you’ll have to keep listening to Foreigner until your hosting company answers the phone. It is the only one that can fix the network and/or restart the server. But at least you now have someone else to blame. And if you are number 126 in the queue, it’s probably because 125 other companies think their websites have suddenly gone down, too.

4. Check Your Web Server Software

If the server is alive but just not serving up websites, then you can make one more check before logging onto the server. Just as your office computer has a lot of software for performing various tasks (Photoshop, Firefox, Mac Mail, Microsoft Excel, etc.), so does your server. Arguably its most important bit of software is the Web server, which is usually Apache on Linux servers and IIS on Windows servers. (From here on in, I will refer to it as “Web server software,� because “Web server� is sometimes used to refer — confusingly — to the entire server.)

When you visit a website, your Web browser communicates with the Web server software behind the scenes, sharing caching information, sending and receiving cookies, encrypting and decrypting, unzipping and generally managing your browsing experience.

You can bypass all of this and talk directly to the Web server software by using the telnet command, available on Windows, Linux and Mac. It will tell you conclusively whether your Web server software is alive. The command ends with the port, which is almost always 80:

ping@MyUbuntu:~$ telnet www.stockashop.co.uk 80

If all were well, then your Web server software would respond with a couple of lines indicating that it is connected and then wait for you to tell it what to do. Type something like this, followed by two blank lines:

GET / HTTP/1.1
Host: www.stockashop.co.uk

The first / tells it to get your home page; you could also say GET /products/index.html or something similar. The Host line tells it which website to return, because your server might hold many different websites. If your website were working, then your Web server software would reply with some headers (expiry, cookies, cache, content type, etc.) and then the HTML, like this:

Checking the web server software with telnet.

But because there is a problem, telnet will either not connect (indicating that your Web server software has crashed) or not respond (indicating that it is misconfigured). Either way, you’ll need to keep reading.

5. Logging Into Your Server

The remote investigations are now over, and it’s time to get up close and personal with your errant server.

First, check your server’s documentation to see whether the server has a control panel, such as Plesk or cPanel. If you’re lucky, it will still be working and will tell you what is wrong and offer to restart it for you (in Plesk, click Server → Service Management).

If your server has a control panel such as Plesk, try logging in to make sure the Web server is running.

If not, then the following commands apply to dedicated Linux servers. You could try them in shared hosting environments, but they probably won’t work. Windows servers are a different kettle of fish and won’t be addressed in this article.

To log in and run commands on the server, you will need the administrative user name and password and the root password, as provided by your host. For shared hosting environments, an FTP user name and password might work.

On Linux and Mac, the command to run is ssh, which stands for “secure shellâ€� and which allows you to securely connect to and run commands on your server. You will need to add your administrative user name to the command after -l, which stands for “loginâ€�:

paul@MyUbuntu:~$ ssh -l admin www.stockashop.co.uk

Windows doesn’t come with ssh, but you can easily download a Windows SSH client such as Putty. Download putty.exe, save it somewhere and run it. Type your website as the host name and click “Open.â€� It will ask you who to log in as and then ask for your password.

Using Putty to SSH from a Windows computer.

Once you have successfully logged in, you should see something like admin@server$, followed by a flashing or solid cursor. This is the Linux command line, very similar to the Terminal or command prompt used above, except now you are actually on the server; you are a virtual you, floating around in the hard drive of your troubled server.

If ssh didn’t even connect, then it might be blocked by a firewall or turned off on the server. If it said Permission denied, then you’ve probably mistyped the user name or password. If it immediately said Connection to www.stockashop.co.uk closed, then you are trying to log in with a user name that is not allowed to run commands; make sure you’re logging in as the administrative user and not an FTP user.

6. Has It Run Out Of Space?

Your server has likely not run out of hard disk space, but I’m putting this first because it’s a fairly easy problem to deal with. The command is df, but you can add -h to show the results in megabytes and gigabytes. Type this on the command line:

admin@server$ df -h

The results will list each file system (i.e. hard drive or partition) and show the percentage of each that has been used.

Checking hard disk usage on a Linux server.

If any of them show 100% usage, then the command probably took eons to type, and you will need to free up some space fast.

Quick Fix

You should still be able to FTP to the server and remove massive files that way. A good place to start is the log files and any back-up directories you have.

You could also try running the find command to search for and remove huge files. This command finds files bigger than 10 MB and lets you scroll through the results one page at a time. You might need to run it as root to avoid a lot of permission denied messages (see below for how to do this). It might also take a long time to run.

root@server# find / -size +10000000c | more

You could also restrict the search to the full partition or to just your websites, if you know where they are:

root@server# find /var/www/vhosts/ -size +10000000c | more

If you want to know just how big those files are, you can add a formatting sequence to the command:

root@server# find /var/www/vhosts -size +10000000c -printf "%15s %p\n"

When you’ve found an unnecessarily big file, you can remove it with rm:

root@server# rm /var/www/vhosts/badwebsite.com/backups/really-big-and-old-backup-file.tgz

Permanent Fix

Clearing out back-ups, old websites and log files will free up a lot of space. You should also identify any scripts and programs that are creating large back-up files. You could ask your host for another hard drive.

7. Has It Run Out Of Memory?

Your server might just be running really, really slowly. The free command will let you know how much memory it is using. Add -m to show the results in megabytes.

admin@server$ free -m

The results will show how much of your memory is in use.

Checking memory usage on a Linux server.

The results above say that the server has 3550 MB, or 3.5 GB, of total memory. Linux likes to use as much as possible, so the 67 MB free is not a problem. Focus on the buffers/cache line instead. If most of this is used, then your server may have run out of workable memory, especially if the swap space (a bit of the hard drive that the server uses for extra memory) is full, too.

If your server has run out of memory, then the top command will identify which bit of software is being greedy.

admin@server$ top

Every few seconds, this gives a snapshot of which bits of software are running, which user started them and how much of your memory and CPU each is using. Unfortunately, this will run very slowly if memory is low. You can press “Q� or Control + C to exit the command.

The Linux top command shows what is running.

Each of the bits of software above is known as a “process.â€� Big pieces of software such as Apache and MySQL will often have a parent process with a lot of child processes and so could appear more than once in the list. In this benign example, a child process of the Apache Web server is currently the greediest software, using 7.6% of the CPU and 1.6% of the memory. The view will refresh every three seconds. Check the Mem column to see whether anything is consistently eating up a large portion of the memory.

Quick Fix

The quickest solution is to kill the memory hog. You will need to be root to do this (unless the process is owned by you — see below). First of all, though, search on Google to find out what exactly you are about to kill. If you kill a core program (such as the SSH server), you’ll be back to telephone support. If you kill your biggest client’s data amalgamation program, which has been running for four days and is just about to finish, then the client could get annoyed, despite your effort to sweeten it with “But your website is okay now!â€�

If the culprit is HTTPD or Apache or MySQLd, then skip to the next section, because those can be restarted more gracefully. In fact, most things can be restarted more gracefully, but this is a quick ignore-the-consequences type of fix.

Find the process ID in the PID column of the command above, and type kill -9, followed by the number. For example:

root@server# kill -9 23421

The -9 tells it to stop completely and absolutely. You can now run top again to see whether it has made a difference. If some other similar process has jumped to the memory-eating position instead, then you’ve probably only stopped a child process, and you will need to find the parent process that spawned all the greedy children in the first place, because stopping the parent will stop all the children, too. Use the process ID again in this command:

root@server# ps -o ppid,user,command 23421

This asks Linux to show you the parent process ID, user and command for the process number 23421. The results will look like this:

PPID  USER     COMMAND
31701 apache   /usr/sbin/httpd

The PPID is the parent process ID. Now try killing this one:

root@server# kill -9 31701

Run top again. Hopefully, the memory usage has now returned to normal. If the parent process ID was 0, then some other process entirely is consuming memory, so run top again.

Permanent Fix

You will probably have to restart the offending software at some point because you may have just disabled your server’s SPAM filter or something else important. If the problem was with Apache or MySQL, you might have an errant bit of memory-eating programming somewhere, or Apache, MySQL or PHP might have non-optimal memory limits. There’s a slim chance that you have been hacked and that your server is slow because it’s sending out millions of emails. Sometimes, though, a server has reached capacity and simply needs more RAM to deal with the afternoon rush.

To find out what went wrong in the first place, check the web logs and/or the log files in /var/log/. When your hosting company has finally answered the phone, you can ask it to also take a look. Figuring out what happened is important because it could well happen again, especially if it’s a security issue. If the hosting company is not responsive or convincing enough, seek other help.

8. Has Something Crashed?

Most Linux servers use Apache for the Web server software and MySQL for the database. It is easy to see whether these are still running (and to restart them if they’re not) or are using up way too much memory. To see all processes running on your server right now, run this command:

admin@server$ ps aux | more

Scroll through the list and look for signs of apache (or its older name httpd) and mysqld (the “dâ€� stands for daemon and is related to the way the programs are run). You are looking for something like this:

USER       PID %CPU %MEM   VSZ   RSS TTY     STAT START   TIME COMMAND
apache   29495  0.5  1.4 90972 53772 ?       S    14:00   0:02 /usr/sbin/httpd
apache   29683  0.3  1.4 88644 52420 ?       S    14:03   0:00 /usr/sbin/httpd
apache   29737  0.3  1.4 88640 52520 ?       S    14:04   0:00 /usr/sbin/httpd

Or you can use the grep command to filter results:

admin@server$ ps aux | grep http
admin@server$ ps aux | grep mysql

If either Apache or MySQL is not running, then this is the source of the problem.

This listing shows that Apache is indeed running.

Quick Fix

If Apache or MySQL is not running, then you’ll need to run the commands below as root (see below). Linux usually has a set of scripts for stopping and starting its major bits of software. You first need to find these scripts. Use the ls command to check the couple of places where these scripts usually are:

root@server# ls /etc/init.d/

If the results include a lot of impressive-looking words like crond, httpd, mailman, mysqld and xinetd, then you’ve found the place. If not, try somewhere else:

root@server# ls /etc/rc.d/init.d/

Or use find to look for them:

root@server# find /etc -name mysqld

Once it is located, you can run a command to restart the software. Note that the scripts might have slightly different names, like apache, apache2 or mysql.

root@server# /etc/init.d/httpd restart
root@server# /etc/init.d/mysqld restart

Hopefully, it will say something like Stopping… Starting… Started. Your websites will start behaving normally again!

Permanent Fix

As above, check the log files, especially the Apache error logs. Sometimes these are all in one place, but usually each website on the server has its own error log. You could look through the ones that were busiest around the time of the crash. Or else you could have a misconfiguration or a programming bug or security breach, so it could well happen again until you identify and address the cause.

Becoming a Super-User

Most of the fixes above require special permissions. For example, you (i.e. the user you have logged in as) will be able to kill or restart processes only if you started them. This can happen on shared servers but is unlikely on dedicated servers, where you will see a lot of permission denied messages. So, to run those commands, you will need to become the server’s super-user, usually known as “root.â€� I’ve left this for last because it’s dangerous. You can do a lot of irreversible damage as root. Please don’t remove or restart anything unless you’re sure about it, and don’t leave your computer unattended.

There are two ways to run a command as root. You can prefix each command with sudo, or you can become root once and for all by typing su. Different servers place different restrictions on these commands, but one of them should work. The sudo command is more restrictive when it turns you into a lesser non-root super-user who is able to run some commands but not others. Both commands will ask for an extra password. For example:

admin@server$ sudo /etc/init.d/httpd restart

When you run su successfully, the prompt will change from a $ to a #, like this:

admin@server$ su
Password:
admin@server#

It might say admin@server or root@server. Either way, the # means that you are powerful and dangerous — and that you assume full liability for your actions.

Conclusion

This article has provided a few tips for recognizing and solving some of the most common causes of a website going down. The commands require some technical knowledge — or at least courage — but are hopefully not too daunting. However, they cover only a small subset of all the things that can go wrong with a website. You will have to rely on your hosting company if it is a networking issue, hardware malfunction or more complicated software problem.

Personally, I don’t mind the ’80s music that plays while I’m on hold with my hosting company. It’s better than complete silence or a marketing message. But it would be even better if the support rep picked up the phone within a few seconds and was ready to help. That is ultimately the difference between paying $40 per month for a dedicated server versus $400.

When the dust has settled, this might be a conversation worth having with your boss — the one still sitting glumly by the phone, eyeing your frown, and waiting for Bono to stop warbling.

(al)

© Paul Tero for Smashing Magazine, 2010. | Permalink | Post a comment | Add to del.icio.us | Digg this | Stumble on StumbleUpon! | Tweet it! | Submit to Reddit | Forum Smashing Magazine
Post tags: admin, Coding, linux, maintenance