It’s great to see the PostgreSQL team continuing to deliver high quality releases with great improvements. While I see many features and performance improvements – this gem in the changelog cought my eye:
“Add native LDAP authentication (Magnus Hagander)
This is particularly useful for platforms that do not support PAM, such as Win32.”
LDAP can be a really cool way to keep your users and data in an open and non-proprietary form and tie services and user accounts together across a heterogeneous network. Kudos to the PGSQL team for adding this and a host of other great features!
Correct me if I’m wrong, but the TPC top ten is nothing more than a money race.
#1 cost nearly $12 million
#10 cost ~$4 million
I other words, unless you feel like donating a huge chunk of money/lots of hardware, you’re NOT going to see PostgreSQL on that list. Nor is that list any indicator of performance to expect. Nor does any *smart* company base their purchasing decision on that *obviously* skewed list.
That’s the wrong list to look at, the TPC-C Price/Performance list is more accurate for real world scenarios (where price of the overall system is more of a factor):
The #6 system is a reasonable $27,500, and at ~18,000 transactions/minute is blistering fast for that price point.
The FAQ has more details (http://www.tpc.org/tpcc/faq.asp) and anyone is free to submit their own system for benchmarking. The only skewing factor is this point in the FAQ:
“Second, vendors choose to run benchmarks that provide their customer base with results relevant to those customers. Third, vendors may choose to run one benchmark over another because they believe one gives them better performance or price/performance than another benchmark.”
The #6 system is a reasonable $27,500, and at ~18,000 transactions/minute is blistering fast for that price point.
18,000 transactions/minute? It’s not that much – our HP DL385 (using a low-end HP MSA1500) clocked 33,059 transactions/minute. This was with EnterpriseDB 8.1, btw. I guess it did cost a little more than $27,000 though..don’t recall how much exactly.
The TPC-C benchmark spec, and the full report on one of the benchmarks, make interesting reading.
Taking the Dell PowerEdge at position 2 in the list, the most expensive component, at 53% of the total cost, is the SCSI disk subsystem, with 56 SCSI drives, each 36 Gb, in a RAID 0 configuration. The benchmark spec requires 60 days’ data to be stored, so storage is always going to be a major part of the expense. The MS software licenses and support are only 18% of the total cost of the system. The requirement for 3 years 24 x 7 support appears to have been interpreted liberally for the MS software, as “one incident” at $245.
The benchmark is designed for a DB running orders, payments and warehousing for a business – quite a large business, if it is processing tens of thousands of transactions per minute.
Many of us will be interested in a different workload, where ACID and RAID are essential, but a pair of disks will suffice, and writes are much less frequent than reads – as the backend of CMS or web hosting facility, for example.
For such a workload, we really need a different benchmark. The price advantage of PostgreSQL will make a much bigger difference to the final performance/price ratio.
One of the the new features I like the most is the INSERT … VALUES (…), (…), statment. It will make it easier to move data from MySQL to Postgresql as this is the form that MySQL uses to dump out data to SQL.
To the Postgresql team – Thanks for a great product!
One of the the new features I like the most is the INSERT … VALUES (…), (…), statment. It will make it easier to move data from MySQL to Postgresql as this is the form that MySQL uses to dump out data to SQL.
This is misleading. There are *much* more things to care to convert from MySQL dump to PostgreSQL dump, and running psql on MySQL dump will never be perfect. You need a conversion script anyway.
And there had been a nice conversion script for a long time, namely mysql2pgsql. As one will run mysql2pgsql (or similar) anyway, in the context of migrating dump, it doesn’t matter whether new PostgreSQL supports particular MySQL syntax or not.
What a great contribution, adding support for DTrace.
This can only make Postgres better, faster, since now they have the system exposed to a tool like DTrace. It will let the folks that run Postgres on Solaris in the wild able to run monitoring scripts when things go bad for better feedback for themselves, and to the Postgres developers.
While DTrace is limited still to Solaris (I think — no, wait, didn’t Apple put it in Leopard??), it will help everyone in the Postgres community long term simply because it lets folks monitor production systems that much easier.
While DTrace is limited still to Solaris (I think — no, wait, didn’t Apple put it in Leopard??), it will help everyone in the Postgres community long term simply because it lets folks monitor production systems that much easier.
I’m quite excited about these DTrace probes. I upgraded to Solaris 10 specifically to use DTrace to measure and tune Postgresql. Even without custom probes inside of Postgresql, I was able to correlate high level measurements (queries, simultanious sessions) with very low level measurements (disk head movements as contention, OS filesystem cache hits/misses).
With Sun choosing PostgreSQL for their database of choice, is there a ‘tracking’ on what contributions they’ve made back? I’m assuming that Sun is investing money into improving its scalability and so forth; is there a roadmap etc?
As both a member of the PostgreSQL Core Team and Sun PostgreSQL Lead, I think I need to respond to the questions regarding Sun’s contributions to PostgreSQL, as well as benchmarking, because the two are related.
Most companies that join the PostgreSQL community take a while to “ramp up” their contributions, and Sun is no exception. However, they do pay my salary and I spend 60% of my time on PostgreSQL PR and performance, so that’s a start. Further, in addition to Solaris stuff (DTrace, Sun Cluster, Zones, ZFS, etc.) Sun is also helping me with PostgreSQL benchmarking.
Maybe most people don’t know it, but Sun actually has one of the larger and more experienced benchmarking teams in the business. We are already working on TPC and Spec benchmarks, but these things take time — a lot of time. Expect to see good things in the next year, but don’t wait on one foot.
It’s great to see the PostgreSQL team continuing to deliver high quality releases with great improvements. While I see many features and performance improvements – this gem in the changelog cought my eye:
“Add native LDAP authentication (Magnus Hagander)
This is particularly useful for platforms that do not support PAM, such as Win32.”
LDAP can be a really cool way to keep your users and data in an open and non-proprietary form and tie services and user accounts together across a heterogeneous network. Kudos to the PGSQL team for adding this and a host of other great features!
It’s also good for platforms like OS X.
Does someone know if there’s some form and report designer for Postgres (linux version)?
Check out glom: http://gnomefiles.org/app.php/Glom
Rekall. Does forms, reports, and has built in support for python scripts.
8.2 performs very well.
http://tweakers.net/reviews/657/6
When they get an entry in the top 10 over on TPC, they’ll be taken seriously in the enterprise RDBMS sector.
It’s worth mentioning that I’m a huge Postgre fan…for small projects where price is a large factor.
Correct me if I’m wrong, but the TPC top ten is nothing more than a money race.
#1 cost nearly $12 million
#10 cost ~$4 million
I other words, unless you feel like donating a huge chunk of money/lots of hardware, you’re NOT going to see PostgreSQL on that list. Nor is that list any indicator of performance to expect. Nor does any *smart* company base their purchasing decision on that *obviously* skewed list.
http://www.tpc.org/tpcc/results/tpcc_perf_results.asp?resulttype=al…
That’s the wrong list to look at, the TPC-C Price/Performance list is more accurate for real world scenarios (where price of the overall system is more of a factor):
http://www.tpc.org/tpcc/results/tpcc_price_perf_results.asp
The #6 system is a reasonable $27,500, and at ~18,000 transactions/minute is blistering fast for that price point.
The FAQ has more details (http://www.tpc.org/tpcc/faq.asp) and anyone is free to submit their own system for benchmarking. The only skewing factor is this point in the FAQ:
“Second, vendors choose to run benchmarks that provide their customer base with results relevant to those customers. Third, vendors may choose to run one benchmark over another because they believe one gives them better performance or price/performance than another benchmark.”
The #6 system is a reasonable $27,500, and at ~18,000 transactions/minute is blistering fast for that price point.
18,000 transactions/minute? It’s not that much – our HP DL385 (using a low-end HP MSA1500) clocked 33,059 transactions/minute. This was with EnterpriseDB 8.1, btw. I guess it did cost a little more than $27,000 though..don’t recall how much exactly.
It should be noted that there are currently no PostgreSQL benchmarks at all on the TPC list.
The TPC-C benchmark spec, and the full report on one of the benchmarks, make interesting reading.
Taking the Dell PowerEdge at position 2 in the list, the most expensive component, at 53% of the total cost, is the SCSI disk subsystem, with 56 SCSI drives, each 36 Gb, in a RAID 0 configuration. The benchmark spec requires 60 days’ data to be stored, so storage is always going to be a major part of the expense. The MS software licenses and support are only 18% of the total cost of the system. The requirement for 3 years 24 x 7 support appears to have been interpreted liberally for the MS software, as “one incident” at $245.
The benchmark is designed for a DB running orders, payments and warehousing for a business – quite a large business, if it is processing tens of thousands of transactions per minute.
Many of us will be interested in a different workload, where ACID and RAID are essential, but a pair of disks will suffice, and writes are much less frequent than reads – as the backend of CMS or web hosting facility, for example.
For such a workload, we really need a different benchmark. The price advantage of PostgreSQL will make a much bigger difference to the final performance/price ratio.
One of the the new features I like the most is the INSERT … VALUES (…), (…), statment. It will make it easier to move data from MySQL to Postgresql as this is the form that MySQL uses to dump out data to SQL.
To the Postgresql team – Thanks for a great product!
One of the the new features I like the most is the INSERT … VALUES (…), (…), statment. It will make it easier to move data from MySQL to Postgresql as this is the form that MySQL uses to dump out data to SQL.
This is misleading. There are *much* more things to care to convert from MySQL dump to PostgreSQL dump, and running psql on MySQL dump will never be perfect. You need a conversion script anyway.
And there had been a nice conversion script for a long time, namely mysql2pgsql. As one will run mysql2pgsql (or similar) anyway, in the context of migrating dump, it doesn’t matter whether new PostgreSQL supports particular MySQL syntax or not.
http://gborg.postgresql.org/project/mysql2psql/projdisplay.php
Sorry if you felt mislead, having the new insert statement is of course not everything that is needed.
Neither is the mysql2psql perl script.
You would may also want to add some mysql specific functions to postgresql. You can find such a library at
http://pgfoundry.org/projects/mysqlcompat/
The new insert statement is still valuable to users that not have perl installed (e.g windows users)
Having this new insert statment, will also make it possible to make conversion scripts that runs faster especially for large data sets.
Getting closer and closer to the point of changing from MySQL to PostgreSQL, but not quite there yet.
A short summary from pgsql-announce:
Among the features of this new version are:
— Higher performance (+20% on OLTP tests)
— Improved Warm standby databases
— Online index builds
— SQL2003 aggregates
— Improved TSearch2 with Generalized Inverted Indexes
— Support for DTrace probes
— Advisory Locks
— New ISN/ISBN and pgCrypto modules
— Selective pg_dump options
… and many more included in the over 300 patches which went into this version.
— Support for DTrace probes
What a great contribution, adding support for DTrace.
This can only make Postgres better, faster, since now they have the system exposed to a tool like DTrace. It will let the folks that run Postgres on Solaris in the wild able to run monitoring scripts when things go bad for better feedback for themselves, and to the Postgres developers.
While DTrace is limited still to Solaris (I think — no, wait, didn’t Apple put it in Leopard??), it will help everyone in the Postgres community long term simply because it lets folks monitor production systems that much easier.
While DTrace is limited still to Solaris (I think — no, wait, didn’t Apple put it in Leopard??), it will help everyone in the Postgres community long term simply because it lets folks monitor production systems that much easier.
Also available for FreeBSD: http://people.freebsd.org/~jb/dtrace/
“(I think — no, wait, didn’t Apple put it in Leopard??)”
I think you are recalling some rumors/evidence that Apple might be porting Sun’s ZFS filesystem. There is a DTrace port for FreeBSD in the works.
http://people.freebsd.org/~jb/dtrace/
I’m quite excited about these DTrace probes. I upgraded to Solaris 10 specifically to use DTrace to measure and tune Postgresql. Even without custom probes inside of Postgresql, I was able to correlate high level measurements (queries, simultanious sessions) with very low level measurements (disk head movements as contention, OS filesystem cache hits/misses).
It can’t wait to see what is possible now.
It *is* in Leopard, apparently – see http://developer.apple.com/leopard/overview/ (under Xray).
“It *is* in Leopard, apparently”
Wow, I’d hadn’t heard about that. Dang. I may have to by a Mac one of these days.
With Sun choosing PostgreSQL for their database of choice, is there a ‘tracking’ on what contributions they’ve made back? I’m assuming that Sun is investing money into improving its scalability and so forth; is there a roadmap etc?
As both a member of the PostgreSQL Core Team and Sun PostgreSQL Lead, I think I need to respond to the questions regarding Sun’s contributions to PostgreSQL, as well as benchmarking, because the two are related.
Most companies that join the PostgreSQL community take a while to “ramp up” their contributions, and Sun is no exception. However, they do pay my salary and I spend 60% of my time on PostgreSQL PR and performance, so that’s a start. Further, in addition to Solaris stuff (DTrace, Sun Cluster, Zones, ZFS, etc.) Sun is also helping me with PostgreSQL benchmarking.
Maybe most people don’t know it, but Sun actually has one of the larger and more experienced benchmarking teams in the business. We are already working on TPC and Spec benchmarks, but these things take time — a lot of time. Expect to see good things in the next year, but don’t wait on one foot.
–Josh Berkus
I’ve really looked forward to this new release — and especially this new feature called hstore. And congratulations to the PostgreSQL developers.
I run this database on several FreeBSD servers, and it certainly performs very well.