Just a little heads-up and a bit of MySQL-related technical content for all of you still out there following along…

At Proven Scaling, we take on MySQL performance problems pretty regularly, I’m often in need of good tools to characterize current performance and find any issues. In the database world, you’re really looking for a few things of interest related to I/O: throughput in bytes, requests, and latency. The typical tool to get this information on Linux is iostat. You would normally run it like iostat -dx 1 sda and its output would be something like this, repeating every 1 second:


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 8.00 0.00 4.00 0.00 96.00 0.00 48.00 24.00 0.06 15.75 15.75 6.30


Most of the output of iostat is interesting and reasonable for its intended purpose, which is as a general purpose way to monitor I/O. The really interesting things for most database servers (especially those in trouble) are:

• avgrq-sz — Average request size, in kilobytes.
• avgqu-sz — Average I/O queue length, in requests.
• await — Average waiting time (in queue and scheduler) before servicing a request, in milliseconds.
• svctm — Average total service time for I/O requests, in milliseconds. This includes await, so should always be higher than await. This is the most interesting number for any write-heavy transactional database server, as it translates directly to transaction commit time.
• %util — Approximate percent utilization for the device.

There are one major problem with using iostat to monitor MySQL/InnoDB servers: svctm and await combine reads and writes. With a reasonably configured InnoDB, on a server with RAID with a battery-backed write cache (BBWC), reads and writes will have very different behaviour. In general, with a non-filled cache, writes should complete (to the BBWC) in just about zero milliseconds. Reads should take approximately the theoretical average time possible on the underlying disk subsystem.

I’ve often times found myself scratching my head looking at a non-sensical svctm due to reads and writes being combined together. One day I was perplexed enough to do something about it: I opened up the code for iostat to see how it worked. It turns out that the core of what it does is quite simple (so much so, I wonder why it’s C instead of Perl) — it opens /proc/diskstats, and /proc/stat and does some magic to the contents.

What I really wanted is a histogram of the reads and writes (separately, please!) for the given device. I hacked up a quick script to do that, and noticed how incredibly useful it is. I recently had to extend it to address other customer needs, so I worked on it a bit more and now it looks pretty good. Here’s an example from a test machine (so not that realistic for a MySQL server):

util:   1.27% r_ios:     0  w_ios:     1  aveq:     0,
ms : r_svctm                     : w_svctm
 0 :                             :
 1 :                             :
 2 :                             :
 3 : x                           :
 4 : x                           :
 5 : xxx                         :
 6 : xxxx                        :
 7 :                             :
 8 : x                           : x
 9 : x                           : xx
10 : x                           : xxxxx
11 :                             : xxxxxxxxxxxxxxx
12 :                             : xxxxxxxxxxxxxxxxxxxxxxxxx
13 : xx                          : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
14 :                             : xxxxxxxxxxxxxxxxxxxxx
15 : xx                          : xxxxxxxxxxx
16 : x                           : xxxxx
17 : x                           : xxxxxx
18 :                             : xxxx
19 : x                           : xx
20 :                             : x
21 :                             : x
22 :                             : x
23 :                             : x
24 :                             : x
25 :                             :
26 :                             :
27 :                             :
28 : x                           :
29 :                             :
30 :                             :
++ : 0                           : 250

It uses Curses now to avoid redrawing the entire screen, and I’ve got a ton of ideas on how to improve it. I have a few more must-haves before I release it formally to the world, but I wonder what more features people would want from it. It is Linux-only for the foreseeable future.

What do you think?

If you've used the UNIX find command for more than a trivial find-and-print, you know how powerful it is; it's almost a miniature programming environment to find and manipulate files and directories. What if you could do the same thing with MySQL tables and databases? That was the inspiration for writing this tool. I was about to write several other tools to do some MySQL administrative jobs when I realized I could generalize and make something much more useful and powerful.

I’ve been looking for an open source project to collaborate for some time now, and given the time I’m spending with MySQL lately and the expertise I’m gaining thanks to MySQL training, it looked like an obvious choice.

During the last advanced bootcamp, Tobias found bug #27894, which apparently was a simple fix. Dates in binlog were formatted as 736 instead of 070306 (for 2007-03-06). During the bootcamp I used my lonely nights at the hotel and came up with a patch, and some days later my first contribution was going into the main MySQL code.

The problem

Now I had to find something bigger. One of the things that most annoys me of MySQL is the lack of some way to abort a procedure or trigger: there is no raise method. To generate a custom error you have to do hacks like:

SELECT `

Error: Invalid firmware series for this model

` INTO dummy FROM model;

The solution

There is a SIGNAL command in the SQL:2003 standard which does the job, but it’s not implemented (yet) in MySQL. The syntax is as follows:

SIGNAL signal_value [ SET signal_information_list ] 

signal_value:
    condition_name
  | sqlstate_value

signal_information_list:
    [ signal_information_list , ] signal_information_item 

signal_information_item:
    condition_name = condition_value

condition_name:
    CLASS_ORIGIN
  | SUBCLASS_ORIGIN
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME
  | MESSAGE_TEXT

In this first part I’ll cover the basics: just the SIGNAL command with a fixed generic error, enough to get rid of the dirty hacks.

The implementation

Getting used to foreign code always takes some level of difficulty, but when you have to deal with grammars and parsers it’s all crazy fun. First, we have to add a symbol for our new command

sql/lex.h

