Databases Running in the Cloud

cloudI’ve been playing around with running databases in the cloud recently. It’s quite simplistic stuff, just to get a feel for it and investigate the possibilities of using it for some projects at work. Here’s what I’ve got so far.

Overview:

Oracle:

MySQL:

SQL Server:

It’s hard to differentiate between the cloud providers if you are just using them to provide a VM and self managing a system on it. It’s just another box provider.

In contrast the DBaaS offerings are much more interesting. I really like what Amazon are doing with RDS for Oracle/MySQL/SQL Server. I think these would work very well for *our* MySQL and SQL Server installations, which tend to be quite simple. I’m not sure I can live with some of the restrictions for RDS for Oracle, but that’s probably because I’m a snobby DBA type, who thinks he knows best. :) The DBaaS for SQL Server on Azure is also really nice. You get less control than the RDS version, but maybe that’s a good thing.

You might have noticed I’ve not written much about Oracle Cloud yet. I should be getting a trial of the platform this month, so I will be able to fill in those gaps then.

Cheers

Tim…

MySQL : What management tools do you use?

A quick question out to the world. What management tools do you use for MySQL?

We currently have:

  • MySQL Workbench : It’s OK, but I don’t really like it. It feels like half a product compared to tools I’ve used for other database engines…
  • phpMyAdmin : I’ve used this on and off for over a decade for my own website. While I’m typing this sentence, they’ve probably released 4 new versions. :) We have an installation of this which we use to access our MySQL databases should the need arise.
  • mysql Command Line : I use the command line and a variety of scripts for the vast majority of the things I do.

When I’m working with Oracle, my first port of call for any situation is to use SQL*Plus along with a variety of scripts I’ve created over the years. The performance stuff in Cloud Control (if you’ve paid for the Diagnostics and Tuning option) is the big exception to that of course.

I still consider myself a newbie MySQL administrator, but I’ve found myself spending more and more time at the command line, to the point where I rarely launch MySQL Workbench or phpMyAdmin these days. I’m wondering if that is common to other MySQL administrators, or if it is a carry over from my Oracle background…

Enquiring minds need to know!

Cheers

Tim…

MySQL Upgrades

I read Wim Coekaerts post about the MySQL 5.6.20-4 this morning. I logged on to my server and did the following command as root.

# yum update -y

That’ll be the upgrade done then… :)

If you are using MySQL on Linux you can use the MySQL Repository for your distribution, rather than using the bundled MySQL version, to make sure you stay up to date with the latest and greatest. As long as you stay within a point release (5.6, 5.7 etc.) of the latest version, upgrades should really be as simple as a “yum update”.

I’ve started the ball rolling for the upgrades to the MySQL servers at work. That will take a bit longer because of the required testing. :)

Now I know that Oracle is a very different beast to MySQL or SQL Server, but the patches for MySQL and SQL Server are so much easier that patching Oracle, it’s not surprising people gravitate to them. I’m sure the pluggable database stuff in 12c is going to simplify things somewhat, but it’s still not going to be anywhere near as simple as this stuff.

Cheers

Tim…

Oracle Linux and MySQL : Progress

A few months ago I wrote about some MySQL on Oracle Linux migrations we were working through. It’s been a long time coming, but last weekend was the go-live for this batch of migrations. So far so good! :)

Most of the elapsed time since my last post on this subject has been spent with the developers and users testing the migrations.

The process has taken a bit longer than some people would have liked. Rather than doing a quick and dirty upgrade, I’ve been pushing to get things done properly. Since I was the person who set up the infrastructure, I’ve been extremely anal about levels of privilege I’m granting. This has caused some minor rewrites of applications, which were essentially relying on admin privileges to perform some actions. Not any more! :)

I’m no MySQL guru, but I think what we have now is pretty darn decent, especially compared to where we started. I guess time will tell how bold a statement that is. :)

Onwards and upwards…

Cheers

Tim…

Oracle Linux and MySQL

I’m in the process of taking on some of the MySQL databases in my company. The first ones are MySQL 4.1 running on Windows, so we are upgrading them to MySQL 5.6 on Oracle Linux. As with many of our systems, these will be running on VMware virtual machines.

Since the current installations are so old, we are planning on dumping out the data and creating fresh installations on the new systems. Based on the advice I got from Ronald Bradford and Sheeri Cabral, we are also taking this opportunity to switch to InnoDB and utf8, rather than MyISAM and latin1 that are currently used.

We are using the MySQL yum repository for the installation, so we can be on the latest MySQL version, rather than that shipped as part of Oracle Linux (or RHEL) 6.5. The other neat thing about this is it takes care of point release upgrades as part of the “yum update” process.

So far all my testing has been done on VMs running on my PC, but we are soon going to start rolling this out. It should be an interesting piece of work. The developers are doing a bunch of testing with InnoDB and utf8 to see what issues we come up against…

Cheers

Tim…

Update. For those new to MySQL, you might like to read this post by Patrick Hurley.

 

mySQL: OR, UNION and UNION ALL

I was doing some SQL tuning on mySQL today and I learned a few interesting things.

Let’s take an example of a simple query where we have an OR of two mutually exclusive conditions, each on indexed columns.

SELECT *
FROM   t1
WHERE  user_id = 1
OR     location_id = 2;

In mySQL there is a feature called an Index Merge that, as the name suggests, runs a scan of the two indexes and merges the results together. Kinda neat.

I guess it’s not disimilar to what Oracle does with bitmap conversions, but it looks a little less scary in the middle of a massive plan than something like this. :).

-----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |    20 |   260 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T1               |    20 |   260 |     6   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                  |       |       |            |          |
|   3 |    BITMAP OR                     |                  |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | T1_LOCATION_ID_I |       |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                  |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | T1_USER_ID_I     |       |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

As it turned out, the query I was tuning was too complicated to take advantage of an index merge, so I tried converting it to use a UNION ALL. Remember, the conditions are mutually exclusive, so there was no need to worry about duplicates. Taking the example of the previous query, it would look something like this.

SELECT *
FROM   t1
WHERE  user_id = 1
UNION ALL
SELECT *
FROM   t1
WHERE  location_id = 2;

The real query I was tuning performed much better with a UNION ALL, but I thought I better check to see if mySQL had any gotchas associated with UNION and UNION ALL. It turns out that both UNION and UNION ALL can create internal temporary tables to get the job done. I suppose it’s not that different to Oracle doing sorts in memory or pushing them out to disk, but it did kinda freak me out when I first read it. :)

Although I could never see myself devoting that much time to another engine, it is quite interesting seeing how they get the job done.

Cheers

Tim…

MySQL Disaster Recovery…

Within a couple of weeks of doing our disaster recovery testing we’ve had a number of disasters, one of which was our Bugzilla server. The recovery was pretty sweet. The database engine (mySQL) was loaded as part of the Linux install and recovering the database was done with one command:

mysql -u root –password=”myPassword” bugs < backup.sql