Jignesh Shah's Weblog: ISV Engineering: OpenSource Databases on Solaris

Recently Felix Malinkevich joined the ISV-E Open Source Team as an Intern working on PostgreSQL along with us. On his first project he takes on pgbench with PostgerSQL 8.3.3 on a drive on OpenSolaris and Ubuntu using VirtualBox on his system. Read about his experience with wal_buffers in PostgreSQL 8.3.3 .

Resources on  PostgreSQL here just went up.

Netbeans 6.1 was released recently which includes PostgreSQL JDBC driver. It had been a while since I had done programming as a developer, so I decided to take it for a test drive to refresh my programming skills with it. Well here is the resulting demo application:

Remember it is just a demonstration application. It requires your client (laptop or desktop ip address) to be trusted in  pg_hba.conf  ip adddress in order to work with your database.

Slides for today's presentation at PGCon 2008

Title:  Problems with PostgreSQL on Multi-core Systems with Multi-Terabyte Data

It is that time of the year when many PostgreSQL fans gather in Ottawa, Canada for PGCon 2008 next week. This will be my first visit to PGCon in Ottawa. Earlier this year I had presented two sessions "PostgreSQL and Benchmarks" and "Best Practices of PostgreSQL on Solaris" at PostgreSQL Conference 2008 East in Maryland. Thanks to that visit, this time I might recognize many people by face this time around.

Sun is a Gold Sponsor at PGCon 2008. There will be quite a bit of presence from Sun in PGCon. Josh, Max, Robert, Magne, Zdenek, Jim, Mayuresh et all will be present out there.

Josh Berkus is doing a tutorial on  "GUCs: A Three Hour Tour" on Tuesday morning 9:00 am.

Susanne Ebrecht (from MySQL team @ Sun) will also be presenting "What PostgreSQL could learn from MySQL" on Thursday 1:30pm.

 I  will be presenting "Problems with PostgreSQL on multi-core Systems with multi-terabyte Data" at Thursday 3:00pm.  I am actually presenting on behalf of the PostgreSQL Performance Team @ Sun and based on various performance work done by the group.

Robert Lor is also doing a  5-min Lightning Talk on New DTrace Probes proposed for PostgreSQL 8.4. Not to steal his thunder but  the demo will be quite interesting. It is part of Lightning Talks on Thursday 5:30pm

Zdenek Kotala will be presenting "PostgreSQL Upgrade Project" on Friday 3:00pm . In-place Upgrade in an eagerly awaited project.

Personally I will be meeting many of the Sun folks also for the first time in person. Generally we talk on conference calls, emails, etc.  Definitely looking forward to that.



 

I briefly mentioned in my earlier post that OpenSolaris binary is probably just a chapter of a book. To really understand how the benefits of OpenSolaris lets look at the needs of a typical Software Application

A Software Application at a very base level of being a usage product needs the following:

1. Operating System: First and foremost which generally nobody wants to rewrite unless there is something missing that you really need. Generally it is part of "Systems requirement" which defines the operating systems supported.
2. Method of distributing the software: Either via CD, DVD or via internet downloads
3. Installer: Installing the software on the target machine
4. Patching/Upgrading: Upgrading the software when new versions are released (specially with security updates)

So how does OpenSolaris solve this problems?

Lets start with (1). For the first time that I am aware, this is first Any Solaris release which makes it so friendly to even bundle an Operating System with a software product that the customers of Software application need not be even concerned about if the server (virtual for that matter) is going to do only the task of running this software application. I may not be far from truth when I mention that since I am aware of many people who are using Virtual Machine which is only doing one tasks. Suddenly the number of operating systems increases drastically and multiplied with number of operating systems available, I forsee more and more people bundling the operating system as part of their software application. In such sense OpenSolaris is already ahead of the curve providing tools to make the whole bundling experience easy with open source projects which includes Distribution Constructor and the new Installer project. Infact the New Installer project even makes it easy to not only install OpenSolaris but also your own application with it  and since it is all open source it can all be modified to have your own branding image out there giving a sense of control to the "key task" product to be highlighted during installation. (Maybe call it PostgreSQL OS). Looks like I am also covering (3).

Now to cover (2) and (4) the repository with pkg(5) command is ready to cover those responsibilities. The thing to realize is that there can be more than one repositories. For instance http://pkg.opensolaris.org is a repository which has all the packages for OpenSolaris itself, but an ISV or OpenSource communities can create their own repositories for their Software application. Now suddenly having a software application which is easy to distribute, install, update is now so easy to create and maintain with this ecosystem, its hard to imagine why this was not done years ago. 

This new eco-system makes it easy to cater to multiple type of Software applications

1. Controller Software application where all installations need to have identical stack. (Creating custom distribution helps achieve that)

2. Latest and Greatest Software application model (using pkg makes it very easy)

3. Pick and Choose model : As repositories increase in number there will be choices where to get packages and pre-built binaries for your needs.

