Jignesh Shah's Blog

Using DVDStore with PostgreSQL

2011-12-20T17:46:00.001-05:00

We now have support for PostgreSQL in the popular DVDStore Benchmark which stresses database using an emulated DVDStore e-Commerce website. DVDStore Benchmark is maintained by Dave Jaffe (Dell) and Todd Muirhead (VMware). It is an open source database test kit. The beauty of the benchmark kit is it allows the same web application being deployed either as

Java/Tomcat and connect to the database,
Web Server/PHP and connect to the database,
IIS/ASP.NET connect to the database or
Direct connect to the database and invoking the business logic as stored procedures stored on the database itself.

Currently the PostgreSQL implementation details are as follows

Java/Tomcat using PostgreSQL JDBC driver,
Web Server/PHP using PHP-postgres modules which uses libpq
Currently there is noIIS/ASP.NET web app implementation for PostgreSQL
Direct connect to PostgreSQL database and business logic implemented in stored procedures however the driver is implemented using .NET C# and requires Npgsql 2.0.11.0

Setup instructions for the database are relatively quite easy.

Download ds21.tar.gz and also ds21_postgresql.tar.gz from http://linux.dell.com/dvdstore/
Unzip them on the system running PostgreSQL
The default data size is 10MB. If you want a different size execute 'perl Install_DVDStore.pl' in the ds2 directory. (Expects perl to be available on the system. I used the option 100, MB , PGSQL, LINUX respectively for the options.)
Assuming you are logged on as the DB Owner and the database is on the localhost at port 5432, execute the script pgsql_create_all.sh in the ds2/pgsqlds2 directory. It will create a database "ds2", two users "ds2/ds2" and "web/web", create tables, load tables, create indexes, update sequences and finally run analyze. (The script needs to be modified slighly if the database is already hardened and you want to control the creation of database and the users.)

Setup for the actual load driver is probably easiest on another Windows platform as follows as it was designed for .NET platform.

Download and install Windows SDK v6.1 and .NET 3.5 framework on a Windows Client machine.
Once installed start the CMD prompt from Programs-> Windows SDKv6.1-> CMD Prompt.
Verify the above CMD prompt has path setup for gacutil in windows (Try 'gacutil/l')
Download Npgsql 2.0.11 for msnet35 and install the dlls using the gacutil.exe (Note other versions of Npgsql may have issues.)

gacutil/i Npgsql.dll
gacutil/i Mono.security.dll
gacutil/i policy-2.0.Npgsql.dll

With the above setup you can use the ds2webdriver.exe in ds2/drivers or the direct ds2pgsqldriver.exe in ds2/pgsqlds2. More on running the benchmark driver itself in another post.

How does PostgreSQL HA works in vFabric Data Director?

2011-10-27T00:00:00.000-04:00

Databases go down due to various reasons. Some reasons are known and some unknown.
Common reasons are hardware failure, software failure, database unresponsive, etc. What is considered as a failure is actually one of the tasks. Various DBA's use a simple select statement as a test to make sure that the database is up and working. But what does one do if that simple select statement fails. I remembers years ago I worked on a module which will start paging engineers in a sequence (and eventually their managers if the engineers failed to respond back in a certain expected way). In this email/text age, scripts will start sending out emails and text messages. What we are is basically in the Event->React-> Respond mode of operation.

However true HA needs to lower downtime which can only be done by having the mode of operation as Event->Respond->React. To explain that when such an event happens, do an automated response first and then React to wake the engineers up :-)

How do you set this up in vFabric Data Director? This can be achieved by selecting the database properties, selecting the Database Configuration tab and set "High Availability" to "Enable". This is also refered as One-Click HA setting.

Of course this assumes that your virtual Data Cluster is set properly for providing the high availability services. How do you set it up properly? Well you need atleast two ESXi Hosts so if one host fails, the other can cover for it. Also vSphere HA property has been enabled in the Virtual Data Center Cluster. Note these settings are all "required" for vFabric Database setup and a "supported" setup does mandate atleast two ESXi Hosts in order for HA to work.

Now that we have gone over the setup requirements, lets go over the scenarios on how the application or user sees it. A user is connected to the database using the connection string. Something happens and the database goes down and the connection drops. Chances are if you reconnect again immediately it may fail. However with certain time which is expected to be less than 5 minutes (which we call our Recovery Time Objective or RTO) by default, if you try again you can connect to the database again.

So what happens in the background? Well if it was Magic, we would not tell you. But it is not really magic though it feels like that. Here is what will typically happen in the background.
For some reason the PostgreSQL fails to respond anymore it could be a "hung" situation or the PostgreSQL server has died. There is a small heartbeat monitor which figures out the status of the database. If it notices that the hung situation or no DB server process, it will try to restart the database. If the database cannot be restarted (because the whole VM appliance cannot respond anymore), it will in novice terms kill the virtual machine. The vCenter Server which has its own heartbeat on the VM appliance will see that the Virtual Machine has died (irrespective of the Database Monitor which may not be working if the whole host dies), the vCenter Server will restart the VM appliance on another server.

Since shared-storage is a requirement, the VM appliance will start on another host and it will feel like a reboot. Once the VM starts, the PostgreSQL server process will be restarted. At this point of time, the PostgreSQL server goes into recovery mode. The biggest question at this point of time typically is how long will the recovery mode take. Typically based on internal tests even with the heaviest workload on 8vCPU, the recovery time can finish within the checkpoint_timeout settings which means our Recovery Time Objective is guided by checkpoint_timeout + heartbeat latency + the time to restart the VM on another hosts. Overall we try to fit that into our Recovery Time Objective of 5 minutes.

Great the virtual machine has restarted and the database has done its recovery and working again. Now what? Well dont forget in this cloud setup, the easiest thing is to use DHCP addresses. Unfortunately DHCP addresses are not guaranteed to be same after reboot . Plus rebooting on a different host makes it more complex to get the same IP. This IP address change can cause the Database connectivity to be lost to the actual end user. In order to shield the end users from this complexity, we sort of implemented our own Database Name Server. However this can only work by modifying the clients which references the database using this "Virtual Hosts" format so that the clients can always find their intended database without really worrying about where it is running. A minor change in the PostgreSQL clients but a huge complexity reducer for end users to fix their IP addresses or domain names to the changed location.

Aha now this explains why vPostgres ships their own clients and libpq library which is API compatible with standard PostgreSQL libpq library.The libpq library is actually 100% compatible with standard PostgreSQL Libpq library. The only addition it has is the feature of Virtual Hosts which is critical for HA to work seemlessly without the users being concerned about the actual IP of the database. Without the change, HA will not work on the framework. Since it is 100% compatible, if an application works standard libpq it will work with vPostgres libpq. Similar changes are also done in the JDBC driver and ODBC Driver for vPostgres so HA is supported across all supported clients.

That said if you use standard libpq/psql and other standard clients and you know the IP Address of the vPostgres database and connect to it via that IP address (and not the virtual host string) it will still work flawlessly. However if the database goes down and restarts with a new IP address then the client will have no ability to figure out the new IP address and will have to bug the Administrator to figure out the new IP address.

