Showing posts with label SunBlogs. Show all posts
Showing posts with label SunBlogs. Show all posts

Thursday, April 15, 2010

My Last Day and Blog Post at Sun

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.






Friday, February 05, 2010

Building latest PostgreSQL on OpenSolaris

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".




Wednesday, January 13, 2010

New Year, New Role and New Build

Happy New Year. Its a new year and I have started a new role in Applications Integration Engineering which is part of the Sun Storage 7000 - Unified Storage Systems group. AIE's main charter is to integrate ISV products with Sun Storage 7000 family. I hope to continue working with databases and other applications and specially how it interacts and integrates with the FISHworks based products. Years ago, interestingly, I don't think I would have recommended NFS to be used with any database application. But looks like it is now way more stabilized in its current form. Then there is also iSCSI. But there is yet another way to connect to these systems soon which I think is more attractive to me and maybe even other database folks at large. More about that when time is right.


 Anyway with the new role, I thought it was time to update my existing OpenSolaris (b128a) to the latest OpenSolaris build 130. I must admit this has been the first OpenSolaris upgrade which was not as smooth as expected. First things first I got hit with bug with the naming of /dev repository. I first heard about the bug from George Drapeau but even though I worked around it I could still not upgrade to the latest build.  Then I heard from Mandy about the problem that if I had ever installed from /contrib repository I could still not upgrade to the latest build with the changed /dev name. I uninstalled all the software from /contrib and crossing my fingers the pkg image-update command still failed. Of course I then realized I probably had couple of packages from the /pending repository and even the Sun /extra repository. Uninstalling all the extra software was not fun but still the darn thing did not upgrade. Finally gave up and read about this forced upgrade using -f  as follows


# pkg image-update -f


and it worked. It started downloading the updates and finally created a new boot environment with the new build.


However the reboot to the new environment just stuck at the graphical boot with the orange bar going from left to right. After 5 minutes I killed the power and rebooted and this time used "e" on the grub menu and deleted the splashfile, foreground and background lines and changed the kernel boot line from console=graphics to console=text and pressed "b" to boot using the modified grub entry. I figured out that the X server refused to start. Cutting a long story short (it actually took me almost a day) to figure a simple solution, re-move my custom /etc/X11/xorg.conf (which I was forced to create few upgrades (b111a)  ago) so the X server can use its new defaults to start without any problems.


Of course that worked till I got the login and when I entered my login information, I ended with a white screen. Arrg yet another bug. Reading the mailing list got the following solution


$ pfexec /usr/sbin/unlink /usr/lib/xorg/modules/extensions/GL
$ pfexec ln -s ../../../../../var/run/opengl/server /usr/lib/xorg/modules/extensions/GL


With the above changes, finally rebooting the desktop into fresh working build 130 of OpenSolaris and I was ready to try out the new Thunderbird 3.0 and Firefox 3.5. Of course AWN (the mac like dock) worked for most part but the dock preferences refused to start. I did file a bug and it seems that it will be fixed in b131 but the quick fix is to edit


/usr/bin/awn-manager and replace the first line


#!/usr/bin/python


to


#!/usr/bin/python2.6


and that should allow you to see your AWN dock preferences once again.


If you ask me was it worth all the pain to upgrade to this new version. My simple answer is yes


