In this evening keynote from YUIConf 2012, the legendary JavaScript architect Douglas Crockford discusses one of the most elusive of all programming concepts: the monad. With this talk, Crockford attempts to break the long-standing Monad tutorial curse by explaining the concept and applications of monads in a way that is actually understandable to the audience.

Are You Getting the Best Out of Your MySQL Indexes – PDF slides
Getting Rid of Scheduled Tasks Using MySQL Events – PDF slides
Better JOINs and Subqueries – PDF slides

Hope you enjoy them!

Taylor Swift, “Speak Now” – be vocal, if you have an idea, do not be shy about it. In an interview, “don’t stop talking” – meaning show them your passion – but don’t force it, of course. Ask lots of questions, do not make assumptions. And if you get stuck in a problem, you can reason your way through it by talking out log.

Bonnie Raitt – “Let’s Give Them Something To Talk About”. Communicate! Let your manager know what’s going on and what you are doing. “Give them stories to tell about you” – and good ones too! Trip reports if you go on a trip, summary status e-mails, one-on-one meetings, etc.

Aretha Franklin – “Respect”. At the end of the day, diversity of opinion, education levels, backgrounds, is key to having successful business ideas. You can learn something from everyone. You can go around at a party, meet people and figure out what they are better than you at.

Frank Sinatra – “Luck Be a Lady”. At some other conference, Jennifer saw a formula for success: Success = hard work + intelligence + luck. She did not like that, because luck is a bunch of randomness, and if we work hard and are smart, we should be successful, right? But it’s completely true that luck is part of the equation. For example, in an interview. You can control working hard and you can control learning, and you will be well-positioned for when opportunity knocks.

Bette Middler – “Wind Beneath My Wings”. Role models – people that you worship from afar, perhaps stalk on Twitter, but probably won’t have a relationship with. Mentors are folks that are actively helping you grow, which you have a relationship with. You can choose a mentor to help you work on a skill or set of skills, and you can choose different people based on what they are good at. Someone who is good at MySQL might not be good at blogging or work/life balance. Jennifer challenges all of us to be role models to other people by speaking and blogging, because those folks are seen as industry experts, so you will become a role model.

Brittany Spears – “Oops I Did It Again”. The importance of making mistakes – making mistakes is good. Take big risks, because when a mistake does happen, you can learn from them. When a panel of successful tech women were asked what they would do differently, they said they would have taking more big risks.

Bill Withers – “Lean on Me”. Delegate if you need to. Ask people for help. Out of time, health and money, you can have any 2 of the 3. Young folks usually have time and health but not money. In your 30′s, you might have health and money but not time. And when you get older, you have time and money, but not health. Optimize for what you do not have. If you do not have time, then make it so you have more time – e.g. buying pre-made salad or getting a cleaning lady is a money/time tradeoff.

Journey – “Don’t Stop Believing”. Believe in yourself. Imposter Syndrome at Wikipedia. If you do not know, say you do not know something.

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

The purposes of doing this type of blog post are:
0) Answering “So what does a DBA do, anyway?”
1) Answering “DBA? At Mozilla? Does Firefox have a database? Why does Mozilla have databases, and what for?”
2) Showing what the DB team does for Mozilla, so that folks will understand that “just keeping things working” is actually a lot of work. It also helps compile yearly reviews of accomplishments.

We are also starting to get some metrics information. This month we started easy – number of MySQL and Postgres machines, number of unique databases (mysql, information_schema, performance_schema and test are ignored, and duplicates, like the same database on a master and 2 slaves, are ignored), and version information.

As of today, we have 9 unique databases across 8 Postgres servers in 4 clusters, with 6 being Postgres 9.0 and 2 being Postgres 9.2 – we are currently upgrading all our Postgres databases to 9.2 and expect that by the end of December all servers will be using 9.2.

We have 427 unique databases across 98 MySQL DB machines in 20 clusters, with 3 being MySQL 5.0, 71 being MySQL 5.1 (mostly Percona’s patched 5.1), and 24 being MariaDB 5.5.

And in the last week of October and the month of November, we have:

• Documented 4 more of our Nagios checks.
  
• Started to upgrade Postgres databases to Postgres 9.2
  
• Decommissioned a legacy database cluster for Firefox themes.
  
• Built a new database cluster (complete with monitoring and backups) for a new Sentry implementation.
  