Though for folks familiar with vSphere terminology, HA is not FT - Fault Tolerant which is a different take on HA to further reduce downtime from minutes to seconds. More on that in future.

Using PostgreSQL Server on Micro Cloud Foundry

2011-10-13T11:14:00.001-04:00

With the recent news that PostgreSQL is now available in the Micro Cloud Foundry, I decided to take it for a test spin. I downloaded the Micro Cloud Foundry VM zip file which is about 1.0GB big. After downloading it I unzipped it on my MacBookPro and use VMware Fusion 4.0.2 to open the VM. As the VM booted up the console shows a message

Micro Cloud Foundry not configured

I selected the option 1 to configure the Micro Cloud. It asked me to configure my VM user password, Networking (DHCP or Static) and then asked me to enter my Cloud Foundry configuration token which was provided to me after I had created a pgtest.cloudfoundry.me domain just before the download.

It took about 5 minutes to setup the cloud

After the setup: I got my micro cloud foundry setup with my local IP (looked like a bridge connection rather than NAT).

Then I installed the VMC tool on my Mac using (Need Ruby)
(NOTE: Skip directly to ssh part if you donot want to install Ruby/vmc)

$ gem install vmc

$ vmc target http://api.pgtest.cloudfoundry.me

Got me connected to my micro cloud.
Then I did a

$ vmc register

to create my user account using a email id and password
Then I logged into the MicroCloud using

$ vmc login

Now when I do the following I see the PostgreSQL Service available with other databases also.

$ vmc services

============== System Services ==============

+------------+---------+---------------------------------------+

| Service    | Version | Description                           |

+------------+---------+---------------------------------------+

| mongodb    | 1.8     | MongoDB NoSQL store                   |

| mysql      | 5.1     | MySQL database service                |

| postgresql | 9.0     | PostgreSQL database service (vFabric) |

| rabbitmq   | 2.4     | RabbitMQ messaging service            |

| redis      | 2.2     | Redis key-value store service         |

+------------+---------+---------------------------------------+

=========== Provisioned Services ============

As you can see there are no provisioned services currently.

Here if you are like a Java/Spring developer you want to creating an application using Xin Li's post on "PostgreSQL for Micro Cloud Foundry- Spring Tutorial".

I am not interested in developing Java applications but I want access to the postgresql server directly.

Now comes the ssh part.

Currently the PostgreSQL server is not exposed externally from the Micro Cloud.
But on the console of Micro Cloud VM, you can configure the password of vcap user. Which means now you have ssh access to the Micro Cloud VM.

$ ssh vcap@mircrocloudip

$ cd /var/vcap/store/postgresql

$ vi postgresql.conf 

and edit listen_address to add your database client ip address out there.
For my demo setup I just opened it to all

listen_addresses='*'

Next assign a Postgres password for the "vcap" user

$ /var/vcap/packages/postgresql/bin/psql -d postgres

psql (9.0.4)

Type "help" for help.

postgres=# ALTER USER vcap WITH PASSWORD 'secret';

ALTER ROLE

postgres=#\q

Now I exit from Micro Cloud VM and using the console I restart the services.
Now the PostgreSQL service can be accessed from postgres client anywhere.

For example from a Macbook Pro

$ psql -h microcloudip -d postgres -U vcap
Password for user vcap:
psql (9.0.5, server 9.0.4)
Type "help" for help.

postgres=#

Try it out!

vFabric Data Director - All in a Box setup

2011-10-07T15:44:00.000-04:00

vFabric Data Director 1.0 is available for download on the VMware download website. Generally the question we get a lot is on how to do a "small" setup for either a departmental setup or trial setup which is actually small enough to fit in a beefy workstation or a small server. Often time this helps people to evaluate the features of Data Director before doing the "standard" HA setup. Maybe it is only for tests/dev databases where one does not want to invest too much in the infrastructure setup itself..

So here is one such way of doing a "small" setup of vFabric Data Director for test/dev/qa databases.
(Note: This is going to be a long blog post)

What do you need?

A beefy Workstation or departmental Server with lots of RAM and lots of Storage and atleast two network adapter (Even though only one need be connected to the departmental network)
ESXi V5.0 installation CD (Software)
vCenter Virtual Appliance/Virtual Machine
DHCP Server Virtual Machine
vFabric Data Director Virtual Appliance Image
Preferably atleast one static IP Address on your departmental network for DB Name Server or have Dynamic DNS on your departmental network
License Keys - Use your VMware contacts.

Step I: Preparing the Workstation
On my test setup I had 12GB of RAM with 2x Quad Core x86_64 chips with 5 disks in it. Since this is my whole setup, the more memory I get, the merrier I am with the setup. It is recommended that you have some sort of Raid controller on the setup and have ability to create multiple LUN devices that will be exposed to the ESXi server. On my setup I had about one disk dedicated to ESXi and for other disks I created a RAID-5 LUN (RAID-10 is preferred but I did not have enough space on my demo setup). If I had more Storage available I would do two setups with RAID-10 setup and a RAID-0 setup which can be used as backup datastore. The RAID preferences depends on individual needs on what to trade off (performance , availability, capacity).

Step II: Installing ESXi 5.0
Using the ESXi V5.0 installation CD, I installed ESXi on the first logical device and eventually setup the RAID protected device as a datastore that ESXi can use.

Step III: Install vCenter Virtual Machine
Here one can use the vCenter Virtual Appliance available also. On my demo setup I had used a vCenter Server based on Windows Server 2003 since that was available. Installation of vCenter Server itself can be its own blog entry and I will leave that to experts. For my purpose I had setup vCenter Server setup done in a virtual machine.

Step IV: Setting up vCenter Server for our task
This is where things are bit different for this special all in a box setup. The idea of setting up is to do vFabric Data Director Appliance which includes the hardware. Hence the idea is only the management console and the databases that it deploys are exposed outside (of course the ESXi also has to be visible outside to set this up) and all other infrastructure related things are hidden within this appliance. This is where two network adapters will come in play. Lets go first with the steps and then a bit of explanation on why do it this way.
I am assuming that the ESXi Server and vCenter VM has network connected to the live network adapter.

Connect to the vCenter through vSphere GUI or through the webclient.
Create a new DataCenter.
Create a new Cluster
Add the ESXi Host to this cluster
Edit Settings for the Cluster to Enable

vSphere HA (even though we cannot do it on a single Host)
vSphere DRS
"VM and Application Monitoring" in VM Monitoring

Create a Distributed vSwitch as follows:

Go to networking in Inventory you will see your corporate network called probably "VM Network"
Add a new distributed vSwitch in the section where you have to add a physical Network Adapter, select the Network Adapter which is not plugged into the departmental network (Skip creating an automatic port group for the switch)
Now for the dvSwitch created, create two port Groups "vCenter Network" and "Internal Network"

For the ESXi host create a vmKernel port in "vCenter Network" portgroup with a static IP address 192.168.2.2
For the vCenter VM, create a new network adapter in the "vCenter Network" port group with a static IP of 192.168.2.1
Change the Managed IP of vCenter Server (Administration->vCenter Server Settings->Runtime Settings) to 192.169.2.1
Make sure vCenter server can still access the ESXi server through the new "vCenter Network" portgroup
Setup DHCP Server Appliance such that its LAN network is on "Internal Network"

