Over 200 Responses in Less than 2 Weeks

Less than two weeks ago, I posted my Database Survey. As of just a few minutes ago, I have had 215 responses. That's pretty awesome. I'd like to get at least twice that though.

I haven't looked deeply at it yet to see if there are any trends. I think it will be best to wait until the survey is closed. I did look at some of the responses, kind of as a quality check. Looks like MySQL is fairly well represented. I didn't see any DB2 responses (for primary database). I did see plenty of Oracle and a few Postgres.

I will leave it up for another 2 1/2 weeks (for a total of 4 weeks). If you haven't taken it yet, please do so if you get a few minutes. It only takes 5-10 minutes as there is only 25 questions.

Also, if you have a blog, post on forums (without spamming), or have any other ways to spread the word, I would appreciate it.

Thanks,

LewisC

Technorati : commercial, database, open source, survey

Infoworld Picks MySQL as Best Database

Infoworld published the 2008 Bossies, Best Of Open Source Software. There are 8 categories and none of them are database:

• Collaboration
• Developer tools
• Enterprise applications
• Networking
• Platforms and middleware
• Productivity applications
• Security
• Storage

I had to look through several of them before I found the database category under Platforms and middleware. Slide 4 is the magic slide:

It says:

Database

While SQLite3 is extremely convenient for development and testing databases, and PostgreSQL has powerful Generalized Search Tree indexes and is very close to being enterprise-ready, MySQL is the choice for many Web sites thanks to its excellent read performance, transparent support for large text and binary objects, and incredibly easy administration. Stored procedures, functions, triggers, and updateable views were added to MySQL in version 5, overcoming the largest technical objections to its deployment at many sites. MySQL also has a large, helpful user base, and some poster-child deployments including eBay, Yahoo, and Craigslist.

I'm not sure why SQLLite would even be on the list. There are plenty of other OSDBs that I would put my bets on before SQLLite. Not that SQLLite is bad, it's just not a "best of" kind of thing. I don't imagine the Postgres folks are too happy at the "also ran" placement. "Close to being enterprise-ready", ouch.

I have to agree that MySQL has a large, helpful user base. I actually think that is one of the best things about MySQL.

LewisC

Technorati : database, mysql, open source

Is Drizzle good for MySQL?

Have you heard of Drizzle? It was announced at OSCON yesterday and is all over the blogosphere. From the Drizzle FAQ:

* So what are the differences between is and MySQL?

No modes, views, triggers, prepared statements, stored procedures, query cache, data conversion inserts, ACL. Fewer data types. Less engines, less code. Assume the primary engine is transactional.

Also from the FAQ is that, right now at least, there is no intention to make this run natively on windows and they make the point:

* "This is not a SQL compliant relational..."

Very true, and we do not aim to be that.

It is a fork of MySQL that takes it backward to pre-5.0 in features but hopefully greatly reduces the bugs and instabilities. I plan to look at it but I don't see much enterprise adoption. It was the enterprise users who wanted stored procedures, views and most of the other stuff that is being removed. I think it will be adopted mainly by read-only (or mostly) web sites that want to serve many pages. That's ok. There's a huge market for that. If I found a fit for a client, I would consider it.

They are very honest with the goals:

* What is the target?

Deliver a microkernel that we can use to build a database that meets the needs of a web/cloud infrastructure. To this end we are exploring http interfaces, sharding enhancements, etc... do not expect an Oracle, MySQL, Postgres, or DB2.

I was reading my usual stable of blogs this morning and ran across two blog entries that got me thinking. First is Ronald Bradford's blog, The new kid on the block - Drizzle. Ronald gives a great overview of what Drizzle is trying to achieve, the current state of MySQL and reasons why Drizzle is a good idea. This is well worth a read. I'm just this far from being convinced. ;-)

The second was On MySQL Forks and MySQL's non-open source documentation on Jeremy Cole's blog. To answer his question, I did not know that the MySQL documentation was not open source. That is a very interesting point and one that never even occurred to me. I wonder if Sun would consider opening it?

