Author: Walter Heck

Posted on by — 3 Comments

Good Practice / Bad Practice: Off-site Backups

In today’s gp/bp an open door will be kicked in: take your backups offsite!
I was actually tempted to create a poll to see how many of you do not have proper backups, and how many of you do not take those backups offsite. It is a simple piece of advice and relatively simple to set up. Offsite in this case would ideally be physically offsite: to a different server in a different building in a different location. A start however is to take them to a different server. And don’t make the mistake of thinking a different VPS on the same physical server is good enough. True, that will protect you from operating system failure of the guest, but it will likely not protect you from hardware failure, or operating system failure on the host OS.

Also, take good care of how you are getting your backups offsite. A normal FTP connection might do the job, but it is hardly secure. Ideally, use SFTP or rsync over ssh to stream your backups offsite.
Some people still take their backups offsite by physically moving a cd, dvd or tape from one location to another. It’s a good start, but in this age of cheap broadband, you might want to think about doing this online. A cron-job is much less likely to not run than it is for you to forget to move that tape.

In our good practice / bad practice series, we will provide you with byte/bite sized pieces of advice on what we consider good (or bad) practice in MySQL-land. The topics can be just about anything, so expect random things to come up. Also, the level of advancedness greatly varies. A topic might be a no-brainer for some, a reminder for others and a revelation for a third person. We strive to tender to all of you!

Posted on by — 3 Comments
Posted on by — 1 Comment

MySQL projects on launchpad, sourceforge, google code and forge.mysql.com

While doing some work for the MySQL MMM project, I got distracted and browsed around for a bit. I started searching for MySQL on google code, and then expanded that search into launchpad, sourceforge and of course forge.mysql.com.
I found that there are literally thousands of FOSS MySQL projects on these sites. No surprise really, but still not something we stop to think about every day.
I thought I would share that with you here so you can go and have a look for yourself to see if you see anything that might be useful to you. Of course if you do, please blog about it so we can all benefit from it.

DISCLAIMER: many of these projects are in development, it depends on the project really. Before you go off and use a new tool you have never heard of in production, make sure to test it properly beforehand.
Finding out some tool doesn’t work as advertised on a production system is pretty nasty.

Posted on by — 1 Comment
Posted on by — 2 Comments

Good Practice / Bad Practice: CREATE TABLE and the Storage Engine

When you write your create table statements, always make sure that you make them non-ambiguous. That way even though other servers might have different configurations, you make sure your table will be created in the same way.
Imagine for instance you are developing an application on a development server, nicely storing all the scripts you need to create the same database on your production server. If the same script creates a table differently on both servers, that might cause you a lot of headache later on. At Open Query, we strive to minimise (or preferrably eliminate) headaches.

One of the parts of the create table statement that has the largest impact is the storage engine specification. When you omit the storage engine from the create table statement, your table is automatically created with the default storage engine type configured for the server. Since the storage engine is a very important choice when designing your tables, you want to make sure that it is always the correct type.

Here’s an example: instead of writing CREATE TABLE city (city_id int, city_name varchar(100)) you should write: CREATE TABLE city (city_id int, city_name varchar(100)) ENGINE=InnoDB

It is a simple adjustment, but it will save you from possible problems if you just make a habit out of specifying the storage engine.

But wait, there one more thing! It’s also very important you have sql_mode=NO_ENGINE_SUBSTITUTION in your my.cnf, otherwise your table may still silently become a MyISAM table if your desired engine (for any reason) is disabled in the binary, configuration, or at runtime. With this setting error, such a situation will cause an error – so you know for sure.

In our good practice / bad practice series, we will provide you with byte/bite sized pieces of advice on what we consider good (or bad) practice in MySQL-land. The topics can be just about anything, so expect random things to come up. Also, the level of advancedness greatly varies. A topic might be a no-brainer for some, a reminder for others and a revelation for a third person. We strive to tender to all of you!

Posted on by — 2 Comments
Posted on by — 14 Comments

Good Practice / Bad Practice: Table Aliases

When writing queries, try making a habit out of using short table aliases, no matter how small the query is. Try using an alias that is as short as possible (one letter if possible, two or three when necessary) to avoid clutter in your queries.
Use these aliases in all places where you refer to a field, no matter if it would technically not be necessary. It will make your queries readable, and if you later need to join in other tables, you will not run the risk of ambiguous field names.

Let’s look at an example using the World database. The following two queries both select the 10 biggest cities by population in the continent of Africa, with a population between 1 and 1,5 million.

Good practice:

SELECT
  ci.name AS city_name,
  ci.district,
  co.name AS country_name,
  ci.population
FROM
  Country AS co
  INNER JOIN City AS ci ON (co.code = ci.countrycode)
WHERE
  (co.continent = 'africa') AND
  (ci.population BETWEEN 1000000 AND 1500000)
ORDER BY
  ci.population DESC
LIMIT 10;

bad practice:

select
  City.name,
  District,
  Country.name,
  City.population
from
  Country
  inner join City on City.countrycode = Country.code
where
  continent = 'africa' and
  City.population between 1000000 and 1500000
order by
  City.population desc
limit 10;

Both queries will execute and return the same results, but the first one is much more readable. Issues with respect to the table aliasing in the second query:
1) It is unclear without looking at the table structure whether District and continent are fields of the city or the country table.
2) The full table name needs to be specified for every field that would otherwise be ambiguous. This clutters the query and thus makes it less readable.
3) if I ever need to change the name of the table I am querying, I have to change it in all places instead of just in the from clause. A good example is when I was writing this article: I used lower case c’s for the country and city table names. So after writing the whole statement I had to go back in and change that lowercase c to an uppercase c in close to a dozen places, while in the first query I just needed to change it in one place.