I had setup DHCP Server such that its own IP is 192.168.1.1 and it does DHCP on the network from range 192.168.1.5 to 192.168.1.250 (for my demo setup)

Step V: Deploy vFabric Data Director OVA Template

Using the vSphere Client (connected to our vCenter Server) deploy the vFabric Data Director OVA template.
The setup wizard will ask you to select the network for vCenter and the Management console. For the vCenter Network select the "vCenter Network" portgroup and for the Management Console, select the "VM Network" which is the live departmental network
If your setup is like mine, I do not have access to static IP adddress in the deparmental network so I just leave the next screen at its defaults to use DHCP and finish the deployment.

Once the deployment finishes there will be a new vApp called VMware Data Director.

Start the vAPP. Once the vAPP starts, expand the "+" sign and select to the Management Server VM.
Select the "Summary" tab of Management Server and wait till it shows an IP address.
Enter that IP address in a browser and you should see message "This connection is untrusted" depending on your browse type, add it to your exceptions and then it should take you to License agreement screen.

Step VI: Setup vFabric Data Director

Read and accept the License agreement to proceed.
Next create an administrator account.
On the next screen since this is a small setup, I selected the Global User Management Mode
Setup the Branding as required on the next screen
Setup the SMTP server information if available (needed for user password resets) (You also need outgoing email id for a successful setup of SMTP )
On the next screen you have to setup vCenter Network Information. Since we dont have any DHCP on our "vCenter Network" portgroup, edit Network adapter settings and select "Static" with netmask information 255.255.255.0
Set static IP addresses 192.168.2.3 for Management server and 192.168.2.4 for DB Name Server
On the next screen change Internal Network to select "Internal Network" portgroup and leave DB Name Server Network as the "VM Network" which is the departmental network.
On the following screen, select the network settings of "Internal Network" DHCP should be already selected. Also check Static and add the network mask as 255.255.255.0
For Management Server - Internal Network adapter, select static IP address and set it to 192.168.1.3
For DB Name Server - Internal Network adapter, select static IP address and set it to 192.168.1.4
Warning: This next bit is where you use your one static IP address or Dynamic DNS based FQDN requirement. We still have to provide DB Name Server - DB Name Service Network Adapter. If you have any influence on your IT, get a static IP address for this one. If you get the static IP, then click the Departmental "VM Network" setup and select static IP address with the associate Gateway, netmask and DNS Server setup. Then set the static IP address for DB Name Server - DB Name Service Network Adapter with the static IP address that you get from your IT..
Next enter your Evaluation License keys for vFabric Data Director and vFabric Postgres
Finally verify information on the summary page and then click Finish
You should get a login screen if it sets up successfully

Note: The most tricky bit is getting the IP address from IT. If for some reasons you do not have a static IP address, fake a fully qualified domain name for DHCP. Once setup and you get a login screen. Figure out the DHCP IP allocated to DB Name Server using vSphere Client (It is first IP address that shows in the Summary tab of DB Name Server VM). Enter using your administrator account credentials, go to "Administration" tab. Select Settings-> Networking setup. Select Edit Network Setup and step through the setup again and change your fakeFQDN with the DHCP IP address and press finish. Of course this is a hack and not recommended since DHCP IP addresses can change anytime if the lease is up or the system is rebooted and other network policies.

Step VII: Setting Up an Resource Bundle in vFabric Data Director
For this we need a special Resource Pool in our Virtual Data Center

Using vSphere Client we create a resource Pool "Resource Bundle1" in the data center
Edit its settings such that it has reservations and limits matching for both CPU and memory
Also "Unlimited" should not be checked for both CPU and Memory.
In my demo setup I set CPU reservations and limits to 4096MB and Memory reservations and limits to 4096 MB.

Enter the vFabric Management Console using your administrator credentials and go to "Manage & Monitor" tab.

Select "Resource Bundle" and create a new resource Bundle "ResourceBundle1".
If the setup is right the next screen should show you the CPU/Memory Resource Pool that we created "Resource Bundle1"
Next select the RAID protected datastore and a size chunk off it for Database Storage. Select any alternative or the same datastore for "Backup Storage" with a sizeable chunk. In my demo I selected my Raid5 based datastore and 100GB sizes for both.
Next select the "VM Network" which is my departmental network through which uses will access the database.
Click Finish to setup the Resource Bundle.

Step VII: Setup an Organization in vFabric Data Director
In "Manage&Monitor" select Organizations and create "+" a new organization called "DataDirectorOrg".

On the next screen you could select an new user or in my case I used "Choose an existing user" and used my administrator account to manage the Organization also.
Next I selected the resource Bundle I just created (need to select "Choose an existing Resource Bundle" to see the resource bundle)
click Finish.

Once created there will be the new Org displayed. If you select it once, the link becomes active. If you then select the active link again it will open a New tab for the Organization for our next step.

Step VIII: Setup a Database Group
Next we have to setup a database group.

In the org tab select "Manage&Monitor" tab to see the list of databases (which is empty).
Select the second tab "Database Groups" to see the empty group list
Create ("+" ) a new database group "DBGroup1" where I selected half of my datastore resources assigned for this group leaving the rest of the entries at default.

Step IX: Create a database

Select and enter the database group we just created.
Create ("+") a new database "dbtest" with owner credentials "dba" and password.
Wait till deployment of the database succeeds and "dbtest" is running.
Once running highlight it , right click to see the properties and get the UUID and Name. The client also needs the IP address of the DB Name server.

Here is an image of the distributed vSwitch from vSphere client on my demo box.

Step X: Connect to the database from a client
Download vPostgres Clients for your platform. Then using psql from the client connect to the database similar to the following example

psql -h {dd9fce1e-db46-4a08-99a1-e9023b8239fe}.129.55.555.55 -d dbtest1 -U dba

It should prompt for the dba password and now you are connected to the database and the setup is working. Check out my previous blog entry on how to use vPostgres Clients.

Finally now the setup of vFabric Data Director all in a box setup is working and tested.

#PGWest 2011 - Using vFabric Postgres - A DB User's Perspective

2011-09-29T16:51:00.000-04:00

Here are my slides from my #PGWest 2011 Presentation " Using vFabric Postgres - A DB User's Perspective" for vPostgres Databases as deployed by vFabric Data Director.

Next Stop: #PgWest 2011 - San Jose

2011-09-18T12:22:00.003-04:00

Returned back from a great #pgopen in Chicago. It was nice to also see and meet again senior VMware executives, PostgreSQL community members and lot of folks (aka customers or potential customers ) who use PostgreSQL as key databases in their IT setup in the conference.

Next stop now is #PgWest 2011 in San Jose starting on Septeber 27,2011. This year again #PgWest is in San Jose, California.

At this conference, I am presenting my first ever "Using vPostgres - A DB User perspective". The gist of this presentation is on how to use the vFabric Postgres client with the vFabric Postgres server deployed by vFabric Data Director. There are some small differences on how the client works compared to community PostgreSQL and we will go over those in the session on exactly how it works, and see on how you use it for running and developing your own applications with it.