Anyway, both of those got me thinking about forking MySQL. If you look at the Postgres community (not comparing the databases, just the goals of the community) over the years, they have taken great pains to not fork the database. The contrib modules are there so that the database can add functionality without forking. The companies who have tried to make a go at monetizing Postgres have also taken great pains to not fork the database.

In fairness to Drizzle, it may have a sort of contrib module like functionality (bolding is mine):

* What is the goal?

A micro-kernel that we then extend to add what we need (all additions come through interfaces that can be compiled/loaded in as needed). The target for the project is web infrastructure backend and cloud components.

With Postgres, even companies like Yahoo and Skype who needed specific functionality have contributed that back to the community so that the code will eventually be reworked into the mainline (or as a contrib). There is a very anti-forking bias in the Postgres community and I think that has helped advance the database.

Ignoring any commercial interests, will the MySQL community become fragmented by forking? Because honestly, the community is the important part of any open source project. If Sun is putting its resources behind MySQL 5.1 and 6, and if they don't open the documentation, where will Drizzle go?

I'm just not convinced that Drizzle is a good thing or that it's needed. Then again, if they concentrate on making this a natively scalable database for the cloud, maybe it is time for a fork. Although, I'm not sure that goal is achievable starting with the MySQL codebase. What do you think?

LewisC

Technorati : competition, fork, fragmentation, mysql

Results of EnterpriseDB Open Source Database Survey

EnterpriseDB announced the results of the survey they did a few months ago at OSCON. Now, take the results with a grain of salt as it was done by EnterpriseDB. EnterpriseDB is based on Postgres so there is a vested interest in making Postgres sound good. Results can be skewed depending on how the survey is worded, what options are available as answers and who the respondents are.

The results summary is available for free.

Some key facts:

500 respondents. The download page says "500 corporate IT leaders". Or maybe, 500 open source developers. ;-)

Only 9% of respondents indicated that they preferred commercial solutions over open source solutions. I would guess that a majority of those responding were open source database people anyway. This is also one place where I think the wording of survey questions makes a difference. I'd like to see the survey again and compare the results to the survey itself.

The survey shows that respondents are using open source to migrate away from Oracle and SQL Server. It says that less than 1% is using open source to migrate away from DB2. Since DB2 is a major investor in EnterpriseDB, that doesn't surprise me. Again, the target users of the survey make a difference as well as the questions themselves.

Of course, Postgres was chosen more than any other open source database for transactional applications and high reliability. Again, not surprising based on who wrote the survey and what they sell.

Before I put very much value on this survey, I would want to see more than just a hand-crafted summary of the results. A spreadsheet of all the questions and the answers chosen would be, at least somewhat, valuable. Without that though, it's just marketing. I can't find anything on the site indicating the full results will be made available.

Del.icio.us : database, enterprisedb, mysql, open source, oscon, postgres, survey

MySQL vs Postgres, Again - Is Postgres Better?

I was browsing the web on this lazy Sunday afternoon and ran across a good article on the Rarest Words blog. The author was trying to get Django installed and running with Postgres. From the author's own admissions, he is not a Postgres fanatic.

Well, this and last year I hear everywhere that PostgreSQL is the way to go and that usage of mySQL in 2008 makes people puke… But without any real arguments (besides "Postgres is the way to go").

After some not so compatible errors with these not so compatible databases, the author did get it working and ran some benchmarks. Postgres did not turn out faster than MySQL. If you ask anyone in the Postgres community which database is faster, they will say Postgres. Ask anyone in the MySQL community and there's no telling what answer you'll get. ;-)

I have now worked with quite a few different databases. Over the last decade most of my time has been spent with Oracle but I have also spent some time with MySQL and Postgres. So, I have to tell you, Oracle is faster. ;-) Just kidding.

What I have found is that any claim that one database is the best database is just kind of silly. Every database has a different feature set, a different set of strengths and a different set of weaknesses. Comparisons are good so that people know where a database is best used but it's pointless for claims of "winners and losers".

