Jignesh Shah's Weblog: ISV Engineering: OpenSource Databases in the New Sun Microsystems

It is the start of the last quarter of 2008 and we engineers in ISV Engineering are still baffled when we hear from new Startups when they say "Oh we are startups, I don't think we can afford Sun Microsystems" or sometimes even from old partners. It's like  Flashback to 1999! Yes we were selling $4million servers based only on SPARC in 1999. However in 2008 we are selling many servers for much less than $1000. (HINT: Join Sun Startup Essentials) In fact now we sell systems based on various CPU architectures like  AMD Opteron, Intel Xeon, UltraSPARC T2, SPARC64 VII. We also have one of the biggest OpenSource offerings right from MySQL, Java, OpenOffice.org, OpenSolaris, Glassfish, Netbeans, etc. (I for one cannot even remember all our OpenSource projects). So what we figured that essentially it is hard  for non-Sun folks to distinguish  the current Sun from the Sun of 1999. Essentially our biggest competition in 2008 is the lingering image of Sun from 1999 as seller of expensive Sun SPARC systems.

It is hard to push that message out when you don't have advertising budget. But we have to get the message out.  So what am I going to do about it?

Just to show the fact that we have changed from last decade, next time when I have to introduce myself,   I am going to say:

"I am Jignesh and I work for The New Sun Microsystems"

to highlight the fact and push the message out. I am changing the introduction in my blog and my email signature to reflect the same. I am hoping people will  read or hear the "New" moniker and ask their favorite Sun contacts "What's new?" and that's the exactly the question that I want those potential customers to ask us.

Recently my boot disk of my workstation which is running OpenSolaris 2008.05 started giving me media errors. Just for the record the errors looked like this:

 WARNING: /pci@5,0/pci1022,7450@4/pci108e,534d@4,1/sd@0,0 (sd2):
     Error for Command: read(10)                Error Level: Retryable
       Requested Block: 4213122                   Error Block: 4213126
       Vendor: FUJITSU                            Serial Number: AAG0P5300991
       Sense Key: Media_Error
       ASC: 0x11 (read retries exhausted), ASCQ: 0x1, FRU: 0x0
 WARNING: /pci@5,0/pci1022,7450@4/pci108e,534d@4,1/sd@0,0 (sd2):
    Error for Command: read(10)                Error Level: Retryable
       Requested Block: 4213122                   Error Block: 4213126
       Vendor: FUJITSU                            Serial Number: AAG0P5300991
       Sense Key: Media_Error
       ASC: 0x11 (read retries exhausted), ASCQ: 0x1, FRU: 0x0

I did not want to wait till the whole disk died so I started thinking about replacing the disk. I talked to our local lab manager to get a replacement disk and within couple of day he came back with a replacement disk. So with a replacement disk in my hand and the boot disk which is my primary drive in the system I went on a strategy to figure out how to change the disk without really re-installing the system.

First of all I am using an EOL product called Sun Java Workstation W2100z. Fortunately it has extra disk slots in it. Halting OpenSolaris 2008.05 I opened the system without a screw driver (it has twist screws) and I inserted my new disk into one of the available disk slot  and it already had ribbon cables and power supply for it. Booted up OpenSolaris and I could see the disks. Great now what.

My primary plan was to clone the disk exactly to be similar to my boot disk so here are my steps to make it look like it

 # format
Searching for disks...done


AVAILABLE DISK SELECTIONS:
       0. c5t0d0 <DEFAULT cyl 17879 alt 2 hd 255 sec 63>
          /pci@5,0/pci1022,7450@4/pci108e,534d@4/sd@0,0
       1. c6t0d0 <DEFAULT cyl 17846 alt 2 hd 255 sec 63>
          /pci@5,0/pci1022,7450@4/pci108e,534d@4,1/sd@0,0
Specify disk (enter its number): 0
selecting c5t0d0
[disk formatted]
/dev/dsk/c5t0d0s0 is part of active ZFS pool rpool. Please see zpool(1M).
/dev/dsk/c5t0d0s1 is currently used by swap. Please see swap(1M).
format> par
partition> name
Enter table name (remember quotes): "myroot"
partition> pr
Current partition table (myroot):
Total disk cylinders available: 17879 + 2 (reserved cylinders)

