SQL Database Performance Tuning

It's not often I get asked to share stuff and even less that I feel compelled to, but this is well worth reading. http://www.toptal.com/sql/sql-database-tuning-for-developers

Comments Gone

Well seeing as I'm going to open this blog up again I figured I better come in and have a little clean up. Checking the comments it was obvious that most of the 1000+ comments were some form of spam so I decided that I'd clear then all out, including the good ones, sorry about that but I didn't have the time to read each one and decide what was worth keeping.

It seems that the blog still gets a number of visitors even though it's not been updated in some considerable time. It's linked from a number of significant places and still rates reasonably well in Google.

In the next few days I'll be writing something about my new Raspberry Pi computer, including setting up MySQL.

It's as if I never went away

It's been getting on for 3 years since I updated this blog and more like 6 since I regularly added to it. It's actually pretty amazing that in that time it's still prettying relevant and useful, that's not something you could have said in my first 6 years in the IT industry back in the late 90's.

I'm writing this sitting on a train, it will take me a couple of minutes to update and post, something even back then that would have seemed pretty out there. But the last six years hasn't seen those same radical shifts in programming, what I wrote back at the start is still relevant today, let's hope that what I write in the following months stays around for as long.

Generating "Random" Data using rand() and sub selects

I'm developing a demonstration site for new system we are about to unleash on the world, so far it's going great but one of the worst things about demo sites is the lack of real world data at hand. I don't want to give too much away but the site is vehicle sales based and I need to generate a bunch of data. The problem I needed to solve was that the table with the cars in didn't have associated dealer data and the pages looked very bare when there wasn't any as much of the data comes from the dealers table.

I didn't fancy going in to the table and amending 165 cars by hand, but I also didn't want to run the update in blocks as I wanted an a pretty even spread of the different cars between the 12 dealers. Take for example this data set...

mysql> select id, Make, dealer from cars_cars limit 5;

+----+------------+-----------+
| id | Make       | dealer    |
+----+------------+-----------+
|  1 | Land Rover | Aldershot |
|  2 | Land Rover |           |
|  3 | Land Rover |           |
|  4 | Land Rover |           |
|  5 | Land Rover |           |
+----+------------+-----------+
5 rows in set (0.00 sec)

Only one of the vehicles has a dealer attached. What I want is to populate that dealer column with a real dealer.

I always run updates in select mode first, to see what the final out put is like. First I want a way to get a random dealer, we can use rand() in the order by to generate a random number which MySQL will sort on.

mysql> select name from cars_dealers 
       order by rand() limit 1;
+------------+
| name       |
+------------+
| Birmingham |
+------------+
1 row in set (0.00 sec)

mysql> select name from cars_dealers 
       order by rand() limit 1;
+--------+
| name   |
+--------+
| Oldham |
+--------+
1 row in set (0.00 sec)

Now I can include that in my original select like so...

mysql> select id, Make, 
             (select name from cars_dealers 
                     order by rand() limit 1) as dealer 
              from cars_cars limit 5;
+----+------------+-------------+
| id | Make       | dealer      |
+----+------------+-------------+
|  1 | Land Rover | Oxford      |
|  2 | Land Rover | Aldershot   |
|  3 | Land Rover | Coventry    |
|  4 | Land Rover | Southampton |
|  5 | Land Rover | Birmingham  |
+----+------------+-------------+
5 rows in set (0.00 sec)

Great, so I now have a random dealer against each vehicle, all I need now is to turn that into an update and we are good to go.

mysql> update cars_cars 
       set dealer = 
           (select name from cars_dealers 
                   order by rand() limit 1)
       where id > 0;

Query OK, 163 rows affected (0.01 sec)
Rows matched: 163  Changed: 163  Warnings: 0

I now have "random" data in my table. I put random in quotes because it is random within a set range of values not totally random as in totally unpredictable. Just to prove it really did work.

mysql> select id, Make, dealer from cars_cars limit 5;
+----+------------+---------------+
| id | Make       | dealer        |
+----+------------+---------------+
|  1 | Land Rover | Aldershot     |
|  2 | Land Rover | Basingstoke   |
|  3 | Land Rover | Wolverhampton |
|  4 | Land Rover | Aldershot     |
|  5 | Land Rover | Wolverhampton |
+----+------------+---------------+
5 rows in set (0.00 sec)

* I used where id > 0 because as mentioned in my previous post I have safe update mode turned on.

I am a Dummy

