Planet Tao of Mac

WSJ on Sandboxing

2012-02-09T02:29:10Z

Jessica E. Vascellaro (via Jason Snell):

Mac developer Mark Munz, of Vancouver, Wash., says to comply with Apple’s new rules, he has to remove key features of his text-reformatting app TextSoap that integrate with other programs.

As a workaround, he’s working on a “helper app” that Mac App Store users could download separately to restore the extra functionality. “It sort of defeats the purpose of what sandboxing is about,” says Mr. Munz, who is president of Unmarked Software LLC.

Unfortunately, that approach isn’t an option if API bugs or omissions prevent core features of the application from working in the sandbox.

How iTunes Match Works for Copyright Holders

2012-02-09T02:05:30Z

Jeff Price (via John Gruber):

A person has a song on her computer hard drive. She clicks on the song and plays it. No one is getting paid. The same person pays iTunes $25 for iMatch. She now clicks on the same song and plays it through her iMatch service. Copyright holders get paid.

Two interesting points here. First, you pay Apple a flat fee for the ability to download, whether or not you actually do; Apple pays the music labels each time you actually download. So Apple is playing the odds here, hoping that the average user won’t have more downloads than expected. Second, the labels get paid even if you’re just downloading a backup of music that you’d already bought on CD. I can see why Apple likes this, but it doesn’t seem like a good precedent for users.

The Real Paper

2012-02-09T01:53:10Z

The Boston Phoenix says that The New York Times published a PDF of another newspaper’s article without obtaining permission (via Hacker News). However, I was unable to verify this because the paper of record has (apparently to avoid embarrassment) removed all references to said PDF.

Path Uploads Your Entire iPhone Address Book to Its Servers

2012-02-08T20:26:52Z

In 2010, I wrote:

I don’t understand why iOS makes such a big deal about permission to access location data, when any random app, even one that shouldn’t need network access at all, can access my address book, photos, and clipboard and upload them to who-knows-where.

Yesterday, Arun Thampi wrote:

Upon inspecting closer, I noticed that my entire address book (including full names, emails and phone numbers) was being sent as a plist to Path. Now I don’t remember having given permission to Path to access my address book and send its contents to its servers, so I created a completely new “Path” and repeated the experiment and I got the same result – my address book was in Path’s hands.

John Gruber notes that the response from Path is not very satisfying. Is this really “currently the industry best practice”? See, for example, these questions from Matt Gemmell. There’s no automated way to get them to delete your data. Rule 17.1 of the App Store Review Guidelines seems to prohibit this sort of behavior without the user’s consent, yet Path has been in the curated App Store for over a year and Apple doesn’t seem to have noticed that it’s sending this information back to the server ~~in cleartext~~. Now that the news has broken, Apple has neither pulled the app nor approved the update that asks users to opt in. As Peter Maurer says, “No technology will ever protect us from Trojan horses. Rules that destroy functionality are mere security theater.”

Update (2012-02-08): The official response from Path:

We are deeply sorry if you were uncomfortable with how our application used your phone contacts.

That could have been phrased better. Also, Gawker quotes Path CEO Dave Morin, in November 2010:

Path does not retain or store any of your information in any way.

Good thing they will “continue to be transparent.”

Also, in 2010, Dave Winer noticed that something wasn’t right, but he’d forgotten about it until now.

Update (2012-02-09): Gawker:

The official version from Morin is that the statement was technically accurate, at the time he made it. He just changed his mind.

Peter Maurer links to this screenshot of Path’s new opt-in alert, noting that it isn’t very transparent. It just asks whether you want to invite your friends, without explaining what this entails for your personal data. However, Ole Zorn links to this screenshot in which the alert actually says “Path needs to send contacts to our server.”

Digitals

2012-02-08T09:00:46Z

Chris Crutchfield created this awesome, freakish little video – Digitals – to recap a moment in time when he got an email, an sms, a phone call, a Facebook message and two tweets all within about 5 seconds of one another.

It’s interesting that it feels so “crazy” when seen like this in a movie, but this the reality for most of us, just another day in our lives.

Designing Better Service Experiences

2012-02-07T17:35:02Z

Continuum and the AIGA convened a group of top business leaders and innovators from a variety of backgrounds and industries to discuss how designing a service experience with purpose can transform a company’s image and make people’s lives better.

AirPort Utility 6.0

2012-02-07T16:11:26Z

Glenn Fleishman:

