Category: MySQL

MySQL related posts

More MySQL foreach()

Development MySQL

In my previous post I’ve shown several generic use cases for foreach(), a new scripting functionality introduced in common_schema.

In this part I present DBA’s handy syntax for schema and table operations and maintenance.

Confession: while I love INFORMATION_SCHEMA‘s power, I just hate writing queries against it. It’s just so much typing! Just getting the list of tables in a schema makes for this heavy duty query:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='sakila' AND TABLE_TYPE='BASE TABLE';

When a join is involved this really becomes a nightmare. I think it’s cumbersome, and as result, many do not remember the names and meaning of columns, making for “oh, I need to read the manual all over again just to get that query right”. Anyway, that’s my opinion.

A SHOW TABLES statement is easier to type, but cannot be integrated into a SELECT query (though we have a partial solution for that, too), and besides, when filtering out the views, the SHOW statement becomes almost as cumbersome as the one on INFORMATION_SCHEMA.

Which is why foreach() offers handy shortcuts to common iterations on schemata and tables, as follows:

Use case: iterate all databases

call foreach('schema', 'CREATE TABLE ${schema}.event(event_id INT, msg VARCHAR(128))');

In the above we execute a query on each database. Hmmm, maybe not such a good idea to perform this operation on all databases? Let’s filter them:

Use case: iterate databases by name match

call foreach('schema like wordpress_%', 'ALTER TABLE ${schema}.wp_posts MODIFY COLUMN comment_author VARCHAR(96) NOT NULL');

The above will only iterate my WordPress databases (I have several of these), performing an ALTER on wp_posts for each of those databases. Continue reading » “More MySQL foreach()”

MySQL foreach()

MySQL

A new routine is now available in common_schema, which makes for an easier execution syntax for some operations:

foreach(collection_to_iterate_over, queries_to_execute_per_iteration_step);

To illustrate what it can do, consider:

call foreach('table in sakila', 'ALTER TABLE ${schema}.${table} ENGINE=InnoDB ROW_FORMAT=COMPACT');

call $('schema like shard_%', 'CREATE TABLE ${schema}.messages (id INT)');

call $('2000:2009', 'INSERT IGNORE INTO report (report_year) VALUES (${1})');

$() stands as a synonym to foreach(). I suspect it should look familiar to web programmers.

The idea for foreach() was introduced by Giuseppe Maxia during a correspondence. At first I was skeptic: this isn’t jQuery; this is SQL. Why would I want to use foreach()?

Then Giuseppe provided some use cases, and as I started thinking about it, I found more and more cases where such a tool might considerably reduce scripting overhead and avoid requiring SQL-fu skills. In fact, I have been using it myself for the past few weeks

I provide examples which I hope will convince the reader as for the simplicity of using such syntax. Showing off the types of input foreach() accepts (query, table search, schema search, set of constants, single or double numbers range), and the types of queries it can execute (single, multiple, using placeholders).

I stress that this is not a replacement for common queries (i.e. does not come to replace your common SELECT, UPDATE, DELETE), but more for working out administrative tasks. Nevertheless, the last example in this post does provide with an interesting insight on possible “normal” use. Continue reading » “MySQL foreach()”

common_schema, rev. 178: foreach(), repeat_exec(), Roland Bouman, query analysis

MySQL

common_schema, revision 178 is now released, with major additions. This revision turns common_schema into a framework, rather than a set of views and functions.

common_schema provides with query scripting, analysis & informational views, and a function library, allowing for easier administration and diagnostics for MySQL. It introduces SQL based tools which simplify otherwise complex shell and client scripts, allowing the DBA to be independent of operating system, installed packages and dependencies.

There’s no Perl nor Python, and no dependencies to install. It’s just a schema.

Some highlights for the new revision:

  • foreach(), aka $(): loop through a collection, execute callback commands per element.
  • repeat_exec(): a repeat-until device: execute queries until some condition holds.
  • exec_file(): execute files a-la SOURCE, but on server side
  • Query analysis: analyze query text, view or routine definitions to detect dependency objects.
  • Improvements to views and routines, new routines introduced.

Let’s take a closer look:

rpbouman

I’m very happy to have Roland Bouman working on this project. He introduced some sophisticated code without which some functionality could not take place. I’m sure I don’t need to introduce his great capabilities; I’ll just pass the note that it is very good working with him!

foreach()

Introducing a looping device which can iterate a collection and execute callback commands.

What’s a collection? A range of numbers; a set of constants; the result set of a SELECT query; tables in your database and more.

What is a callback? A query or set of queries to invoke on the specific elements in the collection. For example:

call foreach('table in sakila', 'ALTER TABLE ${schema}.${table} ENGINE=InnoDB ROW_FORMAT=COMPRESSED');

I’ll publish dedicated posts on foreach(), aka $(), following this post. Official documentation is here.

