When the MySQL server receives a SELECT query, the query goes through several consecutive phases:

• parsing: SQL words are recognized, the query is split into different parts following the SQL grammar rules: a list of selected expressions, a list of tables to read, a WHERE condition, …
• resolution: the output of the parsing stage contains names of columns and names of tables. Resolution is about making sense out of this. For example, in “WHERE foo=3“, “foo” is a column name without a table name; by applying SQL name resolution rules, we discover the table who contains “foo” (it can be complicated if subqueries or outer joins are involved).
• optimization: finding the best way to read tables: the best order of tables, and for each table, the best way to access it (index lookup, index scan, …). The output …

This week we discuss more Percona tools: pt-online-schema-change and pt-slave-find. Ear Candy is using the CONNECT storage engine to read GPX files, and At the Movies is Performance at Scale with TokuDB.

Events
DB Hangops - every other Wednesay at noon Pacific time

Upcoming MySQL events

OSCon 2014 at the Oregon Convention Center in Portland, Oregon from Sunday July 20th through Thursday July 24th.

Training
SkySQL Trainings

read more

While Shard-Query can work over multiple nodes, this blog post focuses on using Shard-Query with a single node.  Shard-Query can add parallelism to queries which use partitioned tables.  Very large tables can often be partitioned fairly easily. Shard-Query can leverage partitioning to add paralellism, because each partition can be queried independently. Because MySQL 5.6 supports the partition hint, Shard-Query can add parallelism to any partitioning method (even subpartioning) on 5.6 but it is limited to RANGE/LIST partitioning methods on early versions.

The output from Shard-Query is from the commandline client, but you can use MySQL proxy to communicate with Shard-Query too.

In the examples I am going to use the schema from the Star Schema Benchmark.  I generated data for scale factor 10, which means about 6GB of data in the largest table. I am going to show a few different queries, and …

Benchmarking lets you test how a system responds when it is given work to do, and removes the guesswork from your performance tuning efforts. The workloads you use when benchmarking are very different from real life deployments, which can be extremely variable. If you remember this, benchmarking is extremely useful, enabling you to measure current performance and determine the performance impact of any changes. Such changes could be the addition of new hardware or opening up your application to more users.

Benchmarking is one of the topics that you can learn more about by taking the MySQL Performance Tuning course. You can take this course in the following formats:

• Training-on-Demand: Start training within 24 hours of registration, following lecture …

The latest benchmarks show that MySQL 5.7 is now able to scale to 60 cores, which is quite an incredible feat when you compare to the 4-8 core scaling of MySQL 5.1 just a few years ago. These improvements are the result of a lot of heavy lifting to reorganize internal locking structures, and I have an earlier blog post on what is a mutex anyway? which may help serve as an introduction.

While I consider horizontal scaling and projects like MySQL Fabric to be very important, it should be stated that horizontal and vertical scaling are really orthogonal choices. That is to say that a given database technology should ideally support both options, …

This week we discuss new optimizer improvements in MariaDB 10. Ear Candy is about the MariaDB threadpool, and At the Movies is Oracle's Tomas Ulin talking about raising the bar with MySQL.

Events
DB Hangops - every other Wednesay at noon Pacific time

Upcoming MySQL events

Training
SkySQL Trainings

Tungsten University trainings

read more

Apache Hadoop is commonly used for data analysis. It is fast for data loads and scalable. In a previous post I showed how to integrate MySQL with Hadoop. In this post I will show how to export a table from  MySQL to Hadoop, load the data to Cloudera Impala (columnar format) and run a reporting on top of that. For the examples below I will use the “ontime flight performance” data from my previous post (Increasing MySQL performance with parallel query execution). I’ve used the Cloudera Manager v.4 to install Apache Hadoop and Impala. For this test …

A little background…

When I ventured into sales and marketing (I’m an engineer by education) I learned I would often have to interpret and simply summarize the business value that is sometimes hidden in benchmarks. Simply put, the people who approve the purchase of products like TokuDB® and TokuMX™ appreciate the executive summary.

Therefore, I plan to publish a multipart series here on TokuView where I will share my simple summaries and thoughts on business value for the benchmarks Mark Callaghan (@markcallaghan), a former Google and now Facebook database guru, is publishing on his blog, Small Datum.

I’m going to start with his first benchmark post and work my way forward to …

This week we talk to Justin Swanhart about Shard-Query, a massively parallel processing query engine for MySQL. Ear candy is about mydumper, and At the Movies is "Common Deadly MySQL Development Mistakes."

Events
Percona Live
DB Hangops - every other Wednesay at noon Pacific time
Upcoming MySQL events

Training
SkySQL Trainings

read more

This is “A new dimension to MySQL query optimizations – part 2″. If you didn’t read part 1 already I suggest you skim through it before reading on.

To summarize, the problem at hand is this: Given a query with a join between two or more tables, the MySQL optimizer’s mission is to find the best combination of join order and access method so that the response time becomes as low as possible. The optimizer does this by calculating the cost of each combination and then picks the cheapest one.

Consider the following query:

SELECT *
FROM employee JOIN department ON employee.dept_no=department.dept_no
WHERE employee.first_name="John" AND
      employee.hire_date BETWEEN "2012-01-01" AND "2012-06-01"

The optimizer will calculate the cost of the alternative plans as follows:

total cost = …