Cloud-powered BLOB type provides ACID guarantees and fast direct access to blobs via Web URLs.

Storing unstructured data

Typically unstructured data (such as pictures, media files, documents)

a) Is either stored on the file system, unlike the related with it relational data which is stored in the database. This is well known, “convenient” practice that allows fast access to files but offers no transactional story and no unified data management (for db and filesystem)

b) Or is stored in BLOBs. This ensures transactional consistency and reduces management complexities, but is really bad for performance and scalability.

We took advantage of the cloud, and came up with an upgrade to the BLOB – a solution that combines the benefits of the two.

Weblob data type

Weblob is a new data type that is supported by the Cloud Storage Engine for MySQL ( …

As a tool author, I really look forward to working with MySQL 5.6. Many of the improvements will make life significantly easier for Percona Toolkit.

One illustration of this is in figuring out what the optimizer is doing when it plans a statement, and how it intends to use indexes. Compound indexes present challenges in some situations. Many of our tools have extensive checks to try to avoid executing queries that have bad execution plans. If the optimizer intends to use only a few of the columns in an index, how will we know?

Of course I have answers for this, but they aren’t as simple as they seem at first glance. The obvious method is to look at key_len in EXPLAIN. But what is the length of the full index? That’s a tricky thing to figure out. The simple case is always easy, but character sets, nullability, and so on make it much harder.

Having a lot more information about the process the planner uses to choose an …

A coworker came to me with a perplexing issue. He wanted to know why these two queries were not returning the same results:

mysql> SELECT COUNT(*) 
    -> FROM parent
    -> WHERE id NOT IN (SELECT parent_id FROM child);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (7.84 sec)

mysql> SELECT COUNT(*)
    -> FROM parent p
    -> WHERE NOT EXISTS(SELECT 1 
    ->                  FROM child c
    ->                  WHERE p.id = c.parent_id);
+----------+
| count(*) |
+----------+
|     5575 |
+----------+
1 row in set (2.95 sec)

At first (and second, and third) glance these two queries look identical. It obviously is an exclusion join and because the MySQL optimizer is what it is, I decided to rewrite it as a LEFT JOIN to see what results came back:

mysql> SELECT …

I’ve been seeing a lot of interest in pt-online-schema-change (nonblocking MySQL schema changes), with a lively discussion on the mailing list (which I think I’m not keeping up with…) and a couple of bug reports filed. I’m really interested whether people have tested it rigorously to ensure that it maintains your data integrity. I have done so, and there is a set of tests for it in the codebase, but nothing replaces real-world testing. If you find any problems or have questions, please address them to the percona-discussion Google Group.

Further Reading:

• Planned change in Maatkit & Aspersa development
• …

Below you will find all code to create the table and the stored procedures to manage hierarchical trees in MySQL. All code is documented and can be downloaded in a zip file.

In (My)SQL, join is a means for combining records from two tables into a single set which can be either returned as is or used in another join. In order to perform the operation a join has to define the relationship between records in either table, as well as the way it will evaluate the relationship. The relationship itself is created through a set of conditions that are part of the join and usually are put inside ON clause. The rest is determined through a join type, which can either be an inner join or an outer join.

The SQL clauses that set the respective join type in a query are [INNER] JOIN and {LEFT | RIGHT} [OUTER] JOIN. As you can see the actual keywords INNER and OUTER are optional and can be omitted, however outer joins require specifying the direction – either left or right (more on that later).

Examples of queries using …

Setting the mode to STRICT_ALL_TABLES when you compile your stored procedure or function can prevent a lot of subtle bugs in your MySQL application. Consider this example: DELIMITER // CREATE FUNCTION test(p_first TINYINT, p_second TINYINT) RETURNS TINYINT BEGIN     DECLARE v_result TINYINT;     SET v_result := p_first + p_second;     RETURN v_result; END// DELIMITER ; compile it and then test the funcion: mysql> [...]

A common problem with storing counters in a table is that every time your application update your counter, a row lock needs to be set on the row the table. If your application has a need for storing counters you can use this package which contains the scripts for a table and some stored procedures to handle managing the counters.

The tools we’ve redesigned in Percona Toolkit recently have moved away from a legacy technique for operating on small numbers of rows at a time, towards a more reliable and predictable method. We call the old version “chunking” and the new version “nibbling.” Many other MySQL tools I’ve seen either operate on entire tables, or use the “chunking” technique and are exposed to the problems it creates. I’ll compare the two briefly to explain the differences.

Chunking attempts to divide a table into ranges of rows of a desired size, such as 1000 rows. It does this by examining the minimum and maximum value of the primary key (or other suitable index), estimating the number of rows in the table, and dividing one by the other to create a list of boundary values. Suppose that the minimum value is 1 and the maximum is 1000000, and there are an estimated 100000 rows in the table. The chunk boundaries will fall on intervals of 10000. …

Please join me tomorrow (Wednesday) for a webinar about the new version of pt-online-schema-change, Percona Toolkit’s tool for performing nonblocking ALTER TABLE operations. Registration is free. I will explain how the tool works and cover some typical use scenarios. This might be helpful if you’re trying to decide whether the tool is right for you. I am also planning to leave plenty of time for questions!

Further Reading:

• Free webinar on MySQL performance this Thursday
• …