Home ›

PostgreSQL Performance Tuning

Issue 88

From Issue #88
August 2001

Aug 01, 2001  By Bruce Momjian
 in
Tweak your hardware to get the most from this open-source database.
Disk Locality

The physical nature of disk drives makes their performance characteristics different from the other storage areas mentioned in this article. The other storage areas can access any byte with equal speed. Disk drives, with their spinning platters and moving heads, access data near the head's current position much faster than data farther away.

Moving the disk head to another cylinder on the platter takes quite a bit of time. UNIX kernel developers know this. When storing a large file on disk, they try to place the pieces of the file near each other. For example, suppose a file requires ten blocks on disk. The operating system may place blocks 1-5 on one cylinder and blocks 6-10 on another cylinder. If the file is read from beginning to end, only two head movements are required—one to get to the cylinder holding blocks 1-5, and another to get to blocks 6-10. However, if the file is read non-sequentially, e.g., blocks 1,6,2,7,3,8,4,9,5,10; ten head movements are required. As you can see, with disks, sequential access is much faster than random access. This is why PostgreSQL prefers sequential scans to index scans if a significant portion of the table needs to be read. This also highlights the value of the cache.

Multiple Disk Spindles

The disk head moves around quite a bit during database activity. If too many read/write requests are made, the drive can become saturated, causing poor performance (Vmstat and sar can provide information on the amount of activity on each disk drive).

One solution to disk saturation is to move some of the PostgreSQL data files to other disks. Remember, moving the files to other filesystems on the same disk drive does not help. All filesystems on a drive use the same disk heads. Database access can be spread across disk drives in several ways:

  • Moving Databases—initlocation allows you to create databases on different drives.

  • Moving Tables—symbolic links allow you to move tables and indexes to different drives. Movement should only be done while PostgreSQL is shut down. Also, PostgreSQL doesn't know about the symbolic links, so if you delete the table and recreate it, it will be created in the default location for that database. In 7.1, pg_database.oid and pg_class.relfilenode map database, table and index names to their numeric filenames.

  • Moving Indexes—symbolic links allow moving indexes to different drives from their heap tables. This allows an index scan to be performed on one disk while a second disk performs heap lookups.

  • Moving Joins—symbolic links allow the movement of joined tables to separate disks. If tables A and B are joined, lookups of table A can be performed on one drive while lookups of table B can be done on a second drive.

  • Moving Log—symbolic links can be used to move the pg_xlog directory to a different disk drive. (Pg_xlog exists in PostgreSQL releases 7.1 and later.) Unlike other writes, PostgreSQL log writes must be flushed to disk before completing a transaction. The cache cannot be used to delay these writes. Having a separate disk for log writes allows the disk head to stay on the current log cylinder so writes can be performed without head movement delay. You can use the postgres -F parameter to prevent log writes from being flushed to disk, but an operating system crash may require a restore from backup.

Other options include the use of RAID features to spread a single filesystem across several drives.

Conclusion

Fortunately, PostgreSQL doesn't require much tuning. Most parameters are automatically adjusted to maintain optimum performance. Cache size and sort size are two parameters administrators can control to make better use of available memory. Disk access can also be spread across drives. Other parameters may be set in share/postgresql.conf.sample. You can copy this file to data/postgresql.conf to experiment with some of PostgreSQL's even more exotic parameters.

