Month: April 2009

More on variables ambiguities

MySQL

Continuing Variables ambiguities in names and values, there are two more issues I wish to present.

First, I’ve shown that variable values may be ambiguous. Thus, 1 and ON are interchangeable. But also 1 and YES.

The important thing to note is that it’s not always like that. You can’t just swap 1 for ON or YES as you will: it depends on the variable. Thus, a specific variable (e.g. query_cache_type) may accept ON for 1 – but will not accept YES. Another may accept YES for 1 – bot not ON.

Confused? Here’s the second issue. This one is really a bug, as I see it. Take a look at the following:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

The ON value is actually illegal: if you try to set this value in your my.cnf file – you’ll get a non-file-per-table behavior. The real value you should be using in my.cnf is 1 (or no value at all).

And so in this latter example, 1 equals ON, but only one of them is valid in the my.cnf file, and yet only the other one is being reported.

The behavior is documented here. But, if, like myself, you find it undesired, please comment on bug #44509.

Variables ambiguities in names and values

MySQL

Writing up some scripts, I see more and more ambiguities with regard to global variables.

For one thing, the names ambiguity between the hyphen (‘-‘) and the underscore (‘_’). So wait_timeout and wait-timeout are the same variable.

But just check out the many levels of inconsistency:

  • Command line arguments (e.g. run mysqld with option variables) use the hyphen convention
  • mysql –verbose –help shows the hyphen convention
  • SHOW GLOBAL VARIABLES uses the underscore convention
  • The MySQL supplied sample configuration files use both conventions interchangeably

Enough? Not quite: there are ambiguities in values, as well. For example, you may set query_cache_type to 1 or ON. These are equivalent. That’s very friendly. However: Continue reading » “Variables ambiguities in names and values”

MySQL Conference: what’s in a name?

MySQL

This is just something that I realized this morning. There were some talks about how the “MySQL Users Conference & Expo” was renamed to “MySQL Conference & Expo” – thereby omitting the “Users” part. The talk was something like “So where are we, the users, in this story?”

But what I’ve just recalled was a discussion (was it previous year, or the one before that?) comparing the “PostgreSQL Conference” and the “MySQL Users Conference”, as it was named back then. In that discussion, the PostgreSQL people were bashing MySQL, saying that the “PostgreSQL Conference” was all about the database and whatever was around it, whilst the “MySQL Users Conference” clearly stated that the attendees were “just users”, not like real participants or members.

Continue reading » “MySQL Conference: what’s in a name?”

MySQL Conference 2009 daily summary: Wednesday

MySQL

Busy day again: today the Percona conference joined the regular schedule, so more stuff to attend!

What did I attend?

Maria In Depth, Monty Widenius, MontyProgram AB (Percona conference): Monty was discussing Maria performance-wise as opposed to his later session which was feature-wise). He did delve just a little bit into the decision making issues in MySQL and in his new company. He also spoke (and I believe this took the most weight of the session, even if not the most time) of his new company, what he wants to achieve and how.

Build Your Own MySQL Time Machine, (Sun Microsystems), Mats Kindahl (Sun Microsystems). A web based utility for creating online backups and performing a point in time restoration was presented. The system is based on MySQL 6.0’s new backup mechanism, and runs on a slave. The presenters showed how easy it would be to say “I would like to restore data to as it was yesterday 14:56pm”. Hope to see such final product in the future!

Continue reading » “MySQL Conference 2009 daily summary: Wednesday”

MySQL Conference 2009 daily summary: Tuesday

MySQL

Tuesday: day of the announcements and of numerous sessions. Busy day!

The day started with the State of the Dolphin by Karen Padir (Sun Microsystems). She threw in some announcement, among which were MySQL 5.4, MySQL Cluster 7.0, better release cycles (matching the Enterprise), better code import from the community. Anyway, there is already a lot of talk about that.

Later on, This is Not a Web App: The Evolution of a MySQL Deployment at Google, Mark Callaghan (Google) was a very interesting session, in which Mark described the needs for an enterprise database Google and (mysterious) others had, and the steps taken to modify MySQL in this direction. Mark explained the build & test process they use at Google, teased the MySQL Query Analyzer guys (“it’s amazing what you can do with sed & awk, actually”) – though expressed his appreciation of their work, and mentioned various contributors and collaborators. It was an interesting session, very wide in scope, I think.