You may like, for most network purposes, the simplicity of version 6. It looks to me as though Apple has pruned at least half of the options available in nested dialog boxes and elsewhere. (You may also hate it for this reason.) For average users, having fewer choices with better presentation is a plus. For network admins and advanced users, you’ll be cursing Apple as you download the previous version.

Will the missing features be restored in an update to 6.0? AirPort Utility 5.6 works today, but what happens the next time Mac OS X and iCloud are updated? Since Apple has not stated its intent, I think you have to assume that the current 6.0 feature set is what they plan to support going forward.

As it happens, though, I’m no longer using AirPort Utility. A few months ago, I decided to retire a Time Capsule that I had inherited. I’d never been able to get Time Machine to work reliably with it. And something had changed so that, even though I never used its storage features, the hard drive was always spinning loudly. Apple’s $179 AirPort Extreme seemed like overkill. I just needed a wireless router with a few Ethernet ports—no AirTunes, printer sharing, or Back to My Mac. Plus, reviews mentioned that the newer Apple base stations make a high-pitched noise. That would drive me crazy when watching TV.

I ended up getting a $35 Cisco router. I ignored the sketchy looking software on the CD, which promised to copy over the settings from my old router, and just set it up via Safari. So far so good. Download speeds are actually faster than with the Time Capsule.

Urbanflow: an operating system for cities

2012-02-07T09:02:16Z

In a time when the urban population has outpaced the one living in rural environments, our cities although a marvelous achievement (think about the logistics and food provision systems that power up a city), they tend to be inefficient, slow and disconnected from the majority of their citizens.

Urbanflow proposition, a joint effort of Nordkapp and Urbanflow (founded by Adam Greenfield) aims to take advantage of urban screens as means to provide not just locally-oriented information (maps, points of interest, etc.) but also to provide a wide range of more generic information about the city (air quality, traffic, etc), converting what used to be a static snapshot of information into an interactive window towards the ins and outs the city.

I found here in Düsseldorf a very interesting example of a realtime sculpture – The Energy Pyramid which displays in realtime the amount of energy, gaz and warm water being consumed by the city.

Robot Readable World

2012-02-07T08:50:46Z

As robots begin to inhabit the world alongside us, how do they see and gather meaning from our streets, cities, media and from us?

Managing MySQL with Percona Toolkit by Frédéric Descamps

2012-02-05T15:58:15Z

Frédéric Descamps of Percona.

Percona Toolkit is Maatkit & Aspersa combined. Opensource and the tools are very useful for a DBA.

You need Perl, DBI, DBD::mysql, Term::ReadKey. Most tools are written in Perl, and whatever is in Bash is being re-written in Perl. There is also a tarball or RPM or DEB packages.

Know your environment. The hardware & OS are crucial for you to know. How much memory/CPU do you use? Do you use swap? Is this a physical/virtual machine? Do you have free space? What kind of RAID controller? Volumes? Disk? What about the network interfaces? What IO schedulers are used? Which filesystem is the data stored on? To answer all that, just use pt-summary.

Know your MySQL environment. Version? Build? How many databases? Where is the data directory? What about replication? What are key InnoDB settings? Storage engine in use? Index type? Foreign keys? Full text indexes? To answer all this and more use pt-mysql-summary.

pt-slave-find shows you the topology and replication hierarchy of your MySQL replication instances. An inventory of replicas!

Where is my disk I/O going? Use pt-diskstats which is an improved iostat. There is pt-ioprofile but it can be dangerous in production.

Now its time to get more intimate with your database. Let’s try to find the answer to these questions: how are the indexes used? Are there duplicate keys? Which queries are eating most of the resources? You can use pt-duplicate-key-checker to check for duplicate/redundant indexes or foreign keys. pt-index-usage can tell you which indexes are unused. If you think you have bad SQL, check out pt-query-advisor.

You can use pt-query-digest to analyze the slow query log and show a profile of the workload. You mostly use this with slow query logs & tcpdump’s. Be careful when you have dropped packets — results may tend to be fake then!

After all this, its time to maintain your environment.

pt-deadlock-logger checks InnoDB status to log MySQL deadlock information. It needs to run continually to capture things.

pt-fk-error-logger extracts and logs MySQL foreign key errors.

pt-online-schema-change to alter tables. It makes a “shadow copy” and swaps them. Extremely useful for large, long-running ALTER. Facebook uses the same technique.

