It's been interesting watching the MySQL drama unfold, but I have to take issue when people start trying to drag Postgres into it again by spreading FUD (Fear, Uncertainty, and Doubt). Rather than simply rebut the FUD, I thought this was a good opportunity to examine the strength of the Postgres project.

Monty recently espoused the following in a blog comment:

Other day I noticed a case where loading the same set of data to InnoDB took only 10 minutes where as loading it to MyISAM took ~2 hours.

Digging it further found that it is all because of well known Repair with keycache issue. But for some reason, it took me a while to get to the root cause of the issue as it was working fine until few days. When MyISAM needs to repair the table (REPAIR, ALTER or LOAD or ENABLE KEYS); it uses two modes for repair:

• repair by sorting
• repair using keycache (falls to this mode by default if repair by sort fails for any reason)

first it tests if the table can be repaired by sorting provided it meets the following requirements:

• table at least has one key
• total size needed for individual key is less than myisam_max_sort_file_size

If it meets the above requirements, then …

Users toggling between MySQL and Postgres are often confused by the equivalent commands to accomplish basic tasks. Here's a chart listing some of the differences between the command line client for MySQL (simply called mysql), and the command line client for Postgres (called psql).

Larry Ellison makes it very clear that Oracle believes in a back to the future model where software and hardware meld together into “systems”, purpose-built, integrated solutions. In other words you won’t buy an Oracle database and a server and configure it to run a data warehouse, instead you’ll buy the “Oracle Data Warehouse Server.” The first such system is Exadata, which is apparently doing quite well, according to Ellison.

This is a classic bundling, although some may call it a tying strategy. Microsoft, seeing that they couldn’t win each office productivity segment individually—including word processing, spreadsheet and presentations—decided to play to their strength and bundle them into a solution that no individual company could compete with. This is bundling. The tying strategy is where Microsoft used their dominance in …

Topics for this podcast:

*2009 review and 2010 preview
*New CAOS survey and report – Climate Change
*Ups and downs in new round of GPL lawsuits
*Oracle-Sun-MySQL saga continues

iTunes or direct download (30:00, 6.9 MB)

In one week, on December 24th, it will be exactly one year since I was first contacted by Packt Publishing. After reading several posts from this blog they asked me if I’d be interested in writing a MySQL administration cookbook with hands-on recipes for those among us who have to make sure the MySQL servers are kept running and in good shape.

Funny thing, I almost deleted their email, because initially I thought GMail’s spam filter had not caught some sort of bulk or phishing email, because I had never heard of Packt Publishing before and at first only saw an unfamiliar sounding sender’s name. As I was one of very few people in the office on that day I decided to read it anyways. Turned out to be not so spammy after all…

What followed were several weeks of sending mails back and forth, convincing a colleague to co-author and together set up a chapter outline. Finally, around February we started writing actual contents. …

In an amazing display of chutzpah, Monty Widenius recently asked on his blog for people to write to the EC about the takeover of Sun by Oracle and its effect on MySQL, saying:

I, Michael "Monty" Widenius, the creator of MySQL, is asking you urgently to help save MySQL from Oracle's clutches. Without your immediate help Oracle might get to own MySQL any day now. By writing to the European Commission (EC) you can support this cause and help secure the future development of the product MySQL as an Open Source project.

"Help secure the future development"? Sorry, but that ship has sailed. Specifically, when MySQL was sold to Sun. There were many other missed opportunities over the years to keep MySQL as a good open source project. Some of the missteps:

• Bringing in venture capitalists
• Selling to Sun instead of making …

Shared-disk databases can be virtualized—making them cloud-friendly—while shared-nothing databases are tied to a specific computer and a specific data set or data partition.

The underlying principle of the shared-nothing RDBMS is that a single master server owns its specific set of data. That data is not shared, hence the name shared-nothing. Because there is no ability to share the data, there is also no ability to virtualize the computing of that data. Instead the shared-nothing RDBMS ties the data and the computing to a specific computer. This association with a physical machine is then reinforced at the application level. Applications leveraging a shared-nothing database, that is partitioned across more than one server, use routing code. Routing code simply directs the various database requests to the servers that own the data being requested. In other words, the application must know which server owns which piece of data. …

For a lot of log processing, I need to store IP addresses in a database table. The standard process was always to convert it to an unsigned int in perl or php and then insert it. Today I discovered an easier way. MySQL's INET_ATON function. It takes an address in dotted quad format and converts it into an INT. So, all you have to do is this:

INSERT INTO table (ip) VALUES (INET_ATON('$ip_address'));

And done.

I am not sure if this is a bug or how MySQL works on validating constraints in association with ON DUPLICATE KEY (late or early checking). For example, consider the following use case (this is irrepective of storage engine and MySQL version):

      mysql> create table t1(id int not null primary key, val int not null) Engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)
 
mysql> insert into t1 values(10,20);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into t1 values(20,10);
Query OK, 1 row affected (0.00 sec)
 
mysql> create table t2(id1 int not null primary key, val1 int) Engine=MyISAM;
Query OK, 0 rows affected (0.14 sec)
 
mysql> insert into t2 values(10,NULL);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t1(id, val) select id1, val1 from t2 ON DUPLICATE KEY UPDATE val=IF (VALUES(val) IS NULL, val, VALUES(val));
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 1  Warnings: …