Also Alex Mirgorodskiy, VMware will be first time presenting in a PostgreSQL conference (that I am aware of at this point of time) on "vFabric Postgres Database Internals". In this session Alex will go over the subtle tansparent changes which makes vFabric Postgres so easy to deploy, manage and perform in the vFabric Data Director world. Not to steal his thunder but this will cover the new Elastic Database Memory in detail.

Bill Hodak, VMware will present about vFabric Data Director itself on how it leverages the features of PostgreSQL and provide a management framework over it to provide enterprise framework and provide self-service features making it easy for even folks who donot know a lot about PostgreSQL database itself to deploy, tune, monitor, backup, restore, clone the database instances.

And to give an overview of VMware's commitment to PostgreSQL Community, Charles Fan, Sr.VP - VMware R&D will be presenting the keynote at #PgWest 2011.

Overall pretty excited about the conference and to meet lot of new people in the bay area.

Running PostgreSQL on Virtual Environments - #pgopen 2011

2011-09-15T17:44:00.000-04:00

Slides from my presentation at Postgres Open 2011 (#pgopen11) in Chicago.

Next Stop: Postgres Open 2011 - Chicago

2011-09-13T10:46:00.001-04:00

Soon I will be in Chicago for Postgres Open 2011. Here I will be presenting once again
"Running PostgreSQL on Virtualized Environments" on Thurday - 11:30am in the Cotillion Ballroom (according to the current schedule).

The title should really be "Running Community PostgreSQL on Virtualized Environments" since this presentation really applies to the experiences of running community PostgreSQL in Virtual Machines. Most of the things in the presentation should really be a checklist which helps you get the most of PostgreSQL in VMs. Of course depending on the VM and the underlying infrastructure, your mileage may vary.

Overall I am also looking forward to attend other sessions (besides mine of course) . Greg Smith has couple of sessions which sounds interesting. There is key note by Charles Fan on Friday (that should be no brainer for me). Probably the most interesting to me is "Unlocking the Postgres Lock Manager" by Bruce Momjian which definitely is in line of my interest (since I try to control/avoid/reduce lock contentions and LWLocks in benchmarks which actually lead to my past presentation in pgcon on "Understanding Postgres LWLocks").

Definitely looking forward for the trip.

VMware vFabric Data Director, VMware vFabric Postgres and CloudFoundry

2011-08-29T08:22:00.002-04:00

Today I am finally pleased to see that we are finally moving out of stealth mode.

VMware announced vFabric Data Director at VMworld 2011 along with vFabric Postgres (vPostgres). You will find lot of information already in the press release.

VMware vFabric Data Director

vFabric Data Director is a new VMware software product that enables enterprises to offer Database-as-a-Service. vPostgres is the the first database supported on vFabric Data Director.
It is a self-service product which takes the repetitive tasks of setting up a database right from virtual bare-metal to a fully running databases in minutes with features that you expect with an Enterprise quality database (deploying, monitoring, backup, restore, resizing, cloning and many other features). It helps to reduce the database sprawl and increases DBA productivity to handle large number of databases. It extends the vSphere platform to now include a data tier.

You can find the product document, a product-demo and other information on the resource page of vFabric Data Director.

VMware vFabric Postgres (vPostgres)

vFabric Postgres is VMware's version of PostgreSQL. vPostgres is optimized for running on vSphere platforms. It is based on PostgreSQL 9.0. However there are some transparent changes which are significant in overall user experience. Lets go over few of these key features

vFabric Postgres Elastic Database Memory: vPostgres Elastic database memory allows dynamic and seamless adaption of bufferpool in response to changing workloads on the hypervisor. In traditional implementation, if you take out memory, the guest VMs performance will goes down the drain. Most cloud users will have seen the symptoms for sure. With vPostgres Elastic database memory , the effective bufferpool shrinks based on memory pressure dynamically and allows the overall system to be more graceful to this changing memory pressures. This feature reduces the over-the-cliff drop in performance observed frequently in Cloud deployments of databases.

Dynamic Checkpoint Tuning: In vPostgres, the priority is given to checkpoint_timeout which is referred to as Recovery Time Objective. In order to do the right balance between this Recovery Time Objective and overall performance, it dynamically shifts checkpoint_segments to keep a fine balance of adherence to RTO, performance and diskspace for WAL Logs. This allows reduction of manual tuning of checkpoints and allow the database server to dynamically find an optimum point based on changing workload.

vPostgres also has some specific features when used with vFabric Data Director. vFabric Data Director allows to change resources (vCPU, memory) set for a particular database. vData Director can then auto-tune the database based on the changed resources minimizing significantly the time to re-tune database. This allows DBAs to focus on more "business goal related tasks rather than day-to-day maintenance tasks.

Currently vPostgres will be only available for download as part of vFabric Data Director. However there is a vPostgres Service available on CloudFoundry.com for Cloud Applications. It is free for use by all applications that support the Cloud Application programming which includes Java, Ruby, node.js in CloudFoundry.com.

$ vmc services

============== System Services ==============

+------------+---------+-------------------------------+
| Service    | Version | Description                   |
+------------+---------+-------------------------------+
| mongodb    | 1.8     | MongoDB NoSQL store           |
| mysql      | 5.1     | MySQL database service        |
| postgresql | 9.0     | PostgreSQL database service   |
| rabbitmq   | 2.4     | RabbitMQ messaging service    |
| redis      | 2.2     | Redis key-value store service |
+------------+---------+-------------------------------

During a cloud deployment, you could select the "postgresql" service for your application.
More on CloudFoundry services in a subsequent blog posts.

VMworld 2011 - Las Vegas

2011-08-27T03:52:00.000-04:00

Arrived in Las Vegas early in the middle of the night for VMworld 2011 before Hurricane Irene hits the north east coast. In last few years, this will be my first out of state conference which is not completely dedicated to PostgreSQL that I am attending. I am part of the Hands On Labs at VMworld which I heard are pretty popular in VMworld. For database lovers I would recommend the

#HOL12 Optimizing Data Access for Your Cloud Infrastructure

It will be pretty interesting. Also there are many other Performance tuning sessions which will be also very interesting.

Also there are many sessions available which may be difficult to short list. One of the things that is different from my other conferences that I attend is that one has to pre-register for the HOL or any sessions that one wants to attend otherwise they are denied entry to that session. That is way difference and I still havent got around to figure out how the whole schedule builder works myself. So I would advice some extra time to make sure you can select your favorite sessions before they fill up.

Understanding PostgreSQL LWLocks - PGCon 2011

2011-05-20T11:08:00.002-04:00

From PGCon 2011 here are my slides on "Understanding PostgreSQL LWLocks"

Or alternatively at the direct link.

Running Postgres in Virtualized Environment - #pgeast 2011

2011-03-24T10:12:00.000-04:00

Slides from today's presentation at PGEast.

As usual feedback, questions welcome.

Introduction to PostgreSQL for System Administrators - #pgeast 2011

2011-03-23T17:48:00.000-04:00

Here are the slides from my presentation today at PGEast 2011.

It turned out to be a fast faced presentation for 45 minutes. Online feedback, questions are welcome as usual.

Update: Also my next presentation "PostgreSQL in Virtualized Environments" has moved to tomorrow (Thurday) at 9:00am in the Boardroom for the early bird attendees :-)

