I am using Singapore time.
Login into vps,
and run
#ln -sf /etc/localtime /usr/share/zoneinfo/Asia/Singapore
Friday, June 29, 2007
Thursday, June 28, 2007
java outofmemory in linux
Debian is a stable easy to maintain platform for Java Web Applications. However if you start using some caching frameworks, you may need to do some JVM tuning and here's the information you need to know.
Read this and to the point where you understand that a 32-process on Linux only has 2GB of addressible space. In fact, back in 1999, Linus decreed that 32-bit Linux would never, ever support more than 2GB of memory. "This is not negotiable."
Stacks are memory chunks used for saving variables and passing parameters when calling functions. It's a low level implementation detail, but the important thing to know is that each thread in each process must have it's own stack. Big stacks add up quickly as in the case of tomcat and most web servers where many threads are used to serve requests. Stacks for threads all come out of the same address space, so if you only used memory for stacks of threads you could have 1024 threads with 2MB stacks. In fact in Debian Sarge, there is no way to reduce the amount of memory allocated for the stack of a thread. [1] [2] [3]. Understand that this is not java specific, the same limitation applies to c programs.
Now that we have some fundamental stuff down, it's easy from here. Say you have 2G of Memory and want to use as much memory as possible for your cash for performance considerations. The objects in the cash will be stored in the heap memory. I think you should calculate how much memory you should use with the following formula.
HeapMemory = ProcessAdressSpace - ThreadMemory - PermGen - JVM
If you're running 32-bit, you can only see 2G max.
ProcessAdressSpace = 2G
If you web app is like mine, you'll need 100 threads. Have some librarys that may create threads, tack on another 100 to be safe. 200 threads X 2Mb/Thread = 400M
ThreadMemory = 400M
PermGen is where your defined classes go. Since you use alot of frameworks and have lots of classes, you should set this to 128M
PermGen = 128M
JVM is relatively small, but you need to give it room so that it can work quickly.
JVM = 256M
With those parameters, HeapMemory should not exceed 1264M! Anything more and you're going to slow down your application more than the cache is speeding it up, or you'll introduce nasty a OutOfMemoryException that will drive you crazy. Here's the parameters you want to use.
java -Xmx1264M -XX:MaxPermSize=128m ...
If you upgrade to etch, and set the stack size to 1MB (not recommending without extensive testing), then you can reclaim another 200MB for the heap.
Read this and to the point where you understand that a 32-process on Linux only has 2GB of addressible space. In fact, back in 1999, Linus decreed that 32-bit Linux would never, ever support more than 2GB of memory. "This is not negotiable."
Stacks are memory chunks used for saving variables and passing parameters when calling functions. It's a low level implementation detail, but the important thing to know is that each thread in each process must have it's own stack. Big stacks add up quickly as in the case of tomcat and most web servers where many threads are used to serve requests. Stacks for threads all come out of the same address space, so if you only used memory for stacks of threads you could have 1024 threads with 2MB stacks. In fact in Debian Sarge, there is no way to reduce the amount of memory allocated for the stack of a thread. [1] [2] [3]. Understand that this is not java specific, the same limitation applies to c programs.
Now that we have some fundamental stuff down, it's easy from here. Say you have 2G of Memory and want to use as much memory as possible for your cash for performance considerations. The objects in the cash will be stored in the heap memory. I think you should calculate how much memory you should use with the following formula.
HeapMemory = ProcessAdressSpace - ThreadMemory - PermGen - JVM
If you're running 32-bit, you can only see 2G max.
ProcessAdressSpace = 2G
If you web app is like mine, you'll need 100 threads. Have some librarys that may create threads, tack on another 100 to be safe. 200 threads X 2Mb/Thread = 400M
ThreadMemory = 400M
PermGen is where your defined classes go. Since you use alot of frameworks and have lots of classes, you should set this to 128M
PermGen = 128M
JVM is relatively small, but you need to give it room so that it can work quickly.
JVM = 256M
With those parameters, HeapMemory should not exceed 1264M! Anything more and you're going to slow down your application more than the cache is speeding it up, or you'll introduce nasty a OutOfMemoryException that will drive you crazy. Here's the parameters you want to use.
java -Xmx1264M -XX:MaxPermSize=128m ...
If you upgrade to etch, and set the stack size to 1MB (not recommending without extensive testing), then you can reclaim another 200MB for the heap.
Saturday, June 23, 2007
safe_asterisk
#!/bin/bash
ulimit -c unlimited
run_asterisk()
{
while :; do
cd /tmp
/usr/sbin/asterisk -c >& /dev/null < /dev/null
echo "Automatically restarting Asterisk."
sleep 4
done
}
run_asterisk &
ulimit -c unlimited
run_asterisk()
{
while :; do
cd /tmp
/usr/sbin/asterisk -c >& /dev/null < /dev/null
echo "Automatically restarting Asterisk."
sleep 4
done
}
run_asterisk &
Monday, June 18, 2007
Tuning file descriptor limits on Linux
Tuning file descriptor limits on Linux
Linux limits the number of file descriptors that any one process may open; the default limits are 1024 per process. These limits can prevent optimum performance of both benchmarking clients (such as httperf and apachebench) and of the web servers themselves (Apache is not affected, since it uses a process per connection, but single process web servers such as Zeus use a file descriptor per connection, and so can easily fall foul of the default limit).
The open file limit is one of the limits that can be tuned with the ulimit command. The command ulimit -aS displays the current limit, and ulimit -aH displays the hard limit (above which the limit cannot be increased without tuning kernel parameters in /proc).
The following is an example of the output of ulimit -aH. You can see that the current shell (and its children) is restricted to 1024 open file descriptors.
core file size (blocks) unlimited
data seg size (kbytes) unlimited
file size (blocks) unlimited
max locked memory (kbytes) unlimited
max memory size (kbytes) unlimited
open files 1024
pipe size (512 bytes) 8
stack size (kbytes) unlimited
cpu time (seconds) unlimited
max user processes 4094
virtual memory (kbytes) unlimited
Increasing the file descriptor limit
The file descriptor limit can be increased using the following procedure:
1. Edit /etc/security/limits.conf and add the lines:
* soft nofile 1024
* hard nofile 65535
2. Edit /etc/pam.d/login, adding the line:
session required /lib/security/pam_limits.so
3. The system file descriptor limit is set in /proc/sys/fs/file-max. The following command will increase the limit to 65535:
echo 65535 > /proc/sys/fs/file-max
4. You should then be able to increase the file descriptor limits using:
ulimit -n unlimited
The above command will set the limits to the hard limit specified in /etc/security/limits.conf.
or---
Increase the limit from 64 (default) to 2048 by issuing the command:
ulimit -n 2048
Also, some applications like the SCO JDK require that the virtual memory resource limit be set to unlimited.
ulimit -v unlimited
Note that you may need to log out and back in again before the changes take effect.
Linux limits the number of file descriptors that any one process may open; the default limits are 1024 per process. These limits can prevent optimum performance of both benchmarking clients (such as httperf and apachebench) and of the web servers themselves (Apache is not affected, since it uses a process per connection, but single process web servers such as Zeus use a file descriptor per connection, and so can easily fall foul of the default limit).
The open file limit is one of the limits that can be tuned with the ulimit command. The command ulimit -aS displays the current limit, and ulimit -aH displays the hard limit (above which the limit cannot be increased without tuning kernel parameters in /proc).
The following is an example of the output of ulimit -aH. You can see that the current shell (and its children) is restricted to 1024 open file descriptors.
core file size (blocks) unlimited
data seg size (kbytes) unlimited
file size (blocks) unlimited
max locked memory (kbytes) unlimited
max memory size (kbytes) unlimited
open files 1024
pipe size (512 bytes) 8
stack size (kbytes) unlimited
cpu time (seconds) unlimited
max user processes 4094
virtual memory (kbytes) unlimited
Increasing the file descriptor limit
The file descriptor limit can be increased using the following procedure:
1. Edit /etc/security/limits.conf and add the lines:
* soft nofile 1024
* hard nofile 65535
2. Edit /etc/pam.d/login, adding the line:
session required /lib/security/pam_limits.so
3. The system file descriptor limit is set in /proc/sys/fs/file-max. The following command will increase the limit to 65535:
echo 65535 > /proc/sys/fs/file-max
4. You should then be able to increase the file descriptor limits using:
ulimit -n unlimited
The above command will set the limits to the hard limit specified in /etc/security/limits.conf.
or---
Increase the limit from 64 (default) to 2048 by issuing the command:
ulimit -n 2048
Also, some applications like the SCO JDK require that the virtual memory resource limit be set to unlimited.
ulimit -v unlimited
Note that you may need to log out and back in again before the changes take effect.
Thursday, June 7, 2007
Tuning PostgreSQL for performance
Tuning PostgreSQL for performance
Shridhar Daithankar, Josh Berkus
July 3, 2003 Copyright 2003 Shridhar Daithankar and Josh Berkus.
Authorized for re-distribution only under the PostgreSQL license (see www.postgresql.org/license).
Table of Contents
1 Introduction
2 Some basic parameters
2.1 Shared buffers
2.2 Sort memory
2.3 Effective Cache Size
2.4 Fsync and the WAL files
3 Some less known parameters
3.1 random_ page_cost
3.2 Vacuum_ mem
3.3 max_fsm_pages
3.4 max fsm_ relations
3.5 wal_buffers
4 Other tips
4.1 Check your file system
4.2 Try the Auto Vacuum daemon
4.3 Try FreeBSD
5 The CONF Setting Guide
1 Introduction
This is a quick start guide for tuning PostgreSQL's settings for performance. This assumes minimal familiarity with PostgreSQL administration. In particular, one should know,
* How to start and stop the postmaster service
* How to tune OS parameters
* How to test the changes
It also assumes that you have gone through the PostgreSQL administration manual before starting, and to have set up your PostgreSQL server with at least the default configuration.
There are two important things for any performance optimization:
* Decide what level of performance you want
If you don't know your expected level of performance, you will end up chasing a carrot always couple of meters ahead of you. The performance tuning measures give diminishing returns after a certain threshold. If you don't set this threshold beforehand, you will end up spending lot of time for minuscule gains.
* Know your load
This document focuses entirely tuning postgresql.conf best for your existing setup. This is not the end of performance tuning. After using this document to extract the maximum reasonable performance from your hardware, you should start optimizing your application for efficient data access, which is beyond the scope of this article.
Please also note that the tuning advices described here are hints. You should not implement them all blindly. Tune one parameter at a time and test its impact and decide whether or not you need more tuning. Testing and benchmarking is an integral part of database tuning.
Tuning the software settings explored in this article is only about one-third of database performance tuning, but it's a good start since you can experiment with some basic setting changes in an afternoon, whereas some other aspects of tuning can be very time-consuming. The other two-thirds of database application tuning are:
* Hardware Selection and Setup
Databases are very bound to your system's I/O (disk) access and memory usage. As such, selection and configuration of disks, RAID arrays, RAM, operating system, and competition for these resources will have a profound effect on how fast your database is. We hope to have a later article covering this topic.
* Efficient Application Design
Your application also needs to be designed to access data efficiently, though careful query writing, planned and tested indexing, good connection management, and avoiding performance pitfalls particular to your version of PostgreSQL. Expect another guide someday helping with this, but really it takes several large books and years of experience to get it right ... or just a lot of time on the mailing lists.
2 Some basic parameters
2.1 Shared buffers
Shared buffers defines a block of memory that PostgreSQL will use to hold requests that are awaiting attention from the kernel buffer and CPU. The default value is quite low for any real world workload and need to be beefed up. However, unlike databases like Oracle, more is not always better. There is a threshold above which increasing this value can hurt performance.
This is the area of memory PostgreSQL actually uses to perform work. It should be sufficient enough to handle load on database server. Otherwise PostgreSQL will start pushing data to file and it will hurt the performance overall. Hence this is the most important setting one needs to tune up.
This value should be set based on the dataset size which the database server is supposed to handle at peak loads and on your available RAM (keep in mind that RAM used by other applications on the server is not available). We recommend following rule of thumb for this parameter:
* Start at 4MB (512) for a workstation
* Medium size data set and 256-512MB available RAM: 16-32MB (2048-4096)
* Large dataset and lots of available RAM (1-4GB): 64-256MB (8192-32768)
PLEASE NOTE. PostgreSQL counts a lot on the OS to cache data files and hence does not bother with duplicating its file caching effort. The shared buffers parameter assumes that OS is going to cache a lot of files and hence it is generally very low compared with system RAM. Even for a dataset in excess of 20GB, a setting of 128MB may be too much, if you have only 1GB RAM and an aggressive-at-caching OS like Linux.
There is one way to decide what is best for you. Set a high value of this parameter and run the database for typical usage. Watch usage of shared memory using ipcs or similar tools. A recommended figure would be between 1.2 to 2 times peak shared memory usage.
2.2 Sort memory
This parameter sets maximum limit on memory that a database connection can use to perform sorts. If your queries have order-by or group-by clauses that require sorting large data set, increasing this parameter would help. But beware: this parameter is per sort, per connection. Think twice before setting this parameter too high on any database with many users. A recommended approach is to set this parameter per connection as and when required; that is, low for most simple queries and higher for large, complex queries and data dumps.
2.3 Effective Cache Size
This parameter allows PostgreSQL to make best possible use of RAM available on your server. It tells PostgreSQL the size of OS data cache. So that PostgreSQL can draw different execution plan based on that data.
Say there is 1.5GB RAM in your machine, shared buffers are set to 32MB and effective cache size is set to 800MB. So if a query needs 700MB of data set, PostgreSQL would estimate that all the data required should be available in memory and would opt for more aggressive plan in terms of optimization, involving heavier index usage and merge joins. But if effective cache is set to only 200MB, the query planner is liable to opt for the more I/O efficient sequential scan.
While setting this parameter size, leave room for other applications running on the server machine. The objective is to set this value at the highest amount of RAM which will be available to PostgreSQL all the time.
2.4 Fsync and the WAL files
This parameters sets whether or not write data to disk as soon as it is committed, which is done through Write Ahead Logging (WAL). If you trust your hardware, your power company, and your battery power supply enough, you set this to No for an immediate boost to data write speed. But be very aware that any unexpected database shutdown will force you to restore the database from your last backup.
If that's not an option for you, you can still have the protection of WAL and better performance. Simply move your WAL files, using either a mount or a symlink to the pg_xlog directory, to a separate disk or array from your main database files. In high-write-activity databases, WAL should have its own disk or array to ensure continuous high-speed access. Very large RAID arrays and SAN/NAS devices frequently handle this for you through their internal management systems.
3 Some less known parameters
3.1 random_page_cost
This parameter sets the cost to fetch a random tuple from the database, which influences the planner's choice of index vs. table scan. This is set to a high value as the default default based on the expectation of slow disk access. If you have reasonably fast disks like SCSI or RAID, you can lower the cost to 2. You need to experiment to find out what works best for your setup by running a variety of queries and comparing execution times.
3.2 Vacuum_mem
This parameter sets the memory allocated to Vacuum. Normally, vacuum is a disk intensive process, but raising this parameter will speed it up by allowing PostgreSQL to copy larger blocks into memory. Just don't set it so high it takes significant memory away from normal database operation. Things between 16-32MB should be good enough for most setups.
3.3 max_fsm_pages
PostgreSQL records free space in each of its data pages. This information is useful for vacuum to find out how many and which pages to look for when it frees up the space.
If you have a database that does lots of updates and deletes, that is going to generate dead tuples, due to PostgreSQL's MVCC system. The space occupied by dead tuples can be freed with vacuum, unless there is more wasted space than is covered by the Free Space Map, in which case the much less convenient "vacuum full" is required. By expanding the FSM to cover all of those dead tuples, you might never again need to run vacuum full except on holidays.
The best way to set max _ fsm _ pages is interactive; First, figure out the vacuum (regular) frequency of your database based on write activity; next, run the database under normal production load, and run "vacuum verbose analyze" instead of vacuum, saving the output to a file; finally, calculate the maximum total number of pages reclaimed between vacuums based on the output, and use that.
Remember, this is a database cluster wide setting. So bump it up enough to cover all databases in your database cluster. Also, each FSM page uses 6 bytes of RAM for administrative overhead, so increasing FSM substantially on systems low on RAM may be counter-productive.
3.4 max _ fsm _ relations
This setting dictates how many number of relations (tables) will be tracked in free space map. Again this is a database cluster-wide setting, so set it accordingly. In version 7.3.3 and later, this parameter should be set correctly as a default. In older versions, bump it up to 300-1000.
3.5 wal_buffers
This setting decides the number of buffers WAL(Write ahead Log) can have. If your database has many write transactions, setting this value bit higher than default could result better usage of disk space. Experiment and decide. A good start would be around 32-64 corresponding to 256-512K memory.
4 Other tips
4.1 Check your file system
On OS like Linux, which offers multiple file systems, one should be careful about choosing the right one from a performance point of view. There is no agreement between PostgreSQL users about which one is best.
Contrary to popular belief, today's journaling file systems are not necessarily slower compared to non-journaling ones. Ext2 can be faster on some setups but the recovery issues generally make its use prohibitive. Different people have reported widely different experiences with the speed of Ext3, ReiserFS, and XFS; quite possibly this kind of benchmark depends on a combination of file system, disk/array configuration, OS version, and database table size and distribution. As such, you may be better off sticking with the file system best supported by your distribution, such as ReiserFS for SuSE Linux or Ext3 for Red Hat Linux, not to forget XFS known for it's large file support . Of course, if you have time to run comprehensive benchmarks, we would be interested in seeing the results!
As an easy performance boost with no downside, make sure the file system on which your database is kept is mounted "noatime", which turns off the access time bookkeeping.
4.2 Try the Auto Vacuum daemon
There is a little known module in PostgreSQL contrib directory called as pgavd. It works in conjunction with statistics collector. It periodically connects to a database and checks if it has done enough operations since the last check. If yes, it will vacuum the database.
Essentially it will vacuum the database when it needs it. It would get rid of playing with cron settings for vacuum frequency. It should result in better database performance by eliminating overdue vacuum issues.
4.3 Try FreeBSD
Large updates, deletes, and vacuum in PostgreSQL are very disk intensive processes. In particular, since vacuum gobbles up IO bandwidth, the rest of the database activities could be affected adversely when vacuuming very large tables.
OS's from the BSD family, such as FreeBSD, dynamically alter the IO priority of a process. So if you lower the priority of a vacuum process, it should not chew as much bandwidth and will better allow the database to perform normally. Of course this means that vacuum could take longer, which would be problematic for a "vacuum full."
If you are not done with your choice of OS for your server platform, consider BSD for this reason.
5 The CONF Setting Guide
Available here is an Annotated Guide to the PostgreSQL configuration file settings, in both OpenOffice.org and PDF format. This guide expands on the official documentation and may eventually be incorporated into it.
* The first column of the chart is the GUC setting in the postgresql.conf file.
* The second is the maximum range of the variable; note that the maximum range is often much larger than the practical range. For example, random_page_cost will accept any number between 0 and several billion, but all practical numbers are between 1 and 5.
* The third column contains an enumeration of RAM or disk space used by each unit of the parameter.
* The fourth column indicates whether or not the variable may be SET from the PSQL terminal during an interactive setting. Most settings marked as "no" may only be changed by restarting PostgreSQL.
* The fifth column quotes the official documentation available from the PostgreSQL web site.
* The last column is our notes on the setting, how to set it, resources it uses, etc. You'll notice some blank spaces, and should be warned as well that there is still strong disagreement on the value of many settings.
Users of PostgreSQL 7.3 and earlier will notice that the order of the parameters in this guide do not match the order of the parameters in your postgresql.conf file. This is because this document was generated as part of an effort to re-organize the conf parameters and documentation; starting with 7.4, this document, the official documentation, and the postgresql.conf file are all in the same logical order.
As noted in the worksheet, it covers PostgreSQL versions 7.3 and 7.4. If you are using an earlier version, you will not have access to all of these settings, and defaults and effects of some settings will be different.
Shridhar Daithankar, Josh Berkus
July 3, 2003 Copyright 2003 Shridhar Daithankar and Josh Berkus.
Authorized for re-distribution only under the PostgreSQL license (see www.postgresql.org/license).
Table of Contents
1 Introduction
2 Some basic parameters
2.1 Shared buffers
2.2 Sort memory
2.3 Effective Cache Size
2.4 Fsync and the WAL files
3 Some less known parameters
3.1 random_ page_cost
3.2 Vacuum_ mem
3.3 max_fsm_pages
3.4 max fsm_ relations
3.5 wal_buffers
4 Other tips
4.1 Check your file system
4.2 Try the Auto Vacuum daemon
4.3 Try FreeBSD
5 The CONF Setting Guide
1 Introduction
This is a quick start guide for tuning PostgreSQL's settings for performance. This assumes minimal familiarity with PostgreSQL administration. In particular, one should know,
* How to start and stop the postmaster service
* How to tune OS parameters
* How to test the changes
It also assumes that you have gone through the PostgreSQL administration manual before starting, and to have set up your PostgreSQL server with at least the default configuration.
There are two important things for any performance optimization:
* Decide what level of performance you want
If you don't know your expected level of performance, you will end up chasing a carrot always couple of meters ahead of you. The performance tuning measures give diminishing returns after a certain threshold. If you don't set this threshold beforehand, you will end up spending lot of time for minuscule gains.
* Know your load
This document focuses entirely tuning postgresql.conf best for your existing setup. This is not the end of performance tuning. After using this document to extract the maximum reasonable performance from your hardware, you should start optimizing your application for efficient data access, which is beyond the scope of this article.
Please also note that the tuning advices described here are hints. You should not implement them all blindly. Tune one parameter at a time and test its impact and decide whether or not you need more tuning. Testing and benchmarking is an integral part of database tuning.
Tuning the software settings explored in this article is only about one-third of database performance tuning, but it's a good start since you can experiment with some basic setting changes in an afternoon, whereas some other aspects of tuning can be very time-consuming. The other two-thirds of database application tuning are:
* Hardware Selection and Setup
Databases are very bound to your system's I/O (disk) access and memory usage. As such, selection and configuration of disks, RAID arrays, RAM, operating system, and competition for these resources will have a profound effect on how fast your database is. We hope to have a later article covering this topic.
* Efficient Application Design
Your application also needs to be designed to access data efficiently, though careful query writing, planned and tested indexing, good connection management, and avoiding performance pitfalls particular to your version of PostgreSQL. Expect another guide someday helping with this, but really it takes several large books and years of experience to get it right ... or just a lot of time on the mailing lists.
2 Some basic parameters
2.1 Shared buffers
Shared buffers defines a block of memory that PostgreSQL will use to hold requests that are awaiting attention from the kernel buffer and CPU. The default value is quite low for any real world workload and need to be beefed up. However, unlike databases like Oracle, more is not always better. There is a threshold above which increasing this value can hurt performance.
This is the area of memory PostgreSQL actually uses to perform work. It should be sufficient enough to handle load on database server. Otherwise PostgreSQL will start pushing data to file and it will hurt the performance overall. Hence this is the most important setting one needs to tune up.
This value should be set based on the dataset size which the database server is supposed to handle at peak loads and on your available RAM (keep in mind that RAM used by other applications on the server is not available). We recommend following rule of thumb for this parameter:
* Start at 4MB (512) for a workstation
* Medium size data set and 256-512MB available RAM: 16-32MB (2048-4096)
* Large dataset and lots of available RAM (1-4GB): 64-256MB (8192-32768)
PLEASE NOTE. PostgreSQL counts a lot on the OS to cache data files and hence does not bother with duplicating its file caching effort. The shared buffers parameter assumes that OS is going to cache a lot of files and hence it is generally very low compared with system RAM. Even for a dataset in excess of 20GB, a setting of 128MB may be too much, if you have only 1GB RAM and an aggressive-at-caching OS like Linux.
There is one way to decide what is best for you. Set a high value of this parameter and run the database for typical usage. Watch usage of shared memory using ipcs or similar tools. A recommended figure would be between 1.2 to 2 times peak shared memory usage.
2.2 Sort memory
This parameter sets maximum limit on memory that a database connection can use to perform sorts. If your queries have order-by or group-by clauses that require sorting large data set, increasing this parameter would help. But beware: this parameter is per sort, per connection. Think twice before setting this parameter too high on any database with many users. A recommended approach is to set this parameter per connection as and when required; that is, low for most simple queries and higher for large, complex queries and data dumps.
2.3 Effective Cache Size
This parameter allows PostgreSQL to make best possible use of RAM available on your server. It tells PostgreSQL the size of OS data cache. So that PostgreSQL can draw different execution plan based on that data.
Say there is 1.5GB RAM in your machine, shared buffers are set to 32MB and effective cache size is set to 800MB. So if a query needs 700MB of data set, PostgreSQL would estimate that all the data required should be available in memory and would opt for more aggressive plan in terms of optimization, involving heavier index usage and merge joins. But if effective cache is set to only 200MB, the query planner is liable to opt for the more I/O efficient sequential scan.
While setting this parameter size, leave room for other applications running on the server machine. The objective is to set this value at the highest amount of RAM which will be available to PostgreSQL all the time.
2.4 Fsync and the WAL files
This parameters sets whether or not write data to disk as soon as it is committed, which is done through Write Ahead Logging (WAL). If you trust your hardware, your power company, and your battery power supply enough, you set this to No for an immediate boost to data write speed. But be very aware that any unexpected database shutdown will force you to restore the database from your last backup.
If that's not an option for you, you can still have the protection of WAL and better performance. Simply move your WAL files, using either a mount or a symlink to the pg_xlog directory, to a separate disk or array from your main database files. In high-write-activity databases, WAL should have its own disk or array to ensure continuous high-speed access. Very large RAID arrays and SAN/NAS devices frequently handle this for you through their internal management systems.
3 Some less known parameters
3.1 random_page_cost
This parameter sets the cost to fetch a random tuple from the database, which influences the planner's choice of index vs. table scan. This is set to a high value as the default default based on the expectation of slow disk access. If you have reasonably fast disks like SCSI or RAID, you can lower the cost to 2. You need to experiment to find out what works best for your setup by running a variety of queries and comparing execution times.
3.2 Vacuum_mem
This parameter sets the memory allocated to Vacuum. Normally, vacuum is a disk intensive process, but raising this parameter will speed it up by allowing PostgreSQL to copy larger blocks into memory. Just don't set it so high it takes significant memory away from normal database operation. Things between 16-32MB should be good enough for most setups.
3.3 max_fsm_pages
PostgreSQL records free space in each of its data pages. This information is useful for vacuum to find out how many and which pages to look for when it frees up the space.
If you have a database that does lots of updates and deletes, that is going to generate dead tuples, due to PostgreSQL's MVCC system. The space occupied by dead tuples can be freed with vacuum, unless there is more wasted space than is covered by the Free Space Map, in which case the much less convenient "vacuum full" is required. By expanding the FSM to cover all of those dead tuples, you might never again need to run vacuum full except on holidays.
The best way to set max _ fsm _ pages is interactive; First, figure out the vacuum (regular) frequency of your database based on write activity; next, run the database under normal production load, and run "vacuum verbose analyze" instead of vacuum, saving the output to a file; finally, calculate the maximum total number of pages reclaimed between vacuums based on the output, and use that.
Remember, this is a database cluster wide setting. So bump it up enough to cover all databases in your database cluster. Also, each FSM page uses 6 bytes of RAM for administrative overhead, so increasing FSM substantially on systems low on RAM may be counter-productive.
3.4 max _ fsm _ relations
This setting dictates how many number of relations (tables) will be tracked in free space map. Again this is a database cluster-wide setting, so set it accordingly. In version 7.3.3 and later, this parameter should be set correctly as a default. In older versions, bump it up to 300-1000.
3.5 wal_buffers
This setting decides the number of buffers WAL(Write ahead Log) can have. If your database has many write transactions, setting this value bit higher than default could result better usage of disk space. Experiment and decide. A good start would be around 32-64 corresponding to 256-512K memory.
4 Other tips
4.1 Check your file system
On OS like Linux, which offers multiple file systems, one should be careful about choosing the right one from a performance point of view. There is no agreement between PostgreSQL users about which one is best.
Contrary to popular belief, today's journaling file systems are not necessarily slower compared to non-journaling ones. Ext2 can be faster on some setups but the recovery issues generally make its use prohibitive. Different people have reported widely different experiences with the speed of Ext3, ReiserFS, and XFS; quite possibly this kind of benchmark depends on a combination of file system, disk/array configuration, OS version, and database table size and distribution. As such, you may be better off sticking with the file system best supported by your distribution, such as ReiserFS for SuSE Linux or Ext3 for Red Hat Linux, not to forget XFS known for it's large file support . Of course, if you have time to run comprehensive benchmarks, we would be interested in seeing the results!
As an easy performance boost with no downside, make sure the file system on which your database is kept is mounted "noatime", which turns off the access time bookkeeping.
4.2 Try the Auto Vacuum daemon
There is a little known module in PostgreSQL contrib directory called as pgavd. It works in conjunction with statistics collector. It periodically connects to a database and checks if it has done enough operations since the last check. If yes, it will vacuum the database.
Essentially it will vacuum the database when it needs it. It would get rid of playing with cron settings for vacuum frequency. It should result in better database performance by eliminating overdue vacuum issues.
4.3 Try FreeBSD
Large updates, deletes, and vacuum in PostgreSQL are very disk intensive processes. In particular, since vacuum gobbles up IO bandwidth, the rest of the database activities could be affected adversely when vacuuming very large tables.
OS's from the BSD family, such as FreeBSD, dynamically alter the IO priority of a process. So if you lower the priority of a vacuum process, it should not chew as much bandwidth and will better allow the database to perform normally. Of course this means that vacuum could take longer, which would be problematic for a "vacuum full."
If you are not done with your choice of OS for your server platform, consider BSD for this reason.
5 The CONF Setting Guide
Available here is an Annotated Guide to the PostgreSQL configuration file settings, in both OpenOffice.org and PDF format. This guide expands on the official documentation and may eventually be incorporated into it.
* The first column of the chart is the GUC setting in the postgresql.conf file.
* The second is the maximum range of the variable; note that the maximum range is often much larger than the practical range. For example, random_page_cost will accept any number between 0 and several billion, but all practical numbers are between 1 and 5.
* The third column contains an enumeration of RAM or disk space used by each unit of the parameter.
* The fourth column indicates whether or not the variable may be SET from the PSQL terminal during an interactive setting. Most settings marked as "no" may only be changed by restarting PostgreSQL.
* The fifth column quotes the official documentation available from the PostgreSQL web site.
* The last column is our notes on the setting, how to set it, resources it uses, etc. You'll notice some blank spaces, and should be warned as well that there is still strong disagreement on the value of many settings.
Users of PostgreSQL 7.3 and earlier will notice that the order of the parameters in this guide do not match the order of the parameters in your postgresql.conf file. This is because this document was generated as part of an effort to re-organize the conf parameters and documentation; starting with 7.4, this document, the official documentation, and the postgresql.conf file are all in the same logical order.
As noted in the worksheet, it covers PostgreSQL versions 7.3 and 7.4. If you are using an earlier version, you will not have access to all of these settings, and defaults and effects of some settings will be different.
Tuesday, June 5, 2007
iax2 one-way audio
After a while of operation, IAX becomes behaving incorrectly, no audio or 1-way, and no-answer :
This problem happened on asterisk 2.1.14.
The solution is configure the
jitterbuffer=no
This problem happened on asterisk 2.1.14.
The solution is configure the
jitterbuffer=no
Subscribe to:
Posts (Atom)