sql

One of the things I preach about a lot is good monitoring of your database servers; having tools in place to tell you both what good looks like and when things go bad is critical for large scale success. But sometimes you just need to monitor a momentary process, where setting up a check in your normal monitoring software is overkill. In these cases one tool that can help out is the watch command.

Case in point, the other day I needed to back up a fairly large partitioned table (about 1.3TB on disk). The plan? A quick little script to pg_dump each of the partitions (about 325). Feed the script through xargs -P so I don't swamp the box, but I get some concurrency out of things. And of course, I planned to run the whole thing in screen session. But dumping this much data will take some time, so how to check on the progress?

When working on databases, one of the most natural things to me is to whip up some SQL to see what going on inside my database. Then you pipe that through watch, and you have some quick and simple monitoring. This example happens to be on postgres, but you could do it with any database's command line program.

Happy to see the first issue of the new Open Source Database Magazine has been released.

For those that aren't aware, Open Source Database Magazine is a re-incarnation of the old MySQL Magazine. The open source database ecosystem has grown a lot of the last year, with the rising popularity of newish systems like Drizzle and MariaDB, the continued growth of the PostgreSQL community, the revival of old concepts like CouchDB, and the really ground breaking stuff like HadoopDB.

So, check out the website, check out my article, and let the OSDBzine folks know what new stuff in Open Source Database you'd like to see more about.

This week I'll be traveling to California to take part in the Percona Performance Conference, a two-day event run concurrently with the MySQL Users Conference, but centered around all aspects of internet based design, performance, and scalability. At OmniTI, we have helped many organizations scale their systems using Oracle, Postgres, and/or MySQL, both for high OLTP loads as well as large OLAP oriented systems. In my talk, "Scaling with Postgres" (Wednesday @ 11:55AM), I plan to explain the general path for scaling Postgres related systems, and compare how this lines up with other database systems.

Since I'll be in town for a few days, I've also signed up to do a Postgres BOF at theMySQL users conference (Wednesday @ 7:30PM). Currently the BOF has no topic, but there are a number of topics that I expect people might want to talk about, including ways to use Postgres and MySQL together, Porting applications and doing cross database development, and of course the straight-forward "why should I use Postgres?" If you are planning on coming to the BOF and have a specific topic or question you'd like answered, please post it in the comments, or send me an email.

Note I'll also be in town Thursday as well; I'm hoping to take in several of the talks during that time, but I've no official duties, so should be available for further questions and conversation during that time as well. Hope to see you there.

Well, it's been a long time (100 editions in fact!) since I have hosted a log buffer, but I thought what better way to break-in the new blog than by hosting the 127th edition of Log Buffer. Let's get started!

Lorenzo Alberton has a nice post about understanding how different databases handle quoting identifiers and case sensitivity. There was one thing I thought worth adding to the discussion, which was to remind folks that along with the inconsistent behavior you get running MySQL on different operating systems, you need to be aware that it also uses a non-standard quoting mechanism; using ` rather than " for quoting identifiers. You can change this behavior to something more standard by using the SQL_MODE parameter, worth looking at if you're doing cross database development (or just future proofing your app). Of course, thinking about SQL_MODE, you also have to be aware of things like lowercase_table_names, which can also change the behavior that you might see (i'd recommend defining that setting as part of your database naming conventions). And of course, you can override a lot of these things at the session level, so you can't necessarily count on setting these in your mysql conf file (consider an application that overrides this behavior). Blech... sometimes TMTOWTDI is more trouble than it is worth.

When I first saw the Google Chrome comic book, I really thought it was a great idea. Delivering tech content in a light-hearted manner has often been my style, and this just raised that to a higher level, with friednly comic style animations to walk you through it. It did make me wonder though, would a postgres book along similar lines be successful? I mean, we've been talking about how processed based architectures lead to more stability for years, but no one seemed to care about that untill Google talked about it.

But, it seems I'm already late to the game, based on this upcoming book (hat tip to Mark for the link):



I know, it sounds like a joke, but check out the description:

In The Manga Guide to Databases, Tico the fairy teaches the Princess how to simplify her data management. We follow along as they design a relational database, understand the entity-relationship model, perform basic database operations, and delve into more advanced topics. Once the Princess is familiar with transactions and basic SQL statements, she can keep her data timely and accurate for the entire kingdom. Finally, Tico explains ways to make the database more efficient and secure, and they discuss methods for concurrency and replication.

No Starch, if you need a tech/book review, mark me down, I can't wait to see this!

(And BTW, if any Japanese folks are familiar with Mana Takahashi's work, I'd love to hear your thoughts or get some links in the comments)

Lukas' recent post on [http://pooteeweet.org/blog/1201 solving the prepared statement problem] got wondering, if open source databases implement something, how likely is it to become a defacto standard? The best example of this is the LIMIT clause, which both [http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html MySQL] and [http://www.postgresql.org/docs/current/interactive/queries-limit.html PostgreSQL] support, and that anyone who has used either database will tell you is a far superior tool to what it is available in other systems (don't get me wrong, [http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html rownum()] has it's place, but for what LIMIT does it's not as good). And while LIMIT is not standard SQL, can you imagine if the SQL Standard Committee were to adopt a syntax that conflicted with the Postgres/MySQL implementations?



Take this with a grain of salt, but really there are only 5 databases that matter anymore, Oracle, DB2, MS SQL, MySQL, and Postgres. Sure, there are many other successful database systems, and also some niche databases that are big players in their segment, but those 5 are the ones that set the tone. Even with Oracle far out pacing MySQL on dollars, the number of people who have joined the tech industry within the last 5 years who have hands on experience with MySQL high enough that it is not uncommon for people to look for their syntax in other database products (for better or worse). And remember, Postgres is [http://freshmeat.net/stats/#popularity even more popular] than MySQL in some places; one should expect the impact of open source databases to rise in this area faster than just by watching market share.



So, what kind of problems could be solved with new syntax? Honestly I am not really sure. One piece of syntax I always though SQL got wrong was the UPDATE statement. I've always thought that the syntax should have been UPDATE table WHERE conditions SET column=data, if only because it would have saved us from countless unconstrained updates from junior DBA's. Other examples might be to take something like MySQL's [http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html ON DUPLICATE KEY] syntax, clear up any sanity issues with implementation, and then implement it in Postgres. (Yes, this examples are questionable, since there are SQL Standard ways of doing this stuff, but the syntax of this command sure seems more user friendly).



Of course, this whole idea overlooks the notion that the SQL Standard committee has often been used as a tool between database vendors to make life tougher for their competitors, so it might be unlikely that the committee would ever adopt the syntax from the [http://planetosdb.org/ FLOSS community] if it meant giving them some type of advantage, but this is also why FLOSS databases could give hope, since as more user oriented projects, if they worked together you should really see syntax that makes everyones lives easier. Imagine Postgres, MySQL, Firebird, SQLite, and Ingres all working together to create their own, user driven syntaxes.