It's sort of like benchmarks. If you benchmark two databases, the loser fan base will always claim that you didn't tune correctly. Or the benchmark was invalid. Or the wrong engine was used. Or whatever. They may be true but even so, with all things being equal, that does not invalidate the benchmark. Under those conditions, one or the other is faster. Is that significant? Probably not.

So, which is better for Django? I haven't a clue. I don't know much about Django. I'd say the best database is the one you are most comfortable using and have the most experience with.

When switching from one database to another, I think most people have pretty much the same experience as the blog author had. It can be really painful at first. I like it. Not the pain so much but digging in to it. I like understanding the differences between one database and another. But then, I'm sort of weird that way. ;-)

For business reasons, if a database is working fine for your needs, don't switch. Most people don't need the absolute performance that a $250/hour brain surgeon DBA might be able to give you. And if you do, you're probably already running Oracle. ;-)

LewisC

Technorati : compare, database, django, mysql, oracle, postgres

Free Database Design Tools

LewisC's An Expert's Guide To Oracle Technology

Sun just announced MySQL Workbench, a new database design tool for MySQL developers and DBAs. I'm a data modeling tool junkie. I like to play with any I can get my hands on. I've used almost every modeling tool that's been built. My all time favorite is probably Erwin.

I decided to download MySQL Workbench and give it a try. Since I was playing with it, I figured I should write about it and while I am writing about it, I might as well write about a couple of other tools, that I have personally used, that you might like.

TOAD Data Modeler

The TOAD Data Modeler from Quest used to have a free version. I can no longer find a link to a free version but you can download a free copy of the latest Beta version. It's unfortunate that Quest has decided not to continue the free version, though. Like most Quest tools, the price tag for Data Modeler is high, $479.00 per seat. That's a bit more than I want to pay.

It is a good looking tool though. If I am mistaken and you can find the free version, it worth checking out.

It has everything you would expect in a data model tool. It also has excellent support for most databases. It supports the commercial databases (SQL Server, Oracle, Sybase and DB2). It also supports Postgres 8.1 and 8.2 (no 8.3 so no XML type) and MySQL 5.

It can be a bit kludgey to use at times and at $479.00 I don't have any reason to recommend it over my next design tool

fabForce DBDesigner 4

DBDesigner is a MySQL database design tool that just happens to provide some support for other databases. It supports SQL Server, Oracle, SQL Lite and ODBC.

There are a few features I really like in DBDesigner4.

• It's free. I like free.
• If you are using a database not directly supported, you can create your own data type. Right click in the data type window and select Create New Datatype.
• Reverse Engineering. That means you can connect to a database and it will import your model. That's rare in a free tool.

I have used this tool for a while now and have designed a couple of different application schemas using it.

My only complaint would be that the interface sometimes seems non-intuitive/non-standard. I find myself looking around trying to find the button or menu option that I know is there, just not where I expect it to be. That's a nit though. I definitely recommend this tool to anyone looking for design tool. I would choose Erwin of DBDesigner but only if someone else was paying for it.

MySQL Workbench

And now on to MySQL Workbench. I have to say that I have only spent a short amount of time playing with it but I like it. Sun is offering a community edition and a standard edition. The community edition is feature limited and free, standard has additional features and costs $99.00. You can read about the differences.

As I used it, I kept thinking to myself how much like DBDesigner it is. I don't know if they licensed DBDesigner code or if it's just coincidence. The interface does have a different look and feel but there is something about it that just makes me think DBDesigner.

Compare the DBDesigner screen shot to the MySQL Workbench screen shot. They're eerily similar.

Still, like most MySQL tools, it's easy to install and use. Even if it is based on DBDesigner, it is restricted to being just a MySQL tool. I couldn't find anyway to connect to any database though. That might be a limitation between standard and community.

NOTE: I just found a Workbench FAQ entry about DBDEsigner 4:

Q.2: Is the MySQL Workbench based on the code of DBDesigner4?