In this file, we have a symbols[] array where we have to add SIGNAL. Since it seems to be sorted in alphabetic order, we’ll put our line between SHUTDOWN and SIGNED:

   { "SHUTDOWN",    SYM(SHUTDOWN)},
   { “SIGNAL”,    SYM(SIGNAL_SYM)},
   { “SIGNED”,    SYM(SIGNED_SYM)},

sql/share/errmsg.txt

Before we get our hands dirty with the parser file, let’s get our custom error prepared. I took a look at the SQLSTATE error messages and I found the 38503 (Exception generated from user-defined function/procedure) enough related to this.

In this file we have a series of error constants with their corresponding error messages in various languages. Since our new error will be related to stored procedures, I decided to put with the rest of SP-related errors:

 ER_SP_CASE_NOT_FOUND 20000
         eng "Case not found for CASE statement"
         ger "Fall für CASE-Anweisung nicht gefunden"
 ER_SP_SIGNAL 38503
         eng “Exception generated from user-defined function/procedure”
 ER_FPARSER_TOO_BIG_FILE
         eng “Configuration file ‘%-.64s’ is too big”
         ger “Konfigurationsdatei ‘%-.64s’ ist zu groß”

sql/sql_yacc.yy

And finally to the point. Here we have to declare that we’ll be using the SIGNAL_SYM which we defined at sql/lex.h as a token.

 %token  SHUTDOWN
 %token  SIGNAL_SYM
 %token  SIGNED_SYM

Then, in the sp_proc_stmt label (look for sp_proc_stmt: at the beginning of a line), we add sp_proc_stmt_signal as another possibility (we’ll define this in a minute):

 	| sp_proc_stmt_iterate
 	| sp_proc_stmt_signal
 	| sp_proc_stmt_open

And finally, between the sp_proc_stmt_iterate and the sp_proc_stmt_open definition we add our code:

sp_proc_stmt_signal:
    SIGNAL_SYM
  	{
            LEX *lex= Lex;
	    sp_head *sp= lex->sphead;
	    sp_instr_error *i;

	    i= new sp_instr_error(sp->instructions(), lex->spcont, ER_SP_SIGNAL);
	    sp->add_instr(i);
	  }

This basically tells the parser to expect the SIGNAL_SYM token (SIGNAL) with no arguments, and generate an error with our new error code (ER_SP_SIGNAL). As you might see there’s some extra code which I copied directly from similar definitions, which I’ll refer to as parser magic (anyone willing to explain what sphead and lex variables are will be very welcome)

Conclusion

This one wasn’t so extremely difficult if you had some previous experience with Bison, but the next part can be more interesting, since I guess we’ll have to add some more functions than sp_instr_error to be able to show custom error messages. Also, we’ll have to prepare some test cases to verify our newly created behaviour.

I hope this helps someone trying to contribute to MySQL. If you want to try this at home you can follow the article or apply the patch

• add triggers to log tables;
• filter log tables depending on user defined criteria, such as query type, user database, or time;
• centralize logs from several servers.

Sun advances OpenJDK with new code and governance board. Dell joins the Microsoft/Novell collaboration effort. GroundWork releases new version with SOA development framework. (and more)

Note: Due to an international flight and limited Internet access in transit, there was no 451 CAOS Links on Monday 05/07/07.

Sun Fulfills Promise of Open and Free Java Technology and Releases Java SE Platform to OpenJDK Community, Sun Microsystems (Press Release)

Dell Joins Microsoft and Novell Collaboration, Microsoft / Novell / Dell (Press Release)

Sun To Develop New Communications Application Server Through Open Source GlassFish Community, Sun Microsystems (Press Release)

New Version Of Groundwork Monitor Powers First Services-Oriented Architecture For Open Source It Management, GroundWork Open Source (Press Release)

Compiere Extends Product Flexibility and Deployment Ease with New Release, Compiere (Press Release)

MySQL AB to Offer Low-Cost, High Availability Solution for Business-Critical LAMP Applications, MySQL AB (Press Release)

Jitterbit Announces Major New Additions for 1.2, Jitterbit (Press Release)

Terracotta Announces Integration of Terracotta Clustering Solution With GlassFish Application Server, Terracotta (Press Release)

Ericsson to contribute to open source application server and provide IMS support for developer communities, Ericsson (Press Release)

CCID Consulting: China’s Linux Market up by 30.9% in 2007Q1, CCID Consulting (Press Release)

Coupa Raises the Bar on Purchasing Simplified with Launch of Coupa eProcurement Enterprise, Coupa (Press Release)

SplendidCRM Announces Launch of SplendidCRM v1.4 Incorporating Key .NET 2.0 Technologies, SplendidCRM (Press Release)

Xandros Releases Xandros Server Standard Edition 2, Offering Enterprise-Grade Features to Windows-Centric SMBs, Xandros (Press Release)

Palamida Extends IP Amplifier’s Day One Reporting of Open Source Intellectual Property and Security Risks, Palamida (Press Release)

Ubuntu plans mobile Linux version, InfoWorld, John Blau (Article)

7 Challenges Red Hat Faces to Remain Open Source’s Finest, SeekingAlpha, Joe Panettieri (Article)

How 12 people banded together to make Firefox 1.0, APC Magazine, Dan Warne (Article)

Open Source Venture Investment Through Q1 of 2007, Larry Augustin’s Weblog, Larry Augustin (Blog)

Will the last top developer out of Novell please turn off the lights?, ZDNet Open Source Blog, Dana Blankenhorn (Blog)