PgEast 2011 - New York

2011-03-16T10:45:00.000-04:00

Its less than week for PgEast 2011 and this time I have two sessions out there.

My first session is on Wednesday at 3:00pm titled "Introduction to PostgreSQL for System Administrators" . This session is not about learning SQL or any real database feature but it is meant for system administrators to get their first exposure to PostgreSQL as an application running on their systems. We look at basic installation and some internals on understanding the various processes running on the system and understanding them.

My session session is on Friday at 10:05am titled "Running Postgres on Virtualized Environments". This session is about running Postgres in VM using VMware's vSphere. Many of the content while specific to vSphere can be used to understand in general when running Postgres on any virtual platform.

With the increase in adoption of "Cloud Computing" in various industries, I expect the usage of Postgres on VMs to increase dramatically in next few years. PgEast 2011 has a roundtable discussion with a panel of experts on Thursday at 2:30pm to precisely answer questions about deploying PostgreSQL in the cloud.

PostgreSQL 9.0 Simple Select Scaling using sysbench

2010-11-22T01:50:00.000-05:00

While I still haven't found a working way to test sysbench read-write test, I decided to continue on sysbench testing with PostgreSQL 9.0. This time I selected the oltp-test-mode=simple which essentially does a select lookup based on a primary key. The basic idea is to see if you are only doing a select statement how far can you scale.

Here are my initial results with PostgreSQL 9.0