Bruce Momjian is cofounder of the PostgreSQL Global Development Team. Momjian currently serves as vice president of database development for Great Bridge, LLC (http://www.greatbridge.com/) and is responsible for leading critical database development projects, particularly for PostgreSQL. Momjian is also the author of PostgreSQL: Introduction and Concepts (Addison-Wesley), the first comprehensive guide for this complex open-source database system.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Found this article useful

Anonymous's picture
Submitted by Anonymous (not verified) on Thu, 09/11/2008 - 09:00.

I found this updated PostgreSQL Tuning article very useful.

This guide was very helpful.

Anonymous's picture
Submitted by Anonymous (not verified) on Tue, 11/14/2006 - 15:27.

This guide was very helpful. Please ignore the ignorant posts saying otherwise. I'd be scared to have those people managing my data.

A brief overview for database system

Breno Leitao's picture
Submitted by Breno Leitao (not verified) on Sun, 04/03/2005 - 18:01.

This article is performance tunning for newbie, nothing to be used as day-a-day book. But one question is important, no one that must tune the pgsql is really newbie about db system. ;-(

Breno Leitao

Re: PostgreSQL Performance Tuning

Anonymous's picture
Submitted by Anonymous on Tue, 08/24/2004 - 02:00.

Hello,
After learning this great article which provides the reader with some very useful and basic understandings of the data path in postgreSQL backend, I must say I have been a little shock by the comments I saw from people who are just waiting others to do what they should also be trying to do. These kinds of comments do not look like an encouragment to Bruce who deserves a lot of credit for the simplicity of his writing, and above all for his willingness toi help others.
Thanks Bruce, your article help explain easily some basic backgrounds required to tune wisely a PostgreSQL database.

Paul

Re: PostgreSQL Performance Tuning

Anonymous's picture
Submitted by Anonymous on Fri, 07/25/2003 - 02:00.

HELLO! CAN SOMEONE TELL ME WHAT THIS ARTICLE IS ABOUT. I EXPECTED TO OPTIMIZE MY DATABASE AFTER READING THIS ARTICLE BUT IT SEEMS THAT I WAS BETTER-OFF WITHOUT READING IT!!!!

Re: PostgreSQL Performance Tuning

Anonymous's picture
Submitted by Anonymous on Thu, 08/07/2003 - 02:00.

I was hoping to learn what the vacuum and vacuum analyze commands do, maybe read about the performance benefits of using them or find out other methods to make my queries run faster. I guess the guy just wanted to advertise his book. Thanks for nothing.

Re: PostgreSQL Performance Tuning

Anonymous's picture
Submitted by Anonymous on Tue, 04/23/2002 - 02:00.

not only is not really about PostgreSQL specifically, but it also has incorrect links.

Re: PostgreSQL Performance Tuning

Anonymous's picture
Submitted by Anonymous on Wed, 03/06/2002 - 03:00.

This document should be called General Overview of Performance Tuning. It provides almost no technical detail or insight into the tuning process. I would like to see an article which delves deeper into the issue and provides some real numbers from real applications on some real hardware. After reading the article, one is no more enabled to do any tuning on a PostgreSQL DB than if one didn't read the article at all. I am disappointed.

Re: PostgreSQL Performance Tuning

Anonymous's picture
Submitted by Anonymous on Tue, 08/21/2001 - 02:00.

This is a test from mso without cookies.

Webcast
More Resources
How to Build an Optimal Hadoop Cluster to Store and Maintain Unlimited Amounts of Data Using Microservers

Realizing the promise of Apache® Hadoop® requires the effective deployment of compute, memory, storage and networking to achieve optimal results. With its flexibility and multitude of options, it is easy to over or under provision the server infrastructure, resulting in poor performance and high TCO. Join us for an in depth, technical discussion with industry experts from leading Hadoop and server companies who will provide insights into the key considerations for designing and deploying an optimal Hadoop cluster.

Learn More

Sponsored by AMD

White Paper
More Resources
Red Hat White Paper: Using an Open Source Framework to Catch the Bad Guy

Built-in forensics, incident response, and security with Red Hat Enterprise Linux 6

Every security policy provides guidance and requirements for ensuring adequate protection of information and data, as well as high-level technical and administrative security requirements for a system in a given environment. Traditionally, providing security for a system focuses on the confidentiality of the information on it. However, protecting the data integrity and system and data availability is just as important. For example, when processing United States intelligence information, there are three attributes that require protection: confidentiality, integrity, and availability.

Learn more about catching the bad guy in this free white paper.

Learn More

Sponsored by DLT Solutions