Notes from an Open Source DBA

Josh Berkus just announced the release of PostgreSQL 8.4. The summer issue of Open Source Database Magazine (out July 15th) will include a story on the new features of PostgreSQL 8.4.  Go forth and download!! Below is the text of Josh’s email giving an outline of the new features:

————————————

The PostgreSQL Global Development Group has released version 8.4, continuing the rapid development of the world’s most advanced open source database. This release contains an abundance of enhancements to make administering, querying, and programming of PostgreSQL databases easier than ever before. Our development team has spent 16 months adding over two hundred improvements to all aspects of database functionality, helping every PostgreSQL user in small or large ways.

Many of the changes in PostgreSQL 8.4 are new or improved administration and monitoring tools and commands. Each user has their own favorite features which will make day-to-day work with PostgreSQL easier and more productive for them. Among the most popular enhancements are:

* Parallel Database Restore
speeding up recovery from backup up to 8 times
* Per-Column Permissions
allowing more granular control of sensitive data
* Per-database Collation Support
making PostgreSQL more useful in multi-lingual environments
* In-place Upgrades through pg_migrator (beta)
enabling upgrades from 8.3 to 8.4 without extensive downtime
* New Query Monitoring Tools
giving administrators more insight into query activity
* Greatly Reduced VACUUM Overhead
through the Visibility Map
* New Monitoring Tools
for current queries, query load and deadlocks

Version 8.4 also makes data analysis easier through the advanced ANSI SQL2003 features of windowing functions, common table expressions and recursive queries. Enhancements to stored procedures, such as default parameters and variadic parameters, make database server programming simpler and more compact. Of course, there are also performance improvements included in this version.

Hello everyone,

I began MySQL Magazine in the early summer of 2007. It was began, not as a lark, but without any idea that it would ever become so popular. It was just a way I thought I could contribute to the MySQL community. Over time it has grown very well with recent issues being downloaded around 10,000 times directly from the website. I have no way of counting other downloads although I know issues are posted in multiple other locations.

I have been debating for about six months on changing things up a bit. As I said in my last post, change for the sake of change is almost never good. This isn’t change just for change’s sake.   I am convinced that enlarging the old MySQL Magazine to include any open source db will be beneficial to everyone. More content and an exchange of knowledge across “cultures” so to speak.  The last several years have seen the rise of various databases that fit the needs of cloud and large-scale computing such as Hadoop. Even more recently, the Drizzle project has gained quite a bit of steam. PostgreSQL has been around since roughly the time period of the cooling of the earth’s crust and has a devoted fan base (and a great product in the appropriate place I might add!!). The list could continue but I would like to keep this blog post a reasonable length.

I plan on writing about both Drizzle and PostgreSQL in addition to my normal MySQL fare. While my knowledge of  either of those systems is minimal at best, they are areas of interest to me and there is always a need for articles targeted at noobs like me who need introductory material. I can at least supply that!

I would point out that exchanging knowledge, as I termed it earlier,  is not mudslinging. I invite comparisons between systems (where appropriate) but if a comparison is not backed up by performance data or supportable by facts it will not be published. Not looking for flame wars, I am looking for a place where developers and administrators can go to learn more about all open source databases. Hope that makes sense!

Articles for the new magazine can be about any of these previously mentioned and any other database that operates under an open source license. MySQL Magazine had been running roughly 20 - 25 pages in size over the last three or four issues of the magazine. I would like to increase the size to around 30-35 pages if possible. I always encourage articles about the administration side of the server as well as the coding side of the server.

I will be using Scribus to build the magazine. It allows decent control over layout, reasonable pdf output and is a fine open source project in its own right. Submissions can be pdf, Open Office or MS Office. As with MySQL Magazine, as long as I have advertisers, if article authors have access to a paypal account I pay $50 an article (plus all the fame you can handle ).

So, if you administer or develop for an open source database, now is the time to step up. I will be publishing the first issue on July the 15th which means all finalized versions of articles must submitted by July the 7th.

The new magazine will reside @ http://www.osdbzine.net.  I just registered the domain so it will take some time for it to propagate through the ‘net. Right now there is only a placeholder for the site.  I will build up some content between now and July the 15th.

Have an idea for an article? Email me at bmurphy AT paragon-cs.net. No need to send a full-length article. Just a brief 50 - 100 word outline of what you are proposing. And don’t be scared. If I recall correctly, I have never turned anyone down. Looking forward to hearing from everyone.

After hearing rumblings that a new release model was coming and then announcements that a new model was coming, I took the time  to watch the MySQL University session on it Thursday. I have thought for a long time that previous method of release management was less than perfect for MySQL Server resulting in long time periods between “GA” releases.

Continue reading →

Dear Larry,

I know that you have no idea who I am. That’s OK. Even so, I would like to request that you please place the ZFS file system under the GPL license. In case you don’t know the ZFS file system is a nice, shiny file system that is part of the Solaris operating system that you acquired along with the rest of Sun’s assets last month.

ZFS has some really nice built in features for a file system. This includes my favorite feature - the ability to take a snapshot of a file system partition that is in use. While this can be done under Linux through the use of  LVM, it’s a bit of a hack (although a hack I appreciate LVM developers!!). Having it all incorporated into the file system would be really nifty.

Currently ZFS is licensed under CDDL.   The problem is that this license isn’t compatible with the more typical GPL license. Oh, and there is 56 patents relating to ZFS. I don’t want to turn this into a discussion of patents, but unless the code is released under GPL there is no way it can be incorporated into Linux. Can I be honest? All this license talk gives me a headache. Let’s break this down so I can understand it. Do you know how many systems out there run Linux, Larry? If ZFS became the de facto file system for Solaris, OS X and Linux you would rule the world! Just think about all those machines out there running your code. You would be bigger than Bill. Just a thought