This is still work in progress in some ways since this test has never been done before with PostgreSQL 9.0 (or atleast I haven't seen any body publish anything around this before).

Anyway my real goal is not to just put the numbers here but to understand what is happening here. Round about 40-48 clients we seen to peak out on scaling and hit a wall as far as scaling goes. The statement is pretty simple select from sbtest with a primary key which is randomly generated.

This is where I miss dtrace and OpenSolaris. Now that I am working on Linux it thought of trying it out with systemtap. The setting up of a working systemtap itself was a big challenge and took me some time to make it to work on my existing kernel. (Boy Linux should get their act straight at user level tracing. Its not omnipresent as dtrace on Solaris).

I converted my old lockstat utility to work on systemtap. The script seems to work but systemtap can act funny sometimes like dont abruptly exit from systemtap otherwise it may send a "wrong" signal to postgres backend which then just commits suicide since it cannot figure out what to do with such "trace/debug" signal.

        LOCKNAME LWID M W/A COUNT SUM-TIME(us) MAX(us) AVG-TIME(us)
      LockMgrLock 45 Ex W 85343 469682510 13152 5503
      LockMgrLock 57 Ex W   57547 30903727 8313 537
      LockMgrLock 44 Ex W 390 34061 1670   87
      LockMgrLock 59 Ex W     375     41570 2032 110
      LockMgrLock 56 Ex W 361     39685 1889 109
      LockMgrLock 47 Ex W 344     24548 1564   71
      LockMgrLock 54 Ex W 335 67770 2319 202
      LockMgrLock 50 Ex W 325 44213 1690 136
      LockMgrLock 49 Ex W 325     39280 1475 120
      LockMgrLock 55 Ex W 323     39448 1584 122
      LockMgrLock 48 Ex W 323     26982 1669   83

What you see above is top WAITS on lwlocks by count and what was the average wait and max wait time for a particular LWLock.

(Thinking it might be related to NUM_LOCK_PARTITIONS, I did some experiments with different sizes but since they are all related to the same table it does not help here.)

Then I modified my lockstat script slightly to do stack straces for those two locks:

Majority of those were pretty much caused by the same code path:

Lock id:45, LockMode:0

 0x000000000062412e : LWLockAcquire+0x25e/0x270 [/usr/local/aurora-1.0/bin/postgres]

 0x00000000006228cc : LockAcquireExtended+0x2dc/0xa40 [/usr/local/aurora-1.0/bin/postgres]

 0x0000000000620788 : LockRelationOid+0x48/0x60 [/usr/local/aurora-1.0/bin/postgres]

 0x000000000064be85 : AcquireExecutorLocks+0xd5/0x190 [/usr/local/aurora-1.0/bin/postgres]

 0x000000000064c96a : RevalidateCachedPlan+0x5a/0x3b0 [/usr/local/aurora-1.0/bin/postgres]

 0x000000000062ee64 : exec_bind_message+0x604/0xab0 [/usr/local/aurora-1.0/bin/postgres]

 0x0000000000630bbd : PostgresMain+0x82d/0x16e0 [/usr/local/aurora-1.0/bin/postgres]

 0x00000000005f302e : ServerLoop+0x96e/0xcb0 [/usr/local/aurora-1.0/bin/postgres]

 0x00000000005f3dec : PostmasterMain+0xa7c/0x1150 [/usr/local/aurora-1.0/bin/postgres]

 0x000000000044e4f0 : main+0x370/0x430 [/usr/local/aurora-1.0/bin/postgres]

Lock id:57, LockMode:0

 0x000000000062412e : LWLockAcquire+0x25e/0x270 [/usr/local/aurora-1.0/bin/postgres]

 0x00000000006228cc : LockAcquireExtended+0x2dc/0xa40 [/usr/local/aurora-1.0/bin/postgres]

 0x0000000000620788 : LockRelationOid+0x48/0x60 [/usr/local/aurora-1.0/bin/postgres]

 0x0000000000477425 : relation_open+0x55/0x90 [/usr/local/aurora-1.0/bin/postgres]

 0x000000000047e5e3 : index_open+0x13/0x90 [/usr/local/aurora-1.0/bin/postgres]

 0x00000000005732a5 : ExecInitIndexScan+0x125/0x1e0 [/usr/local/aurora-1.0/bin/postgres]

 0x0000000000560275 : ExecInitNode+0x135/0x290 [/usr/local/aurora-1.0/bin/postgres]

 0x000000000055ed80 : standard_ExecutorStart+0x530/0xc70 [/usr/local/aurora-1.0/bin/postgres]

 0x000000000063278b : PortalStart+0x1bb/0x380 [/usr/local/aurora-1.0/bin/postgres]

 0x000000000062ee9c : exec_bind_message+0x63c/0xab0 [/usr/local/aurora-1.0/bin/postgres]

 0x0000000000630bbd : PostgresMain+0x82d/0x16e0 [/usr/local/aurora-1.0/bin/postgres]

 0x00000000005f302e : ServerLoop+0x96e/0xcb0 [/usr/local/aurora-1.0/bin/postgres]

 0x00000000005f3dec : PostmasterMain+0xa7c/0x1150 [/usr/local/aurora-1.0/bin/postgres]

 0x000000000044e4f0 : main+0x370/0x430 [/usr/local/aurora-1.0/bin/postgres]

So what I understand is that we have two problems here:
1. RevalidateCachedPlan (the Major bottleneck)
2. AccessShare Lock entry (the second bottleneck)

Well atleast now I know the area where there seems to be some scaling bottlenecks which can limit simple SELECT statement scalings. The question now is what to do about them. Back to the mailing list.

sysbench, PostgreSQL 9.0 and OLTP complex read-write test

2010-11-18T15:00:00.000-05:00

Continuing with my sysbench saga with PostgreSQL 9.0, I was generally not encountering any errors except recently. I found two differences on how I did execute this test in order to hit this problem. One is I used a relatively small number of rows (1 million) which forces the special distribution of sysbench clients to be smaller and a reasonably high number of threads (80) as follows

$ sysbench --db-driver=pgsql --oltp-dist-type=special --oltp-table-size=1000000 --oltp-read-only=off --oltp-test-mode=complex --max-requests=0 --max-time=300 --num-threads=64 --test=oltp run

sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 80

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
FATAL: query execution failed: 9490352
FATAL: database error, exiting...
Done.

And the test failed with error reported in pg_log:

ERROR: duplicate key value violates unique constraint "sbtest_pkey"
DETAIL: Key (id)=(500815) already exists.
STATEMENT: INSERT INTO sbtest values($1,0,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')

The way sysbench works for the complex read-write test transaction, after doing some bunch of selects and updates, it deletes a row and inserts the same row back. So in a transaction logic it should not hit this error since it just deleted the row. This took me some time to recreate it in a way that I could understand what is happening.

What's happening is the timing of the start of a query in transaction with respect to another transaction in flight
working with the same key-value row such that the other transaction just deleted the row and also inserted the same row back in the same transaction.

Lets consider two transactions A and B

Transaction A                      Transaction B

BEGIN;

                                   BEGIN;

DELETE FROM sbtest WHERE id=500815;

(returns DELETE 1)

INSERT INTO sbtest values(500815,0,'','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy');

(returns INSERT 0 1)

                                   DELETE FROM sbtest WHERE id=500815;< ------- hangs/waits

END;

(COMMIT)

                                   (returns DELETE 0 – returns success but doesn’t delete any rows . It doesn't roll back the transaction)

                                    INSERT INTO sbtest values(500815,0,'','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy');

                                    ERROR: duplicate key value violates unique constraint "sbtest_pkey"

                                    END;

                                    (ROLLBACK)

The way MySQL-InnoDB handles it is slightly different. It actually deletes the new row inserted and hence always can do the INSERT successfully and thats why sysbench with MySQL - InnoDB never showed that problem.

If you read the documentation on PostgreSQL for READ COMMITTED Isolation level, it says: "In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run". So had the DELETE actually started after the END of the first transaction it would delete the new INSERTed row.I am not sure how other databases (ORACLE, DB2, etc) behave in this scenario.

As one of my co-worker mentioned "Correctness is in the Implementation of Beholder", it is hard to say who is right or wrong..

Just another one of the minor differences between MySQL and PostgreSQL.

PostgreSQL in Virtual Environment - PGWest 2010

2010-11-03T17:18:00.001-04:00

Yesterday I had my presentation at PgWest 2010 at San Francisco.

If you have questions on the presentation please feel free to leave comments.

Postgres 9 and sysbench 0.4.12

2010-10-04T15:50:00.001-04:00

Ever since the release of Postgres 9 I was hoping for a chance to do some performance tests on it. I finally got around to try it out. This time I tried Postgres running in a virtual machine on top of VMware's vSphere Infrastructure 4.1.

Since there were plenty of sysbench numbers available for mysql, I thought it was time to try it with sysbench. The version I tried was sysbench 0.4.12. Compiling sysbench 0.4.12 for PostgreSQL itself was a huge challenge that it deserves a separate blogpost but for now lets start at the point where I got a working sysbench binary that I run it from a separate virtual machine against my Postgres database server.

The first step to do a sysbench oltp was to do the "prepare" step where the benchmark tool creates the table and loads rows into the table. I saw many mysql tests running with 1 million rows or some with 10 million rows.

So anyway I started with 1 million rows with similar options. The load started and went on and went on for a while.. (I should have taken a lunch break instead of waiting for it to finish.) It literally finished after some 20 odd minutes. I found that odd. So I downloaded MySQL 5.5 rc binaries and created a mysql database and compiled sysbench to run against MySQL and started the same "prepare" step with 1 million rows.

The "prepare" step finished for MySQL in an amazing 30 seconds or so. My first reaction was MySQL is damn fast than Postgres. As a Postgres lover I found that hard to believe. There's got to be some other problem. I added the --debug flag to sysbench and I saw the insert statements that sysbench did to mysql. When I used the same debug flag for Postgres version it did not show me any statements that it used.. That was no help.

Anyway the Postgres was running on a version of the "late" OpenSolaris and used the query_time_breakdown.d script from pgfoundry dtrace scripts to see that the query that it was using to insert rows. I noticed that it was doing multi-row inserts on MySQL and single-row inserts on Postgres.

Now came the hard part of reading through the sysbench code which took me some while to understand its layout and how it uses the database driver. To cut the long story short, I figured sysbench uses "drv_caps_t" structure to record the capabilities of the database drivers. The MySQL driver enables multi-row inserts while the Postgres driver had multi-row support disabled.

Checking the documentation of Postgres I saw multi-row inserts in a INSERT statement is allowed.

So I modified sysbench/drivers/pgsql/drv_pgsql.c and changed

/* PgSQL driver capabilities */
static drv_caps_t pgsql_drv_caps =
{
0,
1,
1,
0,
0,
1,
0,
NULL
};

to the following

/* PgSQL driver capabilities */
static drv_caps_t pgsql_drv_caps =
{
1,
1,
1,
0,
0,
1,
0,
NULL
};

The resulting difference that it now loads 1000 rows in each INSERT statement (like it does for MySQL)

As for the difference it makes on the "prepare" operation:
$ time ./sysbench --pgsql-user=pguser --pgsql-password=xxxxxx --pgsql-db=sbtest --pgsql-host=pgdb --db-driver=pgsql --oltp-dist-type=special --oltp-table-size=1000000 --oltp-read-only=off --oltp-test-mode=complex --max-requests=0 --init-rng --max-time=300 --num-threads=96 --test=oltp --debug prepare

sysbench 0.4.12: multi-threaded system evaluation benchmark
Creating table 'sbtest'...
NOTICE: CREATE TABLE will create implicit sequence "sbtest_id_seq" for serial column "sbtest.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "sbtest_pkey" for table "sbtest"
Creating 1000000 records in table 'sbtest'...

real 17m47.695s
user 0m0.328s
sys 0m0.700s

to:

$ time ./sysbench --pgsql-user=pguser --pgsql-password=xxxxxx --pgsql-db=sbtest --pgsql-host=pgdb --db-driver=pgsql --oltp-dist-type=special --oltp-table-size=1000000 --oltp-read-only=off --oltp-test-mode=complex --max-requests=0 --init-rng --max-time=300 --num-threads=96 --test=oltp --debug prepare

sysbench 0.4.12: multi-threaded system evaluation benchmark
Creating table 'sbtest'...
NOTICE: CREATE TABLE will create implicit sequence "sbtest_id_seq" for serial column "sbtest.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "sbtest_pkey" for table "sbtest"
Creating 1000000 records in table 'sbtest'...

real 0m36.237s
user 0m0.180s
sys 0m0.064s

This looks much better and acceptable. Now I can proceed to the real sysbench testing.
If somebody knows the maintainer of the sysbench, can you please point him to this blog article to get the drv_pgsql.c patched up to be similar to MySQL driver.

Virtualization and Postgres

2010-06-20T22:46:00.000-04:00

It will be soon couple of months for me at VMware. I have been learning a lot of VMware and Virtualization. Often time I try to view it from the point of view of a database like Postgres. To say the least it has been a learning experience.

In the most simplest form of understanding, virtualization (not to be confused with simulation or emulation) is a set of resources. The primary resources are CPU, Memory, Disk and Network. Then there are others but most others are not that interested for majority of the database users (or alteast for me). For a java programmer this is more like an interface which can be implemented by a class but retains its properties. One cannot use it directly but you use a class typically implementing that interface. Similarly for virtualization while you think only in terms of this interface resources it is actually implemented by the underlying physical resources of the system. Hence it is not to be confused with "emulation".

That said one would think that they need not think of it as a "special" case in their deployment. This is where expectations and reality start to diverge and we could have a case of finger pointing. To set the right expectations for virtualization one needs to understand the philosophy behind virtualization which I guess is what I have been doing recently. It is more like soul-searching in some sense. From what I get the philosophy behind virtualization is actually efficient and simplified utilization of resources. Again lets go back to our primary resources CPU, Memory, Disk, Network. What virtualization tries to achieve is efficient utilization of these resources beyond one operating system. I think everyone understands the efficient utilization part. The clause that becomes tricky is "beyond one operating system". This clause is like the last minute change included by some smart lawyer which no one will understand till some years letter when another legal review is done. :-)

This clause is what is causing many operating systems, applications like database to rethink their behavior of "My world is the only world here". The details of these behaviour are actually too long to list but to give an idea, an idle operating system throws enough tick interrupts to keep the clock up to date that few of these operating systems while doing nothing can keep a core busy just to do these time updates. Occassionally it can happen that the interrupt that does not happen in time which can cause time drifting in operating systems.

Similarly my experience with databases and shared memory is often the databases just pin the max memory we expect the database will use at peak loads. So even though it may be say 10-20% of the case that peak utilization will occur but memory has been fully allocated and pinned (marked "unusable" for others) which may not be the most efficient way of using that resource. (Primary true in my past life where the immediate thing we do is use Intimate Shared Memory on Solaris so nothing else can use it.) Other thing that can come to mind is spin locks where CPU cylces are forced to spin to do nothing but wait, network polling to see if there is more incoming data and many other operations.

Plus with the isolation provided a person within an operating system now has no idea what other things could be using the same resources that the OS is using which makes it more difficult for both the administrator and the Virtualization professional to reach some common understanding. :-)

Anyway this is probably my first post on this topic in relation with Postgres. As I learn more things about Virtualization, you can expect me to share my learning of the impact of Virtualization on Postgres and vice versa. In the mean while if you have other "bugging" questions related to Postgres and Virtualization do let me know and maybe I can test it out on my spare time.

Choosing stripsize in LUN or recordsize in ZFS for PostgreSQL

2010-05-09T01:00:00.004-04:00

In every database deployment, you have to request storage for the database. Typically in enterprises or cloud deployments, the requests goes to Storage Administrator who will ask you basics of the level of protection and probably some needs of the database if you are lucky. Many times they just give you a LUN or a filesystem (either based on NFS or something else). You have no idea whether that storage will suit your needs for the database or not. In this blogpost, lets even ignore the protection requirements to get the best performance out of the LUN.. RAID-0 typically is the best performing level. Some say RAID-1 is better for reads but I still havent enough practical cases to say yes that's the case.

But even in RAID-0 I have seen charts all over the place. The Storage Administrator has no idea (unless you tell him/her) what the database will be doing on the LUN. Typically all RAID has what's known as the stripsize which in some ways is the blocksize that it uses to do a logical break and go to the next spindle. For example for a RAID-0 over 8 disks with a stripsize of 8K. Every write you do will be divided in terms of 8K and then will spread the writes over those 8 disks. Its is typically assumed that you can write every hard disk simultaneously so in this case for the same latency of writing to a single disk now you can do 8K x 8 or 64K simultaneously which typically will be less than writing 64K to the same spindle. (64K is also a typical stripsize available.) So why do we need to worry about the stripsize, we should select the smallest stripsize and just forget about it. Well for many arrays smallest strip size available is native block of the disk which is 512 Bytes to a typical max of 256KB or 512KB.

Two reasons to worry about it:

1. Modern disks technologies including new SSDs or Flash technology basically are many times optimized for a block size (including flash technologies). In some flash based storage technologies, the write latency is same whether you write 512B or 4K. In such cases basically you are creating more work by writing smaller than that size. So obviously the smallest stripsize is not the most optimum.

2. Also there is a penalty of using the small strip size when you consider the throughput case. What is the throughput case? Throughput case is that you are really trying to get large amoun of data. How's it limited? Well it is limited by IOPS. Every disk has a working max range of how many IO operations it can sustain per second. Irrespective of what manufacturers say, the real random limit is more like anywhere from 100 IOPS to 160 IOPS per second with SATA on the real slow side, SAS somewhere in between and the Fiber Channel based disks on the high side.

So lets say you can get about 150 IOPS per disk in your datacenter then your maximum sustainable throughput that can get from a stripsize of 4K is 4K x 150 x 8 which is like 4800KB/sec or abour 4.6 MB/sec.. Pretty slow. Now increasing the stripsize to 8K gives you double the througput and if you use the bigger stripsize like 128K you get about 150MB/sec and 512kb (typical max stripsize) allows you 600MB/sec. Ofcourse by this time you could already be limited by your bus technology. Both SAS/SATA have 3 Gig links which means realistically the max you can get is about 300-350MB/sec.
So in this case 256KB is a good choice for systems which needs to move large amount of data like Data Warehousing/DSS.

But again remember the throughput comes at a cost. What is that cost? Latency. Time taken to read write increases as you increase the size of the IO. Repeating again the disks or flash technologies are optimized at a blocksize. If you go beyond that size the latency could increase linearly or sometimes exponentially (if it is a bad design). So you have to know your disks properly and make the right selection. For OLTP which is very sensitive to read and write operations for every transactions, an increase in latency by 100% can cause a drop of transactions by 50% (depending on the workload) and hence can cause dramatic impact. For such database deployments, everything to cut down latency helps.

So your storage can right now either do the best of OLTP layout or the best of throughput layout which is typically used by Data Warehousing or so-called Decision Support Systems. Many people sometimes do a trade-off to be somewhere in between and hence end up selecting stripsize somewher in between. I have seen 64KB/128KB as common stripsize where they try to do a balance. So instead of making one group of people using the database unhappy, this tends to make all groups of people unhappy. But sometimes due to the nature of the usage of the database and cost, that's probably the right way to go. Though I would recommend actually figuring out the various databases and have multiple LUNS of various stripsize available to the database so that the DBA can make righ decisions based on the usage.

The setting in ZFS called recordsize is similar to this stripsize except for the fact that its range is only from 8KB to 128KB. The default is 128KB for a reason that it can do a good tradeoff for all uses. However if you are going to do only OLTP type transactions, then changing that to 8KB is very helpful. The drawback is as soon you run any long running query which requires to scan through large amount of data and if you have 8KB recordsize you will be limited by the throughput as explained above.

What is really needed is dynamic stripsize or recordsize so it adapts to the write being done. However again the reads will be then dependent on the write stripsize which may not be case that you want. Most DSS/DW systems reads from OLTP systems so those will still be impacted if you have dynamic stripsize for writes which will be small if done through OLTP system. Hence an engineering problem to solve to remove dependence of these various stripsizes and record size.. Hey even databases have block sizes maybe even these blocksizes need to be adaptive :-)

