SQL

PostgreSQL 8.3 - Finally Released!

Tagged:  

After much waiting ProstgreSQL 8.3 has been released. This is a much better performing release from version 8.2. You can find some benchmarks that I wrote about previously here.

Below is an excerpt from the press release:

4 February 2008, New York, NY: The PostgreSQL Global Development Group today announced the release of version 8.3 of the high-performance object-relational database management system. This release includes a record number of new and improved features which will greatly enhance PostgreSQL for application designers, database administrators, and users, with more than 280 patches by dozens of PostgreSQL contributors from 18 countries.

"The continued evolution of the open source PostgreSQL database gives users the option of deploying a non-proprietary database, allowing them to save money, improve performance and increase productivity. PostgreSQL 8.3 is an impressive new release and we encourage customers around the world to explore it," said Rich Green, executive vice president of software at Sun Microsystems.

Version 8.3 provides greater consistency of performance than previous versions, ensuring that every user can depend on the same high performance demonstrated in recent benchmarks for every transaction, whether in peak hours or not, seven days a week, 52 weeks per year. Major performance enhancements include:

* Heap Only Tuples (HOT), which eliminate up to 3/4 of the maintenance overhead of frequently updated tables
* Spread checkpoints and background writer autotuning, which reduce the impact of checkpoints on response times
* Asynchronous commit option for much faster response times on some transactions

These changes also significantly accelerate transaction processing throughput, between 5% and 30%, depending on the workload.

"PostgreSQL just got smarter," said Bruce Momjian, PostgreSQL Core Team member.

PostgreSQL is the first open source database to implement Synchronized Scan, which greatly reduces I/O for data mining. The Windows team has enabled Visual C++ compilation of PostgreSQL, improving stability and performance on Windows, as well as accessibility to the project to Windows contributors. New logging options have been added and the overhead of the statistics collector has been diminished in order to make it easier to monitor your servers.

Of course, this wouldn't be a proper PostgreSQL release without providing lots of new features for application developers, including:

  • ANSI-standard SQL/XML support, including XML export
  • Text search: our advanced full text search tool, TSearch2, has been merged into the core distribution with better management and new dictionaries and languages
  • GSSAPI and SSPI authentication support
  • New data types: UUIDs, ENUMs and arrays of composite types

"We process over 18,000 queries per second on over 300GB of user data on our PostgreSQL servers, and those numbers go up every month," said Gavin Roy, CTO of social networking site MyYearbook.com. "We're very excited about HOT, shared buffer scan improvements and integrated tsearch indexing. Preliminary testing shows that 8.3 will improve our database load profile while decreasing response times. We're really looking forward to it."

In addition to the many new core features, several new optional components have matured and released 1.0 versions during the 8.3 release cycle. These include SNMP support, horizontal scalability options such as PL/Proxy, pgPool-II, and Bucardo, a graphical debugger for stored procedures, and a scalable connection pooler called pgBouncer. These add to the rich set of accessories already available.

You can read the full release here.

If you are using PostgreSQL 8.2, I definetly recommend upgrading to version 8.3.

You can download the version 8.3 here.

Sun acquires MySQL

Tagged:  

It's happened. This morning, Sun Microsystems announced plans to acquire MySQL AB. Reactions have been mixed, including excitement, pride, disbelief and satisfaction, but also anxiety.

Sun has been a leader in the open source community, just look at: Java, OpenSolaris, Open Office / Star Office, GlassFish app server, and the NetBeans IDE for starters. So, it can't be all bad that they've acquired MySQL, right?

The question stirring up some anxiety is: will MySQL’s support for other programming languages and operating systems now be given less attention due to Sun's vested interest in, and success with, Java and Solaris?

Here's the answer from Sun's blog:

Absolutely not. MySQL is still being managed by the same people, and the charter is still the same. There is no need for reducing the set of platforms or languages. It only makes sense for us to continue to support defacto Web development standards like LAMP, as well as emerging ones like Ruby and Eclipse. This deal is about addition, not subtraction.

What does all of this mean for the developer community? Hopefully just some added value to MySQL due to the immediate access to technical, marketing, OSS developer relations it will now have.

Read more about the acquisition over at Kaj Arno's MySQL blog or the press announcement on Sun's website.

PostgreSQL 8.3 vs. 8.2 - a simple benchmark

Tagged:  

I ran across an interesting post that benchmarks the newest version of PosrgreSQL (8.3) versus the previous release (8.2). PostgreSQL is one of the most used databases for web 2.0 startups (behind MySQL).

The below is from the post.

With 8.3 just around the corner more and more people are actually starting to test 8.3 with their code base and wondering if it will be worth to switch/upgrade and so did I.
The following is not really an in-depth benchmark but meant as a simple testing of 8.3 on a very specific (but not uncommon) workload and with small set of different configuration parameters.

All the testing is done on a DL380 G5 with two Quadcore Intel Xeon CPUs (X5345 - so 2,33Ghz per core) 12GB of RAM and 6 disks in a RAID1+0 with 512MB BBWC on a Smartarray P400.
The box is running Debian/Etch AMD/64 with the debian supplied kernel (2.6.18 more or less) and a current -HEAD snapshot of PostgreSQL (more or less BETA4 code) compiled as a 64bit binary.

The benchmark database is initialized with a scaling factor of 100 (equals to 10M rows) which seems to be a reasonable size for a table in an OLTP style database) all testing was done with 100 clients and 100000 transactions/client which comes out to 10M transactions and an average runtime of about 1,5 hours.
The first test is a simple comparison of 8.2 vs 8.3B4 in both the default (ie. unchanged postgresql.conf) and one with a somewhat reasonable tuning:

* checkpoint_segments=192
* maintenance_work_mem=128MB
* shared_buffers=1536MB
* wal_buffers=1024kB
* effective_cache_size=3084MB
* filesystem(ext3) mounted with noatime

82vs83b4.gif

what we can see here is that 8.3B4 is about 2.2x faster than 8.2 out-of-the-box for this very update heavy workload and is able to keep that advantage after similar tuning is applied to both instances.
The main reason for this rather large improvement is HOT. pgbench is one of the workloads that benefit most from it and a bit of preliminary testing with similiar real-life workloads (session tables or stuff like the SQL-Bayes support in Spamassassin) show equally impressive gains.

the second thing I tried to test was the effect of various shared_buffer settings on the transaction rate(the other parts of the configuration stayed the same as in the "tuned" variant above):

83b4shm.gif

there are two main things to note here - one is the obvious one that the scaling on the x-axis is not linear.
The other one is that the best performance seems to be around 1,5GB of shared memory which is about the size of the database on-disk in 8.3. Higher settings to not help and in fact cause a slight performance degeneration.

Read the full post here

From these tests it looks like 8.3 is going to very good release. With databases performance tuning is critical to having your application run its best.

As always if you have any questions about the above you can comment on this post or message me on Social Ajaxonomy. Also if you have an interesting site, library or story you can use the link on the side bar of the blog (or click here) to submit the item.

Syndicate content