I'm now at Sun Tech Days St Petersburg. MySQL got involved in it on a very short notice so there are no MySQL sessions or tutorials, and we only have a small booth. It's decently manned though, there are six MySQLers here in total and we have no less than three people on the booth at nearly all times.

We organized some ad hoc sessions, so far, Alexey (aka Kaamos)'s talk about MySQL and dtrace attracted the most listeners, and Kostja was the second with his talk about new MySQL 5.1 features.

We get lots of questions on wide range …

I am one of the 10 jury members for the first international Hackontest. Basically this is a 24 hour open source coding competition that takes place on September 24/25 here in Zurich. However in order to make it to the competition a first hurdle is to get "past" the jury, which includes Jeremy Alison and Bruce Perens, who get to select the top 3 most promising teams on August 1st. So if you think you got what it takes, head on over to the Hackontest site and register your team! All my favorite OSS projects (PHP, MySQL and PostgreSQL) have not registered teams yet.

Aside from being a general all around cool thing for open source and a fun challenge for the teams that make it to the competition, this is also pretty exciting for me personally of course, since the jury holds many people that I would also …

Here is a situation I’ve run into a few times when dealing with mysql databases. We’re trying to run a one-off query against a high-traffic, large table and the WHERE condition is against a non-indexed field. Let’s say our table is 5GB in size. We issue the following:

SELECT count(*) from five_gb_myisam_table WHERE non_idx_field = 'asdf';

and we wait…

and wait some more.

5GB is not a small table, but this ideally should not take more than a few minutes on a relatively modern system.

iostat is your friend

In cases like this, iostat -x 5 is your friend. While the query was running, this was a typical 5 second interval:

avg-cpu: %user %nice %sys %iowait %idle
2.30 0.00 1.30 96.40 0.00

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.70 …

MySQL Proxy, in addition to dealing with the packets sent between client and server, can optionally send text messages to the terminal window where it was launched. These messages, using the built-in function print(), can be very useful when you develop an application, because can give you information on what is going on. However, when the script is well tuned, all these messages can be distracting and even annoying.
OTOH, if you plan to extend the development of the script, leaving the telling messages in place can be very useful. One handy solution is to include conditional print messages, controlled by an environment variable.

1. Change all occurrences of print to print_debug;
2. Create a function print_debug that will print the message depending on the value of a local variable DEBUG;
3. At the start of the script, initialize the DEBUG variable from the …

I’m happy to report that my patch for a potentially nasty bug in the mysqlhotcopy script has been accepted into the mysql codebase. It’s a great feeling to finally contribute something, however small, back to the mysql community.

If you have a master/slave environment with multiple slaves, and you do periodic backups of your system on the slave with mysqlhotcopy, the –record_log_pos parameter will pull the wrong co-ordinates from the master and cause you to restore new copies of your database that are potentially corrupt.
An example failure scenario can be found here.

Today I was asked a question about defining custom extensions for vim syntax highlighting such that, for example, vim would know that example.lmx is actually of type xml and apply xml syntax highlighting to it. I know vim already automatically does it not just based on extension but by looking for certain strings inside the text, like <?xml but what if my file doesn't have such strings?

After digging around I found the solution. Add the following to ~/.vimrc (the vim configuration file):

1
2
3

syntax on
filetype on
au BufNewFile,BufRead *.lmx set filetype=xml

After applying it, my .lmx file is highlighted:

…

Today a customer asked me to help them to convert their sequence generation process to the stored procedure and even though I have already seen it somewhere I did not find it with two minutes of googling so I wrote a simple one myself and posting it here for public benefit or my later use

PLAIN TEXT SQL:

1. delimiter //
2. CREATE FUNCTION seq(seq_name char (20)) returns int
3. begin
4.  UPDATE seq SET val=last_insert_id(val+1) WHERE name=seq_name;
5.  RETURN last_insert_id();
6. end
7. //
8. delimiter ;
9.  
10. CREATE TABLE `seq` (
11.   `name` varchar(20) NOT NULL,
12.   `val` int(10) UNSIGNED NOT NULL,
13.   PRIMARY KEY  (`name`)
14. ) ENGINE=MyISAM DEFAULT CHARSET=latin1
15.  
16. INSERT INTO seq VALUES('one',100);
…

Using callstacks to look at code is very useful. If you are not familiar with callstacks, I suggest you read my earlier blog about it. I was trying to understand the mysql code path using sysbench as the test and found something interesting. An image of the callstack is shown below. The SVG version, with much more information, is also available . The width of the block is proportional to the time it took for the function, and the height is the level (or depth) of the stack.

Mysql uses mysql_execute_command() to execute queries. Looking at the callstack you can see very clearly that mysql_execute_command() calls open_and_lock_tables which then tries to open tables via open_table(). The code path gets interesting here. As you can see in the image …

One of my colleagues made a typo in a query today that led to me discovering this issue — I think it’s a bug, though it may just be how the language is defined. But certainly a subquery that cannot run should not act as if it returns TRUE.

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table foo (fid tinyint unsigned not null primary key); create table bar (bid char(1)
not null default ”);
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> insert into foo (fid) VALUES (1),(2); insert into bar (bid) VALUES (’1′),(’a');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

…

With the advent of fast and reasonably sized SSD drives, I have to admit that even I, as one who still thinks the standard hard-drive tends to be a better choice, am warming up to solid-state. One crazy benchmark I have always wanted to do is how using consumer flash cards (SecureDigital, CompactFlash, etc.) stack up to their dedicated drive brethren. I just noticed that you can now buy off-brand 4GB High-Speed SD cards for around $10. That is amazingly impressive when you consider, even a few months ago, how much 4GB cards cost. After doing a bit of math, the economics work out pretty well. To match a 64GB SSD drive in capacity, I need 16 4GB SD cards. At $10 a piece, that's only $160. That's a pretty cheap way to match the capacity of an SSD drive without the cost.

Of course, there's a huge catch, or everyone would likely already be doing this. Actually there are quite a few. The numbers I ran don't include the cost of USB card …