Tuesday, July 25, 2006

Sun Fire X4500 with PostgreSQL or Bizgres MPP

The new Sun Fire X4500 (codename: Project Thumper) has Solaris 10 6/06 as the default Operating System. Solaris 10 6/06 apart from ZFS also has PostgreSQL bundled in the release for the first time (well in Solaris 10).

We have already talked about using DB2 on a single X4500 but lets talk now about using using nothing else but the Solaris 10 distribution on X4500.

Note the following is not something that I have tried out on Sun Fire X4500 but I would like to try it out soon.

PostgreSQL unlike many commercial database still doesn't support multiple simultaneous file systems or files or devices for the same tablespace. The philosophy is simple... If that's the job of the OS, let the OS handle it..
Adhering to the same design philosophy, we will also try to be simple with this approach since now that ZFS is available it is not much of a problem now.

So that said if I have a RAID10 pool of 22+22 disks and I have one postgresql instance created on it, then I have to drive all the disks using the one core that PostgreSQL will drive.. Not efficient since the Opteron core could end up doing all the 1.1GB/sec io and getting no additional work done or (what will really happen) do bits and pieces of work and then wait for IO and do bits and pieces and iostat will show an average IO which will be no where near the potential for the system. Unless of course you have atleast four such separate queries running at the same time which then becomes very database schema dependent whether it scales or not.
Also if it is designed to actually store lots of mostly-read documents which is then served back to an online application purposes for viewing then it suddenly becomes the perfect low-cost platform because the cost of storing such immense read only data will be too steep on other platforms.( I think we could possibly store the whole google map database on one X4500? And if number of users using it increases just buy another X4500 and replicate the setup and put it in the webserver pool)

That said the default PostgreSQL instance as it is though useful for OLTP Type applications or delivery of static contents, it probably may not enough to fully utilize the system all by itself for BIDW type of workload where processing data is also important and the ability to use multiple processing units is required to speed up the processing.

Solution: Use Multiple PostgreSQL instances

Again you donot need anything else except Solaris 10 6/06. In such case using four zones each with its own PostgreSQL instance running and having four such pools would drive up the utilization of the system and get better value for your money. (And yes this is possible with ZFS and Solaris Zones). ZFS with 4 pool each with RAID 10 (10 + 10) with few hot spares will help out there.

Of course people always question about Support.. Well Support plans for both Solaris support and PostgreSQL support are in place for it.

This configuration can also allows it to be used for combination of OLTP Type transactions or small BIDW type transactions with enough scratch area to repetitively process the data as per the analysts all isolated in separate zones. Also replicating data between the pools for "Testing purposes" will also be easier with ZFS snapshot and ZFS send command options. (Suddenly the sky opens for multiple uses of the X4500)

For folks who still want to try it out for Data Warehousing with a single database, all hope is not lost. Greenplum's Bizgres MPP is something that might then appeal to you. Bizgres MPP (based on PostgreSQL) uses distributed multiple PostgreSQL based instances to distribute the load and drive up the utilization of the system at the same time (in a theorectical world) reduce the time it takes to process to 1/n th of the time it takes for a single instance to process the same amount of data.

Infact as per GreenPlum's website, GreenPlum has partnered with Sun for such a DW Appliance based on Sun Fire X4500 .

UPDATE: Its finally official. The DW Appliance based on X4500 is finally here.

No comments: