Measuring Performance


Tuning a PostgreSQL database is done for many reasons and can be done in many ways. On the one hand, users and developers want to see their applications run faster; on the other hand, performance is a good argument for selling a product to a customer. One way to achieve higher performance is to invest in the latest hardware, such as faster processors, more memory, or faster I/O systems. This is a good solution, but higher performance can also be reached by tuning and focusing on the intelligent design of your application. It is up to you to decide which way to go if you need higher performance. We will describe some ways to tune your PostgreSQL database and see how much performance we can gain.

Tuning can be done in many ways, and the number of parameters affecting the speed of your application is nearly endless. The most important components on the hardware side are the power of your CPU, memory, and the speed of your I/O system. The I/O system has an especially great influence on the behavior of your system. In most cases, a database system has to perform a lot of I/O operations, but only a few calculations. Retrieving data means reading data, and reading data needs a lot of I/O performance, but very little CPU time. You should keep this in mind when planning database environments and deciding which hardware to use.

If you want to tune your database, it is necessary to do some kind of performance measuring. Tuning can only be done by comparing data and testing various settings.

One very important parameter for tuning is the average CPU load of the system. On Unix systems, the uptime command gives us some information about the CPU load:

 [hs@bachata hs]$  uptime  3:03pm  up 68 days,  1 user,  load average: 0.20, 0.16, 0.07 

uptime tells us how long the machine has been up and running. In the previous example, we can see that the machine has been running for 68 days. One user is currently logged in.

We get three values that show us the average load of the system:

  • The first value tells us that during the past minute we had a 20 percent average load.

  • The second number means that during the past 5 minutes we had a 16 percent average load of; over the past 15 minutes we had an 7 percent average load of.

It is important to mention that the system load can also be higher than 100 percent. If this happens, you can feel that the speed of the system is decreasing and that processes take longer to finish.

The higher the three values are, the worse for your system. It is important to keep the CPU time under 100 percent; otherwise , you should consider an upgrade of your hardware or further tuning.

I/O performance cannot be as easily evaluated as CPU time. Some benchmark suites exist for various operating systems, but in most cases, it depends on the kind of database you want to build as to which settings and which hardware will be suitable.

If you want to find out which hardware components are available, we recommend that you check out the hardware guide by Tom Pabst (the guy who found latency errors in Intel's 1.13Ghz CPU that caused Intel to recall the chip) that can be found at www.tomshardware.com. www.anandtech.com, by Anand Shimpi, is also a good choice for objective hardware reviewing.

One of the most important hardware parameters in database business is the amount of memory of your system. Memory is not only needed to perform sort operations faster. Memory is also used for caching queries and data. The amount of shared memory PostgreSQL uses can be defined when starting the postmaster .

When tuning your database, it is very important to know how much memory is consumed by your system and how memory is used by the database. On Linux systems, you can use a command called free to find some basic information about memory use of the operating system. free will inform you about the peak amount of memory used by the system. That information will help you to get an idea of what is going on on your system:

 [hs@duron hs]$  free  total       used       free     shared    buffers     cached Mem:        257532     254060       3472     135556       5280      99412 -/+ buffers/cache:     149368     108164 Swap:       265032      15488     249544 

The information provided by free is directly read from the kernel. If you want to see which processes use how much memory, we recommend to use a tool called top . top provides an ongoing look at processor activity in real time. You can even sort the processes by memory (Shift+M) or CPU usage (Shift+P).

We won't discuss memory management of all those operating systems out there in detail because this information is very hardware-dependent and may change from one version to the other. As far as Windows and Linux are concerned , some powerful tools with nice GUIs to monitor memory management are available.

In the next sections of the book, we will discuss database performance with the help of some scripts. Most of the results will be very hardware and platform-dependent, but the scripts can help you to tune your database efficiently .



PostgreSQL Developer's Handbook2001
PostgreSQL Developer's Handbook2001
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 125

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net