Validate your upgrades as upgrades are the leading cause of downtime. Are queries using different indexes? Is query execution plan different? New errors? See pt-upgrade for this. Best to run this on a third machine (i.e. the old machine and a new machine to see how it goes).

Verify replication integrity – pt-table-checksum. Perform an online replication consistency check or checksum MySQL tables efficiently on one or many servers. Use it routinely (mandatory for 95% of MySQL users). Put it in a weekly crontab. Repair differences with pt-table-sync.

Repair out-of-sync replicas – pt-table-sync

Measure delay acfurately – pt-heartbeat

Deliberately delay replication – pt-slave-delay

Watch & restart MySQL replication after errors – pt-slave-restart

When there are problems, get the symptoms when it hurts. Look at pt-stalk (wait for a condition to occur them begin collecting data – eg. everytime the threads go over 2,000 you have a problem, so it collects stuff – it calls pt-collect), pt-collect (collect information from a server for some period of time), and pt-sift.

pt-mext looks at many samples of MySQL SHOW GLOBAL STATUS side-by-side. Default STATUS shows counter since the MySQL instances started. It is very helpful to see a delta of recent activity.

The future: pt-query-digest will do query reviews; pt-stalk will do “magical fault detection algorithm”. Its all opensource and its all on Launchpad at lp:percona-toolkit.

Replication features of 2011 by Sergey Petrunia

2012-02-05T14:32:45Z

Sergey Petrunia of the MariaDB project & Monty Program.

MySQL 5.5 GA at the end of 2010. MariaDB 5.3 RC towards the end of 2011 (beta in June 2011).

MySQL 5.5 is merged to Percona Server 5.5 which included semi-sync replication, slave fsync options, atuomatic relay log recovery, RBR slave type conversions (question if this is useful or not), individual log flushing (very useful, but not many using), replication heartbeat, SHOW RELAYLOG EVENTS. About 2/3rds of the audience use MySQL 5.5 in production, with only 2 people using semi-sync replication.

MariaDB 5.3 brings replication features brings group commit in the binary log, which is merged into Percona Server 5.5. Checksums for binlog events which is merged from MySQL 5.6. Sergey goes in-depth about the group commit for the binary log. To find out a little more about MariaDB replication changes, see Replication in the Knowledgebase.

There are several implementations of group commit. Facebook started it, followed by MariaDB & Oracle. Percona 5.5 is GA so the feature is there, its not in MySQL 5.6 (yet?), and MariaDB 5.3 is where its at. Seems like the MariaDB implementation is the best so far – refer to the Facebook benchmark performed by Mark Callaghan.

Annotated RBR poses a compatibility problem. MariaDB 5.3 has annotate_rows, while MySQL 5.6 has rows_query event. They are different events. So you cannot have a MariaDB 5.3 master and a MySQL 5.6 slave at this moment. So MySQL 5.6 will have a flag to mark “ignorable” binlog events which will be merged into MariaDB and this will make binary logs compatible again.

There is now also optimized RBR for tables with no primary key.

MySQL 5.6 also has crash-safe slave (replication information stored in tables). Crash-safe master (binary log recovery if the server starts & sees the binary log is corrupted). Parallel event execution is something that is new in MySQL 5.6 which is the most important feature for Sergey.