• Upgraded X machines for general operating system updating purposes and to ensure that be2net drivers are up-to-date; out-of-date drivers can (and have!) caused servers to crash. (b1-db1, b1-db2, addons1, addons2, 3,4,5,
  
• Upgraded MySQL 5.0 to MySQL 5.1 across X clusters and Y machines (a01, b02, b2, 7)
  
• Did a quarterly purge of Crash Stats data.
  
• Had to re-sync 6 slaves when a transaction rolled back on the master, but some of the tables modified were MyISAM. So the master had data in some tables that was out of sync with the slaves.
  
• Assisted in converting to use UTC timestamps in the Elmo database behind the Mozilla localization portal and the Bugzilla Anthropology Project, prompting a blog post on converting timezone-specific times in MySQL.
  
• Decommissioned a legacy “production generic” database cluster that had over 60 databases on it.
  
• Built a 5th database backup instance due to growing backup needs.
  
• Changed binary log format to MIXED on our JIRA installation due to JIRA requirements and an upgrade to MySQL 5.1 issuing warnings that MySQL 5.0 had not.
  
• Added checksums to the database cluster that runs Input and Firefox about:home snippets.
  
• Archived and dropped the database behind Rock Your Firefox.
  
• Exported Bugzilla data for a research project. Did you know if you are doing academic research, you can get a copy of Mozilla’s public Bugzilla data?
  
• Gave read-only database access to a developer behind the Datazilla project.
  
• Updated the email list for vouched Mozillians.
  
• Backfilled missing crash-stats data after some failed cron scripts.
  
• Cleared some junk data from the Datazilla databases.
  
• Added new custom fields to our implementation of Bugzilla for upcoming release versions: Firefox 20, Thunderbird 20, Thunderbird ESR 20 and seamonkey 217.
  
• Added 10 new machines to the Graphs database, and added new sets of machines for Mozilla ESR 17 and Thunderbird ESR 17.
  
• Gave read-only database access to the two main leads of the Air Mozilla project.
  
• Debugged and turned off a 10-second timeout in our load balancing pool that was causing Postgres monitors and processors to lose connection to their databases.
  
• Discovered that the plugins database actually does better with the query_cache turned on, and tuned its size.
  
• Tweaked tokudb_cache_size and innodb_buffer_pool_size on our Datazilla databases so that less swap would be used.
  
• Created 2 read/write accounts for 2 people to access the development database for Mozillians.
  
• Gave access to Datazilla databases for staging.
  

Wednesday, Nov 28th was my 1-year anniversary at Mozilla. Tomorrow is December! 2012 went by very quickly.

As before I will show a simple module for a common scenario. Rather than considering this module a blueprint for every module out there you should instead study its design and use it as a starting point when writing your own modules. You can build on it and adapt it but the basic approach should translate well to more complex modules.

I should note that while I work for Puppet Labs I do not know if this reflect any kind of standard suggested approach by Puppet Labs – this is what I do when managing my own machines and no more.

The most important deliverables

• A module should have a single entry point where someone reviewing it can get an overview of it’s behavior
• Modules that have configuration should be configurable in a single way and single place
• Modules should be made up of several single-responsibility classes. As far as possible these classes should be private details hidden from the user
• For the common use cases, users should not need to know individual resource names
• For the most common use case, users should not need to provide any parameters, defaults should be used
• Modules I write should have a consistant design and behaviour

The module layout I will present below is designed so that someone who is curious about the behaviour of the module only have to look in the init.pp to see:

• All the parameters and their defaults used to configure the behaviour of the module
• Overview of the internal structure of the module by way of descriptive class names
• Relationships and notifications that exist inside the module and what classes they can notify

This design will never remove the need for documenting your modules but a clear design will guide your users in discovering the internals of your module and how they interact with it.

More important than what a module does is how accessible it is to you and others, how easy is it to understand, debug and extend.

Thinking about your module

To go from nowhere to having NTP on your machine you would have to do:

• Install the packages and any dependencies
• Write out appropriate configuration files with some environment specific values
• Start the service or services you need once the configuration files are written. Restart it if the config file change later.

There is a clear implied dependency chain here and this basic pattern applies to most pieces of software.

These 3 points basically translate to distinct groups of actions and sticking with the above principal of single function classes I will create a class for each group.

To keep things clear and obvious I will call these class install, config and service. The names don’t matter as long as they are descriptive – but you really should pick something and stick with it in all your modules.

Writing the module

class ntp::install {
   package{'ntpd':
      ensure => $ntp::version
   }
}
 
class ntp::config {
   $ntpservers = $ntp::ntpservers
 
   File{
      owner   => root,
      group   => root,
      mode    => 644,
   }
 
   file{'/etc/ntp.conf':
         content => template('ntp/ntp.conf.erb');
 
        '/etc/ntp/step-tickers':
         content => template('ntp/step-tickers.erb');
    }
}
 
class ntp::service {
   $ensure = $ntp::start ? {true => running, default => stopped}
 
   service{"ntp":
      ensure  => $ensure,
      enable  => $ntp::enable,
   }
}

Here I have 3 classes that serve a single purpose each and do not have any details like relationships, ordering or notifications in them. They roughly just do the one thing they are supposed to do.

Take a look at each class and you will see they use variables like $ntp::version, $ntp::ntpservers etc. These are variables from the the main ntp class, lets take a quick look at that class:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# == Class: ntp
#
# A basic module to manage NTP
#
# === Parameters
# [*version*]
#   The package version to install
#
# [*ntpservers*]
#   An array of NTP servers to use on this node
#
# [*enable*]
#   Should the service be enabled during boot time?
#
# [*start*]
#   Should the service be started by Puppet
class ntp(
   $version = "present",
   $ntpservers = ["1.pool.ntp.org", "2.pool.ntp.org"],
   $enable = true,
   $start = true
) {
   class{'ntp::install': } ->
   class{'ntp::config': } ~>
   class{'ntp::service': } ->
   Class["ntp"]
}

This is the main entry point into the module that was mentioned earlier. All the variables the module use is documented in a single place, the basic design and parts of the module is clear and you can see that the service class can be notified and the relationships between the parts.

I use the new chaining features to inject the dependencies and relationships here which surfaces these important interactions between the various classes back up to the main entry class for users to see easily.

All this information is immediately available in the obvious place without looking at any additional files or by being bogged down with implementation details.

Line 26 here requires some extra explanation – This ensures that all the NTP member classes are applied before this main NTP class so that cases where someone say require => Class["ntp"] elsewhere they can be sure the associated tasks are completed. This is a light weight version of the Anchor Pattern.

Using the module

Let’s look at how you might use this module from knowing nothing.

Ideally simply including the main entry point on a node should be enough:

include ntp

This does what you’d generally expect – installs, configures and starts the NTP service.

After looking at the init.pp you can now supply some new values for some of the parameters to tune it for your needs:

class{"ntp": ntpservers => ["ntp1.example.com", "ntp2.example.com"]}

Or you can use the new data bindings in Puppet 3 and supply new data in Hiera to override these variables by supplying data for the keys like ntp::ntpservers.

Finally if for some or other related reason you need to restart the service you know from looking at the ntp class that you can notify the ntp::service class to achieve that.

Using classes for relationships

There’s a huge thing to note here in the main ntp class. I specify all relationships and notifies on the classes and not the resources themselves.

As personal style I only mention resources by name inside a class that contains that resource – if I ever have to access a resource outside of the class that it is contained in I access the class.

I would not write:

class ntp::service {
   service{"ntp": require => File["/etc/ntp.conf"]}
}

These are many issues with this approach that mostly come down to maintenance headaches. Here I require the ntp config file but what if a service have more than one file? Do you then list all the files? Do you later edit every class that reference these when another file gets managed?

These issues quickly multiply in a large code base. By always acting on class names and by creating many small single purpose classes as here I effectively contain these by grouping names and not individual resource names. This way any future refactoring of individual classes would not have an impact on other classes.

So the above snippet would rather be something like this:

class ntp::service {
   service{"ntp": require => Class["ntp::config"]}
}

Here I require the containing class and not the resource. This has the effect of requiring all resources inside that class. This has the effect of isolating changes to that class and avoiding a situation where users have to worry about the internal implementation details of the other class. Along the same lines you can also notify a class – and all resources inside that class gets notified.

I only include other classes at the top ntp level and never have include statements in my classes like ntp::confg and so forth – this means when I require the class ntp::config or notify ntp::service I get just what I want and no more.

If you create big complex classes you run the risk of having refreshonly execs that relate to configuration or installation associated with services in the same class which would have disastrous consequences if you notify the wrong thing or if a user do not study your code before using it.

A consistant style of small single purpose classes named descriptively avoid these and other problems.

What we learned and further links

There is a lot to learn here and much of it is about soft issues like the value of consistency and clarity of design and thinking about your users – and your future self.

On the technical side you should learn about the effects of relationships and notifications based on containing classes and not by naming resources by name.

And we came across a number of recently added Puppet features:

• Parameterized classes
• Chaining Arrows
• Data Bindings as introduced in Puppet 3

Parameterized Classes are used to provide multiple convenient methods for supplying data to your module – defaults in the module, specifically in code, using Hiera and (not shown here) an ENC.

Chaining Arrows are used in the main class to inject the dependencies and notifications in a way that is visible without having to study each individual class.

These are important new additions to Puppet. Some new features like Parameterised classes are not quite ready for prime time imho but in Puppet 3 when combined with the data bindings a lot of the pain points have been removed.

Finally there are a number of useful things I did not mention here. Specifically you should study the Puppet Style Guide and use the Puppet Lint tool to validate your modules comply. You should consider writing tests for your modules using rspec-puppet and finally share it on the Puppet Forge.

And perhaps most importantly – do not reinvent the wheel, check the Forge first.

We confirmed that NDB does not perform well using m1.large instances. In fact, it’s totally unacceptable -  no setup should ever have a minimum latency of 220ms - so m1.large instances are not an option. Apparently the instances get CPU bound, but CPU utilization never goes above ~50%. Maybe top/vmstat can’t be trusted in this virtualized environment?

So, why not use m1.xlarge instances? This sounds like a better plan!

As in the original post, our dataset is 15 tables of 2M rows each, created with:

./sysbench --test=tests/db/oltp.lua --oltp-tables-count=15 --oltp-table-size=2000000 --mysql-table-engine=ndbcluster --mysql-user=user --mysql-host=host1 prepare

Benchmark against NDB was executed with:

for i in 8 16 32 64 128 256

do

./sysbench --report-interval=30 --test=tests/db/oltp.lua --oltp-tables-count=15 --oltp-table-size=2000000 --rand-init=on --oltp-read-only=off --rand-type=uniform --max-requests=0 --mysql-user=user --mysql-port=3306  --mysql-host=host1,host2 --mysql-table-engine=ndbcluster --max-time=600 --num-threads=$i run > ndb_2_nodes_$i.txt

done

After we shutdown NDB, we started Galera and recreated the table, but found that running sysbench was failing. A suggestion from Hingo was to use --oltp-auto-inc=off, which worked.

Our benchmark against NDB was executed with:

for i in 8 16 32 64 128 256

do

./sysbench --report-interval=30 --test=tests/db/oltp.lua --oltp-tables-count=15 --oltp-table-size=2000000 --rand-init=on --oltp-read-only=off --rand-type=uniform --max-requests=0 --mysql-user=user --mysql-port=3306  --mysql-host=host1,host2 --mysql-table-engine=ndbcluster --max-time=600 --num-threads=$i --oltp-auto-inc=off run > galera_2_nodes_$i.txt

done

Below are the graphs of average throughput at the end of 10 minutes, and 95% response time.

Galera clearly performs better than NDB with 2 instances!

But things become very interesting when we graph the reports generated every 10 seconds.

Surprised, right? What is that?

Here we see that even if the workload fits completely in the buffer pool, the high number of TPS causes aggressive flushing.

We assume the benchmark in the Galera blog post was CPU bound, while in our benchmark the behavior is I/O bound.

We then added another 2 more nodes (m1.xlarge instances), but kept the dataset at 15 tables x 2M rows , and re-ran the benchmark with NDB and Galera. Performance on Galera gets stuck, due to I/O. Actually, with Galera, we found that performance on 4 nodes was worse than with 2 nodes; we assume this is caused by the fact that the whole cluster goes at the speed of the slower node.

Performance on NDB keeps growing as new nodes are added, so we added another 2 nodes for just NDB (6 nodes total).

The graphs show that NDB scales better than Galera, which is not what we expected to find.

It is perhaps unfair to say that NDB scales better than Galera, but rather that NDB checkpoint causes less stress on I/O than InnoDB checkpoint, thus the bottleneck is on InnoDB and not Galera itself. To be more precise, the bottleneck is on slow I/O.

The follow graph shows the performance with 512 threads and 4 nodes (NDB and Galera) or 6 nodes (only NDB). Data collected every 30 seconds.

HTTP/1.1

We’re now out of Working Group Last Call on all of our “core” documents, so the editors are working through the issues that brought up. As soon as that’s done, we’ll go to IETF Last Call, and hopefully soon after well have a number of new RFCs defining HTTP/1.1.

Here, you can see the upswing in number of issues during our WGLC period:

To see for yourself, use the "work-in-progress" documents linked from our home page.

As part of that process, I also spent some time updating the parts of the documents that detail changes from RFC2616, since this will be the easiest way for most developers to get an idea of what’s changed. See:

• part 1: Messaging - Changes from RFC2616
• part 2: Semantics - Changes from RFC2616
• part 4: Conditional Requests - Changes from RFC2616
• part 5: Range Requests - Changes from RFC2616
• part 6: Caching - Changes from RFC2616
• part 7: Authentication - Changes from RFC2616 and RFC2617

Note that these lists are by no means complete, and they’ll likely change more before we publish.

HTTP/2.0

We also started work in earnest on HTTP/2.0, with initial discussions focusing on header compression and the upgrade mechanism. We now have a first draft (which is just a straight copy of the SPDY document, to give us a decent basis for future diffs) and the beginnings of an issue list.

The rough approach to upgrade being discussed is to use something like NPN if the connection is using TLS; we’ve communicated that requirement to the TLS Working Group, and they have decided (with a little nudging from their AD ;) to begin work on that. Note that NPN is just one proposal in this space.

If TLS isn’t being used, we’re looking at using HTTP upgrade as a base; see Gabriel and Willy’s draft for a good description of the considerations around that. Furthermore, we want to be able to optimise it, potentially using a DNS record (see Eliot’s new draft for a proposal), and perhaps a header like SPDY’s Alternate-Protocol.

For compression, we have a number of proposals to replace zlib, since CRIME took it off the table. So far, the only one with an implementation is Roberto’s; we’d like to be able to do a bake-off and use a common set of sample headers to compare them.

To keep things moving, we’ve scheduled an interim meeting for HTTP/2.0 issues in late January. If you’d like to come, please respond by the deadline; be aware, however, that this will be very much a working session.

Finally, some people may be interested to know that we now have a http_2 twitter account that will occasionally spout HTTP/2.0-related news; for those who want to track the effort without all of the details, it may be what you’re looking for.

Here are a bunch of the HTTP-related folks having dinner in Atlanta at the conveniently in-hotel Trader Vic’s:

In this presentation from YUIConf 2012, Mojito engineer Caridy Patiño talks about the Mojito project and its usefulness for YUI developers. In particular, Caridy discusses Mojito’s rich offering of boilerplate and building capabilities for YUI projects, with a variety of options to build traditional YUI web apps, mobile apps to deploy on devices, and Node.JS applications as well. Caridy also describes how Mojito leverages YUI library and YUI tool chains to build at scale.

Collecting data

It’s quite easy to collect statistics about index usage in Percona Server (and others) using the User Statistics patch.  By enabling ‘userstat_running’, we start to get information in the INFORMATION_SCHEMA.INDEX_STATISTICS table.  This data collection does add some overhead to your running server, but it’s important to leave this running for a good long while to get a good dataset that is representative of as much of your workload as possible.

If you miss collecting index stats while some occasional queries run, you run the risk of dropping indexes that are being (seldomly) used, but are still important for the health of your system to have.  This may or may not impact you, but I’d highly recommend you manually review the list of unused indexes being generated above before you simply drop them.

Depending on your sensitivity to production load, you may therefore want to run this several days, or just sample different short windows during your normal production peak.  In either case, you may want to compare or repeat this index analysis, so let’s setup a separate schema to do this.  Its important that this index analysis is on a server with your full production dataset loaded, but it could be a master, or just a slave somewhere (just be careful not to break replication!).

mysql> create schema index_analysis;

If our index_statistics are collecting on the same server, then we can simply get a snapshot of it into our schema with one command:

mysql> create table index_analysis.used_indexes select * from information_schema.index_statistics;

If the stats come from some other server, then you may need to dump and load a copy of that table into your working index_analysis schema.

Merging stats from several servers

In the case of this client, they had a master and several slaves taking read traffic.  The index workload on these two sets of servers was different and I wanted to make sure I considered the index statistics from both of these sources.  Be sure you include all relevant index stats from all aspects of your application, otherwise garbage-in, garbage-out and you risk dropping necessary indexes.

To accomplish merging multiple result sets, I gathered statistics from both their master and slave and loaded them into my schema as separate tables.  Then I simply created a view of a UNION DISTINCT of those two tables:

mysql> create view used_indexes as 
   (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) 
    UNION DISTINCT 
   (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from slave_index_stats) 
   ORDER BY TABLE_SCHEMA, TABLE_NAME;

Now I can query the ‘all_known_index_usage’ and see the union of both of those datasets.  This, of course, can be extended to all the datasets you want.

Interpreting the data

So, this is all well and good, but how do we then easily determine a list of indexes that are not being used?  Well, for this we need to back to the INFORMATION_SCHEMA to get a list of ALL the indexes on my system (or at least the schemas I want to consider dropping indexes in).  Let’s keep using views so this dynamically updates as our schema changes over time:

mysql> create view all_indexes as
select
   t.table_schema as TABLE_SCHEMA,
   t.table_name as TABLE_NAME,
   i.index_name as INDEX_NAME,
   i.NON_UNIQUE as NON_UNIQUE,
   count(*) as COLUMN_CNT,
   group_concat( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR ',') as COLUMN_NAMES
from
   information_schema.tables t join information_schema.statistics i using (table_schema, table_name)
where
   t.table_schema like 'sakila%'
group by
   t.table_schema, t.table_name, i.index_name;

Now I can query this view to see my indexes:

mysql> select * from all_indexes limit 1 G
*************************** 1. row ***************************
TABLE_SCHEMA: sakila
TABLE_NAME: actor
INDEX_NAME: idx_actor_last_name
NON_UNIQUE: 1
COLUMN_CNT: 1
COLUMN_NAMES: last_name
1 row in set (0.03 sec)

Now I need a way to find the set of indexes in all_indexes, but not in used_indexes.  These indexes (if our original index statistics are good) are candidates to be dropped:

create view droppable_indexes as
select
   all_indexes.table_schema as table_schema,
   all_indexes.table_name as table_name,
   all_indexes.index_name as index_name
from
   all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME)