The rest of my schedule for the day was: Continue reading » “MySQL Conference 2009 daily summary: Tuesday”

MySQL Conference 2009 Community Awards

MySQL

OK. That was a surprise!

In retrospective, there have been some hints along the way. But I don’t get hints. I’m the kind of man who, when watching a complicated movie, needs his girlfriend to explain him what goes on.

I was utterly astonished and honored to find my name on the screen, and have me being one of three people called to accept the MySQL Community Award for 2009.

Let me tell you: it is heavy! And it doesn’t fit in my bag, either, so I hang around carrying this big heavy box in both hands…

I guess this calls for a short Oscar speech, a written one.

Continue reading » “MySQL Conference 2009 Community Awards”

MySQL Conference 2009 daily summary: Monday

MySQL

[See http://forge.mysql.com/wiki/MySQLConf2009MondayNotes]

Monday: day of tutorials. Plenty of interesting tutorials on the Conference itself, plus a session with Mark Callaghan – it was hard to choose. I settled for two tutorials, which turned out to be three.

Practical MySQL Plugin Development: As a C/C++/Java developer, I am very interested in the plugin API. I have used UDF before, and these turned out to be extremely helpful, and solved me a lot of headache. With the new plugin API I was expecting to learn how to properly write INFORMATION_SCHEMA tables, functions and engines.

Wasn’t it possible to learn all this on the web? Sure, but this presentation was delivered by Roland Bouman and Sergei Golubchik, and I was anxious to hear from their experience. Well, that’s what the conference is all about, isn’t it?

The session was very good. Roland & Sergei covered the basics of the Plugin API, the general ideas, then went on to present the specific implementations: daemon plugins, INFORMATION_SCHEMA, FULLTEXT. The session was accompanied by convincing and enlighting examples. For example, a QUERY_CACHE_TABLE: an INFORMATION_SCHEMA table which lists which queries are currently in the query cache, along with the number of used blocks etc.

Continue reading » “MySQL Conference 2009 daily summary: Monday”

A note on Baron’s command line tip for comparing result sets

MySQL

A while ago Baron Schwartz published a MySQL command-line tip: compare result sets.

A “SELECT * FROM world“, for example, can be checksummed, compared with another checksum made on a replica, or otherwise another table which is supposed to contains the exact same data.

I just wanted to note that if you’re dealing with a MyISAM table, a simple “SELECT * FROM” will not necessarily be too useful, since MyISAM can store rows in any particular order: two different settings of concurrent_insert, or perhaps an OPTIMIZEd table, can make for different ordering, hence different checksums.

Use of “ORDER BY …” is required if you want to have a consistent checksum. With MyISAM, you don’t usually want to count on natural row ordering, at any case.

“Why?” of the week

MySQL

As progress on oak-online-alter-table goes on, I’m encountering more and more limitations, for which I must find workarounds. Here’s two:

CREATE TABLE … LIKE …

It works well, but it doesn’t copy any foreign key constraints. So, if the original table is this:

CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY  (`emp_no`,`dept_no`),
  KEY `emp_no` (`emp_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then CREATE TABLE dept_emp_shadow LIKE dept_emp results with: Continue reading » ““Why?” of the week”

The depth of an index: primer

MySQL

InnoDB and MyISAM use B+ and B trees for indexes (InnoDB also has internal hash index).

In both these structures, the depth of the index is an important factor. When looking for an indexed row, a search is made on the index, from root to leaves.

Assuming the index is not in memory, the depth of the index represents the minimal cost (in I/O operation) for an index based lookup. Of course, most of the time we expect large portions of the indexes to be cached in memory. Even so, the depth of the index is an important factor. The deeper the index is, the worse it performs: there are simply more lookups on index nodes.

What affects the depth of an index?

There are quite a few structural issues, but it boils down to two important factors:

  1. The number of rows in the table: obviously, more rows leads to larger index, larger indexes grow in depth.
  2. The size of the indexed column(s). An index on an INT column can be expected to be shallower than an index on a CHAR(32) column (on a very small number of rows they may have the same depth, so we’ll assume a large number of rows).

Continue reading » “The depth of an index: primer”