As I’ve become a better shell programmer over the last year or two, I’ve been surprised to discover some tools I didn’t know about. It eventually dawned on me, as I did more and more brute-force processing of large datasets, as well as some of the more delicate things that went into Aspersa -> Percona Toolkit, that many tasks I used to do with SQL and spreadsheets can be accomplished easily with well-structured text files and Unix utilities. And they don’t require loading data into a database or spreadsheet (the latter of which almost always performs terribly).

To give an idea, here are some of the relational operations (in SQL speak) you can perform:

1. SELECT col1, col2… can be implemented with several variants of Unix utilities: cut and awk are the two most obvious. I tend to use awk only when needed, or when it’s more convenient to combine …

The MySQL Server has a quite simple interface for plugins to create tables inside INFORMATION_SCHEMA. A minimal plugin for creating a table with nothing but a counter might look like this:

static int counter_fill_table(THD *thd, TABLE_LIST *tables, Item *cond)
{
  ulonglong value= 0;
  
  while (1)
  {
    table->field[0]->store(value++, true);
  }
  
  return 0;
}

static ST_FIELD_INFO counter_table_fields[]=
{
  {"COUNT", 20, MYSQL_TYPE_LONGLONG, 0, MY_I_S_UNSIGNED, 0, 0},
  {0, 0, MYSQL_TYPE_NULL, 0, 0, 0, 0}
};

static int counter_table_init(void *ptr)
{
  ST_SCHEMA_TABLE *schema_table= (ST_SCHEMA_TABLE*)ptr;

  schema_table->fields_info= counter_table_fields;
  schema_table->fill_table= counter_fill_table;
  return 0;
}

static struct st_mysql_information_schema counter_table_info =
{ MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION };

mysql_declare_plugin(counter)
{
  MYSQL_INFORMATION_SCHEMA_PLUGIN,
  &counter_table_info,          /* type-specific …

This Log Buffer Edition once again touches upon the bold and best blog posts from the wide world of Oracle, MySQL and SQL Server. This Log Buffer #290 is all about ideas and their implementation and much more. Oracle: Reliability is inherently a property of systems, not of people, says Paul Vallee. How to restore [...]

Last Summer I implemented a non-blocking client API in MariaDB, and it was included in the MariaDB 5.5 release. But somehow I never got around to announcing it.

However, that did not prevent Brian White from noticing it, and using it to implement a new mysql binding for node.js called mariasql.

Now, node.js is a single-threaded, event-driven framework for web application sever development. In such frameworks, all I/O is done non-blocking or asynchronously, as are all other actions that may need to wait for external events. There is a single event loop which uses a poll() or similar system call to wait for any pending I/O or other event to complete, and then dispatches the appropriate event handler(s). Such frameworks are often used instead of a multi-threaded …

In a previous post, I had demonstrated how subquery materialization, introduced in MySQL 5.6.5, improves the performance of certain queries, like query Q16 of DBT3. Such improvement was easily explained:

• Subquery materialization has a high start up cost (it needs to create and fill the temporary table).
• But afterwards it has fast lookups (temporary table has a hash index, no duplicates, and is in memory).
• In other words, compared to EXISTS, the first evaluation of the IN predicate is slow (high start up cost) and all following evaluations are fast (just a hash lookup).
• In the DBT 3 setup, one outer table (named "part") has 200,000 rows, so there are 200,000 evaluations of …

Percona is glad to announce the release of Percona Server 5.5.27-29.0 on October 11th, 2012 (Downloads are available here and from the Percona Software Repositories).

Based on MySQL 5.5.27, including all the bug fixes in it, Percona Server 5.5.27-29.0 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.5.27-29.0 milestone at Launchpad.

New Features:

• …

If you missed Percona Live MySQL Conference in NYC, download these Continuent Tungsten talks!

FUTURE-PROOFING MYSQL FOR THE WORLD-WIDE DATA REVOLUTION by Robert Hodges SOLVING LARGE-SCALE DATABASE ADMINISTRATION WITH CONTINUENT TUNGSTEN by Robert Hodges and Neil Armitage CONSOLIDATE YOUR SHARDED DATABASE INDEXES IN REAL-TIME by Jeff Mace

We are sponsoring the upcoming Percona Live events in

Mark Leith writes that it’s time to say goodbye to SHOW PROCESSLIST in MySQL 5.6, and use the Performance Schema replacement for it instead, because the older tools cause some blocking, and the Performance Schema replacement is completely non-blocking.

On the face of it that’s a good thing, but I wonder whether we’ll want to keep some blocking functionality around anyway. Inspecting systems that are doing concurrent work can be hard unless you can see a variety of views on them. One such is looking at the state of all the concurrent work at an instant in time. This is sometimes indispensable for troubleshooting: you will see causes and effects you’ll never see in counters and metrics, no matter how many you capture or how sophisticated the analysis.

I haven’t seen MySQL 5.6 in production usage …

Last week, Baron wrote a great post entitled “What’s the benefit of the cloud?” The post was short and made the point that “the benefit of the cloud” is “less ops, more dev.” But Baron is coming from the point of a developer, and from the point of an ops person, there is not necessarily “less ops”.

Some commenters made points along the lines of, “you can just rent rack space in some datacenter for that.” And I agree. There are some ops benefits that Amazon adds, such as easier monitoring and backups, but for the most part, there is not *less* work from an operations standpoint when you are in a cloud environment – my time doing remote DBA work at Pythian and PalominoDB certainly taught me that!

There are still …

Last week, Baron wrote a great post entitled “What’s the benefit of the cloud?” The post was short and made the point that “the benefit of the cloud” is “less ops, more dev.” But Baron is coming from the point of a developer, and from the point of an ops person, there is not necessarily “less ops”.
Some commenters made points along the lines of, “you can just rent rack space in some datacenter for that.” And I agree. There are some ops benefits that Amazon adds, such as easier monitoring and backups, but for the most part, there is not *less* work from an operations standpoint when you are in a cloud environment – my time doing remote DBA work at Pythian and PalominoDB certainly taught me that!
There are still …