where
   used_indexes.INDEX_NAME is NULL and
   all_indexes.INDEX_NAME != 'PRIMARY' and
   all_indexes.NON_UNIQUE = 1;

Note that we also want to avoid dropping PRIMARY and UNIQUE indexes since those tend to enforce important application data constraints, so we added some additional criteria to the end of our SELECT.

I can now select my droppable (unused) indexes from this view:

mysql> select * from droppable_indexes;
+--------------+---------------+-----------------------------+
| table_schema | table_name    | index_name                  |
+--------------+---------------+-----------------------------+
| sakila       | actor         | idx_actor_last_name         |
| sakila       | address       | idx_fk_city_id              |
| sakila       | city          | idx_fk_country_id           |
| sakila       | customer      | idx_fk_address_id           |
| sakila       | customer      | idx_fk_store_id             |
| sakila       | customer      | idx_last_name               |
| sakila       | film          | idx_fk_language_id          |
| sakila       | film          | idx_fk_original_language_id |
| sakila       | film          | idx_title                   |
| sakila       | film_actor    | idx_fk_film_id              |
| sakila       | film_category | fk_film_category_category   |
| sakila       | film_text     | idx_title_description       |
| sakila       | inventory     | idx_fk_film_id              |
| sakila       | inventory     | idx_store_id_film_id        |
| sakila       | payment       | fk_payment_rental           |
| sakila       | payment       | idx_fk_customer_id          |
| sakila       | payment       | idx_fk_staff_id             |
| sakila       | rental        | idx_fk_customer_id          |  
| sakila       | rental        | idx_fk_inventory_id         |
| sakila       | rental        | idx_fk_staff_id             |
| sakila       | staff         | idx_fk_address_id           |
| sakila       | staff         | idx_fk_store_id             |
| sakila       | store         | idx_fk_address_id           |
+--------------+---------------+-----------------------------+
23 rows in set (0.02 sec)

From here I can use some clever SQL to generate the precise ALTER TABLE statements to drop these indexes, an exercise left to the reader.

Estimating the size of these indexes

But, what if we want to see if it’s worth doing first?  Do these indexes actually represent a significant enough amount of disk space for it to be worth our while?

We need some more information to answer this question, but fortunately in Percona Server, we have it in the INFORMATION_SCHEMA.INNODB_INDEX_STATS table and the ‘index_total_pages’ column.  A page in Innodb is (usually) 16k, so some simple math here should help us know how much disk space an index utilizes.