No, MySQL Workbench is a complete rewrite in C++ / C# / Objective-C. Not a single line of code is shared between the projects. But Workbench does build on the experience and feedback got from the DBDesigner4 project and should be better than its predecessor in every respect once GA quality is reached.

If you're MySQL only, Workbench could be a good choice. DBDesigner does offer reverse engineering and documentation for free though, in addition to supporting multiple databases. I think I will stick with DBDesigner4 for now (when my employer/client doesn't provide their own modeling tool).

LewisC

Technorati : data, data model, database, design, model, mysql, oracle, postgres, tool

MySQL vs Postgres Wiki

There is a new wiki comparing MySQL to PostgreSQL. Because it's a wiki, hopefully it can be kept updated so that it's current AND accurate. The wiki is MySQL vs PostgreSQL. Personally, I'd like to see this grow into a universal comparison site that the community could keep updated. LewisC

Hiding SQL in a Stored Procedure

I recently wrote a blog entry (on my Postgres blog) about hiding SQL in a stored procedure, Hiding SQL in a Stored Procedure. I decided to see if I could convert that same concept to a MySQL stored procedure.

It doesn't work exactly the same. For one, the syntax is a little different. I expected that and the syntax differences really aren't that bad. Minor tweaks really.

The second issue is the major one. While I could write the proc and return a result set, I am not, as far as I can tell, able to treat the procedure as a table. In Postgres, I created a function with a set output. Unfortunately, MySQL does not allow sets as a function result. You can return a set from a procedure though, as odd as that sounds.

So here is what I found.

My create table command and inserts ran unchanged. I did run into an issue with the timestamp though.

mysql> create table test_data (

-> name text,

-> address text,

-> create_date timestamp );

Query OK, 0 rows affected (0.09 sec)



mysql> insert into test_data values (

-> 'lewis',

-> '123 abc st',

-> timestamp '2001-01-01 10:00:00');

Query OK, 1 row affected (0.02 sec)



mysql> insert into test_data values (

-> 'george',

-> '456 def dr',

-> timestamp '2091-01-01 10:00:00');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>

mysql> select * from test_data;

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

| george | 456 def dr | 0000-00-00 00:00:00 |

+--------+------------+---------------------+

2 rows in set (0.00 sec)

Notice the timestamp in the "george" record is all 0s. I figure that's a configurable issue but I don't really care to research it at this moment so I'll just delete it and use a timestamp that's a little closer to NOW.

mysql> delete from test_data where name = 'george';

Query OK, 1 row affected (0.03 sec)

mysql> insert into test_data values (

-> 'george',

-> '456 def dr',

-> timestamp '2021-01-01 10:00:00');

Query OK, 1 row affected (0.02 sec)

mysql> select * from test_data;

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

| george | 456 def dr | 2021-01-01 10:00:00 |

+--------+------------+---------------------+

2 rows in set (0.00 sec)

mysql>

Ok. Now I'm ready to go. I look at the proc that I wrote for Postgres:

CREATE OR REPLACE FUNCTION get_data_by_creation(

          timestamp without time zone, 

          timestamp without time zone)

RETURNS SETOF test_data 

AS

$$ 

SELECT name, address, create_date

  FROM test_data

  WHERE create_date >= $1

    AND create_date <= $2;

$$

LANGUAGE 'sql' VOLATILE;

That's obviously not going to work but like I said above, the changes are fairly minor. I need to add a delimiter call and drop the postgres specific stuff:

delimiter //

CREATE PROCEDURE get_data_by_creation(

    IN param1 timestamp, 

    IN param2 timestamp)

BEGIN

  SELECT name, address, create_date

    FROM test_data

    WHERE create_date >= param1

      AND create_date <= param2;

END;

//

That compiles fine. Now for the test. I can't use select so I will do a call. I write three call statements: one to return both records, one to "george" and one to return "lewis".

call get_data_by_creation('2000-01-01 10:00:00','2025-01-01 10:00:00');

call get_data_by_creation('2002-01-01 10:00:00','2025-01-01 10:00:00');