A free software milestone, here be dragons, Mark Shuttleworth (Blog)

MySQL is indebted to the Software Freedom Law Center for very good advice and insight on how to combine Free Software with a viable business model. SFLC provides legal representation and other law-related services to protect and advance Free and Open Source Software. Founded in 2005, the Center now represents many of the most important and well-established free software and open source projects.

Professor Eben Moglen, SFLC director and FSF legal counsel, has provided us with profound guidance over the years. We have tried to give something back through our work in the GPLv3 Committee B, but our time resources as a small company are limited in comparison to our fellow committee members.

In recognition of Eben’s help and as a token of our appreciation, we’ve made a small donation to support the continued work of the SFLC. We encourage others who build their business on free software to do the same.

Thank you, Eben!

At the MySQL Conference the closing keynote was on Yahoo! Pipes, by Pasha Sadri, a Principal Software Engineer, Advanced Development Division, Yahoo!. I wanted to try it, but I was on Firefox 1.5 on Fedora Core 6 and there was no way I was going to build a pipe during the talk.

Fast forward a week or so later, and a boring Friday night ensued. What better thing to do, than to play with Pipes. In under fifteen minutes, I created the Edwin Pipe. What is it? Its a pipe that is all things MySQL - comprehensive source of news, whats cool, and so forth. There are some limitations - regular expression support is supposedly like Perl’s, but is not quite complete. The Unique operator is pretty cool, filtering is good (can be improved with better regex support), and maybe some sort of fuzzyness in the way data is displayed (I don’t only want all Digg mysql related items popping up at the top, or I don’t only want all mysql job forum details at the bottom, etc.). Language conversion via a Babelfish operator exists, but not language filtering (maybe I only want all English text displayed in my final pipe output).

That aside, the forums are pretty active. Pipes are ridiculously easy to create. Its simply great stuff. Oh, shorter URLs - the URLs are so long and not feasible, in my opinion. Impressive is the support to then get RSS output, and also JSON (so all processing is done on the server side). Happy I am with sites that provide JSON feeds.

Now, for some notes I took during the closing keynote.

• A while ago, he wanted to find an apartment near a park. Go to Craigslist and find apartment lists, then click the map link, and also check distance to a park on the map… This is tedious, and not automated.
• Craigslist apartment RSS feed. Yahoo! Local API to find Parks. Why not tie this in together? It started with about 50 lines of Perl code, and it combined feeds + web services (this is your Web 2.0 mashup).
• Pipes: free online service that lets you remix data and create mashups using a visual editor.
• Pipes treats the web as a big database, as they do joins across different ‘tables’.
• Design principles came from the Unix Pipes. They’re like pipes for the Web. Build useful applications from simple primitives.
• The more open Pipes is, the more useful it will be (so Google goodness will also work).
• Output available in JSON, so it can be used as another application. Get email or SMS from output, even. RSS is obviously available.
• App Examples: Last.fm + Flickr, Babbler (Second Life, language translation) by Max Case.
• Must enable users to solve ad-hoc problems. User generated “features” and disposable applications -> the future.
• Pipes uses MySQL, squid for caching, PHP & Perl (lots of CPAN modules) for serving and back-end processing of the pipes.

Edwin 2.0 is already in the works. It will have more cool feeds, and probably work out all the language issues with more separated regexes. More fuzzy organizing of data, if possible. If you want to see a MySQL Blogger Photo Gallery, bman_seattle created a pipe too.

Technorati Tags: mysqlconf, mysqlconf07, yahoo, pipes, edwin pipe, json, visual editing, mashup

[root@db31:/] dtrace -l | more
   ID   PROVIDER            MODULE                          FUNCTION NAME
    1     dtrace                                                     BEGIN
    2     dtrace                                                     END
    3     dtrace                                                     ERROR
    4    syscall                                               nosys entry
    5    syscall                                               nosys return
    6    syscall                                               rexit entry
    7    syscall                                               rexit return
    8    syscall                                             forkall entry
    9    syscall                                             forkall return
   10    syscall                                                read entry
   11    syscall                                                read return
   12    syscall                                               write entry
   13    syscall                                               write return
   14    syscall                                                open entry
   15    syscall                                                open return
   16    syscall                                               close entry
   17    syscall                                               close return
   18    syscall                                                wait entry
   19    syscall                                                wait return
   20    syscall                                               creat entry
   21    syscall                                               creat return
...

[root@db31:/] dtrace -n BEGIN
dtrace: description 'BEGIN' matched 1 probe
CPU     ID                    FUNCTION:NAME
  1      1                           :BEGIN
^C

[root@db31:/] dtrace -i 1
dtrace: description '1' matched 1 probe
CPU     ID                    FUNCTION:NAME
  0      1                           :BEGIN
^C

[root@db31:/] dtrace -i 1 -i 2 -i 3
dtrace: description '1' matched 1 probe
dtrace: description '2' matched 1 probe
dtrace: description '3' matched 1 probe
CPU     ID                    FUNCTION:NAME
  0      1                           :BEGIN
^C
  0      2                             :END

[root@db31:/] dtrace -n BEGIN -n END -n ERROR
dtrace: description 'BEGIN' matched 1 probe
dtrace: description 'END' matched 1 probe
dtrace: description 'ERROR' matched 1 probe
CPU     ID                    FUNCTION:NAME
  1      1                           :BEGIN
^C
  0      2                             :END

[root@db31:/] dtrace -n BEGIN -n END -n ERRORS
dtrace: invalid probe specifier ERRORS: probe description :::ERRORS does not match any probes

