The latest from the MySQL community

This morning I was looking for something on Ohloh and realized I should “stack” maatkit. Since I couldn’t find “maatkit” nor “mysql toolkit” in Ohloh, I created a new project for it at:

http://www.ohloh.net/projects/10083

If you’re on Ohloh, stack it!

www.ohloh.net is a neat social networking tool for open source software. Instead of searching freeware lists, search ohloh, and you can find reviews, # of people using the software, and direct links to download pages. My profile is at:

http://www.ohloh.net/accounts/8446http://www.ohloh.net/accounts/8446
and you can see my stack at:
http://www.ohloh.net/accounts/8446/stacks/default

This is the last day I'm taking off work to hack on mk-table-sync, and I thought it was time for (yet another) progress report. Here's what I have done so far. (Click through to the full article to read the details).

One of the major parts of my job is/was developing the interface to our MIS that handles hundreds of connections on a nightly basis from other MIS systems which push data in a CSV format. These CSV files contain information that then needs to be pushed into our MIS (thousands of inserts and updates, plus some deletes).

Now, creating the insert statements are a breeze with the "insert into tbl0(...) values(...),(...),..." MySQL syntax. I can push thousands of inserts in a single query, the only limit being the MySQL packet size, hence the 'ish' in the title. But what about updates? before now I've resorted to just running the updates as single queries, which lets face it, is slow. So lets look at how we can speed this up.


The Meat

First create a test table:

create table test0(sid int, staffid varchar(255), name varchar(255), unique(sid, staffid));


…

A

Since few days I was debugging a case from Yahoo! Japan team (thanks to Akira Kitada) where the query runs fine in about 0 secs with the 5.1.16 build where as the same query takes almost close to 2 minutes with the latest 5.1.22 release.

One can simply identify that this is a optimizer issue as it is not picking the right index. Lets consider the following two tables urls and urls_categories…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

| urls  | CREATE TABLE `urls` (
  `url_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `url` VARBINARY(2000) NOT NULL,
  `url_status` …

… I didn’t get two-way sync done, and I didn’t get the Nibble algorithm done. That much I expected. But I also didn’t get the current work released tonight because I’m paranoid about breaking things. I’m trying to go through all the tools and write at least a basic test for them to be sure they can do the simplest “unit of work” (such as mk-find running and printing out that it finds the mysql.

This is the last day I’m taking off work to hack on mk-table-sync, and I thought it was time for (yet another) progress report. Here’s what I have done so far: All the code, except for a tiny bit of “glue” and “setup” code, is in modules. Lots more tests for the modules. A new sync algorithm (I still haven’t rewritten the top-down and bottom-up, which are designed for network efficiency more than MySQL efficiency, and are very complicated).

Saw this on the Association for Computer Machinery SIGMOD announcements.

Tribute to Honor Jim Gray

It is scheduled for May 31, 2008 at UC-Berkeley.

As a follow-up to my earlier blog, I happened to get 15 minutes hands-on use of the Amazon Kindle, their new portable eBook reader. As it happens, Amazon's Lab 126 development team is located in the same building as MySQL's US headquarters, and so when I happened to spot a team of folks with a Kindle in hand at a local restaurant for lunch, I dove in and asked if I could try it out. First point to note: the display is fantastic. To me, it's as easy to read as a book. While you can argue that the text... READ MORE