call get_data_by_creation('2000-01-01 10:00:00','2010-01-01 10:00:00');

When I run these, I get the expected results:

mysql> call get_data_by_creation('2000-01-01 10:00:00','2025-01-01 10:00:00');

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

| george | 456 def dr | 2021-01-01 10:00:00 |

+--------+------------+---------------------+

2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.05 sec)



mysql> call get_data_by_creation('2002-01-01 10:00:00','2025-01-01 10:00:00');

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| george | 456 def dr | 2021-01-01 10:00:00 |

+--------+------------+---------------------+

1 row in set (0.02 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> call get_data_by_creation('2000-01-01 10:00:00','2010-01-01 10:00:00');

+-------+------------+---------------------+

| name | address | create_date |

+-------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

+-------+------------+---------------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Sweet! This code is actually not all that far from Oracle's PL/SQL. I'll do up an example of that next.

LewisC

Does MySQL GIS Make The Grade?

Bob Zurek of EnterpriseDB posted a blog entry today titled, "We slammed into a brick wall with MySQL". If you read his blog entry, the information he is referencing is in this press release, FortiusOne Migrates GeoCommons Intelligent Mapping Website to EnterpriseDB Advanced Server. If you read that press release, it says:

“We slammed into a brick wall with MySQL,” said Chris Ingrassia, chief technology officer, FortiusOne. “As an example, MySQL’s rather limited and incomplete spatial support dramatically impacted performance. We were looking for an affordable database solution, but we required enterprise-class features and performance that MySQL simply couldn’t deliver. Plus, philosophically we want to support open source-based technologies like EnterpriseDB.”

I'm not at all familiar with the MySQL GIS support and only remotely familiar with PostGIS (PostgreSQL GIS). Is MySQL GIS support lacking or was that particular application of MySQL GIS a bad fit? Anyone familiar with both? I'm curious as to how they compare. LewisC

Tablespaces in MySQL, Oracle and Postgres

If you are not familiar with tablespaces you may be wondering what the big deal about them is. Tablespaces are a logical addition to a database that helps maintenance, and potentially, can improve performance.

In Oracle and MySQL, a tablespace is a logical unit meant to store segments (i.e. tables and indexes). In Postgres, a tablespace is a physical unit. It is a symbolic link to a directory. Postgres does not allow tablespaces on operating systems that do not support symbolic links (such as windows).

The data file is the actual physical storage mechanism in Oracle and MySQL. Postgres stores tables in individual files. Postgres support of tablespaces is minimal. In MySQL and Oracle, performance can be improved by a more granular spread of data across disks. Ease of maintenance is maintained due to the logical grouping of tablespaces.

Oracle syntax for creating a tablespace is much the same as MySQL but with many more options. Oracle also allows a single tablespace to be made up of many data files.

Below is a very simple example of creating and using a tablespace in MySQL:

mysql> create tablespace testts

-> add datafile 'myfirstfile'

-> engine = falcon;

Query OK, 0 rows affected (0.48 sec)

mysql> create table testmyts (

-> abc integer )

-> tablespace testts;

Query OK, 0 rows affected (0.11 sec)

mysql>

The first command creates the tablespace (naming it testts) and assigning it a file name of myfirstfile. Of course, we are using the falcon engine. If you look in your MySQL data directory after running this command, you should a new file named myfirstfile.

The second command creates a table using our new tablespace. If you look at the tablespace now, it should be bigger. If not, insert a bunch of rows and watch it grow.

Oracle syntax, in its simplest form, is very close to MySQL syntax. Here is the same example in Oracle.

SQL> create tablespace testts

2 datafile 'myfirstfile'

3 size 10M;

Tablespace created.

SQL> create table testmyts (

2 abc integer )

3 tablespace testts;

Table created.

SQL> 

In Oracle, we leave off the engine keyword and we need to declare a size. We can create a small data file and use autoextend if we want, but we MUST declare an initial size.

In Oracle, it is possible to create each partition of a partitioned table in its own tablespace. I'm not sure if that's possible with MySQL, but I plan to try it out!