Month: February 2012

MySQL monitoring: storing, not caching

MySQL

I’ve followed with interest on Baron’s Why don’t our new Nagios plugins use caching? and Sheeri’s Caching for Monitoring: Timing is Everything. I wish to present my take on this, from mycheckpoint‘s point of view.

So mycheckpoint works in a completely different way. On one hand, it doesn’t bother with caching. On the other hand, it doesn’t bother with re-reads of data.

There are no staleness issues, the data is consistent as it can get (you can never get a completely atomic read of everything in MySQL), and you can issue as many calculations as you want at the price of one take of monitoring. As in Sheere’s example, you can run Threads_connected/max_connections*100, mix status variables, system variables, meta-variables (e.g. Seconds_behind_master), user-created variables (e.g. number of purchases in your online shop) etc.

mycheckpoint‘s concept is to store data. And store it in relational format. That is, INSERT it to a table.

A sample-run generates a row, which lists all status, server, OS, user, meta variables. It’s a huge row, with hundreds of columns. Columns like threads_connected, max_connections, innodb_buffer_pool_size, seconds_behind_master, etc.

mycheckpoint hardly cares about these columns. It identifies them dynamically. Have you just upgraded to MySQL 5.5? Oh, there’s a new bunch of server and status variables? No problem, mycheckpoint will notice it doesn’t have the matching columns and will add them via ALTER TABLE. There you go, now we have a place to store them.

Running a formula like Threads_connected/max_connections*100 is as easy as issuing the following query:

SELECT Threads_connected/max_connections*100 FROM status_variables WHERE id = ...

Hmmm. This means I can run this formula on the most recent row I’ve just added. But wait, this also means I can run this formula on any row I’ve ever gathered. Continue reading » “MySQL monitoring: storing, not caching”

Call for Nominations for 2012 MySQL Community Awards

MySQL

This post complements Henrik’s Call for Nominations for 2012 MySQL Community Awards.

Recap: we keep the tradition of awarding MySQL community members for their notable contributions to the MySQL ecosystem.

Previously, the awards were given by MySQL AB/Sun. Later on they were given by the community itself, as will follow this year, when the awards are presented during the Percona Live MySQL Conference & Expo in Santa Clara, this April.

Henrik describes in details the three categories: community contributor, application, corporate contributer -of the year.

A bit more about the categories

To add to Henrik’s description of the categories, keep in mind the following:

  • Community member would be a person. That person could be working by himself/herself, or can be part of some corporate. It does not matter. What matters is the person’s contribution.
  • Application: some code or project which either enhance/complement MySQL (e.g. Replication/HA solution) or uses MySQL. In the latter case, it is important that MySQL role’s in the application is significant. For example, some popular site built with some CMS using MySQL may not qualify, if it could run just the same with PostgreSQL or other databases, or if the owners are not actually aware or at all mindful about the specific database technology they are using.
  • Corporate: we’re still figuring this one out. The general idea is to acknowledge a corporate which, some way or the other, did a good thing to the MySQL ecosystem or the community.

Continue reading » “Call for Nominations for 2012 MySQL Community Awards”

MySQL/QueryScript use case: DELETE all but top N records per group

MySQL

Some administrative tasks can be simplified by using common_schema/QueryScript. I’m collecting a bunch of these for documentation. Here’s one for example:

The DBA/developer has the task of retaining only top 3 most populated countries per continent. That is, she has to DELETE 4th, 5th, 6th, … most populated counties in each continent.

Is it possible to work out with a single query? Yes. But the query is not pretty. In fact, it is quite complicated, and either involves unintuitive subqueries, or unintuitive hacks. A normal DBA would not want to write, neither maintain this kind of query, unless top-notch-geek, which is fine.

Since this is a one time job, we just need to get it done. And common_schema/QueryScript provide with the intuitive solution: if we read our demand aloud, we realize we want to delete 4th, 5th, 6th, … populated countries for each continent.