Wow, this is really neat. Joe Celko, author of various SQL books including my beloved Trees and Hierarchies book, is going to provide virtual training courses on DB Design with MySQL.

There’s not too much detail at the link, but this would be an excellent course to attend by a real SQL master.

It’s also a nice turnaround to see MySQL go from being dismissed by Celko for lack of standards compliance (search MySQL, see page 98) to using the same PL/PSM stored procedures as his books and having him train others on design with MySQL.

Time to save my nickels, this will be a course that shouldn’t be missed!

The 3rd Barcamp Brussels is over, I was a bit dissapointed over the target audience of the first Barcamp, missed the second one, but the 3rd one was right where I expected it to be A healthy mix of technology, startup projects and the social aspect of technology.

I took my Tuxdroid with me, planned on having him tell the audience what they were twittering about, the idea was to take the logfile of my twitter jabber session and feed that to the text to speech deamon into Tux. I failed due to the lack of network hence incoming twitter stream, however the Tuxdroid proudly whacked its wings at the end of each talk :)

So about those talks :)

After Peter gave the startshot for the fight for a slot to talk I went to see the talk about gegis by Dirk Frigne. I totally didn't know there was an OpenSource project written for the Belgian governement which was into Gis .. cool stuff to see.

Frank then was next with his talk about OpenID, I really should spend time with openid, hmm.. I kind of think I`m repeating myselve :) Then Bernard continued with his talk on Performance of webb applications which off course included a large part about Databases.. I need to delete lots of stuff from my presentation :)

John introduced us to NotSoso, now if only I could find some spare minutes to actually look into NotSoso, I guess that that will be for later this week, or even after the weekend.

Then we had a 2 hour break for lunch and a groupfoto by Steven

Maarten did a talk about the Blogoloog and how he started out with an old Laptop with Fedora Core and now evolved into something more suitable , and Peter explained us about Belgian Tax Law .

Then some people found me interresting enough to come and listen to my talk about MySQL Cluster, I actually had taken the talk I had been preparing for the MySQL Symposium that should have taken place in Amsterdam earlier this year and got cancelled. That talk supposed to be over 2 hours. So I kind of cut over 70% of the slides I had tried fitting them into a 20 minute talk. I kind of "managed" :)

Bart gave an interresting talk about your online reputation, how to screw it and how to protect it. An interresting topic came up, should you poison your blog to attract a different audience ? Because according to him your peers are probably not your customers and you should try to get new potential customers to know you . So with that in mind.. what should I start blogging about ?

Clo and Bart Becks held a "Powerpoint Karaoke" on how to create a Sect, Pietel managed to take pictures that suggested that everybody actually joined the sect, that certainly wasn't true ! After It's in the details by Jesse and an even more interresting talk about what we could do with Freebase by Will I headed home exhausted, but satisfied.

I`m sure I missed some other interresting talks like the one from Toon Vanagt about TransferItOnline but other events will come where I can meet and talk to those people again !

typedef struct st_skeleton_share {
char *table_name;
char data_file_name[FN_REFLEN];
uint table_name_length,use_count;
pthread_mutex_t mutex;
THR_LOCK lock;
} SKELETON_SHARE;

fn_format(share->data_file_name, table_name, "", ".XML",
MY_REPLACE_EXT|MY_UNPACK_FILENAME);

void ha_skeleton::start_bulk_insert(ha_rows rows)
{
DBUG_ENTER("ha_skeleton::start_bulk_insert");

xmlbuf= xmlBufferCreate();
writer= xmlNewTextWriterMemory(xmlbuf, 0);

xmlTextWriterStartDocument(writer, NULL, MY_ENCODING, NULL);
xmlTextWriterStartElement(writer, BAD_CAST "TABLE");
xmlTextWriterSetIndent(writer, 1);

DBUG_VOID_RETURN;
}

int ha_skeleton::write_row(byte * buf)
{
DBUG_ENTER("ha_skeleton::write_row");

char content_buffer[1024];
String content(content_buffer, sizeof(content_buffer),
&my_charset_bin);
content.length(0);

xmlTextWriterStartElement(writer, BAD_CAST "ROW");
xmlTextWriterSetIndent(writer, 2);

for (Field **field=table->field ; *field ; field++)
{
if ((*field)->is_null())
{
xmlTextWriterStartElement(writer, BAD_CAST "FIELD");
xmlTextWriterEndElement(writer);
}
else
{
(*field)->val_str(&content);
xmlTextWriterWriteElement(writer, BAD_CAST "FIELD",
BAD_CAST content.c_ptr_safe());
}
}
xmlTextWriterEndElement(writer);

DBUG_RETURN(0);
}

int ha_skeleton::end_bulk_insert()
{
File writer_fd;
DBUG_ENTER("ha_skeleton::end_bulk_insert");

xmlTextWriterEndDocument(writer);
xmlFreeTextWriter(writer);

writer_fd= my_open(share->data_file_name, O_WRONLY|O_CREAT, MYF(0));
my_write(writer_fd, (byte*)xmlbuf->content, xmlbuf->use, MYF(0));
my_close(writer_fd, MYF(0));

xmlBufferFree(xmlbuf);

DBUG_RETURN(0);
}

I’ve talked to several people that have questions about how alter table works under the hood. They want to know how it handles locking tables why they can sometimes use a table during alter table and other times they can’t. Also why it’s so slow

