Year: 2013

Easy SELECT COUNT(*) with split()

MySQL

The two conservative ways of getting the number of rows in an InnoDB table are:

  • SELECT COUNT(*) FROM my_table:
    provides with an accurate number, but makes for a long running transaction which take ages on large tables. Long transactions make for locks
  • SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=’my_schema’ AND TABLE_NAME=’my_table’, or get same info via SHOW TABLE STATUS.
    Gives immediate response, but the value can be way off; it can be two times as large as real value, or half the value. For query execution plans this may be a “good enough” estimation, but typically you just can’t trust it for your own purposes.

Get a good estimate using chunks

You can get a good estimate by calculating the total number of rows in steps. Walk the table 1,000 rows at a time, and keep a counter. Each chunk is its own transaction, so, if the table is modified while counting, the final value does not make for an accurate account at any point in time. Typically this should be a far better estimate than TABLE_ROWS.

QueryScript’s split() construct provides you with the means to work this out. Consider this script: Continue reading » “Easy SELECT COUNT(*) with split()”

Converting compressed InnoDB tables to TokuDB 7.0.1

MySQL

Or: how to make it work in TokuDB version 7.0.1. This is a follow up on a discussion on the tokudb-user group.

Background

I wanted to test TokuDB’s compression. I took a staging machine of mine, with production data, and migrated it from Percona Server 5.5 To MariaDB 5.5+TokuDB 7.0.1. Migration went well, no problems.

To my surprise, when I converted tables from InnoDB to TokuDB, I saw an increase in table file size on disk. As explained by Tim Callaghan, this was due to TokuDB interpreting my compressed table’s “KEY_BLOCK_SIZE=4” as an instruction for TokuDB’s page size. TokuDB should be using 4MB block size, but thinks it’s being instructed to use 4KB. Problem is, you can’t get rid of table options. When one converts a table to InnoDB in ROW_FORMAT=COMPACT, or even to MyISAM, the KEY_BLOCK_SIZE option keeps lurking in the dark.

So until this is hopefully resolved in TokuDB’s next version, here’s a way to go around the problem. Continue reading » “Converting compressed InnoDB tables to TokuDB 7.0.1”

mycheckpoint revision 231 released

MySQL

A new release for mycheckpoint: lightweight, SQL oriented MySQL monitoring solution.

If you’re unfamiliar with mycheckpoint, well, the one minute sales pitch is: it’s a free and open source monitoring tool for MySQL, which is extremely easy to install and execute, and which includes custom queries, alerts (via emails), and out of the box HTTP server and charting.

This is mostly a maintenance release, with some long-time requested features, and of course solved bugs. Here are a few highlights:

  • Supports MariaDB and MySQL 5.6 (issues with new variables, space padded variables, text-valued variables)
  • Supports alerts via function invocation on monitored host (so not only checking alerts via aggregated data like ‘Seconds_behind_master’ but also by SELECT my_sanity_check_function() on monitored instance). See alerts.
  • Supports single-running-instance via “–single” command line argument
  • Supports strict sql_mode, including ONLY_FULL_GROUP_BY, overcoming bug #69310.
  • Supports sending of pending email HTML report
  • Better re-deployment process
  • Better recognizing of SIGNED/UNSIGNED values
  • Some other improvements in charting, etc.

mycheckpoint is released under the BSD license.

Downloads are available from the project’s page.

opeark-kit revision 196 released

MySQL

This is a long due maintenance release of openark-kit. This release includes bugfixes and some enhancements, mainly to oak-online-alter-table.

oak-online-alter-table Changes / bug fixes include:

  • Support for keyword-named columns
  • Use of FORCE INDEX due to lack of MySQL’s ability for figure out the chunking key at all times
  • –sleep-ratio option added; allows for sleep time proportional to execution time (as opposed to constant sleep time with –sleep)
  • Support for chunk-retry (in case of deadlock) via –max-lock-retries)
  • Fixed order of cleanup
  • Fixed bug with verbose messages with non-integer chunking key
  • Fixed bug with single-row tables (people, no need for this tool for single row tables :))
  • Friendly verbose messages to remind you what’s being executed

oak-chunk-update changes includes:

  • Verbosing query comment if exists (friendly printing of what’s being executed)

Continue reading » “opeark-kit revision 196 released”

On compiling TokuDB from source

MySQL

Sharing my experience of compiling TokuDB + MariaDB 5.5. Why? Because I must have this patch to Sphinx 2.0.4.

Note: I was using what seems to be the “old” method of compiling; quoting Leif Walsh:

… We are looking at deprecating that method of building (MariaDB source plus binary fractal tree handlerton).  It only really needed to be that complex when we were closed source.

I also tried the “new” method of compiling, which I couldn’t work out.

Here’s how it goes: TokuDB is newly released as open source. As such, it got a lot of attention, many downloads and I hope it will succeed.

However as stable as the product may be, it’s new to open source, which means anyone compiling it from source is an early adopter (at least for the compilation process).

Installation process

This is an unorthodox, and actually weird process. See section 6 on the Tokutek docs. In order to compile the project you must download:

Percona Live 2013 keynotes: followup questions and discussion

MySQL

Here are a few questions remained open for me from Percona Live 2013 about things that have been said during keynotes; I will appreciate a discussion on comments. Here goes:

Question #1

Brian Aker (HP) asks Simone Brunozzi (Amazon) what the underlying technology for DynamoDB is. Simone says can’t disclose. Brian says: “it’s MySQL!!”. Simone says: “can’t disclose”. Brian insists: “it’s MySQL!!”

Seriously? I will be very much surprised to learn that DynamoDB uses MySQL; it doesn’t make sense to me. Why would Brian Aker say that though? Did he just mean to tease Simone or is there something I just don’t get? Continue reading » “Percona Live 2013 keynotes: followup questions and discussion”