Let’s go update our all_indexes view to include this information:

mysql> drop view if exists all_indexes;
mysql> create view all_indexes as
select
   t.table_schema as TABLE_SCHEMA,
   t.table_name as TABLE_NAME,
   i.index_name as INDEX_NAME,
   i.NON_UNIQUE as NON_UNIQUE,
   count(*) as COLUMN_CNT,
   group_concat( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR ',') as COLUMN_NAMES,
   s.index_total_pages as index_total_pages,
   (s.index_total_pages * 16384 ) as index_total_size 
from
   information_schema.tables t join information_schema.statistics i using (table_schema, table_name)
   join information_schema.innodb_index_stats s using (table_schema, table_name, index_name) 
where
   t.table_schema like 'sakila%'
group by
   t.table_schema, t.table_name, i.index_name;

Now we can can see index sizing information in the all_indexes view:

mysql> select * from all_indexesG
...
*************************** 33. row ***************************
TABLE_SCHEMA: sakila
TABLE_NAME: rental
INDEX_NAME: rental_date
NON_UNIQUE: 0
COLUMN_CNT: 3
COLUMN_NAMES: rental_date,inventory_id,customer_id
index_total_pages: 27
index_total_size: 442368
...

Now we just need to update our droppable_indexes view to use that information:

mysql> drop view if exists droppable_indexes;
mysql> create view droppable_indexes as
select
   all_indexes.table_schema as table_schema,
   all_indexes.table_name as table_name,
   all_indexes.index_name as index_name,
   ROUND(all_indexes.index_total_size / ( 1024 * 1024 ), 2) as index_size_mb 
from
   all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME)
where
   used_indexes.INDEX_NAME is NULL and
   all_indexes.INDEX_NAME != 'PRIMARY' and
   all_indexes.NON_UNIQUE = 1
order by index_size_mb desc;

Now we can easily see how big each index is if we dropped it (not big in this case with test data):

mysql> select * from droppable_indexes;
+--------------+---------------+-----------------------------+---------------+
| table_schema | table_name    | index_name                  | index_size_mb |
+--------------+---------------+-----------------------------+---------------+
| sakila       | payment       | fk_payment_rental           |          0.27 |
| sakila       | rental        | idx_fk_customer_id          |          0.27 |
| sakila       | rental        | idx_fk_inventory_id         |          0.27 |
| sakila       | rental        | idx_fk_staff_id             |          0.19 |
| sakila       | payment       | idx_fk_staff_id             |          0.17 |
| sakila       | payment       | idx_fk_customer_id          |          0.17 |
| sakila       | inventory     | idx_store_id_film_id        |          0.11 |
| sakila       | inventory     | idx_fk_film_id              |          0.08 |  
| sakila       | film_actor    | idx_fk_film_id              |          0.08 |
| sakila       | film          | idx_title                   |          0.05 |
| sakila       | film          | idx_fk_original_language_id |          0.02 |  
| sakila       | city          | idx_fk_country_id           |          0.02 |
| sakila       | film_category | fk_film_category_category   |          0.02 |
| sakila       | customer      | idx_last_name               |          0.02 |
| sakila       | store         | idx_fk_address_id           |          0.02 |
| sakila       | actor         | idx_actor_last_name         |          0.02 |
| sakila       | customer      | idx_fk_address_id           |          0.02 |
| sakila       | staff         | idx_fk_address_id           |          0.02 |
| sakila       | film          | idx_fk_language_id          |          0.02 |
| sakila       | address       | idx_fk_city_id              |          0.02 |
| sakila       | customer      | idx_fk_store_id             |          0.02 |
| sakila       | staff         | idx_fk_store_id             |          0.02 |
+--------------+---------------+-----------------------------+---------------+
22 rows in set (0.02 sec)

Recovering filesystem space

Now astute innodb experts will realize that this isn’t the end of the story when it comes to reclaiming disk space.  You may have dropped the indexes, but the tablespaces on disk are still the same old size.  If you use innodb_file_per_table, then you can rebuild the tablespace for your table by simply doing:

mysql> alter table mytable ENGINE=Innodb;

However, this blocks and on a large table can take quite some time. All the normal tricks and tips about doing a long blocking schema change without affecting your production environment apply here and is out of scope for this blog post.

Happy hunting for those unused indexes!

A design contract is like a business card—it comes from the same desk, and bears the same creative mark. But it’s also the business card you hate handing out: a folder of legal gibberish with terrible formatting that reminds the client of everything that could possibly go wrong before the work has even started.

Is this just a necessary evil? Why can’t contracts evolve like everything else?

Actually, they can—and should. Modernizing your contract will not only make it match your carefully crafted brand, but it can also help you reach an agreement faster, and even strengthen your position when negotiating. This is not an easy task. Legal content is a delicate matter, and you definitely can’t start tweaking your contract like it’s a blog post.

Before we start modernizing contracts, we first have to understand their purpose, and how and why they got the way they are. It’s a long journey back.

Five Roman principles of contracts still valid today

The Romans developed a sophisticated system of commercial law that has become the foundation for pretty much all of the Western world’s legal systems. A design contract was probably signed to make the incredible decorations of Ara Pacis. Such a contract would have been created to accomplish something not that different from today’s products of design: defining what must be done, the deadline, the client’s approval, and the price. The concept of copyright did not exist yet, but unauthorized and fraudulent copies of literary works were socially unacceptable. (As for non-literary works, good luck copying those marble statues.)

While our work has evolved, contracts have essentially stayed the same—for a number of good reasons. In fact, several principles are just as important in today’s contracts as they were in Roman times.

1. Verba volant, scripta manent

Spoken words fly away, written words stay.

In a world where few people could read or write, a written contract was much more difficult to obtain—and therefore much more valuable than a handshake. Romans were the first to establish a now-universal principle of civil procedure: The burden of proof is on the plaintiff (onus probandi incumbit ei qui dicit). Therefore, a written contract protects the wronged party. This is still true today, so don’t only use a written contract before work begins; make sure every modification is documented in writing.

That’s a much easier task today than in Roman times. You don’t have to run to a scribe, or even a notary. E-mail has been proved legally binding multiple times, so to amend a contract, you can just drop a line like, “As discussed in today’s meeting, we mutually agree to modify the statement of work as follows…”

Some contracts even have a clause that requires all amendments to be in writing. If that’s the case, you’ll want to make certain you follow it; otherwise, the client can make excuses for not paying you for extra work.

AIGA’s standard agreement for design services uses a nifty solution to make sure all modifications are in writing and that there’s a limit to the number of modifications that can be requested:

4.2 Substantive Changes. If Client requests or instructs Changes that amount to a revision of at least 15% of the time required to produce the Deliverables, and or the value or scope of the Services, Designer shall be entitled to submit a new and separate Proposal to Client for written approval. Work shall not begin on the revised services until a fully signed revised Proposal and, if required, any additional retainer fees are received by Designer.

As you can see, it’s the same old verba volant, scripta manent still in use.

2. Aliquid dare, aliquid retinere

Give something, keep something.

The value of a project depends not only on what you put in a contract, but also what you leave out. This is particularly true for design, which is not strictly a product, nor strictly a service. It’s a hybrid set of “deliverables,” and the contract (not the e-mail with the design attached) is the place where you give them to your client.

Be wary of what you give and keep. If possible, hold onto copyright: Delay the assignment, or the effective date of the license, until the money is in the bank. This is the best leverage you have.

Clients will try to do the same with payment, of course. Welcome to contract negotiation.

On this inevitable battlefield, details make a difference. For example, imagine you are an illustrator who creates a set of characters for a story. Your client picks the ones they like, and those are the deliverables they buy. Why shouldn’t you keep the rest, and “recycle” them for future projects? If you don’t specify this in the contract, the client will be assigned all the work in connection with the project, including unused sketches.

Same thing if you are delivering code. It’s common to incorporate snippets of code into multiple projects, but just because that code ends up in that project doesn’t mean that client owns it. These are usually called “design tools” in a contract—which means instead of giving something away, you’re simply giving your client permission to continue using the tools.

3. Leges sine moribus vanae

Laws are useless without customs.

Just as graphical and technical standards are essential to designing, standards and industry practices play a crucial role in negotiating contracts. Following best practices not only lowers transaction costs and streamlines the process, but also fosters more balanced deals.

What are the contractual standards of design? The AIGA agreement mentioned earlier is a great start, but standards can also live in single clauses. Eric Adler, a lawyer who works with creative professionals, knows which clauses of his contract are more likely to be negotiated, and takes care to explain those to his clients.

An excerpt from Eric Adler’s contract annnotations.