repeat_exec()

Repeat executing queries in a given interval, until some condition holds.

What kind of condition? You can loop forever, or until a given time has passed, a given number of iteration has passed. Continue reading » “common_schema, rev. 178: foreach(), repeat_exec(), Roland Bouman, query analysis”

Percona Live: MySQL Conference And Expo 2012 – a note on proposals

MySQL

As a member of the conference committee I review the session and tutorial proposals for the Percona Live MySQL conference in Santa Clara, 2012.

The sessions are expected to be technical, and I’m happy the proposals follow this guideline. I use Giuseppe‘s and Baron‘s general guidelines for submitting a proposal. I wish to humbly add a couple notes myself.

Be fairly brief

Explain your session/tutorial as clearly as you can. The reader should be able to get the general impression of the session from two or three paragraphs. Some can make a point in two sentences; for most it would take somewhat more.

If you’re going to talk about some database feature, for example, please do not write the manual for that feature. That’s for the session itself. Just explain how you’re going to discuss the feature, and why it should be of interest (e.g. what the benefits of this feature are, the risks or pitfalls, the ingenious C code behind it or the quirks of the operating system involved).

Clarify

It’s important for me to understand two things when reading a proposal, which establish the grounds for better evaluating the proposal:

  • Who the target audience is (newbies, developers, DBAs, Linux internal experts etc.)
  • To what depth are you going to deliver the content you describe.

That is not to say you should explicitly state “This session is for MySQL DBAs”, but the attendee should be able to easily decide whether your session appeals to his type of work or expertise. I, myself, have happened upon sessions that were completely different from what I expected. To illustrate, I give two examples, while not disclosing the exact details:

  • A session which was about locking in database. I got the impression it was about ways to avoid locking, issues with mutexes etc. It turned out to be a discussion between the presenter and a few member of the audience about the specific code internals, lines 667-684 in the lock_module.cc file, and the recently reported bug. To me it was more like the weekly rnd meeting of some company. I couldn’t understand anything of the entire talk.
  • A session promising insight on working out great scale-out with some product: I was expecting to hear of the “DOs and DON’Ts”, or of great configuration and implementation tricks on the subject. However, it turned out to be more of a general talk on “how we used the product in our company and found it to work great”.

The two sessions above were perfectly valid, and had their place in the conference. But were poorly described in the two respects I mentioned.

A great submission, in my opinion, is one where attendees get what the expect, and don’t shyly leave the conference room 15 minutes into the talk.

Submit a proposal here.

Reading results of SHOW statements, on server side

MySQL

SHOW statements are show stoppers on server side. While clients can get a SHOW statement as a result set just as any normal SELECT, things are not as such on server side.

On server side, that is, from within MySQL itself, one cannot:

SELECT `Database` FROM (SHOW DATABASES);

One cannot:

DECLARE show_cursor CURSOR FOR SHOW TABLES;

One cannot:

SHOW TABLES INTO OUTFILE '/tmp/my_file.txt';

So it is impossible to get the results with a query; impossible to get the results from a stored routine; impossible to get the results by file reading…

Bwahaha! A hack!

For some SHOW statements, there is a way around this. I’ve been banging my head against the wall for weeks now on this. Now I have a partial solution: I’m able to read SHOW output for several SHOW statements. Namely, those SHOW statements which allow a LIKE or a WHERE clause.

For example, most are familiar with the following syntax:

USE mysql;
SHOW TABLE STATUS LIKE 'user';

However not so many know that any SHOW statement which accepts LIKE, can also accept WHERE: Continue reading » “Reading results of SHOW statements, on server side”

Quoting text JavaScript/Python style

MySQL

Unless your MySQL is configured to use ANSI_QUOTES in sql_mode, you are able to quote your text in one of two forms: using single quotes or double quotes:

UPDATE world.Country SET HeadOfState = 'Willy Wonka' WHERE Code='USA'
UPDATE world.Country SET HeadOfState = "Willy Wonka" WHERE Code="USA"

This makes for JavaScript- or Python-style quoting: you quote by your needs. Say you have a text which includes single quotes:

It is what you read when you don’t have to that determines what you will be when you can’t help it. – Oscar Wilde

You wish to insert this text to some tables. You could go through the trouble of escaping it:

INSERT INTO quotes (quote, author) VALUES (
  'It is what you read when you don\'t have to that determines what you will be when you can\'t help it.', 'Oscar Wilde');

or you could just wrap it in double quotes:

INSERT INTO quotes (quote, author) VALUES (
  "It is what you read when you don't have to that determines what you will be when you can't help it.", 'Oscar Wilde');

I find this useful when using SQL to generate queries. Take, for example, eval() for MySQL: the statement: Continue reading » “Quoting text JavaScript/Python style”

Oracle ACE

MySQL

I am honored to have been nominated for, and to have received the Oracle ACE award.