Part      Tag    Flag     Cylinders         Size            Blocks
  0       root    wm     262 - 17878      134.95GB    (17617/0/0) 283017105
  1       swap    wu       1 -   261        2.00GB    (261/0/0)     4192965
  2     backup    wu       0 - 17878      136.96GB    (17879/0/0) 287226135
  3 unassigned    wm       0                0         (0/0/0)             0
  4 unassigned    wm       0                0         (0/0/0)             0
  5 unassigned    wm       0                0         (0/0/0)             0
  6 unassigned    wm       0                0         (0/0/0)             0
  7 unassigned    wm       0                0         (0/0/0)             0
  8       boot    wu       0 -     0        7.84MB    (1/0/0)         16065
  9 unassigned    wm       0                0         (0/0/0)             0

partition> quit
format> disk
AVAILABLE DISK SELECTIONS:
       0. c5t0d0 <DEFAULT cyl 17879 alt 2 hd 255 sec 63>
          /pci@5,0/pci1022,7450@4/pci108e,534d@4/sd@0,0
       1. c6t0d0 <DEFAULT cyl 17846 alt 2 hd 255 sec 63>
          /pci@5,0/pci1022,7450@4/pci108e,534d@4,1/sd@0,0
Specify disk (enter its number)[1]: 1
selecting c6t0d0
[disk formatted]
format> ver
WARNING - This disk may be in use by an application that has
      modified the fdisk table. Ensure that this disk is
      not currently in use before proceeding to use fdisk.
format> fdisk
No fdisk table exists. The default partition for the disk is:

  a 100% "SOLARIS System" partition

Type "y" to accept the default partition,  otherwise type "n" to edit the
 partition table.
y
format> par
partition> pr
Current partition table (original):
Total disk cylinders available: 17846 + 2 (reserved cylinders)

Part      Tag    Flag     Cylinders         Size            Blocks
  0 unassigned    wm       0                0         (0/0/0)             0
  1 unassigned    wm       0                0         (0/0/0)             0
  2     backup    wu       0 - 17845      136.71GB    (17846/0/0) 286695990
  3 unassigned    wm       0                0         (0/0/0)             0
  4 unassigned    wm       0                0         (0/0/0)             0
  5 unassigned    wm       0                0         (0/0/0)             0
  6 unassigned    wm       0                0         (0/0/0)             0
  7 unassigned    wm       0                0         (0/0/0)             0
  8       boot    wu       0 -     0        7.84MB    (1/0/0)         16065
  9 unassigned    wm       0                0         (0/0/0)             0

partition>

Unfortunately since somehow my cylinder counts were different I could not use select to choose the layout of "myroot" and hence had to set the layout manually to be similar to my boot disk.

partition> 0
Part      Tag    Flag     Cylinders         Size            Blocks
  0 unassigned    wm       0                0         (0/0/0)             0

Enter partition id tag[unassigned]:
Enter partition permission flags[wm]:
Enter new starting cyl[0]: 262
Enter partition size[0b, 0c, 262e, 0.00mb, 0.00gb]: 17845e
partition> 1
Part      Tag    Flag     Cylinders         Size            Blocks
  1 unassigned    wm       0                0         (0/0/0)             0

Enter partition id tag[unassigned]:
Enter partition permission flags[wm]:
Enter new starting cyl[0]: 1
Enter partition size[0b, 0c, 1e, 0.00mb, 0.00gb]: 261e
partition> pr
Current partition table (unnamed):
Total disk cylinders available: 17846 + 2 (reserved cylinders)

Part      Tag    Flag     Cylinders         Size            Blocks
  0 unassigned    wm     262 - 17845      134.70GB    (17584/0/0) 282486960
  1 unassigned    wm       1 -   261        2.00GB    (261/0/0)     4192965
  2     backup    wu       0 - 17845      136.71GB    (17846/0/0) 286695990
  3 unassigned    wm       0                0         (0/0/0)             0
  4 unassigned    wm       0                0         (0/0/0)             0
  5 unassigned    wm       0                0         (0/0/0)             0
  6 unassigned    wm       0                0         (0/0/0)             0
  7 unassigned    wm       0                0         (0/0/0)             0
  8       boot    wu       0 -     0        7.84MB    (1/0/0)         16065
  9 unassigned    wm       0                0         (0/0/0)             0

partition> lab
Ready to label disk, continue? y

partition>

Now my disk layout looks like my boot disk image. Now I need to tell ZFS to move to the new disk. I wasn't comfortable doing all this in GUI so I reverted to the character mode by issuing the following command

# svcadm disable gdm

 In the character login mode,  I used the following step to move the ZFS rpool to the new disk

# zpool replace rpool c5t0d0s0 c6t0d0s0

