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

My saga with RSS continues. As I stated before, changes to MySQL's forum site led me to pursue using an RSS reader as my main interface to the forums. That worked quite well until recently. My only issue had been that the various replies to a post showed as separate entries to the reader, with no connection between them. Coming back from a self-imposed hiatus, I find that this problem has been fixed, at the expense of creating another one. Now, each thread shows up once only in the reader, along with an indication of how many replies have been posted. This is fine, except that the thread is not marked as updated, and so threads that get replied to are never highlighted as unread by the reader. This of course makes it extremely difficult to conduct timely and efficient interactions with someone on the board. I now have to peruse the forum web page with the thread …

This is the configuration file of MySQL. For new DBAs, depending on your needs (eg. if the database is small, medium, large), just copy the appropriate my.cnf in /usr/local/mysql/support-files/ . Then just change couple of parameters and you are all set. There is no need to make a huge fuss about it. Many experts will tell you to change this file, I recommend not to listen to them. MySQL was created to be simple... If you really need to change it, then make sure you know what you are doing.

Our Services team do regular surveys on our Support customers. I just had a look at the latest data from proactive calls during July to November 2007. In the sample, the Platinum, Gold, Silver and Basic levels were roughly equally big slices, with a bit fewer Gold and a bit more Basic.

The figures are quite interesting. One out of two top conclusions is that we seem to need some more evangelising among our customers, especially for MySQL Enterprise Monitor and for the MySQL Enterprise Knowledge Base.

• Between half and two-thirds of the surveyed customers have heard of Monitoring; the higher the SLA level, the more.
• The Knowledge Base has more penetration than …