The problem
Every once in a while it happens. A customer comes to us for a review of their site or application. We have our initial interview and start picking up the signs: they are developing their product alone. They are self-taught, self made men/women. They word certain concepts in slightly strange way. Then, we log in to the server to take a first look, and there it is: no primary keys. no indexes. Everything is a VARCHAR(100), sometimes even TEXT or LONGTEXT. There is no my.cnf, or it is only 5 lines long. No updates have been run on the server since it was installed 2 years ago. The code is spaghetti and has no comments in it.
First off, let me say that we totally understand how this happens and it is not a bad thing. Everybody has to start somewhere and when there is no one looking over your shoulder every once in a while, who is going to suggest improvements to you? Well, we will!
Also, note that this blog post is not inspired by a hypothetical case. This does happen. Luckily not that often, but it happens. Never be ashamed to ask us for help. It is the most cost-effective way to make things better!
The solution
The problem is that in these cases battles need to be fought on many levels. Some are within our reach completely, like running updates to the software on the server (if you want us to take care of that, of course) . We don’t need you to do anything for that, we just need to make sure not to break anything. But for many things, we need you to change your codebase, and your time (like ours and everyone else’s) is simply limited. So where do we start?
It might seem obvious, but we start listing what needs to be done. We make a list of the most important things, the so-called ‘low hanging fruit’. For every item we list how much of our time it will take, whether we need you to do anything for that change, a category, a priority level and why we think it needs to be done. We send this list to you for review and (often based on what you can spend both money- and time-wise) you tell us which things you would like to do yourselves, which things you want us to do, etc.
Determining priority levels
So what things get highest priority? First off, there are minor version updates to the operating system. If it’s Linux you run (often the case since it is mostly free) and you used repositories for all or at least most of your installs, it is usually a matter of one command and a few minutes of waiting time. If you installed many pieces of software from source or from RPM, it obviously gets a little more complicated.
Next comes the database. First off, all we add primary keys. We analyse the data to see if we can just go ahead and add them to existing columns. Or, when there are many problems with the data in the columns we would like to make primary key, we discuss adding a new column and making it autoincrement for now.
Then, we move on to changing at least the blobs to more appropriate data types and if possible the varchars too. We add indexes where appropriate. These things can largely be done without any required action from you.
Then, we will want to start using our primary key columns (e.g. userid) instead of username when we reference a user from another table. This requires some coordinated effort from both us and the user. Data needs to be cleaned up (remove duplicates and orphaned references) and at the same time we make our change, you need to change your code to now insert a user id instead of a username into e.g. the actionlog table. Depending on your development model we determine a best course of action and discuss it with you. Only then do we execute it, and with extreme caution and backups in place of course.
In this way we work our way down the list. When you have limited resources on time or money, we have no objection in spreading all this work over many days, weeks or even months. You decide how fast you want to go, not us. If you can not afford more than 2 hours a week (or even a month) that is fine with us, we will move at your desired speed. Of course we will at least recommend to plug any serious security holes as fast as possible.
Improving processes
When the biggest problems on the operating system, database and application side have been taken care of we can move on to other, less critical improvements. These are the improvements in your processes. They can involve setting up monitoring and signaling systems, proper backup strategies, version control systems etc.
These things are often very easy to setup and they will go a long way in improving the way you work and thus the quality of your service. To proactively get notified of a server running low on disk space is infinitely better than to find out on christmas eve at 11 pm that your website has stopped working. To be able to easily go back to a previous version of your site or application when you deploy an update that didn’t have the effect you thought it would have is a comforting feeling.
Of course, this is one of many scenario’s. It is just an example of what kind of services we offer. We cater to everyone: large and complex and plain and simple. We believe in making the web a better place, one server at a time!
Education
One of teh things you can expect from us, is that whatever we do, we will explain to you why we do it and what we did exactly. In that way, even when you decide not to stick with us for maintaining your server(s) you will still have a complete log of what happened, who did it and why. Also, it will teach you how to not make the same mistake twice. That way, no one has to correct the same mistake twice.
Tags: consulting, mysql, performance improvements, security improvements