Of course questions for the reader is whether the blocksize of the database should be equal to strip size or equal to N x stripsize where N is the number of "effective" disks in the array/LUN.
The real answer is it depends on the database technology.

Now at VMware Inc

2010-05-04T00:00:00.002-04:00

It looks like I haven't blogged for a while. Thing have been busy. I am now working at VMware Inc. I will primarily be doing performance work. Hopefully I can still continue blogging here about Postgres and other Open Source technologies.

My Last Day and Blog Post at Sun

2010-04-15T17:00:00.001-04:00

I am getting close to finish my 10th year at Sun Microsystems, Inc now part of Oracle America, Inc. However before I finish the year, it is time for me to move on with my new ventures.

Today is my last day in Sun/Oracle.

My new coordinates http://jkshah.blogspot.com and my LinkedIn Profile.

Stay in touch.

9.0devel and PGMonitor

2010-03-02T21:38:00.001-05:00

Using the latest CVS head, just upgraded my home setup to 9.0devel on OpenSolaris 2009.06

$ psql postgres

psql (9.0devel)

Type "help" for help.

postgres=#

Also I upgraded the PostgreSQL Monitor to use 8.4 JDBC4 driver instead of the 8.3 JDBC3 driver. Hopefully I will get some more time to enhance the tool itself.