When it comes to liability, Adler suggests that it’s standard to cover your asse(t)s up to the overall net value of the project. You could try to ask for more, but no one wants to make a client nervous over a legal boilerplate, and standards make sure this doesn’t happen.

Standards don’t just come from lawyers or unions. Andy Clarke’s Contract Killer is extremely popular among freelance designers—in fact, a version of his contract is one of the most viewed and downloaded items at my company, Docracy, which provides an open collection of legal documents. This is likely due to Clarke’s strict no-legalese policy. He even dropped the classic impersonal language, transforming it into a natural dialogue with the client: “What both parties agree to do.”

The result is a set of informal yet clear rules that cover essential legal provisions, like assigning copyright only upon full payment and reserving portfolio rights.

But where is all the horrible small print?

There is none. This contract shows that it’s possible to enter a binding agreement using everyday English. Your lawyer may not like it, because he may fear not being taken seriously enough, or feel uneasy not following his standard. Fortunately, this is something that has actually changed since the Romans. They had to use formulae and magic words to make sure the contract would be upheld in court, while we typically enjoy shared language and literacy skills.

4. Clausulae insolitae indicunt suspicionem

Unusual clauses will raise flags.

We all like standards, but let’s face it: Everything is negotiable, and people will always try to sneak advantageous clauses into the contract. You need to make sure you don’t sign anything you’ll regret, and spotting bad provisions is not a lawyers-only job. Scanning contracts is a necessity sometimes, so always look closely at the following parts:

• Parties, particularly when companies are involved: Make sure the people you’re dealing with have the power to bind their companies.
• IP provisions: Who owns copyright and when, and what the licensing limitations are.
• Your representation and warranties—the fewer, the better: underpromise and overdeliver!
• Termination: What happens if someone wants to get out of the deal early?
• Dispute resolution: The clause no lawyer ever wants to give up. Watch this one, because you don’t want to let a client drag you to a court a thousand miles away. If you can agree to arbitration or mediation, even better.

The more contracts you read, the better you’ll get at spotting weird provisions. Trust your judgement: If something doesn’t seem quite right, it probably isn’t.

At minimum, you should ask for an explanation. This is never a waste of time. If you have a lawyer do this, just find someone who doesn’t bill by the hour, or this negotiation will take forever.

5. Pacta sunt servanda

A deal is a deal.

Both in Roman times and today, if you don’t deliver, it’s on you. Keeping promises is fundamental for a professional reputation. That’s why you have to be clear and consistent in the promises you make.

How do inconsistencies arise? One common way is having a statement of work (SOW) that’s not compatible with a master service agreement (MSA). This happens more often than you might think, particularly if no one has ever read that thirty-page agreement. If it’s not clear which one prevails (yes, you have to write it down), you can find yourself in a legal mess.

For example, capping your hours in the MSA is a great way to mitigate the fixed-fee or milestone-based pricing you agreed to in the SOW, but only if the cap prevails! Vice-versa, if you know you’ll only be looking at the SOW and all the special payment provisions are in there, then it should probably override any older pricing rule buried in that thirty pages of small print.

Even better, an MSA doesn’t really need to include thirty nasty pages of small print.

Making a modern contract

I bet you didn’t read iTunes’ latest Terms and Conditions before clicking “I Agree.” We try to read contracts when we think it’s important, but it’s not easy, for several reasons:

• Contracts are optimized for print, but today we read mostly on screen.
• They are often poorly formatted and typographically awful.
• Many elements are difficult to read, like definitions and ALL CAPS PARAGRAPHS.
• They’re full of legal jargon, not plain language.

The good news is, these problems can be fixed.

Typography

Let’s start with font. Designers and clients alike now mostly read on screens. Electronic signing is a reality, so there are few arguments for optimizing a contract for print.

If you’ve studied typography, you know how to use contrast, proximity, and alignment to create emotional and persuasive effects, and you can apply these same principles to legal text.

Matthew Butterick, author of Typography for Lawyers, has even developed a font optimized for legal text: Equity, a serif font that also looks good on screen—a nice compromise. Whatever you choose, ensure you give your contract balance and contrast.

Typesetting

Contracts are a very peculiar subset of legal documents. How can you use typesetting skills to improve their layout?

• Structure them in nested lists. HTML does such a great job handling nested lists and headings, so why use a crappy text editor? You often see reckless tabbing and manual line breaks made by frustrated people desperately trying to keep order. Using tools of the trade like Markdown, LaTeX, and Illustrator, you can do better in no time.
• Divide the boilerplate from the custom terms. Highlight relevant content like party names, important numbers, and percentages so they stand out from the boilerplates and can be easily skimmed.
• Make important clauses stand out, but never use all caps. The law only asks the drafter, in specific situations, to highlight certain provisions—and there are ways to do that without sacrificing readability. If your lawyer thinks differently, she’s wrong.
• Allow longer paragraphs. Words need to “breathe,” but contracts also need to cluster like clauses for readers. For this reason, line length is a delicate choice that depends both on the length of the clauses in your contract and on the font you choose to use. If you opt for a sans serif, you might get away with longer lines, but be sure to keep generous margins and line spacing (ideally, 120 to 145 percent of the point size, according to Typography for Lawyers).

You’ll also need to decide whether to justify or left-align text. The general rule is that justified text only works with proper hyphenation. This means you’ll have to manually input non-hyphenated breaks for the words you want to keep on the same line. Unless you’re drafting the contract yourself from start to finish, this is a daunting task. And, if your contract manages to have short paragraphs, ragged-right looks more natural, particularly on screen.

When we redesigned Docracy’s PDF typography, we opted for a longer line with lots of white space on the sides. This lets even the longest contract breathe, yet creates a compact final look:

Plain language

Now for the million-dollar question: Why are contracts written in legal jargon? Sadly, it’s because lawyers are too lazy and change-averse to rewrite their forms. The good news is, this is changing. And you can contribute; most formulaic “legalese,” like herein, thereof, or hereby, can just be replaced with “this.” You might even be able to remove entire lines, but better check with a lawyer to make sure.

Here’s an example of traditional contract language rewritten in plain English. Not only is the new version half the length, but it’s much easier to understand:

Classical roots, contemporary documents

There are many reasons the core rules of contracts are still in place two millennia after the fall of Rome. But there are other elements that we can, and should, take to the twenty-first century.

If we want to address the readability problems unique to our era—and improve communication with our clients—then it’s time we fix the language, layout, and typesetting of our contracts. And who better than designers to do it?

Translations:
Italian

RSS readers: Don't forget to join the discussion!

Je ne suis pas monsieur Lebowski. C’est vous monsieur Lebowski. Moi, je suis le Duc.

—The Big Lebowski, French version

There is a world where Harry Potter’s arch enemy is “Du-weißt-schon-wer,” Facebook users click the “Me gusta” button, and the Dude is named “le Duc.” This world is a translated world.

We—the people who make websites—now study almost every aspect of our trade, from content and usability to art direction and typography. Our attention to detail has never been greater as we strive to provide the best possible experience. Yet many users still experience products that lack personality or are difficult to understand.

They are users of a translated version.

When we pledge to embrace the adaptable nature of the web—to make our websites responsive and even future-ready—we’re typically talking about diversity of devices. But the web’s diversity also comes in the form of different languages and cultures.

Translation affects users’ experiences—and our organizations’ success. It’s time we consider translation part of our jobs, too.

Waiting for C-3PO

“Do you want your forum clean like this?”

I had just set up a user forum in French when I stumbled upon this rather bizarre banner. “What makes the forum so clean?” I wondered. “Do they tidy the code every day?” I had to change the language back to English to understand it: “Do you want your own forum like this?”

In French, “propre” means either “own” or “clean,” depending on how it’s used. The rule is simple; any translator would know it. More precisely, any human translator. Google Translate, the system behind the French version of the forum, obviously wasn’t so sure.

It’s not just Google Translate, either. In the 1950s, Alan Turing, the father of computer science, devised a test to evaluate machine intelligence through conversations. The biggest Turing test ever was held last June to celebrate what would have been Turing’s hundredth birthday. The winner was probably the most advanced chatbot ever created, yet Eugene Goostman—as this bot is named—failed to fool the judges 70 percent of the time. When will machines pass the test? In the year 2029—maybe.

This should come as no surprise. Languages are amongst the richest and most complex systems humankind has ever produced. When machines gain the ability to really speak (and therefore translate), it will be possible to use Google Translate in a professional context—and no doubt we’ll also have Google Design and Google Copywriting by then. But today, Google Translate is to translation what the auto mode is to photography: a quick-and-dirty solution. It comes in handy when you need to get an idea of what’s being said about your project on Weibo (China’s version of Twitter), but it isn’t a good option when you need to translate your website into Spanish.