I am sure there are some really smart people at Oracle who could figure out how accomplish this in no time. Just send out the proper email and it could be done in a week I bet. One little act and you would build a huge pile of credibility in a snap. And yes, “huge pile” is a technical term for “lots”.

While I certainly can’t speak for Oracle database administrators, I am willing to bet that those who run Oracle on Linux would love this also. It’s not just us MySQL people who could use ZFS.

All of us who use Linux on a daily basis would really appreciate it. Just one less thing to worry about.

Looking forward to hearing from you.

Sincerely,

Keith

“Just a MySQL DBA out here in the trenches”

The following was in the just released monthly bug report for the Falcon storage engine:

“With the news that Sun has aggreed to be purchaced by Oracle, Some inevitable changes will occur. Once the acquisition is made, the need for Falcon as a MySQL storage engine will be re-evaluated. Until then, Falcon will continue to improve stability and performance. The team will also evaluate other technical niches that may be unique to Falcon.”

I for one would be very disappointed to see Falcon not supported by Oracle. I know they have worked very hard to create a next-generation storage engine.  While it could be argued that InnoDB can fill all use cases, I believe that choices are a good thing and having one less choice is not a good thing.

Good luck all on the team. You have been nothing but kind and generous when answering my dumb questions via email and in person. You can count my vote for “keep it!!”.

Not to rehash an old argument, but I am watching a recent  video on performance work being done by Sun hosted by Allan Packer (a recorded MySQL University session) and one of his bullet points was “Scale out when it makes sense, rather than just because there is no alternative”. That is what they are working on in his group at Sun.

You mean I will be able to buy 8 and 16 cores boxes and be able to use all the cores?

Hallelujah and Amen brother

Allan, thanks to you and all other who are working on this both inside and outside Sun.

This isn’t exactly MySQL stuff.. but it relates in the sense that I am looking to move from our current workflow setup for MySQL Magazine to something that is (in order of importance):

• Able to generate pdf output along with attendant hyperlinks,etc.
• Able to effectively create a professional document with input from a number of authors
• Open Source if it meets the above criteria

I have been more than a little unhappy with previous setups (we tried openoffice and now currently Word to Adobe distiller). After the six freaking hours I wasted on the last go around because columns where right justified and looked perfect in Word, but when distilled in pdf the output came out with random spacing (including INSIDE WORDS), I am ready to chunk this approach and try something new.

One of the authors kindly offered to work with me on getting a latex setup. While I am grateful for the offer,  I am not sure about going that route. I know that it could do the work, but I am not really comfortable working in an a non- wysiwyg environment for these type of tasks.

In my research for alternatives to our current setup I came across Scribus. It has been around for quite some time, but I haven’t ever actually worked with before. Over the last few hours I setup Scribus and put together one of the articles from the last issue and ended up with a document that, on screen, looks almost identical to the published copy from a couple of days ago. This is the first time I used to the program so I spent a significant amount of time looking up how to do things. Overall it made sense and seems to do the job. I will have to wait until tomorrow to check the pdf output, but it is promising. Plus, authors can send in OpenOffice files and they can be imported without much trouble.

Anyone else use Scribus for anything significant? Your thoughts?

Other than Latex and Scribus is there anything else that would work as a opensource tool for this job?  I am not sure docbook would even work with multi-column text, but it is going be fairly cumbersome to work with.

I have used Adobe Framemaker and while it would do the job is frightfully expensive and of course not open source.

Anyone have input on Scribus or other programs I might not have thought of?

I just finished updating my website with new information about a training class I will be teaching in the Metro Atlanta area next month.  This five-day class will begin on March the 30th and will cover everything that a beginner to intermediate-level database administrator needs to know to work effectively with MySQL Server.

Every student will receive a free laptop for use during the class. When the class is done, the student gets to take the laptop home!

The curriculum is available at http://www.paragon-cs.com/training

I limit the class size to twelve students in order to provide individual attention to each student so spacing is definitely limited.  In addition, if you sign up before the end of day on March the 2nd there is a $200 discount.

On February the 18th I have the privilege of participating in a seminar that Sun is putting on about one of the MySQL 5.1 case study finalist. I will kick things off covering the new features of 5.1.  Chris Lavigne, from Breadboard BI,   will be discussing how his company put together a business intelligence solution for the Boats.com and YachtWorld.com web properties. The project was developed using a 100% open source stack that leveraged Linux, Pentaho and MySQL, specifically using 5.1’s new table partitioning feature. Hear Chris explain how he helped his client unlock valuable information from their large and disparate data sources so that far more informed sales, production and marketing decisions could be made.

Join us! Registration is available here.

This is one of the largest issues so far.  In addition, it has some of the best content we have ever had. The articles are all in-depth with some exciting new information:

• Introduction to XtraDB: an overview of the new XtraDB storage engine along with benchmarks and information about planned future improvements
• Changes in the MySQL DBA and Developer Exams for Version 5.1: what’s coming in the new exams covering MySQL Server 5.1
• Covering Indexes in MySQL: how to create indexes that optimize query execution
• PBXT’s Coder’s Guide: going in-depth on how you can work with the code for the new PBXT storage engine
• Coding Corner: Peter’s regular column continues his look at transaction time validity

It is available for download, along with all previous back issues, at http://www.mysqlzine.net.