hadzramin dot com

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.

scottrbailey

One of the things I spoke about last year, and this year again at the Developer meeting, was what should be our highest priorities for technical development to drive PostgreSQL adoption. That is, what features would make PostgreSQL attractive to the greatest number of users, or remove obstacles to adoption for the most people.

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.

I got the opportunity to test out some of the new Fusion-io Solid State ioDrive, and I thought I would post some results.

Fusion-io has created a SSD product called ioDrive that is based on PCIe cards vs replacing SAS or SATA drives with SSD directly. This approach allows for much lower latency because of the use of the PCIe bus vs traditional disk channels geared towards slow disk. The 320GB model I used in my test are made of Multi Level Cell (MLC) NAND flash and are quoted by Fusion-io to achieve throughput somewhere in the 70k IOPS neighborhood.

For this test I used two identical Dell 2970 boxes, one using a 6 disk RAID 10 disk, and the other using a single Fusion-io 320GB NAND flash PCIe card. Here are the important configuration items:
- Dell 2970 2u
- (6) SAS disk RAID 10 with Perc6i controller or Fusion-io 320GB PCIe ioDrive.
- 32GB RAM
- Quad-Core AMD Opteron(tm) Processor 2347 HE 1895 MHZ 512 KB Cache
- SuSE linux; 2.6.16.46-0.12-smp x86_64
- VxFS file system with 8k block size and cached I/O
- PostgreSQL 8.2.4 with 2GB buffer cache and fsync=on
- All data on the same mount point; /data

The test I used is a custom set of pgbench scripts that represent a real world workload. The script is launched from a third host and is not run on the database host itself. The test is about 80% reads and about 20% writes. The test does not perform deletes, just select,insert,update. Typical queries are index range scan type queries where multiple rows are fetched per result set.

The performance was measured using pgstat.

The test results are shown below. Some interesting things to note:
- Notice about 400% peak improvement in performance using SSD.
- Notice at about 25 concurrent backends the machine with SSD starts to degrade.
- Notice at about 100 concurrent backends the machine with disk starts to degrade.

When considering SSD there are some new things to think about vs traditional disk. In this test I used RAID10 for SAS drives and a single Fusion-io 320GB card. Unfair? Perhaps a bit, but one thing to consider is that SSD is more reliable than traditional disk even though it has a limited lifetime. Another thing to consider is the machine with SSD does not need as much RAM because the disk is so fast. So comparing disk to SSD directly is not always a perfect comparison. In the real world I would run 8GB of RAM on the SSD machine, and perhaps run RAID1 of 2 cards. Here is a white-paper outlining some of the differences. One other item to note is because SSD’s lifetime is effected by number of writes being performed to the drive. So RAID5 while economical could cause premature end of lifetime (writing all the parity).

The Fusion-io cards are simple to install and configure. The drivers are available on

vxddladm addforeign path=/dev/fioa
vxdisk scandisks
vxdg init dbdg fio=fiob cds=off
vxassist -g dbdg -p maxsize
vxassist -g dbdg make fusionA 313563136
mkfs.vxfs /dev/vx/rdsk/dbdg/fusionA -o bsize=4096,largefiles
mount -t vxfs /dev/vx/dsk/dbdg/fusionA /data

Last week I got a lot of positive feedback from my PGCon presentation in Ottawa about how to benchmark systems at a low-level when the intended application is to run a database. There were three main topics I was trying to cover in that:Why you should always run your own hardware benchmarks on every piece of hardware you canExamples of the simplest benchmarks I’ve found to be accurateHow do

One thing that is pretty neat about windowing functions in PostgreSQL 8.4 aside from built-in windowing functions (row_number(), rank(), lead(), lag(), dense_rank(), percent_rank(), cume_dist(), first_value, last_value, nth_value) as documented in the manual Windowing Functions is that you can use windows with most aggregate functions (built-in or custom defined) as well as define your own specific windowing functions. In a later article, we’ll demonstrate creating custom windowing functions.

In our PGCon 2009 PostGIS presentation one of the last slides demonstrates using lead() and lag() windowing functions
to show a family’s income level in the same record with the income levels of the next door neighbors in the fictitious town we created. This is not terribly useful unless you live
in a somewhat dysfunctional neighborhood where everyone is concerned about how rich their neighbors are compared to themselves. Our town was very dysfunctional but mostly geographically dysfunctional. We will have much more useful use cases of this as applied to GIS in our upcoming PostGIS in Action book.

Hitoshi Harada and David Fetter did a presentation of this in PGCon 2009 which sadly we missed since we were giving our own presentation.
Check out the PGCon2009 PostgreSQL 8.4 Windowing Functions Video. Also check out the slides at Introducing Windowing Functions.

Those who have used SQL Server 2005+, Oracle or IBM DBII are probably familar or have run into examples of Windowing functions in those products. Windowing in PostgreSQL 8.4 works more or less the same way. In a prior article, we demonstrated how to return running totals and sums using rudimentary SQL.
To precelebrate the eminent arrival of PostgreSQL 8.4 and the current PostgreSQL 8.4 beta 2 release, we shall demonstrate the same exercise using the new ANSI SQL:2003 Windowing functionality built
into the upcoming PostgreSQL 8.4.

Continue reading “Running totals and sums using PostgreSQL 8.4 Windowing function”

I can’t really compare PgCon 2009 with previous years versions, last time I
enjoyed the event it was in 2006, in Toronto. But still I found the
experience to be a great one, and I hope I’ll be there next year too!

I’ve met a lot of known people in the community, some of them I already had
the chance to run into at Toronto or Prato, but this was the first time I
got to talk to many of them about interresting projects and ideas. That only
was awesome already, and we also had a lot of talks to listen to: as others
have said, it was really hard to get to choose to go to only one place out
of three.

I’m now back home and seems to be recovering quite fine from jet lag, and I
even begun to move on the todo list from the conference. It includes mainly
Skytools 3 testing and contributions (code and documentation),
Extension Packaging work (Stephen Frost seems to be willing to help, which I
highly appreciate) begining with search_path issues, and posting some
backtrace to help fix some SPI_connect() bug at _PG_init() time in an
extension.

The excellent lightning talk about How not to Review a Patch by Joshua
Tolley took me out of the dim, I’ll try to be bright enough and participate
as a reviewer in later commit fests (well maybe not the first next ones as
some personal events on the agenda will take all my "free" time)…

Oh and the Golconde presentation gave some insights too: this queueing based
solution is to compare to the listen/notify mechanisms we already have in
PostgreSQL, in the sense that’s it’s not transactional, and the events are
kept in memory only to achieve very high distribution rates. So it’s a very
fine solution to manage a distributed caching system, for example, but not
so much for asynchronous replication (you need not to replicate events tied
to rollbacked transactions).

So all in all, spending last week in Ottawa was a splendid way to get more
involved in the PostgreSQL community, which is a very fine place to be
spending ones free time, should you ask me. See you soon!