While we’re waiting for C-3PO, we need professional translators. We must also acknowledge their creativity and recognize them as peers.

Great design deserves great translation

Translating is a respectable, valuable, creative and worthwhile use of a human brain.

—David Bellos, Is That a Fish in Your Ear?

Le Big Lebowski is a masterpiece. I would even argue that it surpasses the original. Everything is just perfect: the dubbing, the humor, the dialogue. The translators retained the essence of the film while adapting it for an audience that has no idea what a “dude” is. They managed to translate not just the words, but the Coen brothers’ genius as well.

E-mail service provider MailChimp is a masterpiece, too. Aarron Walter’s UX team has succeeded in creating a unique personality. Much of this personality manifests itself through copy: the greetings from Freddie, the company’s joke-cracking mascot; the always-relevant error and help messages; and—above all—the “funny but not goofy, informal but not sloppy” voice and tone used throughout the application.

Now, if MailChimp were to be translated into Spanish, Russian, or Chinese, what would become of this personality? What does it mean to be “informal but not sloppy” in Japanese? Should the mascot’s name still be “Freddie Von Chimpenheimer IV” in German, or could that be misinterpreted? Can you greet an Indian user with “Hi. You could be a part-time model”?

There are no easy answers to these questions. Translating is walking a tightrope. The challenge is to remain faithful to the original design while adapting it for a new audience, for a different culture.

If you think a machine can do this, take a look at this Google translation of MailChimp’s success message, “High fives! Your list has been imported”:

Cinco años de alta! Su lista ha sido importado.

Show that to a Spanish-speaking friend and you’re sure to get a bewildered look.

The road ahead

The web is home to plenty of innovation. But when it comes to translation, other industries are far ahead.

If we want to reap the benefits of translation, we must learn what it takes to do it well—and why it matters. Let me give you two examples.

Linguistic validation

The pharmaceutical business may not seem to share much with web design, but it has one best practice that could inspire us: linguistic validation.

Introducing a new drug into the market is a complex and controlled process that includes a long series of trials and reviews. Some of these tests involve the patients themselves, such as Patient-Reported Outcomes questionnaires, which assess whether a drug has actually improved a patient’s quality of life. These questionnaires are written in English by clinicians and then translated into hundreds of languages.

Ordinary translation is usually a two-step process: translation then proofreading (some even skip the proofreading). The linguistic validation of patient questionnaires has a few more steps, such as doing both forward and backward translations and pilot testing.

Why such a complicated and costly process? Two reasons: First, the original version is a precise research instrument. Nothing has been left to chance. Second, it is essential for patients to perfectly understand the questions, because what they report will serve as scientific data. The questionnaire must therefore be intuitive and patient-friendly.

Thoughtfully designed products, user-friendly interfaces—aren’t these what we aim for? If we care equally about all our users, it’s time we start thinking of translation as something slightly more complex than a word-to-word job.

Cultural expertise

Raving Rabbids is a humorous party game designed in Ubisoft’s Paris studio. The development team includes a localization specialist in charge of the game’s eight localized versions. She works hand in hand with designers to ensure their jokes, references, and altogether craziness are translatable. For the U.S., Rabbids’ biggest market, a duo of Americans from Nickelodeon even gave the team a little extra cultural insight.

It costs millions of dollars to produce a major video game, and even more to target international audiences. Because playing a game is such an immersive experience, the teams behind Rabbids and many other games have found that localization specialists are critical. They are not given a finished product to adapt—they take part early in the project, as their feedback on cultural matters may profoundly change the game’s design.

The game industry prefers the term “localization” to “translation” because the latter is too often restricted to text. This says a lot about how seriously game studios take cultural expertise. Because they know a cultural misfit can stall a game’s chances of success—and they know for every dollar invested in localization, there’s a $25 return.

Because they know that translation—sorry, localization—is UX.

Translate early, translate often

Most startups employ what could be called the lemonade tycoon approach: Start in your neighborhood, amongst the people you know; this is your best bet. Get it right at home before expanding into far-off lands.

I’m not saying you shouldn’t start in your own country. Local knowledge is priceless. But why wait to internationalize? Unlike lemonade selling, the web is international by nature. From day one, your website will be accessible to any person on this planet.

What’s more, procrastination has a cost. According to Smartling, a translation software company, “it can take companies 12-18 months to internationalize their code and launch their first foreign language site, absorbing much of the company’s engineering resources.”

Companies face the same problem when they develop a mobile version of their site afterward. Good thing many now adopt a “mobile first” process.

Perhaps they should consider “foreign first,” too.

It’s a big world out there

When you come from a non-English-speaking country, as I do, a “foreign first” approach is very likely to mean “English first.” But what if you’re based in New York, Manchester, or Auckland? Which language should you go for?

The answer is actually not to think “language,” but rather “opportunity” and “culture”—as these three companies have:

• Wufoo is a popular form builder from Tampa, Florida. At the beginning of 2012, it launched Wufoo Español, its first foreign version. You won’t find the Spanish version at wufoo.es, but at wufoo.com.mx—because it saw an opportunity in a neighboring market, and language was a means to reach that market. Besides, Wufoo doesn’t mix up language and local culture: It plans to roll out additional localized versions for Spain and Argentina.
• CanaDream is a Canadian RV rental company whose website is available in three languages. English and French are obvious choices, but the third one is trickier: German. Again, the company saw an opportunity—Germans love RV travel. But German people generally speak good English, don’t they? Yes, many do—but they will still prefer a company that attends to them in their own language.
• Bla Bla Car is a car-sharing service born in France. Here we can see that “English first” isn’t always the rule. Bla Bla Car’s first foreign version was in Spanish. The car-sharing market was less competitive in Spain than in other European countries, which gave Bla Bla Car the opportunity to test-run its internationalization before moving on to other markets—which it eventually did. Car sharing is getting more and more popular in Europe, and Bla Bla Car aims for leadership in the region—and in a multilingual area, this has required translation to seven languages and counting.

Bargain-basement market research

Most startups can’t afford international market research. That’s why they focus on their home market. But just as Paul Boag taught us about bargain basement usability testing, we can find affordable market research techniques, too.

Once you’ve settled on a country to target, go to ProZ and look for a translator or agency based there. Brief her about your project and send your prototype or an access to your beta. Ask her to translate the key screens. Even at this stage, you can get lots of feedback: “Are you aware your app name can hardly be pronounced—let alone remembered—by Brazilians?” “I’m sure having Acme Inc. as a client is a great reference in the U.S., but nobody knows them here.” “This photo of a blond-haired, blue-eyed guy probably won’t resonate with a Turkish market.”

Then ask your translator to run a user test using her network of proofreaders. You don’t need hundreds of people—with only ten participants, you’ll uncover any major cultural faux pas. You’ll also gain a general understanding of whether people are interested in the project, what their main questions are, and whether they like the visual design.

Finally, discuss your personas with the translator: Maybe Harriet should be renamed María and relocated to Valparaiso. And what about adding Hugo, the typical backpacker from the Netherlands? With localized personas, all your users will be given equal consideration throughout the design process.

Of course, you’ll need more precise data eventually. But this quick-and-dirty research is enough to get you started. You’ll iterate from there.

Your new teammate

When you start translating early, you make the translator part of your team. Chances are this will be a very rewarding experience. At Novius, my company, it’s changed the way we work.

For major projects, we now create and feed a glossary—or as I like to call it, a “style spreadsheet.” CSS stylesheets are understood by both designers and developers and guarantee style consistency across an entire website. Similarly, glossaries are by and for the whole team and ensure the consistency of content. Just like you want a color scheme that’s thoroughly followed, you also want to make sure “module,” “plugin,” and “extension” aren’t all used to refer to the same concept. Le fond et la forme.

We have also learned that a quality translation begins with the code. Developers strive for reusable code, and strings are no exception. Depending on how a developer handles them, he could make the translator’s job straightforward, or virtually impossible.

When dealing with sentences like, “1 person has this question” and “X people have this problem, including you,” translators are often asked to translate strings like: “person has,” “people have,” “this,” “question,” “problem,” and “including you.”

Even with context, deconstructing these sentences is a translator’s nightmare. For languages with gender, the string “this” is untranslatable (e.g., esta pregunta and este problema in Spanish). In many languages, like Russian, plurals take several forms (e.g., for the plural “persons,” you would say four челове́ка, but five челове́к). And the list goes on.

Since language isn’t code, developers and translators have a lot to learn from each other. Translators will tell them the software they use has translation memory, so there’s no need to avoid repetition. They will discuss how to handle variables in text. They will also decide together which internationalization system (such as gettext) and text file format (like XML or PO) to use.

Not a one-off thing