(Sometimes you may have to use force flag if you have some some data already on c6t0d0s0

# zpool replace -f rpool c5t0d0s0 c6t0d0s0

The command did look like it returned quite quickly but it then I found it spawns a background task to start moving the pool to the new system.  I observed it by using

# zpool iostat -v

to see the IOs happening from the old disk to the new

and also

# zpool status -v

Once it successfully completed replacing the disk which could take a while, then I  made sure the swap location is also moved to the new disk in /etc/vfstab

# vi /etc/vfstab

(replace c5t0d0s1 to c6t0d0s1)

Now to make sure Grub is also available on the new disk and it knows where the new ZFS Root drive will be located:

# cd /boot/grub

# installgrub stage1 stage2 /dev/rdsk/c6t0d0s0

Final step before rebooting the operating system, I made sure the mini-root is updated with the new information

# bootadm update-archive

 And now I rebooted the system. Here is where there is a bit of timing issue since I need to press F2 to get into the boot menu and select the other disk to boot from in the CMOS menu. Booting into the other hard disk and seeing a working grub menu gave me a positive feeling that things are going right.  Selecting the OpenSolaris installation started booting Solaris again. Since I had disabled the GUI login, it took me to the login prompt. Login using my primary id and  then "su" into root shell

I checked using zpool status -v and "swap -l" to verify that rpool and swap are pointing to the new disk. Once I was satisfied, I  enabled GUI login as follows:

# svcadm enable gdm; exit

( Note as soon as you enable GUI via gdm it starts X windows and leave your shell hanging logged in. Hence I had  the extra ";exit" to  immediately exit after starting the GUI. )

Then I took the system down again to remove my dying disk from it and sent it back to my lab manager.

Nobody like to see their portfolios being devalued as fast as it has been in these last few months. While I do not work for a financial company, I do work for a company which does consider financial sector as one of the markets that it caters. However that's not the point of my blog entry.

 My point is at this time many financial companies are trying to figure out the true value of many securities and assets that it owns. Also at the same time they are trying to offload assets to generate cash to be viable. Well most companies are selling off assets to gain cash in returns. IT  Departments will also impacted or "told" to re-evaluate their Asset Value to figure their worth.

So what are the assets that you put value in the IT Department in times like this? I put the value as the best price that I can sell it to a buyer in current condition. (After all there are bargain hunters in this economy who are putting in billions of dollars in deals right now)

Anyway things assets that IT Departments typically have are as follows

• Skills (in terms of People)
• Shelves (in terms of hardware racks with server, switches,etc)
• Software CD & Licenses

Fortunately Skills and Shelves (Hardware) have a re-sale value and depending on how much you have you can still a generate cash from it.  However I am not sure on how the IT put value on the Software CDs & Licenses that it owns. Does it have a resale value? After all you did pay millions for these  licenses in the first place? It better have some resale value.

I hope the financial institutions are already re-evaluating their strategies on investing something that has a resale value instead of Proprietary Expensive Software which cannot be resold in times like this to stay in business.

Recently I am been testing few pieces of Storage projects of OpenSolaris with PostgreSQL. One of tests involves using an iSCSI disk  with PostgreSQL.  Unfortunately the storage that's available is  in Colorado  while my PostgreSQL server is  located in Massachusetts. Latency will definitely be one of my top problems since storage  is halfway across the country (in Colorado). Plus the fact that I will be running a database server on my end  doesn't really sound like a good idea. Come to think about it, this could be a more common problem nowadays since Cloud Storage (for example Amazon S3 Webservice ) could be  optimistically  half way across the country and pessimistically be on the other side of the world.

 So what are my options to solve such problems?  ZFS in OpenSolaris 2008.05 has many new features, couple of which can potentially help with my problem. 

• ZFS Separate Intent Log: Ability to separate out the ZFS Intent Log (or log writes in simple terms)
  
• ZFS L2 ARC: Ability to use a Level 2 Adaptive Replacement Cache which can be block device (or cache reads on device in simple terms).

Thats an interesting set of new features that I thought will be useful in my case. One to log writes separately which can be on a fast disk and another to use a fast disk for caching reads. Of course I am not the first to say on this topic since these new features have been discussed in length a lot specially with SSDs. But I plan to  solve the problem of latency of my Cloud Storage with these new ZFS features and some local disks partitions that I have in my system.

Many people do the analogy that compared to a regular 7,200 rpm SATA or 10,000 rpm SATA/SAS or 15,000 SAS drives, the SSDS act like 40,000 rpm drives. Well extending this to Cloud Storage, I think Cloud Storage is like more like a  500 rpm to 1000 rpm drives depending on the phase of the moon and/or the stock market.

Anyway to continue with my setup, I used an iSCSI disk exported  in Colorado.   I created a regular zpool on top of it  on the server in Massachusetts  and called it "colorado" as shown below:

# zpool create colorado c9t600144F048DAAA5E0000144FA6E7AC00d0

Then I created a PostgreSQL database in /colorado/pgdata and started loading up data in it using pgbench. It was painful to do this late in the day and then  waiting for it to finish. At this point of time I also wished that pgbench had a scale factor of smaller than 1 (maybe it does I don't know). Anyway I did not have the patience to let it finish. I terminated the process after about 8 minutes as  that scenario was unacceptable.

$ time /usr/postgres/8.2/bin/pgbench -i -s 1 pgbench
creating tables...
10000 tuples done.
20000 tuples done.
30000 tuples done.
40000 tuples done.
50000 tuples done.
60000 tuples done.
^C

real    8m1.509s
user    0m0.052s
sys     0m0.011s

I destroyed that "colorado" pool .  I referred  the Solaris ZFS Administration Guide to get help with the updated syntax of these new features and recreated the pool using a local disk partition for cache and another for log as follows:

 # zpool create -f colorado2 c9t600144F048DAAA5E0000144FA6E7AC00d0 log c5t0d0s0 cache c5t0d0s1

And then repeated the steps and then recreated the database on it. Then I started loading the data again with pgbench.

Boom!!!  it finished in record time:

$ time /usr/postgres/8.2/bin/pgbench -i -s 1 pgbench
creating tables...
10000 tuples done.
20000 tuples done.
30000 tuples done.
40000 tuples done.
50000 tuples done.
60000 tuples done.
70000 tuples done.
80000 tuples done.
90000 tuples done.
100000 tuples done.
set primary key...
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "branches_pkey" for table "branches"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "tellers_pkey" for table "tellers"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "accounts_pkey" for table "accounts"
vacuum...done.

real    0m4.560s
user    0m0.076s
sys     0m0.011s

Not bad. Cutting the latency of writes of something that would have taken in excess of 8-10 minutes is atleast recorded within 4 seconds on nonvolatile cache/log combination and allowing ZFS to sync it up to the actual storage. 

Now trying a quick pgbench run to make sure it executes as expected.

$ time /usr/postgres/8.2/bin/pgbench -c 1 -s 1 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 113.030111 (including connections establishing)
tps = 119.500012 (excluding connections establishing)

real    0m0.144s
user    0m0.005s
sys     0m0.008s

So using these new features of ZFS in OpenSolaris 2008.05  can helps hide the latency of these low-cost Cloud Storage and actually make them usable even as a database server.

Plus I heard ZFS is also coming out with recovery  options which will allow to recover not only with the log but also without the separate log and cache device available. If your server dies and takes your ZIL disk with it, you can go and build another server and attach to your cloud device  to regain your data even if you don't have the device to replay the ZIL.If you have your ZIL Log, you can use it to get most current version.  This is important during disaster recovery where you are willing to take whatever you have and start the business again from that point.

How to install Openbravo ERP on OpenSolaris 2008.05

Or it should read how to setup an ERP System quickly for your enterprise using PostgreSQL. 

What you need:

• A x86 or x64 based server running OpenSolaris 2008.05
• Fast access to Internet
  

Preparing OpenSolaris 2008.05 to be Openbravo ready:

Let's start from the point where you have already installed OpenSolaris 2008.05 configured to access the internet.

Requirements for Openbravo:

• JDK
• ANT
• Tomcat
• PostgreSQL 

Before executing the binary you need to install the following packages on  OpenSolaris 2008.05.

# pkg install SUNWj6dev

# pkg install SUNWant

# pkg install SUNWtcat

# pkg install SUNWpostgr-82-server

# pkg install SUNWpostgr-82-client

# pkg install SUNWpostgr-82-jdbc

# pkg install SUNWpostgr-82-server-data-root

# svccfg import /var/svc/manifest/application/database/postgresql.xml

# svcadm enable svc:/application/database/postgresql:version_82

To improve data loading speed during installation, you might want to edit /var/postgres/8.2/data/postgresql.conf and increase checkpoint_segments=16  and if you have 1GB RAM or more, increase shared_buffers = 128MB and wal_buffers=512kB. Restart PostgreSQL as follows:

# svcadm restart svc:/application/database/postgresql:version_82

Make sure that your newly installed tomcat setup has a valid server.xml file or copy it from an example file included. 

# cp /var/apache/tomcat/conf/server.xml-example /var/apache/tomcat/conf/server.xml

Installing Openbravo ERP 2.40Beta on OpenSolaris : 

Go to the download page of OpenBravo ERP

Select the version (I selected 2.40Beta) and download the installation binary of OpenBravo ERP for Solaris 10 (INTEL).

 $ pfexec ./OpenbravoERP_2.40beta-solaris-intel-installer.bin

Here is my quick cheat sheet of answers for the questions that OpenBravo installation GUI asks:

• /opt/OpenbravoERP
• /opt/OpenbravoERP/AppsOpenbravo/attachments
  
• Complete
• Standard
• /usr/jdk/latest
• /usr/bin/ant 
• /var/apache/tomcat
• PostgreSQL
• /usr/postgres/8.2/bin
  
• localhost     5432
• (Enter password for postgres user as "postgres" twice)
• openbravo    tad     (Enter password for tad user  twice)
• Context name: openbravo
• Date format: DD MM YYYY, Date Separator -, Time format 24h, Time Separator :
• Demo data: Y or N depending on your preferences

After the information the installation GUI takes quite a bit of time to complete specially if you select to load the demo data. (Hope you made changes to PostgreSQL before to tune this loading.)

Once the installation completes  start tomcat as follows

# /usr/apache/tomcat/bin/startup.sh

And then open a browser and go to

http://localhost:8080/openbravo

If you see the login screen if everything goes right !. Use Openbravo as username and openbravo (all lower case) as password to login and set it up for your business.

More help on installation is available on their wiki site. 

During the course of last year, I did have opportunities to talk to various Sun Customers about PostgreSQL. One of the thing I want to list out the common problems that I heard from them which may not be really trends but at least the one that I was "hit"  from customers while doing my job. This time I will list the ones (in no particular order) more general than performance which was my focus then.

• How do I migrate from my existing other database?
• Does PostgreSQL have features equivalent with the database to be replaced?
  
• How do I scale PostgreSQL on big systems?
  
• What's the High availability solution for PostgreSQL?
• Is there an easy horizontal scaling solution for PostgreSQL?
  
• How can XML data updates be improved in PostgreSQL?
• How can PostgreSQL be more efficient with images and videos?
• So many replication strategies for PostgreSQL which one is recommended?

I think the spectrum is both traditional application users looking to replace expensive databases and also the so called Web 2.0  companies wanting to store everything: user profile data, their images, their videos, etc within PostgreSQL and wanting to make it more efficient and easier.

Of course what you hear from customer depends on circumstances of when, how and what time you meet them so what you hear could be different than what I heard.

A new success story about a Sun Customer OurStage, Inc  has been published.

OurStage.com gives up-and-coming artists a chance to have their work seen and heard while site users pick monthly contest winners in various genres.

The Success story mentions OurStage.com uses PostgreSQL and OpenSolaris 2008.05

Ever since Sun Microsystems acquired MySQL, the most common question  that I had to answer  was "Is PostgreSQL dead at Sun?".  This question was not only raised from just PostgreSQL contributors but also other Sun Employees and  potential customers who may not even know that Sun actually sells PostgreSQL support services. 

This question will be further fueled with Josh Berkus's departure. (Josh, we will miss you.)  I admit from the period between the announcement of MySQL acquisition till recently, I too had my own doubts. But as Sun enters it new Fiscal Year FY09, and the clear directions from Management has given the right positive goals to the Sun PostgreSQL Team which will lead to Positive Growth, Self Sustaining model and help contribute to the bottom line of Sun. The very first positive outcome of the new year is the hiring of Peter Eisentraut. As Josh mentions in his blog, Peter has lot of actual customer experience specially in migrations from other databases to PostgreSQL. He will bring lot of customer experience to the table to help us focus more on customer needs.

Of course this does not mean that we don't have other challenges in contributing to the bottom line of Sun.  One of the biggest challenges that the Sun PostgreSQL team still has is perception.   Most potential customers are not even aware that Sun actually sells PostgreSQL support. I am just dazed by the comment.. "Didn't Sun buy MySQL? Do they really sell PostgreSQL Support services?" (Hey Peter, do you have a silver bullet for this problem?)

It is important for us to overcome this perception problem this year so that we spend less time marketing that "we exist" and more time on "how do we help with your PostgreSQL problems?".

 If you ask me there are many other challenges but I think they deserve their own blog entries. In the mean time .. PostgreSQL is alive and kicking well at Sun and we look forward to a very interesting year with a refined focus.

Just read the  entry on  "On the Record" blog on blogs.sun.com that Peter Eisentraut has joined Sun's PostgreSQL Team. Peter Eisentraut is a PostgreSQL Core Team member. Check more about Peter on his blog.

I had met Peter for the first time when  I had attended Peter's tutorial at PGCon 2008. Looking forward to work with him.

Peter, Welcome to Sun.

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.