In our good practice / bad practice series, we will provide you with byte/bite sized pieces of advice on what we consider good (or bad) practice in MySQL-land. The topics can be just about anything, so expect random things to come up. Also, the level of advancedness greatly varies. A topic might be a no-brainer for some, a reminder for others and a revelation for a third person. We strive to tender to all of you!

Posted on by — 14 Comments
Posted on by

MySQL conference ’09 Keynote: “State of the Dolphin” by Karen Padir

Here is a semi-liveblog from the opening keynote of the 2009 MySQL Conference & Expo on April 21st in Santa Clara, California. Karen Padir presents “State of the Dolphin”.

The room is big, with 2 screens on each side of the central stage. It seems to be completely filling up, but I got a good spot on the second row in the front. Directly around me I see Dups, Sheeri, Giuseppe, Kai, Mark Callaghan and many other faces I know, even they don’t necessarily know me 🙂

Opening music, fancy animations. Quotes from satisfied customers, combined with tribal-style music and statistics.

Colin Charles kicks off. Good morning, welcome. learn a lot, provide feedback. Thanking sponsors in the first minute..

Introduces Karen Padir, she promised to bring more women to the conference next year.

Welcome, she introduces herself, tells where she is from and what she did in her education. Jokes: “Odds are good but the goods are odd” about sun’s crowd.
Went to Redhat for a year in between working for Sun, because of opensource interest. She came back to Sun because of Sun’s OSS commitment. Shows a slide with a bunch of different Open Source-ish projects at Sun.
She worked with Glassfish for a long time, encourages people to go by them at the expo hall.

Now, on Oracle. Talks about Oracles commitment to opensource. names two examples of sideline involvement of Oracle in Sun projects. Doesn’t really come across, but might be me.

Everyone uses MySQL everyday, whether on Facebook, Google or whatever.
MySQL had a great year! some stats fly by. 5.1 has gained momentum: 3+M downloads in 100 days. 54% of users have 5.1 in production.

Sheet: What I’ve heard. Waht she has learned in the past 2 months since she has been on the job.

MySQL 5.4 is released today. What happened to 5.2 and 5.3 http://www.mysql.com/5.4
– innodb scalability: up to 16 way x86
– subquery optimizations
– new query algorithms

Some stats fly by for InnoDb that look promising, but i wonder what will hapopen in real life.

Introduces Ken Jacobs. Ken starts talkng about where the improvements of 59% come from: opensource ecosystem.
5.4 will have more features in teh near future. Fast online index creation, compressed table storage. Will be intorduced in the next weeks.
Ken announces new product: embedded InnoDb. low level non-sql api.
karen gives him acquirer of teh year award, with a bottle of vodka. Whoever comes with 9 billion next year will win then 🙂

Karen takes over again, introduces MySQL cluster 7.0. It disappears before I can look at the slide, apparently not that important 🙂
Next: MySQL query analyser. She asks who uses it. very few hands. Pulls mark Matthews and Gary Whizin up on the stage with a joke. They work for MySQL Monitor engineering team.
They tell a story about what happens when you have a slow app. The oldway: dig in and have not so much fun. New way: enterprise monitor. Mark showcases the tool by analyzing an off-the -helf CRM, not very interesting.

Karen comes back, asks all former and current MySQL employees to stand up. She thanks them all, quite genuinely. Nice thought 🙂

Community contributions. Says 5.4 is a good example of community contributions. Sounds strange since noone knew it was coming.

commits to monthly updates on all shipping community releases.

Invites Kaj Arno to the stage. Kaj says he wants to have documentation GPL’ed.

Next: MySQL drizzle project: she calls it shepherded by Sun/MySQL.

Next: MySQL awards.
Partner awards: Intel, infobright, lifeboat.
Applications of the year: Zappos.com, Alcatel-lucent and Symantec.
Community: Marc Delisle, Ronald Bradford and Shlomi Noach

Announces the rest of the conference.

Colin Charles announces Mark Callaghan (turns out he was sitting right in front of me 🙂 ) from google. introduces himself.
Talks about adapting MySQL to their needs, easier than making apps adapt to MySQL. Talks about using MySQL at google, cannot name numbers unfortunately.
many primary shards, with many replica’s per shard. They are happy with MySQL.
Tells that he is amazed at how many slaves can be coupled with a master. They found out by trial-and-error.

Thanks, Monty, MySQL people, Heikki Tuuri and company. inspiration provided by Yasufumi and Percona.

History of MySQL at google: first MyISAM and another RDBMS. after that 4.0 and Inno. After mark arrived they upgraded to 5.0.

Talks abotu features of an enterprise DBMS, the problems they overcame, how they test new MySQL builds: stress test, sample workloads, killing off servers, check for compiler warnings and check for data drift (test same workload with old and new build and see if they are the same).

How to deploy: automatically searching aggregate error logs. Keep track of number of crashes on daily basis. Automated removal of machines from service.

How to monitor: user_stats and table_stats patches. Monitor top-down, they use more bash and awk then mysql enterprise monitor 🙂

Talks abotu features they added (and sometimes removed) from/to replication. Crash safe-slaves, Semi-sync replication, global transaction ID’s
Performance features they added. InnoDb improvements, backport connection pool feature from MySQL 6. Stresses they are not the only ones doing it (Percona, Sun, Innodb)

Manageability features they added and other features they added: row-change logging, Flapulator (bash and awk scripts in cron jobs checking for crashes etc.), Online data drift checking.

Tells about the crises they have had and the open problems they have, the runs out of time.

Next it is on to Kickfire, who are a big sponsor. I end my liveblog here 🙂

I will post pictures asapI’ve posted photos, although the camera was pretty crappy, so it might not be very good quality. Donations for a new camera are welcome 😉

Zak’s uploaded the photos for me to his flickr account – view the set here.

Posted on by