Nomination for this award is made by Oracle community members, and in this case those being Oracle employees Keith Larson and Dave Stokes. The award is given by Oracle for my involvement in the Oracle/MySQL community and for my contributions.

While open source involvement is generally done in the mere purpose of sharing knowledge and solutions, recognition plays a role in it. For the most part, one who writes blogs wants them to be read, and one who writes code wants it to be downloaded and tested, which is an elemental type of recognition, and what I aim for.

The recognition given by the Oracle ACE award makes for a wonderful complement, being given by the corporate with whose products I’m involved. It is great to get a surprising “Hey, good work” acknowledgement. Sun/MySQL told me that back in 2009, and I was caught utterly unprepared. I am still unprepared!

Thank you, the community, the people from whom I learn and benefit, anyone who ever reads my blogs, who comment, anyone who tries my code, who provides feedback, the multitude of people writing, sharing, blogging, speaking, coding, fixing, spreading, who make it such a great community.

Thank you, Oracle for this award!

Self throttling MySQL queries

MySQL

Recap on the problem:

  • A query takes a long time to complete.
  • During this time it makes for a lot of I/O.
  • Query’s I/O overloads the db, making for other queries run slow.

I introduce the notion of self-throttling queries: queries that go to sleep, by themselves, throughout the runtime. The sleep period means the query does not perform I/O at that time, which then means other queries can have their chance to execute.

I present two approaches:

  • The naive approach: for every 1,000 rows, the query sleep for 1 second
  • The factor approach: for every 1,000 rows, the query sleeps for the amount of time it took to iterate those 1,000 rows (effectively doubling the total runtime of the query). Continue reading » “Self throttling MySQL queries”

Impressions from Percona Live, London

MySQL

Am now back from Percona Live, London. Here are some quick impressions of mine.

The physical location of the conference was excellent, not far from Tower Bridge. The conference area itself was nice and has good atmosphere. Very well organized. Kudos to Percona!

Theme

The official theme for the conference was “Discover the Power of MySQL”. However, looking back at the conference, I think the de-facto theme was “High Availability and Scale Out for MySQL”.

Why do I say that? Look at the sponsors of the event; look at the talks. There were talks by and about Clustrix, Tungsten (parallel replication), Schooner, Choosing a HA solution (by Henrik), Fusion-IO, MySQL Cluster, MySQL in the Cloud, Galera Replication, Scalebase, Spider, MHA, Various HA Solutions (by Oli Sennhauser)…

There were other talks, about all sorts of things. But to me it seemed like scale-out was the big thing, and HA the next one. Especially looking at sponsors booths it looked that way.

People

Of course, it’s no secret that meeting the people is a great fun. I met with familiar faces, and was happy to meet new ones. It’s great to just discuss stuff with people, share opinions.

The after party was fun, and well organized. Kudos to Clustrix who sponsored it!

Business talk

I wasn’t at the conference to sell anything myself, and so was on a relaxed mood. I was happy that whatever sales talk I shared, heard or overheard was highly technical and very open. It was also good to openly discuss possible relationships.

I was in particular pleased to see the popularity some of my tools and ideas were gaining. I could recognize at least two talks where my tools were mentioned; I was delighted to talk to DBAs telling me they were using openark-kit in general, oak-online-alter-table in particular, or some SQL solutions and tricks I offered in my blog. This is really wonderful! To write something having people use it to their benefit — I suppose this is what open source aspires to. It’s great to hear feedback, and better yet to get good feedback.

I also had the pleasure of meeting people after hours, and have good discussions, not necessarily MySQL related.

To top it all, I had free time on Wednesday, where I had a superior visit to the Natural History Museum, which left me in awe.

Contest for Glory: write a self throttling MySQL query

MySQL

What’s all this about?

I’ve you’ve been to my talk in London, then you’ve already got this as homework. If not, allow me to fill in the details:

I was speaking about MySQL’s programmatic nature in many aspects (best if you read the slides!). We discussed user defined variables, derived tables, ordering constructs, order of evaluation, time suspension and time calculation.

An issue I presented was that of a very long running query. Say it runs for 20 minutes. It kills your I/O. Do you KILL the query or wait? It could terminate in 5 seconds from now, and if you kill it now, you lose everything. But it may yet run for 3 more hours!

We discussed a futuristic feature where the query would gracefully terminate after some designated time. However, futuristic wishes do not help us.

A self throttling query

I suggested the idea for a self throttling query. We know how to throttle writing queries, such as DELETE queries: we break them into small chunks, then work each chunk at a time, setting ourselves to sleep in between chunks. This is how –sleep and –sleep-ratio work in oak-chunk-update. It is how –sleep and –sleep-coef work in pt-archiver.

But can the same be done for SELECT queries? Continue reading » “Contest for Glory: write a self throttling MySQL query”