First let’s look at the basic process alter table typically goes through.

1. If a transaction is open on this thread, commit it.
2. Acquire a read lock for the table.
3. Make a temporary table with new structure
4. Copy the old table to the temporary table row by row changing the structure of the rows on the fly.
5. Rename the original table out of the way
6. Rename the temporary table to the original table name.
7. Drop the original table.
8. Release the read lock.

The slowest part of the process is copying rows from the original table to the temporary table. For large tables this can take minutes to hours. There are a few optimizations built into this process. If the alter table query only renames the table then mysql doesn’t bother copying all the rows to a temporary table and just renames it. For most other things such as renaming columns, adding/dropping indexes, making columns nullable, changing the column default all require copying the entire table.

During the first 4 steps MySQL allows other clients to read from the table being altered. When alter table is done copying rows to the temporary table and is ready to rename it it changes the table lock. MySQL instructs all other clients currently reading from the table to close the table when they are done. While alter table is waiting for existing clients to finish reading from the table it prevents other clients from starting to read from the table. During this time selects will be blocked on “Waiting for tables”. When the last client is done reading from the table alter table continues renaming the table.

This has some interesting implications for transactions and repeatable reads. Internally innodb keeps track of when rows are created. When a transaction is started it can only see rows that were created before the transaction was started (using repeatable read). Any rows created after are not returned. Since alter table copied rows from the old table to the new table rows get a new version number as they are inserted into the temporary table. An alter table can cause a dirty read in transactions that span an alter table. Transactions started before alter table will get no rows back from the table after alter table is finished. If your application is sensitive to dirty reads or getting no rows back from a table (really dirty read ) then don’t run alter table on a server when clients are running.

Here is an example.

mysql a> alter ignore table t add unique index (t);

mysql b> begin;
Query OK, 0 rows affected (0.00 sec)

#This select is from the original table while alter table is copying rows
mysql> select * from t limit 10;
+——+
| t |
+——+
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
+——+
10 rows in set (0.00 sec)
#alter table finishes
#Rows created in the temporary table before we issued begin
mysql> select * from t limit 10;
+———-+
| t |
+———-+
| 10 |
| 6920631 |
| 27998430 |
| 41865298 |
| 49403894 |
+———-+
5 rows in set (0.00 sec)

#Get a new view of the table
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

#This select returns all the rows.
mysql> select * from t limit 10;
+———–+
| t |
+———–+
| 10 |
| 6920631 |
| 27998430 |
| 41865298 |
| 49403894 |
| 50522347 |
| 84441015 |
| 109401269 |
| 110202688 |
| 123590778 |
+———–+
10 rows in set (0.00 sec)

If we start the transaction before alter table the select after alter table has finished will return no rows even though there are rows in the table. I’m not sure why innodb allows rename of a table when transactions still have a few of that table open. It seems like a bug to me.

Yes, I know there are a lot of software development "top n" lists out there, and many dealing with writing "-[i/a]ble" software (extensible, maintainable, saleable, etc), but personally I wanted to get these concepts that have been rattling around in my brain for sometime down somewhere and hopefully start a discussion around them. Even though this isn't a wiki, I'll probably end up treating this like a living document myself.

For a long time (in software years), I've been maintaining the code base for the JDBC driver for MySQL. I also work very closely with the support team on various connectivity-related issues (JDBC, ODBC, ADO.Net, P-this-or-that, etc.) that our customers have, and have had the (mis)fortune of debugging all kinds of problems in various stacks over the years.

Sometimes we (MySQL) make debugging more of a problem than it needs to be, and sometimes it's just inherent problems in the "stack". Given that I have a Java (or at least VM-based language) "bent", maybe not all of these concepts are workable at a technical level, but I'd like to at least see that on my team we adhere to as many of them as possible.

• Keep as low a bug count as possible - this is common-sense, keeping the bug count low (through quality design, maintenence, frequent releases, etc.) makes sure that your users aren't running into stupid issues that waste their time (and yours, in diagnosing the issues and working with the user on issues that really just shouldn't happen).

• Grow empathy, not contempt for your users by being a user, rather than just acting like one (or relying on your QA team to act like one) - I'm a firm believer of eating one's own dogfood. I run MySQL "internally" (i.e. personally) for a lot of different things, and am always looking for more opportunites to use the software I work on for day-to-day tasks.
  
  If you use your own software for day-to-day things (especially if it helps you run the photo gallery that the family all looks at, helps with monitoring your network, and keeps the e-mail flowing), you'll run into similar issues that your users will, and end up creating an empathy for your users that is much better than the contempt for them that I have felt and seen from developers of various products.
  
  In my opinion, it's time to "throw in the towel" if you get to the point where as a developer you start to have contempt for your users and the issues they have with your software. If it weren't for the users, why would the software you work on exist in the first place?

• Stay close to your users - blog (yes, I could be better about this!), answer questions on the forums, participate in IRC, etc. Make sure that you help out the "newbies" as well as the "old hands with tough problems", because they span the gamut of user types your software will have, and will bring more than one viewpoint to how easy to use (and thus supportable) your software is.