I present a solution made available by QueryScript, and discuss the ways in which the code overcomes limitations, or simplifies complexity:

var $num_countries_to_delete;
foreach($continent, $num_countries: SELECT continent, COUNT(*) FROM world.Country GROUP BY continent)
{
  if ($num_countries > 3)
  {
    set $num_countries_to_delete := $num_countries - 3;
    DELETE FROM world.Country WHERE Continent = $continent ORDER BY Population ASC LIMIT :$num_countries_to_delete;
  }
}

Discussion

The first thing that should be apparent from the above is that this is a programmatic solution. Queries are declarative, which is why complex ones sometimes look incomprehensible. The above is more straightforward. Continue reading » “MySQL/QueryScript use case: DELETE all but top N records per group”

common_schema rev. 218: QueryScript, throttling, processes, documentation

MySQL

common_schema, revision 218 is released, with major new features, top one being server side scripting. Here are the highlights:

  • QueryScript: server side scripting is now supported by common_schema, which acts as an interpreter for QueryScript code.
  • Throttling for queries is now made available via the throttle() function.
  • Enhancements to processlist-related views, including the new slave_hosts view.
  • Inline documentation/help is available via the help() routine.
  • more…

QueryScript

common_schema makes for a QueryScript implementation for MySQL. You can run server side scripts, interpreted by common_schema, which allow for easy syntax and greater power than was otherwise previously available on the MySQL server. For example:

foreach($table, $schema, $engine: table like '%')
  if ($engine = 'ndbcluster')
    ALTER ONLINE TABLE :$schema.:$table REORGANIZE PARTITION;

QueryScript includes flow control, conditional branching, variables & variable expansion, script throttling and more.

Read more on common_schema’s QueryScript implementation.

Continue reading » “common_schema rev. 218: QueryScript, throttling, processes, documentation”

QueryScript: SQL scripting language

MySQL

Introducing QueryScript: a programming language aimed for SQL scripting, seamlessly combining scripting power such as flow control & variables with standard SQL statements or RDBMS-specific commands.

QueryScript is available fro MySQL via common_schema, which adds MySQL-specific usage.

What does QueryScript look like? Here are a few code samples:

Turn a bulk DELETE operation into smaller tasks. Throttle in between.

while (DELETE FROM archive.events WHERE ts < CURDATE() LIMIT 1000)
{
  throttle 2;
}

Convert all InnoDB tables in the ‘sakila’ database to compressed format:

foreach ($table, $schema, $engine: table in sakila)
{
  if ($engine = 'InnoDB')
    ALTER TABLE :$schema.:$table ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
}

Shard your data across multiple schemata:

foreach($shard: {USA, GBR, JAP, FRA})
{
  CREATE DATABASE db_:$shard;
  CREATE TABLE db_:$shard.city LIKE world.City;
  INSERT INTO db_:$shard.city SELECT * FROM world.City WHERE CountryCode = $shard;
}

Continue reading » “QueryScript: SQL scripting language”

MySQL command line vs. visual editors – reflections

MySQL

My previous post drew some attention, and in particular two comments I wish to relate to. I also wish to make a more fine-grained observation on visual editors.

One comment is by Peter Laursen, who rejected the generalization in my post, and another by wlad, who was harsher (but to the point), suggesting my post translated to “I don’t know it, therefore it sucks”.

I must have delivered the wrong message, since apparently people read my post as “don’t use visual editors, they are bad for you”, which is not what I intended to say, nor is it what I actually think. I took a very specific aspect of visual editors and commented on that. My comment should not extrapolate to “anything about visual editors is bad”.

I don’t think people should only be using the command line, and am not in the least interested in vi/emacs/eclipse/netbeans kind of wars. Of course everyone should be using whatever works best for them in terms of productivity and ease of use. And of course visual editors have great advantages over command line.

Continue reading » “MySQL command line vs. visual editors – reflections”