Optimizing
One of the first things I noticed after turning on the CGI program, was
that although queries were returned almost as fast as from the previous
MySQL based system, the load on the server was much higher — in fact
almost 90-percent! Then I started to go down into the
nitty-gritty of things. I had optimized MySQL before by greatly
increasing cache and buffer sizes and by throwing more ram towards the
problem.
that although queries were returned almost as fast as from the previous
MySQL based system, the load on the server was much higher — in fact
almost 90-percent! Then I started to go down into the
nitty-gritty of things. I had optimized MySQL before by greatly
increasing cache and buffer sizes and by throwing more ram towards the
problem.
The single biggest thing that you have to do before running Postgresql,
is to provide enough shared buffer space. Let me repeat: provide
enough buffer space! Let’s say you have about 512MB of ram on a
dedicated database server, then you need to turn over about 75-percent
of it to this shared buffer. Postgresql does best when it can load most
or — even better — all of a table into its shared memory space. In
our case, since our database was fairly small, I decided to allocate
128MB of RAM towards the shared buffer space.
is to provide enough shared buffer space. Let me repeat: provide
enough buffer space! Let’s say you have about 512MB of ram on a
dedicated database server, then you need to turn over about 75-percent
of it to this shared buffer. Postgresql does best when it can load most
or — even better — all of a table into its shared memory space. In
our case, since our database was fairly small, I decided to allocate
128MB of RAM towards the shared buffer space.
The file /var/lib/pgsql/data/postgresql.conf contains settings for the
database server. Postgresql uses system shared memory as a buffer. On a
Linux system, you can see how much shared memory was allocated by your
system by running the command:
database server. Postgresql uses system shared memory as a buffer. On a
Linux system, you can see how much shared memory was allocated by your
system by running the command:
cat /proc/sys/kernel/shmmax
And to view shared memory use on the system:
ipcs
The result will be in bytes. By default RedHat 7.1 allocates 32MB of
shared memory, hardly enough for postgresql. I increased this limit to
128MB by doing the command:
shared memory, hardly enough for postgresql. I increased this limit to
128MB by doing the command:
echo 128000000 > /proc/sys/kernel/shmmax
Be aware that once you reboot the server, this setting will disappear.
You need to place this line in your postgresql startup file, or by
editing the /etc/sysctl.conf file for a more permanent setting.
You need to place this line in your postgresql startup file, or by
editing the /etc/sysctl.conf file for a more permanent setting.
Then in our postgresql.conf I set shared_buffers to 15200. Because
Postgresql uses 8K segments, I made a calculation of 128000/8192 plus a
512K overhead. I also set our sort_mem to 32168 (32Megs for a sort memory
area). Since connection pooling was in effect, I set max_connections to 64.
And fsync was also set to false.
Postgresql uses 8K segments, I made a calculation of 128000/8192 plus a
512K overhead. I also set our sort_mem to 32168 (32Megs for a sort memory
area). Since connection pooling was in effect, I set max_connections to 64.
And fsync was also set to false.
shared_buffers = 15200 sort_mem = 32168 max_connections=64 fsync=false
You can read the manual to tweak other settings, but I never had the
need to do so. Note that if you set shared_buffers to more than what
your shared memory limit is, postgresql will refuse to start. This
confused us for a while, since no logging was taking place. You can
tweak the startup file in /etc/init.d for the postmaster to write its
output to a log file.
Change the fragment from:
need to do so. Note that if you set shared_buffers to more than what
your shared memory limit is, postgresql will refuse to start. This
confused us for a while, since no logging was taking place. You can
tweak the startup file in /etc/init.d for the postmaster to write its
output to a log file.
Change the fragment from:
/postmaster start > /dev/null 2>
to
/postmaster start > /var/lib/pgsql.log 2>
(or wherever you want to store the log.)