Few thing fixed for me:



  • The new login screen is much nicer (in last few builds I could hardly read what I was typing in the login name text field on a widescreen monitor.

  • On build 128a I saw that the screen saver unlock screen was taking a long time to respond which seems to have gone away with this build.

  • I like the full text search capabilities of Thunderbird 3.0


Of course your reasons may be different then mine to upgrade and who knows build 131 might be out soon in next week or two then it probably might be a smoother upgrade if you can wait for it. (I can't.)



Monday, October 12, 2009

Accelerate your Payroll Performance with F20 PCIe Card

I guess you already heard about Sun Storage F5100 Flash Array and its world record benchmarks.  


But it's not F5100 that I am going to talk about but its smaller sibling called Sun Flash Accelerator F20 PCIe Card.  The name is a mouthful like all Sun product names so I will just call it "The Accelerator Card" in the remainder of this blog entry.  Of course the idea is not to start with the answer and find a problem with it. But I am going to narrate  is how we saw a problem and then thought of using this answer to solve the problem.


Recently our group ISV-E was doing our standard thing of  making applications run best on Sun. In this particular project with PeopleSoft Enterprise 9.0 on M5000 system using Sun Storage 6540, we encountered a problem that certain batch jobs where taking a long time to execute. Peoplesoft Enterprise 9.0 actually have ways to breakup jobs and run them in parallel so as to use the multi-core of the multi-processor system. But yet we could not really leverage the system enough to be satisfactory.  In this project they were using Oracle Database 11g. I got to give it to Oracle, they do have good tools. We used Oracle Enterprise Manager and saw for the troubled batch process, it was showing lot of blue color in its output.






Also looking at the top Objects, the tool reported which tables and index were  troublesome which was causing that amount of blue appear in the chart. This "Blue" problem is what led us to an idea to test out the Accelerator Card in the system and see if can help out here. What we did was created a few tablespaces and spread them out on the four Flash Modules on the Accelerator Card and moved the highly active (or "hot" ) tables and indices to the newly created tablespace. What we saw was simply huge reduction in the blue area and more green. That lead to the slogan in our team


"Go Green with the Accelerator Card !"


The Accelerator card not only reduced the time on this process but many other batch processes which had high IO components.  Here is a relative comparison of how it helped (with additional slight boost from upgrading SPARC64 VII from 2.4Ghz to 2.53Ghz CPUs).





Of course the next question is what if you take the same thing to its bigger sibling, Sun Storage F5100 Flash Array, well that's exactly what we did and as they say the rest is history.(Hint: Read the world records link and search for PeopleSoft)  For more information check out Vince's blog entry on  PeopleSoft Enterprise Payroll 9.0 NA and also  Why Sun Storage F5100 is good for PeopleSoft 9.0 NA Payroll application.


Truly if you use Oracle and use Oracle Enterprise Manager to monitor your application performance and are turning blue by seeing lot of Blue area in the chart then just remember


"Go Green with the Accelerator Card !"



Monday, September 14, 2009

Infobright Tuning on OpenSolaris/Solaris 10

Recently I was working on a project which used Infobright as the database. The version tested was 3.1.1 both on OpenSolaris as well as Solaris 10. Infobright is like a column-oriented database engine for MySQL primarily targeted towards data warehouse, data mining type of project deployments.


While everything was working as expected, one thing we did notice that as number of concurrent connections tried to query against the database we noticed that queries deteriorated fast in the sense that not much parallel benefits were being squeezed from the machine. Now this sucks! (apparently sucks is now a technical term). It sucks because the server has definitely many  cores and typically each Infobright query still can at the max peg a core. So the expectation will be typically to atleast handle concurrent queries which is close to the number of cores  (figuratively speaking though in reality it depends).


 Anyway we started digging into this problem. First we noticed that CPU cycles were heavy so IO was probably not the culprit (in this case). Using plockstat we found



# plockstat -A -p 2039    (where 2039 is the PID of mysqld server running 4 simultaneous queries)

^C
Mutex hold

Count nsec Lock Caller
-------------------------------------------------------------------------------
3634393 1122 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_Znwm+0x2b
3626645 1047 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_ZdlPv+0xe
2 536317885 0x177b878 mysqld`_ZN7IBMutex6UnlockEv+0x12
12 6338626 mysqld`LOCK_open mysqld`_Z10open_tableP3THDP13st_table_listP11st_mem_rootPbj+0x55a
9057 1275 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_Znwm+0x2b
8493 1051 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_ZdlPv+0xe
7928 1119 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_ZdlPv+0xe
5 326542 0x177b878 mysqld`_ZN7IBMutex6UnlockEv+0x12
683 1189 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_Znwm+0x2b
564 1339 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_Znwm+0x2b
564 1274 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_Znwm+0x2b
564 1156 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_ZdlPv+0xe
17 36292 0x1777780 mysqld`_ZN7IBMutex6UnlockEv+0x12
2 246377 mysqld`rccontrol+0x18 mysqld`_ZN7IBMutex6UnlockEv+0x12
57 8074 mysqld`_iob+0xa8 libstdc++.so.6.0.3`_ZNSo5flushEv+0x30
218 1479 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_Znwm+0x2b
4 78172 mysqld`rccontrol+0x18 mysqld`_ZN7IBMutex6UnlockEv+0x12
4 75161 mysqld`rccontrol+0x18 mysqld`_ZN7IBMutex6UnlockEv+0x12
….

R/W reader hold

Count nsec Lock Caller
-------------------------------------------------------------------------------
44 1171 mysqld`THR_LOCK_plugin mysqld`_Z24plugin_foreach_with_maskP3THDPFcS0_P13st_plugin_intPvEijS3_+0xa3
12 3144 mysqld`LOCK_grant mysqld`_Z11check_grantP3THDmP13st_table_listjjb+0x38c
1 14125 0xf7aa18 mysqld`_ZN11Query_cache21send_result_to_clientEP3THDPcj+0x536
1 12089 0xf762e8 mysqld`_ZN11Query_cache21send_result_to_clientEP3THDPcj+0x536
2 1886 mysqld`LOCK_grant mysqld`_Z11check_grantP3THDmP13st_table_listjjb+0x38c
2 1776 mysqld`LOCK_grant mysqld`_Z11check_grantP3THDmP13st_table_listjjb+0x38c
1 3006 mysqld`LOCK_grant mysqld`_Z11check_grantP3THDmP13st_table_listjjb+0x38c
1 2765 mysqld`LOCK_grant mysqld`_Z11check_grantP3THDmP13st_table_listjjb+0x38c
1 1797 mysqld`LOCK_grant mysqld`_Z11check_grantP3THDmP13st_table_listjjb+0x38c
1 1131 mysqld`THR_LOCK_plugin mysqld`_Z24plugin_foreach_with_maskP3THDPFcS0_P13st_plugin_intPvEijS3_+0xa3

Mutex block

Count nsec Lock Caller
-------------------------------------------------------------------------------
2175 11867793 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_ZdlPv+0xe
1931 12334706 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_Znwm+0x2b
3 93404485 libc.so.1`libc_malloc_lock mysqld`my_malloc+0x32
1 11581 libc.so.1`libc_malloc_lock mysqld`_ZN11Item_stringD0Ev+0x49
1 1769 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_ZnwmRKSt9nothrow_t+0x20
..


Now typically if you see libc_malloc_lock in a plockstat for a  multi-threaded program then it is a sign that the default malloc/free routines in libc is the culprit since the default malloc is not scalable enough for a multi-threaded program. There are alternate implementations which are more scalable than the default. Two such options which are already part of OpenSolaris, Solaris 10 are libmtmalloc.so and libumem.so. They can be forced to be used instead of the default without recompiling the binaries by preloading anyone of them before the startup command.


In case of the 64-bit Infobright binaries we did that by modifying the startup script mysqld-ib and added the following line just before invocation of mysqld command.



LD_PRELOAD_64=/usr/lib/64/libmtmalloc.so;
export LD_PRELOAD_64


What we found was now the response times for each query was more in-line as it was being executed on its own. well not true entirely but you get the point. For a 4 concurrent queries we found that it had improved from like 1X to 2.5X reduction in total execution time.


Similary when we used libumem.so we found the reduction more like 3X when 4 queries were executing concurrently.


LD_PRELOAD_64=/usr/lib/64/libumem.so;
export LD_PRELOAD_64




Definitely something to use for all Infobright installations on OpenSolaris or Solaris 10.


In a following blog post we will see other ways to tune Infobright which are not as drastic as this one but still buys some percentage of improvements. Stay tuned!!






















Wednesday, July 22, 2009

iGen with PostgreSQL 8.4 on Sun Fire X4140

Recently I got access to the refreshed Sun Fire X4140 consisting of 2 x 6-core Opterons with 36GB RAM. Since the release of the final PostgreSQL 8.4 bits I had not tried it out so I downloaded the Solaris 10 binaries of PostgreSQL 8.4 (64-bits) from the download site of postgresql.org and took it for the test drive with the same iGen benchmarks that I had used earlier for my PGCon2009 presentation.


The system already had Solaris 10 5/09 installed with couple of  SSDs  and a RAID LUN for the database. I put the WAL log on an internal drive with ZFS intent log on SSDs and the tablespaces on the RAID LUN (on an external storage array).





Notice the crossing of the 400K tpm boundary with PostgreSQL here using this benchmark toolkit. None of my tests have ever done that before. I consider this to be a milestone achievement with PostgreSQL, Solaris 10, Sun Fire Systems with Opterons.







Monday, July 20, 2009

Olio on 6-core Opterons (Istanbul) based Sun Systems

Sun is launching systems with multisocket  6-core Opterons (Istanbul) today. Last week I got access to  Sun Fire X4140 with 2 x 6-core Opterons with 36GB RAM. It is always great to see such a 1RU system packaged with so many x64 cores.



# psrinfo -vp

The physical processor has 6 virtual processors (0-5)

  x86 (chipid 0x0 AuthenticAMD family 16 model 8 step 0 clock 2600 MHz)

    Six-Core AMD Opteron(tm) Processor 8435

The physical processor has 6 virtual processors (6-11)

  x86 (chipid 0x1 AuthenticAMD family 16 model 8 step 0 clock 2600 MHz)

    Six-Core AMD Opteron(tm) Processor 8435



I decided to take the system for a test drive with Olio. Olio is a Web 2.0 toolkit consisting on a web 2.0 event calendar application  which can help stress a system. Depending on your favorite scripting language you can use either PHP, Ruby on Rails, Java as the language used to create the application. (I took the easy way out and selected Olio PHP's prebundled binary kit)


Please don't let the small 2MB kit size fool you thinking it will be a easy workload to test it out. While setting it up I figured that to generate the data population for say 5000 users you will need space with atleast 500GB disk space for the content that it generates for it. Yes I quickly had to figure out how to get a storage array for Olio with about 800GB LUN.


Olio requires a webserver, PHP (of course) and  a database for its metadata store (it has scripts for MySQL already in the kit). The system came preconfigured with Solaris 10 5/09. I downloaded MySQL 5.4.1 beta  and also the Sun WebStack kit which has Apache Httpd 2.2, PHP 5.2 (and also MySQL 5.1 which had not used since I had already downloaded MySQL 5.4 Beta). Memcached 1.2.5 is part of the WebStack download and Olio is configured to use it also by default (but can be disabled too).


Eventually everything was installed and configured in the same X4140 and using the Faban Harness on another system started executing some runs with file store and the meta store preconfigured to handle all the way up to 5000 concurrent users. The results are as follows:




OlioPHP



Here are my observation/interpretations:


  • Eventually beyond 10 cores run I find that the system memory (36GB) is not enough to sustain more concurrent users to fully utilize the remaining cores. I would probably need RAM  in the range of 48GB or more to handle more users. (PHP is not completely thread-safe and hence the web server used here spawns processes)
  • This 1RU system can handle more than 3200 users  (with everything on the same system) with CPU cycles to spare is pretty impressive. It means you still have enough CPU to log into the system without seeing degraded performance.
  • Actually you can see here that SMP (or should be called  SMC - Scalable Multi Cores) type system helps when the initial cores are added  instead of using multiple single core systems (ala in Cloud).

 In an upcoming blog entries I will talk more about the individual components used.






Tuesday, June 02, 2009

Minimal OpenSolaris 2009.06 Appliance Image for VirtualBox 2.2.4


With the release of the OpenSolaris 2009.06, I thought it is time to update the Minimal OpenSolaris 2008.11  Appliance OVF image that I had created earlier. The script create_osol2009006_app.sh has been updated to create minimal OpenSolaris 2009.06 Appliance images for VirtualBox. 


How to use the OVF image?


  • Download VirtualBox 2.2.4 and install it on your host platform.
  • Download the OpenSolaris 2009.06 App OVF image zip file and then
    unzip it.
  • Fire up Virtualbox GUI and  use menu item VirtualBox->File->Import Appliance to
    import the image (using the  OSOL200906App.ovf file ) into a new VirtualBox VM
  • Start the newly created VM and in few minutes you will be  ready to login
    into OpenSolaris 2009.06 kernel.The preset login information is user: root with password: opensolaris.




Comments welcome.

Friday, May 29, 2009

Read Only Scalability Patch

Simon Riggs of 2nd Quadrant recently submitted a patch for testing which should improve read only scalability of Postgres. I took it for a test drive for my setup. In the first set of tests I used the same benchmark as previous ones so as to have the same reference point.





It seems changing the Number of Buffer Partitions for this workload does not have any impact. My dataset for this iGen benchmark is pretty small and should easily fit under 2GB size and hence may not be stressing the buffer partitions too much to warrant bigger number. The patch still helps to get good healthy 4-6% gain in peak values.




Thursday, May 28, 2009

Postgres 8.4 Testing with new JDBC Drivers

At PGCon 2009, Jesper Pedersen talked to me about the new Binary Transfer patch which was submitted to the JDBC Driver for Postgres 8.4. I thought it will be nice to compare how the JDBC 8.4 driver compared to older 8.3 JDBC Driver. Hence I took it for a drive





The 8.4 JDBC Driver with BinaryTransfer patch seems to get to a better peak faster but since to taper off at high clients. I don't know if this benchmark was the right benchmark for it. Need more benchmarks which uses JDBC to see the performance difference with this feature.



Wednesday, May 27, 2009

Postgres on OpenSolaris using 2x Quad Cores: Use FX Scheduler

During my PGCon 2009 presentation there was a question on the saw tooth nature of the workload results on the high end side of benchmark runs. To which Matthew Wilcox (from Intel) commented it could be scheduler related. I did not give it much thought at that time till today when I was trying to do some iGen runs for the JDBC Binary Transfer patch (more on that in another blog post) and also Simon's read only scalability runs . Then I realized that I was not following one of my one tuning advice for running Postgres on OpenSolaris. The advice is to  use FX Class of scheduler instead of the default TS Class on OpenSolaris . More details on various scheduler classes can be found on docs.sun.com.


Now how many times I have forgotten to do that with Postgres on OpenSolaris I have no idea. But yes it is highly recommended specially on multi-core systems to use FX scheduler class for Postgres on OpenSolaris. How much gain are we talking about? The following graph will give an indication using the default TS scheduler class Vs the FX Scheduler class using the iGen benchmark.



The gain is about 14% by just switching over to FX Class. How did I get Postgres server instance to use FX class? I cheated and put all processes of the user (with userid 236177)  in FX class using the following command line.


# priocntl -s -c FX -i uid 236177


One thing to figure out is how to make sure Postgres uses FX scheduler class out of the box on OpenSolaris so I don't keep forgetting about that minute performance tip.

Thursday, May 21, 2009

PGCon 2009: Performance Comparison of Postgres 8.3 Vs Postgres 8.4


On the first day of PGCon 2009 I presented on my results of my testing with Postgres 8.4beta1 vs the earlier version (8.3.7). The good news is it should not cause any regressions to existing users of 8.3.7 to upgrade and exploit the opportunity to use the new features of Postgres 8.4. 






Comments/Questions welcome.



Monday, May 18, 2009

Postgres 8.4 Lock Wait Statistics Tool

While working on my upcoming presentation for PGCon 2009 on Thursday, I found that sometimes it is misleading to just take one snapshot of locks to figure the hot locks in PostgreSQL workload characterization.


So again starting from one of the DTrace scripts I arrived at pglockwait_84.d


NOTE: It only works with operating systems that support DTrace. I have only tested it on OpenSolaris as of now.


It can either be used to track to summarize all PostgreSQL backends (using '*')  or selected one using process id using 10 second interval. It also prints time so that it can be dumped into a file for post-processing analysis. 


An example output  is show below during dbt-2 runs using PostgreSQL 8.4 beta1.



# ./pglockwait_84.d '*'

2009 May 19 02:52:14 Lock-Id Mode Wait-Time(ms) Count
Dynamic Locks Exclusive 0 5
ProcArrayLock Shared 0 37
Dynamic Locks Shared 1 52
CLogControlLock Exclusive 1 85
BufFreelistLock Exclusive 1 81
CLogControlLock Shared 1 103
ProcArrayLock Exclusive 2 112
BgWriterCommLock Exclusive 10 123
BufMappingLock Exclusive 11 636
XidGenLock Exclusive 17 2
BufMappingLock Shared 34 1566
WALInsertLock Exclusive 49 2305
LockMgrLock Exclusive 65 852

2009 May 19 02:52:24 Lock-Id Mode Wait-Time(ms) Count
XidGenLock Shared 0 1
XidGenLock Exclusive 0 12
ProcArrayLock Shared 1 86
BufFreelistLock Exclusive 4 240
BgWriterCommLock Exclusive 5 213
Dynamic Locks Shared 5 157
CLogControlLock Exclusive 6 238
CLogControlLock Shared 6 384
ProcArrayLock Exclusive 57 360
Dynamic Locks Exclusive 158 7
WALInsertLock Exclusive 187 7837
LockMgrLock Exclusive 226 3251
BufMappingLock Exclusive 289 2141
BufMappingLock Shared 895 5513

2009 May 19 02:52:34 Lock-Id Mode Wait-Time(ms) Count
XidGenLock Shared 0 0
Dynamic Locks Exclusive 0 6
XidGenLock Exclusive 0 5
ProcArrayLock Shared 1 76
BufFreelistLock Exclusive 3 183
BgWriterCommLock Exclusive 4 118
ProcArrayLock Exclusive 5 229
Dynamic Locks Shared 5 91
CLogControlLock Exclusive 29 198
CLogControlLock Shared 62 272
BufMappingLock Exclusive 141 1685
LockMgrLock Exclusive 206 2175
WALInsertLock Exclusive 221 5540
BufMappingLock Shared 279 4180

2009 May 19 02:52:44 Lock-Id Mode Wait-Time(ms) Count
XidGenLock Shared 0 0
Dynamic Locks Exclusive 0 3
XidGenLock Exclusive 0 5
ProcArrayLock Shared 0 67
BgWriterCommLock Exclusive 1 69
BufFreelistLock Exclusive 2 148
CLogControlLock Shared 3 262
CLogControlLock Exclusive 4 199
ProcArrayLock Exclusive 47 277
WALWriteLock Exclusive 64 2
BufMappingLock Exclusive 79 1599
WALInsertLock Exclusive 151 5949
LockMgrLock Exclusive 198 2377
BufMappingLock Shared 223 4345
Dynamic Locks Shared 1568 144
^C


It throws an output every 10 second and the time spent in acquiring the locks. For the BufMappingLock, LockMgrLock and Dynamic Locks it aggregates all of them together respectively. It's bit high on system resources if you track all Postgres backends but if you already know which one then it can be low on overhead. Hope it is useful to you too as I found it for my purpose.





Friday, May 15, 2009

PostgreSQL Transactions Per Second Using Dtrace

 I modified one of Robert's dtrace scripts so that it is  useful for my purpose to measure often asked transactions per second  for random workload running on PostgreSQL.




The script is as follows:


#!/usr/sbin/dtrace -qs
postgresql*:::transaction-start
{
@startpersec["New"] = count();
}
postgresql*:::transaction-commit
{
@commitpersec[ "Commit"] = count();
}
postgresql*:::transaction-abort
{
@abort["Abort"] = count();
}
profile:::tick-1s
{
printf("******** Transactions Per Second *********\n");
printf("%20s %15s\n", "Txn Type", "Count");
printf("==========================================\n");
printa("%20s %@15d\n", @startpersec);
printa("%20s %@15d\n", @commitpersec);
printa("%20s %@15d\n", @abort);
printf("\n");
clear(@startpersec);
clear(@commitpersec);
clear(@abort);
}



UPDATE: You can also download it pgtps.d



When you execute it you see outputs every second as follows:












# ./tps.d
******** Transactions Per Second *********
Txn Type Count
==========================================
New 192
Commit 192
Abort 1

******** Transactions Per Second *********
Txn Type Count
==========================================
New 175
Commit 172
Abort 0

******** Transactions Per Second *********
Txn Type Count
==========================================
New 195
Commit 198
Abort 0

******** Transactions Per Second *********
Txn Type Count
==========================================
New 183
Commit 178
Abort 2





How to interpret the output?



  • New mentions how many transactions started per second

  • Commit talks about how many transactions commited per second.

  • Aborts talks about transactions aborted in that second


Useful specially when some one  asks a questions that they are generally reading from a questionaire like how many transactions per second are we doing?


Where is your TPS report?


Tuesday, April 21, 2009

Try Postgres 8.4 Beta1 using OpenSolaris Appliance for VirtualBox

Postgres 8.4 Beta1 community binaries are now for OpenSolaris 2008.11. The Beta1 binaries for OpenSolaris can be downloaded from postgresql.org binary location. Postgres 8.4 binaries for Solaris 10 are also available.


For people who don't have OpenSolaris installed on their laptop but want to try out the new improved DTrace Probes in Postgres 8.4beta1, you can install the Minimal OpenSolaris Appliance OVF image for VirtualBox 2.2 and install the Postgres 8.4beta1 binaries in the appliance to try it out. You can also use the DTrace probes on your Mac OS X too.


Easiest way to install the binaries on the OpenSolaris Appliance is to first install SUNWwget package from the OpenSolaris repository



pkg install SUNWwget

and then using copy the download mirror url for those binaries using http and download it with wget in the appliance



wget "http://wwwmaster.postgresql.org/redir/198/h/binary/v8.4beta\
/solaris/opensolaris/i386/postgresql-8.4beta1-opensolaris.i386-32.tar.bz2"

The community binaries typically should be untarred in /opt.



bzcat postgresql-8.4beta1-opensolaris.i386-32.tar.bz2 |tar -xf -

This will then have the binaries in /opt/postgres/8.4beta1/. If you also untar the 64-bit binaries then the the 64-bit binaries are available from /opt/postgres/8.4beta1/64.


One thing that I have noticed with these binaries that it does not pick up the libraries if installed in /opt by default so depending on the type of bits you may need to set the following



LD_LIBRARY_PATH=/opt/postgres/8.4beta1/lib; export LD_LIBRARY_PATH

or



LD_LIBRARY_PATH_64=/opt/postgres/8.4beta1/lib/64; export LD_LIBRARY_PATH_64

Beyond that everything should work as you would expect. Well almost... One thing to also note is that the new 8.4 GUC parameter effective_io_concurrency to allow readahead for bitmap scan index scans is disabled on OpenSolaris / Solaris 10.


 If you do find something that doesn't seem to work, please feel free to leave comments.


 


Wednesday, April 15, 2009

Lessons with OpenSolaris Appliances

Going through the comments for Minimal OpenSolaris Appliance entry,  I thought I will go over the problems I encountered when I was working on the create_solaris_appliance.sh script and what I think we can do to improve OpenSolaris in those areas.


1. Setting up the disk


This is probably the first thing that most appliance creators will have to do is to format a new media before it is usable by OpenSolaris. Now to make a disk usable by OpenSolaris (on x86/x64 platforms) two things needs to be done, one a primary Solaris partition needs to be created using fdisk and then a regular Solaris VTOC needs to be initialized on the Solaris partition. While the experience is bit easier with the interactive option of the commands, putting it in a script can be challenging.


Fortunately fdisk has -B option. From the man page:



    -B
Default to one Solaris partition that uses the whole
disk. On an x86 machine, if the disk is larger than 2 TB
(terabytes), the default size of the Solaris partition
will be limited to 2 TB.


Hence I could use the following easily in my script:


fdisk -n -B /dev/rdsk/${INSTALLDISK}p0


Unfortunately I saw no such option for fmthard. Infact it made it more difficult since you need to enter the geometry information of the target disk. I took the easy way out by finding the same for the default VirtualBox disk size which is 16GB and using it as follows:


fmthard -d 0:2:00:48195:33447330 /dev/rdsk/${INSTALLDISK}p0

There are some scripting ways to work this around as Vikram Datta commented on my earlier entry:



  SecCnt=`prtvtoc /dev/rdsk/${INSTALLDISK}p0 | awk '/sectors\/cylinder/ { print $2 }'`
LastSect=`prtvtoc /dev/rdsk/${INSTALLDISK}p0 | awk '$1 == "2" { print $5 }'`
LastSect=`expr $LastSect - $SecCnt`
fmthard -d 0:2:00:${SecCnt}:${LastSect} /dev/rdsk/${INSTALLDISK}p0


But I think it should be as easy as  fdisk.. i.e. doing the following:


fmthard -B /dev/rdsk/${INSTALLDISK}p0

Hence I have filed a new RFE 6829475. I think this RFE is useful not just for my script but in general helps improve usability of the command to a new learner of OpenSolaris.


2. Setting up the ZPool


The next step of creating zpool for the root device was pretty straight forward


zpool create -f rpool ${INSTALLDISK}s0
zfs set compression=on rpool
zfs create -o mountpoint=legacy rpool/ROOT
zfs create -o mountpoint=$PKG_IMAGE rpool/ROOT/VOSApp
zfs create -V 128M rpool/swap
zfs create -V 16M rpool/dump
zfs create rpool/ROOT/VOSApp/opt
zfs create rpool/ROOT/VOSApp/var
zfs create rpool/export
zpool set bootfs=rpool/ROOT/VOSApp rpool

Here I took liberty of separating out /opt /var into separate dataset so that I can enable zfs snapshots just for "optional" and "variable" data of the applications. This is a point of view of deployment. Your view may be different here.


3. Setting up the packages from OpenSolaris repository


The next step on how to use OpenSolaris repository to install the pacakges. Alex Eremin had great pointers on his blog that I adapted . The initial setup can be easily done by exporting the PKG_IMAGE environment variable to the directory where you are currently mounting the zpool and then using pkg image-create command.


pkg image-create -f -F -a opensolaris.org=http://pkg.opensolaris.org/release $PKG_IMAGE
pkg refresh

Then  I played with the package list over and over again to get a minimal size with most network adapter drivers to get on the internet and all required pacakges to allow "pkg" command to work. Of course this is the piece that took me quite a bit of trial and error to figure out the right mix of pacakges so OpenSolaris does boot up successfully and allow "pkg" to run successfully.


pkg install SUNWcsd
pkg install SUNWcs
pkg install SUNWcar SUNWcakr SUNWkvm SUNWos86r SUNWrmodr \
SUNWpsdcr SUNWpsdir SUNWcnetr SUNWesu SUNWkey SUNWnfsckr \
SUNWnfsc SUNWgss SUNWgssc SUNWbip SUNWbash SUNWloc SUNWsshcu \
SUNWsshd SUNWssh SUNWtoo SUNWzfskr SUNWipf SUNWintgige SUNWipkg \
SUNWadmr SUNWadmap SUNWPython SUNWperl584core SUNWgrub SUNWxcu6\
SUNWxcu4 SUNWgawk SUNWgtar SUNWgnu-coreutils SUNWscp SUNWfmd \
SUNWxge SUNWbge SUNWnge SUNWrge SUNWrtls \
SUNWixgb SUNWchxge SUNWzfs-auto-snapshot SUNWsolnm

I did realize one thing.. Loading one package at a time with pkg is awfully slow. By putting all the packages within one line (except for SUNWcsd and SUNWcs) I could cut down the time from hours to minutes. This was my "Eureka" moment when I could install the packages to a bare-metal .. well a bare-virtualbox VDI within 10 minutes.


4. Setting up the SMF Database on the system


cp $PKG_IMAGE/lib/svc/seed/global.db $PKG_IMAGE/etc/svc/repository.db
chmod 0600 $PKG_IMAGE/etc/svc/repository.db
chown root:sys $PKG_IMAGE/etc/svc/repository.db

# setup smf profiles
ln -s ns_files.xml $PKG_IMAGE/var/svc/profile/name_service.xml
ln -s generic_limited_net.xml $PKG_IMAGE/var/svc/profile/generic.xml
ln -s inetd_generic.xml $PKG_IMAGE/var/svc/profile/inetd_services.xml
ln -s platform_none.xml $PKG_IMAGE/var/svc/profile/platform.xml
# Set the environment variables for svccfg.
SVCCFG_DTD=${PKG_IMAGE}/usr/share/lib/xml/dtd/service_bundle.dtd.1
SVCCFG_REPOSITORY=${PKG_IMAGE}/etc/svc/repository.db
SVCCFG=/usr/sbin/svccfg
export SVCCFG_DTD SVCCFG_REPOSITORY SVCCFG

${SVCCFG} import ${PKG_IMAGE}/var/svc/manifest/milestone/sysconfig.xml

Again this is one area I think OpenSolaris can improve a bit. It does some amount of research (google, Alex Eremin) before understanding how to set it up properly. (But then I am not a kernel engineer.)



5. Other Miscellaneous but important stuff to get a bootable system


The following are basically hacks in some ways to get to a bootable system. 


# Set TimeZone
echo "$TIMEZONE" >⁞ $PKG_IMAGE/etc/TIMEZONE
echo $HOSTNAME > $PKG_IMAGE/etc/nodename

# configure our new /etc/vfstab
printf "rpool/ROOT/VOSApp -\t/\t\tzfs\t-\tno\t-\n" >> $PKG_IMAGE/etc/vfstab
printf "/dev/zvol/dsk/rpool/swap\t-\t-\t\tswap\t-\tno\t-\n" >> $PKG_IMAGE/etc/vfstab
chmod a+r $PKG_IMAGE/etc/vfstab

# turn off root as a role
printf "/^root::::type=role;\ns/^root::::type=role;/root::::/\nw" |\
ed -s $PKG_IMAGE/etc/user_attr
# Edit etc/ssh/sshd_config to allow ssh to root account
printf "/^PermitRootLogin no\ns/^PermitRootLogin no/PermitRootLogin yes/\nw" |\
ed -s ${PKG_IMAGE}/etc/ssh/sshd_config

# Generate ssh keys
ssh-keygen -t dsa -f $PKG_IMAGE/etc/ssh/ssh_host_dsa_key -N ''
ssh-keygen -t rsa -f $PKG_IMAGE/etc/ssh/ssh_host_rsa_key -N ''



6. Finally the boot archive and  grub


# configure /dev in the new image
devfsadm -R $PKG_IMAGE
bootadm update-archive -R $PKG_IMAGE
$PKG_IMAGE/boot/solaris/bin/update_grub -R $PKG_IMAGE
# For zfs root, menu.lst has moved to /rpool/boot/grub/menu.lst. #
# create the new real grub menu
cat <⁞<-EOF > /rpool/boot/grub/menu.lst
default 0
timeout 10
splashimage /boot/grub/splash.xpm.gz
title  Appliance based on OpenSolaris 2008.11
findroot (pool_rpool,0,a)
bootfs rpool/ROOT/VOSApp
kernel\$ /platform/i86pc/kernel/\$ISADIR/unix  -B \$ZFS-BOOTFS
module\$ /platform/i86pc/\$ISADIR/boot_archive
EOF
# make the grub menu files readable by everyone.
chmod a+r $PKG_IMAGE/boot/grub/menu.lst
chmod a+r /rpool/boot/grub/menu.lst
# setup /etc/bootsign so that grub can find this zpool
dir -p /rpool/etc>
echo pool_rpool > /rpool/etc/bootsign
zfs set mountpoint=/ rpool/ROOT/VOSApp
zfs set compression=off rpool



Hope this makes it easier for  someone thinking of making their own appliances based on OpenSolaris.


NOTE: There was fair number of people who did download the images. I did accidently lose the OpenSolaris image once after it was downloaded 75 times.  But you can track the images directly at mediacast.sun.com/tags/appliance






Sunday, April 12, 2009

Openbravo ERP 2.40 Appliance using Postgres 8.3 appliance with OpenSolaris OVF

Few days ago I talked about a Postgres 8.3 Appliance based on OpenSolaris. Today lets look at how to use that appliance image to get an Openbravo ERP 2.40 appliance based on OpenSolaris in VirtualBox.


Download the Postgres 8.3 Appliance OVF image and unzip the two files. Fire up VirtualBox 2.2 and use File->Import Appliance and point it to the .ovf file  from the zip file. Change the networking from NAT to "Bridged Network" and start the VM and soon you get "postgresdb login:" screen.  Use root/opensolaris to login into the system and verify that postgres instance is already running as follows:


# svcs -a |grep postgres
disabled       19:08:00 svc:/application/database/postgresql_83:default_64bit
online         19:08:23 svc:/application/database/postgresql_83:default_32bit


The default options of postgresql.conf are pretty low so bump them up slightly


# vi /var/postgres/8.3/data/postgresql.conf


shared_buffers=128MB

wal_buffers=128kB

checkpoint_segments=16

listen_addresses='*'

# svcadm restart svc:/application/database/postgresql_83:default_32bit


 Now import other required dependencies for Openbravo ERP 2.40



# pkg install SUNWj6dev SUNWant SUNWtcat
DOWNLOAD                                    PKGS       FILES     XFER (MB)
SUNWj6dev                                    0/4     25/4756    1.08/84.90


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

 Now download Openbravo ERP 2.40 installer as follows:


# pkg install SUNWwget


# wget "http://voxel.dl.sourceforge.net/sourceforge/openbravo/OpenbravoERP_2.40-solaris-intel-installer.bin"


# chmod a+x OpenbravoERP_2.40-solaris-intel-installer.bin


# ./OpenbravoERP_2.40-solaris-intel-installer.bin


 And use the following options:




  • /opt/OpenbravoERP | /var/OpenbravoERP/AppsOpenbravo/attachments

  • Complete |Standard | /usr/jdk/latest | /usr/bin/ant 

  • /var/apache/tomcat

  • PostgreSQL

  • /usr/postgres/8.3/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

Now from any other machine (or host machine) fire a browser and enter the IP address of the VM with port 8080 and uri openbravo and you now have a virtual VM with Openbravo running


http://myVMipaddress:8080/openbravo


The login screen for Openbravo should appear. Use
Openbravo as username and openbravo (all lower case) as password to
login and set it up for your business.







Thursday, April 09, 2009

Minimal OpenSolaris Appliance OVF image for VirtualBox 2.2

One of the things that I always miss in OpenSolaris is text install and the primary reason for using this is in VirtualBox where I want to install just enough OpenSolaris to do a particular task with it like Database Server, Drupal, Java Application Server, etc (but more server like tasks). I really don't need GNOME or any other desktop utilities and it is primary a waste of space if I really have to do it for multiple VMs.


I have created a new OVF image which is less than 270MB size and can be used to play with the OpenSolaris kernel in VirtualBox 2.2 and if you like it can install the rest of the OpenSolaris Desktop to get to the default full blown installation of OpenSolaris 2008.11


Download the OpenSolaris App OVF image, unzip it and just use VirtualBox->File->Import Appliance to import the image into a new VirtualBox VM and you are ready to boot into OpenSolaris kernel. The preset login information is user: root with password: opensolaris.



TIP: If you like this minimal install and would like to try out the full fledge Desktop install just execute the following command


# pkg install slim_install

This will take a long time since it installs packages of another 600MB on top of the installation. But it will be pretty much the same set of packages that comes with OpenSolaris 2008.11 CD.  (Actually I expected "# pkg install entire" to do the intended task but unfortunately it just installed "entire" and not its dependencies. Must be a bug somewhere with "entire". )

 If you are interested in your own custom OVF image, here is how I have created the image:



  • For the time being this requires the  OpenSolaris 2008.11 CD image (650+ MB) only to execute my script create_solaris_appliance.sh which is about 5KB.
  • Download Virtualbox and install it
  • Create a New Virtual Box VM with following parameters in the Wizard Screen

    • Call it  My OpenSolaris Appliance
    • Select OS Type "OpenSolaris" 
    • Base Memory  768MB or increase it to 1GB if you can spare your RAM for it
    • Create a New Dynamic Expanding Image with exactly 16.00 GB (Any other size may not work)

  • Once the VM is created, immediately  click the blue Network link and modify it to select a "Bridged Network"  (in the setup make sure it is connected to the active host interface - wired or wireless depending on the host system)
  • Also Click the CD-ROM image and point it to the osol-200811.iso image that you downloaded earlier
  • Boot up the VM and select the first LiveCD option in the Grub Menu options
  • Select through the defaults to get the full Gnome Desktop
  • Open Firefox in the VM and make  sure your VM  has internet access
  • Open a terminal window and execute the following commands in sequence

    • wget "http://blogs.sun.com/jkshah/resource/create_solaris_appliance.sh"
    • time pfexec sh -x create_solaris_appliance.sh
    • pfexec halt

  • At this point stop the VM and disconnect the CD image connected to the VM and use VirtualBox->File->Export Appliance Wizard to export the image
It takes about 12 minutes to create an image of about 270MB using the above steps with a decent internet connection and decent desktop.


If you want to own customized Image, take a look at create_solaris_appliance.sh and create_pg_appliance.sh and create your own versions of it for say Drupal, Ruby, Glassfish, etc.



Wednesday, April 08, 2009

Postgres 8.3 Appliance for VirtualBox 2.2

With the release of VirtualBox 2.2 today, exporting and importing appliances becomes lot easier. The new features of VirtualBox 2.2 includes import and export of appliances based on OVF or Open Virtualization Format. 


One of  my earlier blog entry talks about creating appliances. Using the same script mentioned in the earlier blog entry,  I now have a OVF Image  which will work with VirtualBox 2.2  "Import Appliance"  and make it easier to try a virtual PostgreSQL 8.3 database appliance running OpenSolaris under the cover.


The PostgreSQL 8.3 Appliance Image is less than 330MB (unlike a full install of OpenSolaris which could take more than 1500MB).  Just unzip the files in a directory and import them through VirtualBox 2.2 using File->Import Appliance Wizard.  Since an appliance (database in this case) is typically accessed through external application servers, you will want to use Bridge Network instead of NAT.  Detailed instructions to make it accessible (both VirtualBox VM as well as PostgreSQL 8.3)  from external clients are published on Postgres 8.3 Appliance page.


Finally  it is lot easier to finally see the following grub menu on your own VirtualBox 2.2:



 Also for PostgreSQL users/developers who are not familiar with OpenSolaris,  this VM  allows you to try the DTrace probes and ZFS snapshots with PostgreSQL 8.3. More information related to it is  coming soon on Postgres 8.3 Appliance page. Also the PostgreSQL DTrace Toolkit 2009.03.29 is available on PGFoundry.org.


Tip: You can also use my PostgreSQL Monitor Demo to connect to it:











As usual if you have questions, I will be happy to answer them.