Way back when I started this blog just under 4 years ago I wrote a post called 'An Oracle Developers Journey', in the early days the blog focused on the transition from Oracle to MySQL. The irony was that after a year or so professionally I move back the other way and for the last 3 years or so it's been all Oracle for me.

We all make mistakes, I know I have made some massive ones in the past, some were saved by a decent back up others were not so easy to recover from. It's been a while since I made anything like a big mistake (certainly not one that was noticed ;), once bitten twice shy tends to make you think about the consequences a little more.

But having now switched back to MySQL again I find myself really worried I'm going to make a mistake, in Oracle it's UPDATE/INSERT or more worryingly DELETE and then a COMMIT, but in MySQL (at least the way we are running it) there is no commit stage. I'm currently working on development systems but I just know that one day I'm going to delete or update something I really don't want to.

Thankfully MySQL is clever enough to make up for my itchy delete finger and includes the --safe-updates startup option. But I love the alias which is --i-am-a-dummy. I'm man enough to admit to being a dummy, well at least prone to doing something dum from time to time and that's why the first thing I did when I switched back to MySQL was to include --i-am-a-dummy in my startup alias.

So hands up who else is a dummy?

p.s. if you don't know --safe-updates sets the following session system variables.

SET sql_safe_updates=1,
sql_select_limit=1000,
sql_max_join_size=1000000;

sql_safe_updates is the key as it stops you running an update or delete without some sort of where clause.

Adventures in Web Performance

I mentioned in my previous post that recently I have been working with web performance. Often when working with clients they want fast performing web sites but then proceed to give you all manner of other requirements which slow the site down to a crawl. Convincing a client to give you the time to look at performance isn't easy, while the gains in speed are there for all to see attributing some monetary value to that is close to impossible. In the longer term you might see an up turn in the traffic levels or a higher percentage of people taking a longer user journey on your site but whether that was a result of higher performance is very difficult to prove.

Thankfully one of our larger clients could see the benefit of having a dedicated resource looking at performance issues, all be it as a result of such poor performance that the sites became difficult to use during peek times. When a client keeps throwing stuff at you it's easy to take your eye off the performance issue and one thing that became clear was that while the tendency was to blame the database many of the things we looked at were nothing to do with that area.

Over the next few posts I'll discuss some of the things we looked at, how we did that and how we radically improved performance on some of our sites with some minimal changes.

Such a long time....

Wow, is it really that long since I updated this thing. I can't believe that it's been close to 3 years since I updated it regularly, but I suppose it is as I have been in my current job for that long and it was a transition from MySQL back to Oracle (and a lot more work) that meant I stopped posting in the first place.

The good news is I'm moving back to working with MySQL after 3 years away and that should give me some more opportunities to update the blog. While there have been some highlights over the 3 years much of the work I have been doing has been maintenance and the normal daily grind of work so it didn't offer that much that was interesting. Of late I have been doing some really cool stuff with web performance but I've been out of the blogging for so long I never really thought about sharing it.

Of course any regular readers will be long gone but the blog still ranks pretty high in Google so maybe some folks will stop by.

MySQL on a PS3

I work in IT for 2 reasons, firstly it pays well but secondly and most importantly it's fun. When I look back on jobs I had before I started working in IT, while it was never a problem getting up in the morning and the social aspects were great I would never have said that the job it self was fun.

It's been sometime since I last updated the blog, 6 moths or so and almost a year since I did on anything like a regular basis, the main reason as I have mentioned in the past is that if you have nothing to say then why bother saying it. Therefore I doubt anybody is listening any longer, but I suppose that's not really an issue.

I have been prompted to write because I have been doing some work on a site in MySQL, in the day job I use Oracle (but do some admin on MySQL sites from time to time) so I no longer have the experiences to blog about. But I recently needed to upgrade a family members web site which required some MySQL work. As it happens this coincided with a recent hardware upgrade, I used to have an X-Box 360 but it recently decided to die on me so I took the plunge to get a Playstation 3. As games consoles go it's pretty decent and with the addition of a Blue-Ray drive it means I can take full advantage of my new HD TV.

Getting back to the point, Sony allow you to format the PS3 hard drive in such a way that it can have two operating systems, the standard console one and also Linux. The install process looked easy and given that Sony fully supports this I thought I would give it a go. The PS3 allows the connection of a USB Keyboard and mouse so it's easy enough to get going. The install was painless enough (if a little long winded) but once it was done I now had a PS3 running Linux, of course one of the first things I did was have a go at running MySQL which of course runs like a dream.

So now my development machine is a Sony PS3 :)