• Stay close to the sales and support teams (if you're working in a commercial software house) - I think you can do no wrong by staying close to the sales and support teams. Both teams have the information you need that you might not always get from the community, and you'll get a lot of real-world, grounded feedback about what's working and what's not from issues that existing and prospective customers are having. You'll also hear about the extra work your software is causing (or preventing) by how supportable it is through your interactions with these teams.

• "Never ask a customer to re-crash the car" - this is lifted from Elliot Murphy's blog, but I wholeheartedly believe in what he writes - don't make users run a "special" build of your software to diagnose problems if you can at all avoid it. Either provide built-in diagnostic tools or make it possible to figure out what's going on (at some level) by the error information you provide, which leads me to....

• Make error messages meaningful - any time your software encounters an error, and interacts with the user to inform them of it, you have an opportunity to show, as a developer how much you actually value the user's time.
  
  In my opinion, an error message should give as much of the story to the user as that tenet you learned about in elementary school: "Who, What, Where, When (Why, and perhaps "how" to resolve the issue)".
  
  Too often we just write up a string that barely describes the issue in our own terminology, which then just sends the user (or the person supporting them) into some search for what is really happening when that error message is encountered.
  
  Luckily, the "who" is usually assumed, the "where" is covered by a stack trace (at least in VM-based languages), and the "when" is covered by the application log. That leaves coming up with a good "what", "why" and "how" to the developer.
  
  One error message I'm proud of in Connector/J is the one that you get when the connection to the database is lost, or can't be made in the first place. The driver knows the various kinds of exceptions that can happen at connect-time (connection refused, bind refused, etc.) and how long it's been since the driver has (ever) communicated with the server. Based on this information it presents different kinds of error messages, and even points to solutions. For example, if there are no client-side ephemeral ports to bind to, the user gets an error message like this:
  
  "The driver was unable to create a connection due to an inability to establish the client portion of a socket.
  
  This is usually caused by a limit on the number of sockets imposed by the operating system.
  
  This limit is usually configurable. For Unix-based platforms, see the manual page for the 'ulimit' command. Kernel or system configuration may also be required. For Windows-based platforms, see Microsoft Knowledge Base Article 196271 (Q196271)."
  
  If the driver thinks that your connection has been idle for longer than the value "wait_timeout" on the server, you get an error message like this:
  
  "The last communications with the server was nnnn seconds ago, which is longer than the server configured value of 'wait_timeout'.
  
  You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem."
  
  There's a few more types of messages that you get depending on the state of things, but the underlying theme is that the software scrapes up what it knows about the current situation, posits why the situation might be happening, and even tries to offer suggestions of what to do to prevent the error from happening.
  
  Not to say that everything's perfect in Connector/J, I do have this very useful error message still lurking about:
  
  "General error"
  
  The general work of collecting stupid error messages has already been done, but needless to say, I've got work to do fixing some of the few bonehead ones left in our wares.

Okay, so that's six points, I hope to grow this to at least ten, but I've got this blog entry off of my "todo" list for now...

By Mike Hendrickson

Tim has asked me to take over writing the quarterly report on the state of the computer book market.

As described in the post Computer Book Sales as a Technology Trend Indicator, our research group has built a MySQL datamart containing Bookscan's weekly top 10,000 titles sold. Bookscan measures actual cash register sales in bookstores to you, the individuals purchasing and reading the books. Retailers such as Borders, Barnes & Noble, and Amazon make up the lion's share of these sales.

Book Market Performance

Here's the year-on-year trend for the entire computer book market since 2003, when we first obtained the data from Bookscan.

Click on the image to get a larger view.




As you can see, the clear seasonal pattern we've pointed out before still exists. The trend line for each year closely mirrors the year before, with remarkably consistent weekly ups and downs. (The computer book market cratered in 2001, shrinking twenty percent a year for three years until it stabilized in 2004 at about half the size that it was in 2000. We only have data going back to 2003. If we had pre-dotcom bust data, it would all be, to borrow a saying from Al Gore, "off-the-charts.") 2005 saw a slight upturn from what we viewed as the bottom of the market in 2004. 2006 got off to an even stronger start, and it looked as though we were recovering from the post-dotcom-bust slump. But by the second quarter of last year, we'd reverted to the norm for the past three years.

In the first quarter of 2006, new interest in web development associated with Web 2.0 and strong performance of books on digital media applications like Photoshop helped to drive the market. In the first quarter of 2007, we hoped that the Microsoft Vista and Office 2007 releases would cause a similar sharp increase in our trend lines. That has not materialized, and in fact, you could say that Microsoft's new releases have not lived up to expectations yet, at least for book sales. I did say "yet" because there are signs that Vista is starting to pick up steam. But the fact is that without a significant bump from Vista and related Office products, the 2007 market has not performed at the 2006 level. I find it very interesting that the web and digital media had more of a market effect in 2006 than a huge, highly anticipated release of a new version of the world's most used consumer operating system and its office productivity suite has in 2007. It's one more sign of the waning of Microsoft's once fearsome market power.

Comparing Quarters

In order to see what is spiking and deflating the trend line above, we use our Treemap visualization tool. This tool helps us pick up on trends quickly, even when looking at thousands of books. It works like this.

The size of a square shows the market share and relative-size of a category, while the color shows the rate of change. Red is down, and green is up, with the intensity of the color representing the magnitude of the change. A new category like Vista, which did not exist last year and hence can't be compared, shows up as black. The following screenshot of our treemap shows gains and losses by category, comparing the first quarter of 2007 with the first quarter of 2006.

1st Quarter 2007 vs. 1st Quarter 2006
Click the image to get a larger view.

So what are all the boxes and colors telling us? The fact that there are far more red boxes than green tells us that the market slump is broad-based, and that new categories like Vista and Office 2007 aren't enough to offset the overall market decline. If you compare this graphic with the one in Tim's post from the first quarter last year, it is quite noticeable that there are fewer green boxes and the green bright spots are smaller than last year.

It's probably not entirely fair to pick on Vista -- Windows book sales are up 21% over the year before, and Vista titles have taken over all the bestseller slots. But Office 2007 has definitely been a non-factor. While the Office category per se (books on the entire suite) shows 39% growth year over year, Excel and Word books are down 7%, with Powerpoint, Outlook, and Project books even further in the red, leaving the entire office applications category down 21%.

And of course, with both Mac OS X and the Adobe suites (now including Macromedia) down while they await new software releases, there's not a lot of support from the digital media end of the market.

As a result, we have to look elsewhere for signs of strength. In the business applications group, we see Sharepoint continuing its importance, and Quickbooks becoming a staple growth area. We also see some first signs of CRM heating up as a publishing area.

In the Web design and development area, it's worth noting that Ruby on Rails has continued its blazing growth, but Ajax books have not. The decline of both PHP and ASP are striking. Flash and Dreamweaver are also down, but as noted above, awaiting the CS3 release. Flex is starting to show its muscle.

Turning to the software development space, Agile is a category that is growing and one to watch. It wasn't too long ago that the dominant software development subjects were UML and Extreme Programming. Those barely show up any more although Extreme Programming was, loosely speaking, a precursor to Agile. Something worth looking at is the size of the boxes and those changes.

Remember, the size of the box represents the size of the market in relation to all items on the treemap. Rails in the bottom middle was a small speck in last year's first quarter post. Now the size of the box fits the size of its name at least. And its market share is almost equal to SQL and has surpassed VBA, Perl and Python. Python is also experiencing good growth, just not at the blazing velocity of Ruby. I would expect by next year, we will see Ruby have an even larger share/square.

On the Microsoft side, we see a large increase in the category of .Net programming. We really need to re-do this category, as .Net has been deprecated as a term, and this group includes several distinct technologies that need categories of their own. The topics driving the growth in this category include the MCTS certification, WPF, and WCF.

As noted above, Photoshop and related Adobe products are declining as everyone awaits CS3's release. The interesting thing with this market is that it pretty much is a one-for-one replacement type of thing. Most professional artists who make money by using these tools need to replace them to keep competitive. We'll watch this and report back in the coming quarters. I expect this to be a huge growth category by the end of 2007.

A useful way to organize the trends is to identify areas that are High Growth Categories [Bright Green], Moderate Growth Categories [Dark Green to Black], Categories to Watch [all colors], and Down Categories [Red to Bright Red]. Most of these descriptions are self-explanatory except perhaps the Categories to Watch. Categories to Watch contains titles that are typically not susceptible to seasonal swings. Subjects that fit into our Computer and Society segment are included here as well as other subjects that are not typically related to a release cycle. Categories to Watch also include stalwarts like Excel [even though it is susceptible to revisions] that sell, and sell and sell. Photoshop is also in this category as we are watching the effect of a looming new release and what is happening to the existing books in the category. To me, the watch list is a bit subjective and more of an intuitive view than a dogmatic view.

The table below highlights and explains some of the data from the chart above. The Share column shows the total market share of that category, and the ROC column shows the Rate of Change. So, for example, you can see that Windows Desktop OS books represent 9% of the entire computer book market, and were up 20%.

Part two of this series will give a closer look at the technologies within the categories. Part three will be about the Publishers, winners and losers. And part four will be some more analysis of Programming Languages.

Technorati Tags: Analysis, Books, Data, Markets, Trends

time gunzip  -f -v entrieva/db4/guestbook_M-070507.tar.gz
entrieva/db4/guestbook_M-070507.tar.gz:
gunzip: entrieva/db4/guestbook_M-070507.tar.gz: unexpected end of file

real    67m56.782s
user    48m40.006s
sys     14m14.584s

• Introduction
• Backing up disk
• Partitioning the disk
• Solaris Express Developer edition installation
• Installing Sun Studio 11

• Solaris 10 on Mac book - part 1,
• Solaris 10 on Mac book - part 2,
• Solaris 10 on Mac book - part 3,
• Solaris 10 on Mac book - part 4,
  
• Solaris 10 on Mac book - part 5

Reading Linux failing to boot screen on the plane I remembered I saw something similar in my flight to the MySQL Users Conference. It wasn’t so critical, but my entertainment screen got an unexpected reboot and I could see the tux logo and the console for like 2 secons while booting. This was with Northwest Airlines.

So, who said Linux had problems with video?

bash-3.00# make
cc -O2 -Wall -Wno-parentheses    bkf.c   -o bkf
cc: Warning: option -2 passed to ld
cc: illegal option -Wall
make: *** [bkf] Error 1

bash-3.00# CC=`which gcc`
bash-3.00# export CC
bash-3.00# make

bash-3.00# make
/usr/local/bin/gcc -O2 -Wall -Wno-parentheses    bkf.c   -o bkf
Undefined                       first referenced
symbol                             in file
gethostbyname                       /var/tmp//ccGSplTt.o
socket                              /var/tmp//ccGSplTt.o
connect                             /var/tmp//ccGSplTt.o
ld: fatal: Symbol referencing errors. No output written to bkf
collect2: ld returned 1 exit status
make: *** [bkf] Error 1

export LDFLAGS="-lsocket -lnsl -lm"

bash-3.00# make
/usr/local/bin/gcc -O2 -Wall -Wno-parentheses  -lsocket -lnsl -lm  bkf.c   -o bkf

CFLAGS=-O2 -Wall -Wno-parentheses

CFLAGS=-xO2 -v

Gcc does have it documented in the 'info' files; you just have to look
under "-Wparenthesis" in the warning options.

For the equivalent outside of gcc, see lint's "-errchk=no%parenthesis"

option.  (Our tool chain doesn't get lint and cc confused.  ;-})

bash-3.00# make
cc -xO2 -v    bkf.c   -o bkf
"bkf.c", line 196: warning: Function has no return statement : clone
Undefined                       first referenced
symbol                             in file
gethostbyname                       bkf.o
socket                              bkf.o
connect                             bkf.o
ld: fatal: Symbol referencing errors. No output written to bkf
make: *** [bkf] Error 1

for i in /lib/*.so
do
 /usr/ccs/bin/nm -Ag $i |grep -v UNDEF |grep gethostbyname
done

/lib/libnsl.so: [2643]  |    110640|      89|FUNC |GLOB |0    |11     |gethostbyname
/lib/libnsl.so: [2683]  |    110202|     222|FUNC |GLOB |0    |11     |gethostbyname_r
/lib/libnsl.so: [3113]  |    128448|     174|FUNC |GLOB |0    |11     |_switch_gethostbyname_r
/lib/libnsl.so: [2841]  |    110108|      44|FUNC |GLOB |0    |11     |_uncached_gethostbyname_r
/lib/libresolv.so: [1585]       |     78996|      38|FUNC |GLOB |0    |11     |res_gethostbyname
/lib/libresolv.so: [1397]       |     79034|      41|FUNC |GLOB |0    |11     |res_gethostbyname2
/lib/libxnet.so: [79]   |         0|       0|FUNC |GLOB |0    |ABS    |gethostbyname

export LDFLAGS="-lsocket -lnsl -lm"

bash-3.00# make
cc -lsocket -lnsl -lm  bkf.o   -o bkf

• The slides for Part 1 in PDF format.
• The slides for Part 2 in PDF format.
• The slides for Part 2 in PDF format.
  
• The webex file for Part 1 is here.
• The webex file for Part 3 is here.

MySQL Master-Master Replication Manager version 1.0-pre3 has been released today. Changes list is really short now:

• Major fix in multiple clusters support - now you can use many clusters with one monitoring node (details are in mmmd_mon man page)
• Man pages for mmmd_mon, mmmd_agent and mmm_control scripts
• Startup scripts added/fixed for mmmd_mon and mmmd_agent
• Installation script now requires iproute package to be installed on server.

As always, if you have any questions/suggestions, post them here or in mmm-devel mail list.

Tags: mysql, master-master, replication, release

Surfs up for the "Core Team"

We keep user data, forever

We have a non disclosure act. It has a non compete clause.

We won't release any code under an open source license

There's not enough financial transparency

There's no elections for positions

gunzip *.gz

pkgadd -d libiconv-1.11-sol10-x86-local 
# this will install SMCliconv

pkgadd -d gcc-3.4.6-sol10-x86-local
# installs SMCgcc 

pkgadd -d openssl-0.9.8e-sol10-x86-local
# installs SMCossl

pkgadd -d wget-1.10.2-sol10-x86-local
# installs SMCwget

pkgadd -d make-3.81-sol10-x86-local
# installs SMCmake

wget http://www.bitmover.com/bk-client2.0.shar
/bin/sh bk-client2.0.shar
cd bk-client2.0
make

So I've been using Virtual PC 2007 since it was released but was always frustrated with its inferior Linux support.  I don't really understand why Microsoft drags their feet on this.  With more and more devs moving to vm only development, it's getting easier and easier to change your host platform and still development for Windows.  I'm sure Microsoft would much rather you run Windows as your host platform and run Linux in a vm when you need.   I've tried VMWare Server but since I run Vista x64 as my host, that's more pain than I really like.

Enter VMWare 6.  Sure it installs and runs on Vista x64 like a champ and performance seems great but what really blew me away was the multi-monitor support.  I've been using twin 19" monitors for nearly 2 years now and I simply can't imagine life without them.  Folks I kid you not.  It was easier to get Ubuntu running on two monitors using VMWare 6 than it was running on my actual hardware.  I installed Feisty Fawn and then followed that up by installing the VMWare tools  (which installed without a hitch).  At the final stage of the VMWare tools install, it asks me what resolution I want to use.  I gave it a reasonable response, finished with VMWare tools, and then logged off so I could restart X.  Bam, the screen jumped to some huge resolution.  Something like 3000x2000.  I hit the maximize button on the VMWare screen and the Ubuntu desktop neatly filled both my monitors.  No editing of any x config files.  No Windows desktop elements visible.  No Windows taskbar.  Nothing.  Sweetness.

Multi-monitor support is not supported in Windows Server 2003 but XP works like a champ.  VMWare 6 puts me a little bit closer to vm only development.

MySQL Table Checksum 1.1.0 adds many improvements, but the most important is a new way to ensure slaves have the same data as their master. Instead of checksumming the slave and the master, it can now insert the checksum results directly on the master via an INSERT.. SELECT statement. This statement will replicate to the slave, where a simple query can find tables that differ from the master. This makes a consistent, lock-free checksum trivially easy.

There are also many other feature improvements and bug fixes, compatibility with MySQL 3.23.2 through 6.0-alpha, and finally I've gotten the documentation finished to my satisfaction.