Ofcourse I am probably skipping many other requirements of an ISV since every ISV is different but if dissected probably the new ecosystem can probably fill most (if not all) of the needs in this new world of Solaris.

OpenSolaris OS 2008.05 is now available for download.  Its quite a departure from traditional Solaris. Why? because the CD that one will be downloading is probably just a chapter in the full book. To understand the full picture one will have to really look at the bigger picture on how the whole deployment model is now changing. The full scope is not my topic of discussion but I probably want to focus on probably couple of pieces today in order to demonstrate on how to create a PostgreSQL OpenSolaris LiveCD.

In order to create a custom LiveCD, its probably best to start with OpenSolaris OS 2008.05 installation though it is not necessary as it can be done on Solaris Express installations also but needs pkg(5) to be installed. But the two things really required to create a custom LiveCD, you need one kit to download and a repository to access. The kit to download is OpenSolaris's Distribution Constructor project. Its quite easy to clone the scripts in the project using mercurial "hg" command. Installing the SUNWmercurial package is as easy as typing "pkg install SUNWmercurial" (provided networking is working and internet connection is available).  The good thing is if your "pkg" command does successfully install SUNWmercurial it means the second requirement of accessing a repository is already fulfilled.  Coming back to Distribution Constructor the project can be cloned as follows

hg clone ssh://anon at hg dot opensolaris dot org/hg/caiman/distro_constructor

This creates a distro_constructor directory in the current working directory which includes all the scripts and templates to create a distribution.  The best way to proceed is to copy test_data directory  and modify the settings in it after reading through the README file on the project source repository.

You will soon realize that pkgs.txt is what one really needs to modify to select what packages to be added as part of one's custom LiveCD. What I realized later is that its easier to add packages than actually remove packages. Since while pkgs.txt  doesnt handle dependencies (in the sense person reading the file cannot figure it out), the actual kit does resolve dependencies. (Though I wouldn't try random packages only since there are some utilities required and hence hard to get it right in first few tries if one is trying to do a minimized LiveCD.) Right now GNOME et all is required since the installer is dependent on it and hence its hard to cut down the size of the LiveCD to a size less than 600MB if one wants an installer from the LiveCD to the hard disk to work. However the good news is there is some space still available to fit PostgreSQL in and still burn a CD that will work. Anyway coming back to the pkgs.txt of the test_data which needs to be edited its surprising to see such a small list. It basically containts slim_install, SUNWslim-utils and entire. There is hardly any stuff that can be removed in it.  However the modification I did was to add PostgreSQL 8.2 packages (SUNWpostgr-82*)  along with pgAdminIII (SUNWpgadmin3) packages which are available on http://pkg.opensolaris.org and as mentioned in README file executed the script file build_dist.bash /pathto/test1.conf. If the setup is right and access to http://pkg.opensolaris.org is fast then maybe in couple of hours (or three) you will get your own OpenSolaris 2008.05 LiveCD including PostgreSQL and PgAdminIII with a size of about 640MB.

Lets start at the point where you have just installed OpenSolaris OS 2008.05 and have logged in using your primary userid on the system.

First thing to do is install the packages for PostgreSQL and MySQL on OpenSolaris OS 2008.05. Right click on the desktop and select "Open Terminal" to start a terminal session. Use "su" to assume the root userid. (The primary user already has root role however some programs still explicitly check for userid of root and hence needed to avoid unexpected surprises.)

Verify pkg is able to communicate with the IPS repository.

This confirms that the repository is accessible. If this does not work confirm you have internet connection and/or try:

#  svcadm restart nwam

which currently takes some time to bring a small popup saying the interface is plumbed up and has an IP address assigned.  Anyway lets assume that the internet connects well and we are ready to install the Packages of PostgreSQL and MySQL

To install PostgreSQL binaries (currently PostgreSQL 8.2.6 32-bit  is available):

Now to install PgAdminIII

Now to install MySQL packages:

If you also want to install webmin:

With this we are now setup with the most common packages required to use Open Source Databases PostgreSQL and MySQL on OpenSolaris 2008.05

Tolven Inc an Open Source Healthcare Solutions provider recently carried out a benchmark using PostgreSQL on Solaris 10 using ZFS on Sun Fire X4600.  The Benchmark report is now available on their website.

The size of the  single database instance using PostgreSQL 8.2.6 (32-bit) peaked at 474GB with the largest table having more than 500 million rows. More information is in the report.

Tolven's  setup guide for developers which includes information about PostgreSQL  setup is also available on their website.

As Sun SPARC Enterprise T5140 is launched along with the Open Application Services solution which includes PostgreSQL, the talk I gave at PostgreSQL East 2008 on "Best Practices with PostgreSQL on Solaris" might be helpful for many new users.

If there are questions let me know.

I started working on my upcoming talk at PGCon 2008 and realized that I haven't put my talk from PostgreSQL Conference East 2008  - PostgreSQL and Benchmarks online yet.

 More on the upcoming presentation later.