Building latest PostgreSQL CVS Head on OpenSolaris

2010-02-05T12:44:00.001-05:00

With the talk about PostgreSQL 9.0 alpha 5, I thought it is time for me to try out another CVS head build on OpenSolaris. Of course this time this was on my home desktop which runs OpenSolaris 2009.06
I wanted to download the CVS head. The instructions are there on the wiki page. However before following it I needed to install the CVS on my OpenSolaris instance.

#pkg install SUNWcvs

The using the instructions I created a copy of the cvs repository and created my own project workspace.

I already had the Sun Compilers on my setup. (If not or have an old copy then you can always install or upgrade it as

# pkg install sunstudio

)
So I started my task of creating the new binaries on OpenSolaris. I found it to be bit bumpy.
Here is my configure options with my standard options.

$ ./configure --prefix=$HOME/project CFLAGS="-xO3 -xarch=native -xspace -W0,-Lt -W2,-Rcond_elim -Xa  -xildoff -xc99=none -xCC" --without-readline

However then I hit my first problem to do make.
Need to do using GNU make which was not installed on my desktop.
Back to pkg manager

# pkg install SUNWgmake

Continuing again with make which proceeded and then eventually stopped again due to missing bison. (I wonder why the "configure" script did not catch that?)

# pkg install SUNWbison

Anyway I started to run make again now that I have installed bison. Strangely it failed again.
Figured it still did not find bison. I had to use ./configure statement again and tried gmake after that which allowed gmake to pick up bison.

However it failed again.

gmake[3]: Entering directory `/export/home/postgres/project/pgsql.project/src/backend/parser'
/usr/bin/bison -d -o gram.c gram.y
gmake[3]: *** [gram.c] Broken Pipe
gmake[3]: Leaving directory `/export/home/postgres/project/pgsql.project/src/backend/parser'
gmake[2]: *** [parser/gram.h] Error 2

This one was not easy to solve. I thought that probably the bison was buggy and was about to give up. Then I thought I will give it a shot using truss to figure out what is happening

$ truss -f /usr/bin/bison -d -o gram.c gram.y 2> /tmp/bisontruss.txt

Going through that file bisontruss.txt I found:

  fcntl(6, F_DUP2FD, 0x00000001)                  = 1

  close(6)                                        = 0

  execve("/usr/sfw/bin/gm4", 0x08047D00, 0x08047DA0) Err#2 ENOENT

I had no clue what gm4 does, but it is missing.
I used the search feature of OpenSolaris to see if there is a package associated with it.

# pkg search gm4

INDEX      ACTION    VALUE                     PACKAGE

.....
basename link usr/sfw/bin/gm4 pkg:/SUNWgm4@1.4.2-0.111

As they say on TV : Yep, there's a pkg for that

# pkg install SUNWgm4

And now back to gmake

Darn another package missing:

gmake[3]: Entering directory `/export/home/postgres/project/pgsql.project/src/backend/bootstrap'

***

ERROR: `flex' is missing on your system. It is needed to create the

file `bootscanner.c'. You can either get flex from a GNU mirror site

or download an official distribution of PostgreSQL, which contains

pre-packaged flex output.

***

# pkg search -r flex

INDEX      ACTION    VALUE                     PACKAGE

....

basename   link      usr/sfw/bin/flex          pkg:/SUNWflexlex@2.5.33-0.111

# pkg install SUNWflexlex

(Of course gmake wont use it immediately till you use the configure statement again)

Finally after a long time (it sure seemed to take a long time) the gmake seemed to hang on preproc.c compilation:

"preproc.y", line 13548: warning: line number in #line directive must be less than or equal to 32767

This is just not my day.
I did a cleanup of the make. At this point I found that I forgot to enable dtrace probes in my configure statement and also since I am using 64-bit kernel, decided to build 64-bit binaries. Retrying with the slightly modified configure still did not solve the loop problem in preproc.y which just runs 100% on the CPU. I left it running for a long while just to see if it finished (till my patience runs out) It did not finish.

I finally updated my sunstudio binaries and retired gmake. This time it succeeded in finishing the gmake. (Hence I changed my wordings above to reflect install/upgrade sunstudio).

After that a quick gmake install

$ gmake install

and the binaries are ready.
A quick check reflects

$ initdb

$ pg_ctl start -l server.log

$ psql postgres

psql (8.5devel)

Type "help" for help.

postgres=#

For a second I was expecting to see psql(9.0develop) but this is still acceptable. :-)

In Summary, you want to do the following before building the latest PostgreSQL source on OpenSolaris

# pkg install SUNWcvs sunstudio SUNWgmake SUNWbison SUNWgm4 SUNWflexlex

and the configure script

$ ./configure --prefix=$HOME/project CFLAGS="-m64 -xO3  -xarch=native -xspace -W0,-Lt -W2,-Rcond_elim -Xa  -xildoff -xc99=none -xCC" --without-readline --enable-dtrace DTRACEFLAGS="-64"

And with gmake I am ready to use the latest build of PostgreSQL on OpenSolaris.

Building latest PostgreSQL on OpenSolaris

2010-02-05T04:52:00.001-05:00

I am moving my PostgreSQL on OpenSolaris realted entries to a new external blog. Since it is not part of my $dayjob anymore. Hope you update your bookmarks too.

Read "Building latest PostgreSQL CVS Head on OpenSolaris".