I won’t lie to you. Once you’ve translated your website, you’re in for good. People don’t care that they’re using a translated version. For them this is the only version. So you’ll have to keep translating.

They will hate being considered “second-rate” users. Once you’re out of beta, 90 percent translated is not OK. How would your users feel if every website update resulted in a buggy mobile version? Users of translated versions experience this all the time, with English text suddenly popping up out of nowhere. To make it worse, the newest features—proudly announced and long-awaited—are usually the ones left partially translated. Users do get the message: You’re not important enough for us to prioritize translation quality.

While good localization boosts conversion rates, bad or partial translation may ruin a user experience, giving users an uneasy feeling about the whole company: If they can’t even get their website right, how bad will the customer support be?

In fact, I recently chose not to purchase a service because of a pricing page that proclaimed, “Give a price to these ladders with your growing company.”

Guess what it was selling? Translation software.

A multilingual web

If I am selling to you, I speak your language. If I am buying, dann müssen Sie Deutsch sprechen.

—Willy Brandt, West German political leader

The language of the web is English as much as HTML. If the web had a capital, it would be somewhere around San Francisco Bay. Web professionals worldwide use English expressions in almost every sentence: Like, browser, responsive, Tweet, SEO, etc.

However, 73 percent of internet users don’t speak English, and their numbers are growing. We now enter the age of glocalisation.

In our move toward universal design, we must not forget languages and the people who master them. “Translating is writing,” said French writer Marguerite Yourcenar.

Today we can also say, translating is designing.

Translations:
Italian

RSS readers: Don't forget to join the discussion!

I tend to think about this a lot and talk to quite a few people about it, since I’m intimately familiar with the approaches to versioning that the HTTP protocol itself takes, and with the general attitude taken to it in the broader Internet architecture by the IETF.

So, I was quite interested to come across Tom Preston-Werner’s effort to define Semantic Versioning. If you haven’t seen it yet, go have a read; it’s a very sensible explanation of how to evolve software.

Let’s apply his “Firetruck” example to services. If you depend on a Ladder service, you have many of the same concerns; you need an instance of it that supports the semantics you understand (major version) and the additional features that you need on top (the minor version). You might also be interested in the patch level, in case you need to do some debugging.

The interesting, confusing and contentious part of applying this common sense to HTTP services is figuring out what you’re versioning, and how to communicate the version. 

One viewpoint is to say that the service is being versioned, the service is identified by the URL, and therefore the version goes in the URL, like this:

http://api.example.com/v1.2.3/ladder

However, there are (at least) two issues to consider with this approach.

First of all, it’s coarse-grained, in that you can’t evolve parts of the system independently. For example, introducing a new format for the “ladder” resource - by the rules, that means a minor version, which means that the URL should now become:

http://api.example.com/v1.3.0/ladder

which, as discussed previously in the API Versioning Smackdown, creates a whole new tree of resources, and tightly couples the client and server.

While that may be fine if you have a very simple API with no interdependencies — such as serving a bunch of JavaScript, as Nicholas Zakas describes — it will quickly become a huge headache for testing, support and operations if you have to support all of the combinations of possible resources and their interactions in a more complex one.

Second, it’s also intermingling the version into identifiers. Because URIs are used in the Web as the fundamental identifier by so many things — caches, spiders, forms, and so on — embedding information that’s likely to change into them make them unstable, thereby reducing their value. For example, a cache invalidates content associated with a URL when a POST request flows through it; if the URL is different because there are different versions floating around, it doesn’t work.

This is actually very similar to the discussion about X- and names for HTTP headers; putting a flag into the name to signify “experimental” doesn’t make much sense when the experiment ends and it gets used “for real.”

Suggested Practices

With that in mind, what should HTTP APIs do? My current thinking (based on the thinking of a lot of other people ;) is below.

Keep Compatible Changes Out of Names

As per above, names should be stable over time, and should identify a known set of semantics — corresponding to the major version number in Semantic Versioning. By “names,” I mean everything that’s used as an identifier, whether it’s a URL, a media type, a link relation name, HTTP header, whatever.

From what I see, most HTTP APIs are already moving in this direction, with structures like this:

http://api.example.com/v2/ladder

Here, only the major version number is put in the URL; the minor and patch versions don’t go in, because backwards-compatible changes don’t need to be signified by changes in the name. Of course, it’d be equally valid to do:

http://api2.example.com/ladder

because the hostname is just as much an identifier as anything else.

Even with this approach, it’s worth noting that letting clients infer that it’s a v2 API just from that path segment is a dodgy thing to do; however, the deeper reasons for this are the subject of a different blog post.

Likewise, minor and patch versions shouldn’t go into other names, such as media types or link relations. There is a train of thought that you don’t need to have numeric major versions at all, since you can call the first one “foo” and the second one “bar” — but that’s just a matter of taste.

Avoid New Major Versions

This is also pretty widely agreed to. Every time you release a new major version, people have to look at it, understand it, write new software to it, debug it, and so on. This is a huge investment on both sides, since you also have to support two (or more) major versions concurrently for some sort of sunset period. 

So, new major versions should be few and far between. In a perfect world, there would be none, but the reality is that every once in a while, you need to clean up a messy API or otherwise make breaking changes. Just make them last as long as you can. 

Make Changes Backwards-Compatible

The biggest way to avoid new major versions is to make as many of your changes backwards-compatible as possible. 

For example, if you want to add support for a new HTTP method, or add a new resource to the mix, this doesn’t necessitate a new version. Likewise, adding support for a new format can be achieved through the miracle of content negotiation. Need to change the meaning of an existing query argument? Don’t — instead, introduce a new one.

Surprisingly, removing support for something can be considered backwards-compatible too. Think about it; if you remove support for, say, the “foo” resource and return a 410 Gone, clients will break. However, it will only break those clients that use it; those that don’t can still smoothly interoperate. Introducing a new major version to say “I don’t support the foo resource” effectively breaks everybody, so it doesn’t do any good.

That naturally leads to…

Think about Forwards-Compatibility

Fundamentally, evolution is about figuring out how to limit the breakage that your changes incurs on clients. As such, you need to place some sorts of expectations and boundaries on how your clients should behave when they encounter certain circumstances. 

In other words, if a client is hardcoded to only work when “foo” is there, “foo” will always have to be there to avoid breaking it. More subtly, if clients don’t expect an extra HTTP header, or an extra member on a JSON object, that can cause problems too, and restrict your options down the road.

Expressing these expectations clearly is something we as an industry needs to get a lot better about. Unlike Web browsers — which are extremely forgiving of unexpected input — most API clients are incredibly brittle. 

For example, XML Schema got this pretty fundamentally wrong, making it very difficult to express a forwards-compatible schema (in 1.0). JSON is better, mostly because implementations are tied pretty closely to dynamic programming language data structures, rather than a schema language.

That’s not to say that you want everything to be extensible. Sometimes it’s a good idea to explicitly NOT allow forward compatibility. For example, in the JSON Patch format, we realised that any extensions were likely to be a fundamental change in the document semantics, thus requiring a new major version (in this case, identifying it with a new media type). After all, an old client that doesn’t respect a new patch operation is going to come up with a different result than the new one that does understand it. So, we disallowed most extensions in that format.

The trick is to think about it and document your expectations carefully — whether you’re designing a format, a link relation type, or a HTTP header. Tell clients to expect and handle (as gracefully as they can) responses like 410 Gone, 405 Method Not Allowed, and 415 Unsupported Media Type.

Version at Appropriate Granularities

Going back to the example above:

http://api.example.com/v2/ladder

a natural question to ask is “what is that ‘v2’ versioning?”

To me, the most natural reading is that it’s a collection of resources that represents a set of functionality,  hierarchical URLs have collection semantics, by putting them under a single path segment (“directory”).

This is an important distinction; v2 is identifying NOT just the ladder, but the whole interface, as a collection of resources (i.e., everything under that “v2”) that works together.

Another conversation that I have sometimes is about how to relate format changes to API versions. In short, they should be completely separate; formats can have lives of their own, and to get the most value out of them, they should do so. It’s fine to say “Version 2 of the API requires the foo resource to support version 5 of the bar format,” of course.

Make Minor and Patch Information Discoverable

There are legitimate needs for minor and patch information; just because we say “don’t put them into names” doesn’t mean that they shouldn’t exist. However, I am fairly skeptical that they do much good “on the wire” as they are.

For example, consider our Ladder service, version 1.2.3. Maybe we added support for a new HTTP method in version 1.2, and fixed a few bugs in patch level 3.

A self-describing service will make it completely evident (e.g., using the Allow response header) that the new method is supported; if it needs to be known about ahead of time (for example, to reflect it in a UI), you can advertise support for that method directly (e.g., with something like this embryonic mechanism).