Pre-heating: There is mk-slave-prefetch (famous quote: “Please don’t use mk-slave-prefetch on #MySQL unless you are Facebook.”). There is replication booster by Yoshinori Matsunobu. There is a Python version of mk-slave-prefetch that Facebook uses.

MySQL Creatively in a Sandbox by Giuseppe Maxia

2012-02-05T13:57:47Z

Giuseppe Maxia of Continuent and long time creator of MySQL Sandbox.

Only works on Unix-like servers. Works with MySQL, Percona & MariaDB servers. MySQL server has the data directory, the port and the socket – you can’t share these.

To use it: make_sandbox foo.tar.gz. Then just do ./use.

$SANDBOX_HOME is ~/sandboxes. You can also create ~/opt/mysql/ and if you have MySQL 5.0.91 binary in that directory, you can just do “sb 5.1.91″.

Sandbox has features to start replication systems as well. You can have varying master/slave setups with varying versions as well (good idea to test from MySQL -> MariaDB master->slave for migration).

You can now also play with tungsten-sandbox, which is a great way to start playing with Tungsten Replicator (see documentation and tungsten-toolbox). There is apparently also a MySQL Cluster sandbox tool that someone is working on.

Optimizing your InnoDB buffer pool usage by Steve Hardy

2012-02-05T13:29:02Z

Steve Hardy of Zarafa.

Work that has been done to make Zarafa better. Why do you optimise your buffer pool? To decrease your I/O load. How can you do it? Buy more RAM, page compression, less (smaller) data, rearrange data.

MariaDB or Percona Server allows you to inspect your buffer pool (unsure if this is now available in MySQL 5.6). Giuseppe in the audience says this is available in MySQL 5.6, but Steve used this on MariaDB 5.2.

Strategies to fix it: Make records smaller. Remove indexes if you can use others almost as efficiently. Make records that are accessed around the same time have a higher chance of being on the same page. Use page compression. Buy more RAM. Try Batched Key Access (BKA) in MariaDB 5.3+.

Best to view the presentation since there are specific examples that speak about how Zarafa solves their problems like a user trying to sort their email, etc.

Practical MySQL Indexing guidelines by Stéphane Combaudon

2012-02-05T13:01:06Z

Stéphane Combaudon of Dailymotion.

Index: separate data structure to speed up SELECTs. Think of index in a book. In MySQL, key=index. Consider that indexes are trees.

InnoDB’s clustered index – data is stored with the Primary Key (PK) so PK lookups are fast. Secondary keys hold the PK values. Designing InnoDB PK’s with care is critical for performance.

An index can filter and/or sort values. An index can contain all the fields needed for the query you don’t need to go to the table (a covering index).

MySQL only uses 1 index per table per query (not 100% true – OR clauses), so think of a composite index when you can. Can’t index TEXT fields (use a prefix). Same for BLOBs and long VARCHARs.

Indexes: speed up queries, increases the size of your dataset, slows down writes. How big is the write slowdown? Simple test by Stephane, for in-memory workloads he says adding 2 keys makes performance 2x worse; for on-disk workloads he says its 40x worse. Never neglect the slowdown of your writes when you have an index. There is a graph in the slidedeck.

What is a bad index? Unused indexes. Redundant indexes. Duplicate indexes.

Indexing is not an exact science, but guessing is probably not the best way to design indexes. Always check your assumptions – EXPLAIN does not tell you everything, time your queries with different index combinations, SHOW PROFILES is often valuable. Slow query log is a good place to start.

Many slides with examples, so I hope Stephane posts the deck soon. If possible, try to sort & filter (an index is not always the best for sorting).

InnoDB’s clustered index is always covering. SELECT by PK is the fastest access with InnoDB.

An index can give you 3 benefits: filtering, sorting, covering.

See Userstats v2 - you need Percona Server or MariaDB 5.2+. See also pt-duplicate-key-checker to find redundant indexes easily. See also pt-index-usage to help answer questions not covered by userstats.

MySQL synchronous replication in practice with Galera by Oli Sennhauser

2012-02-05T12:30:42Z

Oli Sennhauser of FromDual.

Synchronous multi-master replication with the Galera plugin. Your application connects to the load balancer and it redirects read/write traffic to the various MySQL Galera nodes. Tested a setup with 17 SQL nodes and you can have even more. Scaling reads and also a little bit for scaling writes is what Galera is good for.

If one node fails, the other two nodes still communicates with each other and the load balancer is aware of the failed node.

Why Galera? There is master-slave replication but its not multi-master, and its asynchronous and you can get inconsistencies. There is master-master replication but its asynchronous and can have inconsistencies and conflicts if you write on both nodes. MHA/MMM/Tungsten are not providing new technology but are based on the MySQL replication technology. MySQL Cluster is another solution but its not InnoDB storage & your need new know-how for Cluster. Also Cluster has problems with fast JOINs. Active/Passive failover clustering, but too often you have resources idling. Schooner being closed & expensive is hard to know much about what they’re doing.

Galera is synchronous & based on InnoDB (others should in theory be possible). Active-active real multi-master topology. True parallel replication on row level. Cluster speaks with each other. There is no slave lag. Won’t lose transactions. Read/write scalability, write throughput can be improved but can’t scale in the way like MySQL Cluster.

Disadvantages? Its not native MySQL binaries/sources but a patch. Codership provides binaries. Higher probability of deadlocks. When you do a full sync (like when a node comes back after downtime), one node is blocked. This is why the minimum you need a 3-node cluster. Also if you do a full sync with a database larger than 50GB, the recommended method is to use mysqldump (which can be very slow). You can use rsync. Percona is working on xtrabackup to do a full sync between nodes.

Setup: 3 nodes are recommended. Or just 2 nodes and one for garbd (Galera Arbitrator Daemon). 2 nodes works but pay attention to a split brain scenario. Go to the Codership website, download their binaries and wsrep (the Galera plug-in). Create your own user on all nodes (don’t use the default root user). You then need to configure my.cnf (there have been discussions for a galera.conf, but Oli just uses my.cnf). Galera works only with InnoDB, so in my.cnf make the default storage engine InnoDB (don’t for example, by accident have MyISAM tables).

The demo has a strange Galera start script, but its not been easy to work. Just start MySQL usually like you would do.

SST is Snapshot State Transfer (SST). Its the initial full sync between the 1st and the other node. SST blocks the donor node (hence why you need 3 nodes). With Galera v2.0, there is also incremental state transfer. It should be GA in February 2012. You can get deltas as opposed to the full sync. You can configure which will be the donor node.

Currently there are 27 variables about Galera in v1.1. You can do it just by doing SHOW GLOBAL VARIABLES LIKE ‘wsrep%’;. The plugin itself, wsrep_provider_options has plenty of options & plenty of room for tuning. SHOW GLOBAL STATUS LIKE ‘wsrep%’; currently has 38 status information fields in Galera v1.1.

For load balancing, you can do it in your application (on your own). You can also use Connector/J which provides load balancing. There is also a PHP MySQLnd that works.

Optimising SQL applications by using client side tools by Mark Riddoch

2012-02-05T11:54:14Z

Mark Riddoch of SkySQL.

This was a talk about the future in general. What people would like. Etc. Not about something that exists yet, hence the sparse notes.

Trace statements are good for the “why”. You move on to the debugger, but what is a useful SQL debugger? Profilers addresses the “when”.

SQL developer tools: manually run queries (traditional route for development, effective way to test SQL statements, some indication of performance), server logs (alerts developers of serious issues like the slow query log – identify poorly written queries, requires server access, not effective in a cloud environment (?)), external monitoring (network sniffing of connection packets, Ethereal dissectors – allow individual connections to be traced, no server access, privileged network access, complex to interpret, possibility of packet loss), intrusive tools (insert “proxy” between client & server to intercept all traffic, imposes delay & requires setup modification).

Client-side tools – client modification or hook via connectors (query logging, profiling). The Java connector has profiling. Should there be a connector slow query log? So there’s no requirement for server access and its per client rather than per server.

Plans: roll out connector query logs for Java, C, and scripting languages built on Connector/C. SQL Parser for report generation/fuzzy matching. Create post-processing tools. Query log comparisons – detect fluctuation in execution time, plan flip alerting, replay log.

MySQL Cluster by Ralf Gebhardt

2012-02-05T11:25:32Z

Ralf Gebhardt of SkySQL.

Cluster: shared nothing architecture (no single point of failure), synchronous replication between nodes, ACID transactions, row level locking. In-memory storage (some data can be stored on disk, but indexes must be in-memory). Checkpointing to disk for durability. It supports two types of indexes – ordered T-trees, unique hash indexes. Online operations like adding node groups, software upgrades, table alterations. Quick standard architecture diagram displayed about MySQL Cluster.

Network partitioning protocol is designed to avoid a split brain scenario. Is there at least one node from each node group? If not then this part cannot continue – graceful shutdown. Are all nodes present from any node group? If so, then this is the only viable cluster – continue to operate. Ask the arbitrator – the arbitrator which parts will continue if no arbitrator is available the cluster shuts down.

Durability – in order for a node to recover fast some data is stored locally. The REDO log is synchronized by global checkpoints (GCP). The DataMemory is synchronized by local checkpoints (LCP).

I agree with Ralf — almost impossible to talk about NDB in 25 minutes. Its very deep, you’d need at least three hours to grasp it well.

Building simple & complex replication clusters with Tungsten Replicator by Giuseppe Maxia

2012-02-05T11:01:26Z

Giuseppe Maxia of Continuent.

MySQL replication is single threaded. Multi-master replication is complex with MySQL. Circular replication works but is very fragile. Once you’ve achieved the feat, how do you avoid conflicts? The lack of global transaction ID today also means you may have slaves that may not be synchronized fully. Finally, some people like to replicate to PostgreSQL, Oracle and MongoDB.

This is where Tungsten Replicator comes into place. Opensource. 100% GPLv2. You can do easy failover (no need to synchronize the slaves manually when a master dies), have multiple masters, multiple sources to a single slave, conflict prevention, parallel replication, and replicate to Oracle/PostgreSQL database (heterogeneous replication – Oracle->MySQL is not opensource, but MySQL->Oracle is).

Parallel replication: ability to replicate with multiple thread at once. Sharded by database (all big sites already use this naturally). Good choice for slave lag problems. Bad choice for single database projects. In their tests to measure slave catch up time between standard MySQL & Tungsten slave with 1hr of sysbench and some 130GB of data, MySQL replication takes 4.5h to catchup, and Tungsten takes less than 1 hour to catchup. This comparison was not made with MySQL 5.6 (the same parallel replication feature at this moment in 5.6 is quite buggy). No need to install Tungsten on the master (Tungsten needs to be on slave). Replication can revert to native salve with 2 commands, but the failover is an issue — so install Tungsten on both!

Conflict prevention. When you have multiple masters you are tempted to do many things that you shouldn’t. Tungsten provides the multi-master topology. Tungsten can help you avoid conflicts. Decide you want to shard your information by database – Tungsten can enforce such rules. Define the rules, applied to either the master or the slave and you can then state to make replication fail or drop silently or drop with a warning.

You can create clusters very quickly with Tungsten. You can install from a centralized point to many servers. You need Java, Ruby, SSH access and a MySQL user with all privileges (used for replication).

How to offload MySQL server with Sphinx by Vladimir Fedorkov

2012-02-05T10:29:05Z

Vladimir Fedorkov of Sphinx.

Presentation started out with a very nice presentation of candies to all the audience members.

What is Sphinx? Another (C++) daemon on your boxes. Can be queried via API (PHP, Python, etc.) or MySQL-compatible protocol and SQL queries (SphinxQL). Some query examples are in the slides, here’s one about SphinxSE in the KB.

MyISAM FTS is good but becomes slow with half a million documents. InnoDB has FTS now but he’s not tried it (and neither has anyone in the audience to see it compare with MyISAM FTS).

Geographical distance is the distance measuring the surface of the earth (two pairs of float values – latitude, longitude). In Sphinx, there is support for GEODIST(Lat,Long,Lat2,Long2) in Sphinx.

Segments are good for price ranges on a site, date ranges, etc. Use INTERVAL(field, x0, x1, …, xN).

Keep huge text collections out of the database. sql_field = path_to_file_text. Tell Sphinx to index text not from MySQL but out in the filesystem. Keep the metadata inside the database but keep the actual data outside of the database. max_file_field_buffer needs to be set properly.

You can do proximity search with Sphinx — find the words “hello world” within a ten word block, for example.

Resources: the documentation, a book by O’Reilly: Introduction to Search with Sphinx: From installation to relevance tuning (sold out at the FOSDEM O’Reilly booth!), and their community page including wiki, forum, etc.

MariaDB 5.3 query optimizer by Sergey Petrunia

2012-02-05T09:59:23Z

Sergey Petrunia of the MariaDB project.

What exactly is not working in MySQL? MySQL is poor at decision support/analytics. With large datasets you need special disk access strategies. Complex queries like insufficient subquery support and big joins are common int he MySQL world.

DBT-3 is used, scale=30, with a 75GB database and run a query “average price of item between a range of dates”. Query time took some 45 minutes to execute. Why? Run iostat -x to see what is going on. See that the CPU is mostly idle, so its an IO-bound load. Next you run SHOW ENGINE INNODB STATUS and you’ll see how many reads per second is happening. Possible solution is to get more RAM or get an SSD (good to speedup OLTP workloads, but analytics over data is probably not viable since SSDs are small and not cheap).

The MySQL/MariaDB solution to the above problem is improved disk access strategies: multi-range read (MRR) and batched key access (BKA). In MariaDB, MRR/BKA need to be enabled (they are not turned on by default). The query time only took 3 minutes 48 seconds, which is some 11.8x faster than the previous 45 minutes.If you look at EXPLAIN output, its almost as same as before, expect the Extra filed. iostat -x will now show some CPU load, svctm down as well (so its not random disk seeks anymore — some 8ms seek time on a regular 7,200rpm disk), SHOW ENGINE INNODB STATUS will show some 10,000 reads per second rather than the previous 200.

If you are on Fedora, check out the Systemtap feature to look at I/O patterns. stap deviceseeks.stp -c “sleep 60″.

Subqueries handling in MariaDB 5.3: check out the Subquery Optimizations Map. Only about 10% of the audience use optimizer hints in MySQL.