common_schema booth on Percona Live 2013

MySQL
common_schema booth in Percona Live 2013
common_schema booth in Percona Live 2013 (someone left their soup on our table)
Allen Kinnard

Yay! I got a booth!

I confess it was mostly deserted. My hero Allen Kinnard, whom I’ve never met before, was kind enough to volunteer to occupy the booth.
So between him and me, both of course also looking to visit other booths and talk to people, the common_schema booth was only moderately attended.

Well, this was the DotOrg Pavilion: free booths for free & open source projects (e.g. OpenStack, PhpMyAdmin, etc.).
We both did our best to explain common_schema to the visitors.

The booth was actually titled “common_schema & openark-kit”. However I don’t recall that anyone asked me about openark-kit. Most were just interested in what common_schema was. I get that openark-kit is well known by now to many.

We did not have so many visitors, which played well with our occasional absence. But this was a last moment arrangement. In future events I may try to get things on top and have an army of volunteers to help me out (and I hope that by next time common_schema is widely used).

cookie_monster

Now for the criminal department: practically all other DotOrg booths came prepared, with printed material, giveaway stuff and the like. We were like way beyond “the minimalist”. We were “the poor and the pitiful”. So I asked Kortney if she could arrange me some decorations (pulling some strings – yeah!). We were thinking a bowl of candies or something. Eventually she brought a few dozen candies (no bowl) and laid them nicely on the table; very nice touch! Good mixture of colors, nice wave design.

And guess what? Visitors actually thought they may take one of our candies! Oh, I schooled them.
But on Wednesday, when we arrived to the booth, a horrid sight stroke us: someone stole (and probably ate) 80% of our candies!
Well, I hope you’ve got bellyache, and next time you should know there will be surveillance, candy-thief!

Slides from my talk: common_schema, DBA’s Framework for MySQL

MySQL

I’ve just uploaded the slides from my talk: common_schema: DBA’s framework for MySQL

My talk was well attended, and I was fortunate to have a warm and engaged audience. Thank you to all those who attended, and thank you for those who provided feedback! Was happy to be able to present my work a great group of people.

You can find my slide either on Percona Live’s website, or on Slideshare, as well as embedded right here.

I set two aims to my talk:

  1. To have the audience know how to download and install common_schema (Check!)
  2. Have everyone in the audience find one tool from common_schema that will make their day better. Quick raise of hands at the end of presentation: Check! All hands are up!

As I’ve noted following a question, rdebug is still in alpha. It modifies one’s routines in such way that does not alter the routine’s behavior, except:

  • Affecting the value returned by ROW_COUNT() (I have no immediate solution to that)
  • Also affecting result of LAST_INSERT_ID() (I expect to have this solved).

Thank you again; give common_schema a try. Best ways to support: submit bug reports, ideas, and above all: spread the word. common_schema for world domination!

common_schema 2.0: DBA's Framework for MySQL from Shlomi Noach

Impressions from Percona Live 2013

MySQL

At the airport, trying to sum up my impressions from Percona Live 2013 conference in Santa Clara.

Woo! Hard to sum up four excellent days. Shall I review the great talks I’ve been to? The keynotes? The well organized events?

You know what, skip it. There was ONE thing that overshadowed everything. It was the ONE thing for me that was the pure essence of the conference and its greatest joy:

Meeting and talking to a great many great people!

I was fortunate to meet up with so many people; none that I planned; things just went in such way that I engaged in so many conversations with so many people. I found myself talking about hamsters, peacocks, living in the village, living in the city, working from home, commute, relocation, working with your spouse, life in Israel, life in Argentina, prisons in the US, having many children, gun control, politics (heaven forbid!), fruit, vegetables, breakfasts, open source, community, buying (non-expensive) presents to your kids, new ventures, zen, philosophy, capitalism, socialism, books, being who you are, weddings, Java, scripting, NoSQL, how to contribute to an open source project, … the list goes on.

I was “adopted” by the PalominoDB team at Pedro’s, crash-partied on Pythian fellows, talked technical (or non technical) with Tokutek guys, meeting up with Oracle people (finally I get the faces behind the names!); the companies do not matter, I’m just throwing in names. The people are awesome! Representing their companies on the technical side, and being purely interesting people on the personal side, I met with men and women from all over the community. Apologies: impossible to list all nor account for!

It’s great to have a place and time where we all meet together.

The bottom line? I am fortunate to have my current profession: I enjoy my work, and the people I meet are fantastic and of the highest quality!

Thanks all with whom I’ve met, and for all of those with whom I haven’t had the chance to speak: see you next time!

Speaking at Percona Live 2013: common_schema, lightning talks

MySQL

In two weeks time I will be giving these talks at Percona Live:

  • common_schema: DBA’s framework for MySQL: an introduction to common_schema, my evolving server side solutions project. This will be a revised version of the talk I gave at Percona Live London; I have felt some weaknesses during that talk, which I’ve thrown out, letting room for cool stuff. I will discuss common_schema‘s various views, interesting and useful routines, the power of QueryScript, and a brief intro to the newcomer rdebug, debugger and debugging API for MySQL. If you’re not familiar with common_schema, it’s a good time to pick up on what I (being most biased) consider to be your smart assistant to MySQL maintenance and administration!
  • The query which is the peak of my career: this is a 6 minute lightning talk. You’re bound to attend if you’re at the community reception (which you are), so I don’t need to do promotional. You already payed the ticket and the doors will be locked. No escapees.

As far as I’m concerned the conference can be closed down the moment I provide these two talks, and we can all go to the beach.

Wait, no, I will also be at the DotOrg Pavillion at the Exhibit Hall, where I present common_schema and openark-kit. Come by to hear more about these!