Tying this information up in a version number only makes the client go and look up a chart of version numbers to see whether the feature they want is supported by the given version; instead, if they can directly interrogate the interface to see if it supports the fancy new “ladder cover” feature (or whatever), it’s a lot more flexible and useful. The same goes for new resources, new formats, and so on.

Aside from that, using linear, numeric minor versions for negotiating new features is really, really limiting; complex APIs will find this especially impractical.

So, where should the minor and patch version numbers go? Easy — it’s useful for the release notes, and a few other forms of documentation. It’s useful for marketing. In the case of a more complex API (as with most standards — whether they come out of a standards body or an open source project), it’s useful for packaging up an agreed-to set of functionality and calling that a spec release.

Mind you, there’s a strong case for including this information about the implementation of the API — server or client side — but that goes in the Server or User-Agent header respectively, and is completely separate from API versioning (i.e., you might have version 0.2.1 of the client accessing version 3.2.3 of the server’s implementation of the API, which itself might have a version of 1.0.3).

As promised, I wanted to keep giving updates on the committee’s progress in selecting the program for the conference.  As many people know, the selections and schedule for tutorial day have been made public.  There are many great tutorials being presented, and I don’t have time to laud them all, so let me give you a few (highly biased) highlights:

Operational DBA In A Nutshell! Hands On Tutorial!

This tutorial will be an extremely hands-on overview of all things pretaining to being a MySQL DBA and would be an excellent choice for anyone either just getting started in MySQL, or who has been doing DBA work on the side and wants to dig further.  Also, it is being given by my excellent Belgian Percona colleagues: Liz, Fred, and Kenny: who are fantastic presenters and trainers.

This is one of 3 tutorials that will span the full 6 hour day and I just couldn’t see covering all this material in less time.

Cookbook for Creating INDEXes — All about Indexing

Now for one of my former colleages: Rick James of Yahoo! Inc.  Rick is giving a 3 hour tutorial focusing on indexing within MyISAM and Innodb with a side of Partitioning.  I’d expect folks interested in efficient schema design for high performance to get a lot of out of Rick’s talk.

A good afternoon follow up to Rick’s  talk might be:

Advanced query optimizer tuning and analysis

This 3 hour session is being given by Timour Katchaounov and Sergei Petrunia from the Monty Program and focuses in on how the query optimizer does what it does, why it does it, and how to make it work for you.  These guys are probably a few of just a handful of developers in the world who have really dug into the optimizer and have a solid understanding of exactly how it works.

These two talks would be an excellent track for anyone who does schema design and writes a lot of queries.

High Availability Tutorials

One of the clearly emerging themes this year is High Availability, though honestly I’m not sure when this was not the case at a MySQL conference.  There are several MySQL HA related tutorials:

• Using Tungsten Replicator to solve replication problems (3 hr)
• Ramp-up tutorial for MySQL Cluster – Scaling with continuous availability (6 hr)
• Percona XtraDB Cluster / Galera in Practice (6 hr)

So what’s next?  The committee is furiously working on rating the huge number of session proposals.  I just finished a multi-week process of rating each and every talk (except my own) just today.  Unlike the tutorial selection process where there was a few dozen proposals, there are hundreds of session proposals to sort through.  Everyone on the committee has his or her own opinion about what’s most important for talks, and it’s through our combined effort that we (hopefully) end up with a schedule of talks that is well rounded and represents where the community is today and where it is headed in the future (and, of course, encourages as many people as possible to attend!).

As always, feel free to ask questions.  I’d be happy to answer what I can!

You have a stored procedure that runs well most of the time but sometimes it's WAYYYYY off. It's almost as though the performance of it went from great to horrible in a split second (like falling off of a cliff). You don't know why but someone says - it's got to be the statistics. In fact, if you have the luxury of time (which most folks don't have), you execute it yourself and you check the plan - WOW, the estimated number of rows is WAY off from the actual rows. OK, it's confirmed (you think); it's statistics.

But, maybe it's not...

See, a stored procedure, a parameterized statement executed with sp_executesql and prepared statements submitted by clients ALL reuse cached plans. These plans were defined using something called parameter sniffing. Parameter sniffing is not a problem itself - but, it can become a problem for later executions of that same statement/procedure. If a plan for one of these statements was created for parameters that only return 1 row then the plan might be simple and straightforward - use a nonclustered index and then do a bookmark lookup (that's about as simple as it can get). But, if that same sp_execute statement/procedure/prepared statement runs again later with a parameter that returns thousands of rows then using the same plan created by sniffing that earlier parameter then it might not be good. And, this might be a rare execution. OR, it could be even more strange. These plans are not stored on disk; they are not permanent objects. They are created any time there is not already a plan in the cache. So, there are a variety of reasons why these plans can fall out of cache. And, if it just so happens that an atypical set of parameters are the first ones used after the plan has fallen out of cache (better described as "has been invalidated") then a very poor plan could end up in cache and cause subsequent executions of typical parameters to be way off. Again, if you look at the actual plan you'll probably see that the estimate is WAY off from the actual. But, it's NOT likely to be a statistics problem.

But, let's say that you think it is a statistics problem. What do you do?

You UPDATE STATISTICS tablename or you UPDATE STATISTICS tablename indexname (for an index that you specifically suspect to be out of date)

And, then you execute the procedure again and yep, it runs correctly this time. So, you think, yes, it must have been the statistics!

However, what you may have seen is a side-effect of having updated statistics. When you update statistics, SQL Server usually* does plan invalidation. Therefore, the plan that was in cache was invalidated. When you executed again, you got a new plan. This new plan used parameter sniffing to see the parameters you used and then it came up with a more appropriate plan. So, it probably wasn't the statistics - it was the plan all along.

So, what can you do?

First, do not use update statistics as your first response. If you have a procedure that's causing you grief you should consider recompiling it to see if you can get a better plan. How? You want to use sp_recompile procedurename. This will cause any plan in cache to be invalidated. This is a quick and simple operation. And, it will tell you whether or not you have a recompilation problem (and not a statistics problem). If you get a good plan then what you know is that your stored procedure might need some "tweaking" to its code. I've outlined a few things that you can use to help you here: Stored procedures, recompilation and .NetRocks. If that doesn't work, then you MIGHT need to update statistics. What you should really do first though is make sure that the compiled value of the code IS the same as the execution value of the code. If you use "show actual plan" you can see this by checking the properties window (F4) and hovering over the output/select.

This will confirm that the execution did (or did not) use those values to compile the plan. If they were the correct values then you might have a statistics problem. But, it's often blamed and it's not actually the problem. It's the plan.

OK, there's a bit more to this...

*Do plans ALWAYS get invalidated when you update statistics? No...

Erin Stellato (blog | twitter) first blogged about this here: Statistics and Recompilation. And, also here: Statistics and Recompilation, Part II.

Here's a quick summary though because it looks like things have changed again in SQL Server 2012...]]> http://www.sqlskills.com/BLOGS/KIMBERLY/ 459298@adders.eu/fever Mon, 03 Dec 2012 10:23:08 GMT This set of notes covers the main things that I think you need to know about working with git. It is not comprehensive and mainly serves as a reminder for myself.

    git clone git@github.com:akrabat/joind.in.git

    cd joind.in
    git remote add upstream git://github.com/joindin/joind.in.git

    git checkout master
    git fetch upstream
    git merge --ff-only upstream/master
    git push origin

    git checkout -b my-branch-name
    git push origin my-branch-name

    git branch -v

    git branch -v -a

    git checkout another-branch-name

    git branch -D my-branch-name
    git push origin :my-branch-name

    git fetch upstream
    git checkout my-branch-name
    git rebase -f upstream/master
    git push -f origin my-branch

    git fetch upstream
    git checkout master
    git branch -t upstream/remote-branch-name
    git push origin remote-branch-name

    git fetch upstream
    git checkout remote-branch-name
    git merge upstream/remote-branch-name
    git push origin

    git add filename

    git commit -m "my commit message"
    git push origin

    git checkout master
    git fetch upstream && git merge --ff-only upstream/master
    git merge --no-ff my-branch-name

    git push

    git add .
    git commit
    git push

    git reset --hard HEAD

    git reflog -10

    git log --oneline -10

    git log -1

    git checkout .

    git reset --hard abcdef0

    git reset --hard HEAD@{1}

    git commit --amend -m "New commit message"

    git diff

    git diff -- my-filename

    git diff master..HEAD my-filename

    git diff HEAD...origin/master

    $ git config --global alias.st status
    $ git config --global alias.staged 'diff --staged'
    $ git config --global alias.unstage 'reset HEAD --'
    $ git config --global alias.last 'log -1 HEAD'