Обсуждение: Strange problems with more memory.

Hi all,

I'm running postgres 7.3.4 on a quad Xeon 2.8 GHz with
Mem:  1057824768 309108736 748716032        0 12242944 256413696
Swap: 518053888  8630272 509423616

on Linux version 2.4.26-custom

Data directory is mounted with noatime.

Nothing else but one 11GB database is running on this machine.
When the database was created, I changed the following defaults :
shared_buffers = 24415
sort_mem = 5120
vacuum_mem = 10240
commit_delay = 5000
commit_siblings = 100

These settings worked fine, but were not optimal, I thought, and processing
stuff on this database was a bit slow. The machine is not nearly used to it's
capacity, and I realized that disk IO is what's slowing me down. So I
decided to give postgres more shared memory  and much more sort memory,
as it does a lot of "group by'"s and "order by"'s during the nightly processing.
These were the new settings I tried :
shared_buffers = 61035
sort_mem = 97657

I thought because it's only one process that runs queries exclusively at night,
I should be able to set the sort_mem this high without worrying about running
out of memory.

It seems I was mistaking, as I started getting these kind of errors in dmesg :
VM: killing process postmaster
__alloc_pages: 0-order allocation failed (gfp=0x1d2/0)
__alloc_pages: 0-order allocation failed (gfp=0x1d2/0)
VM: killing process postmaster

and I kept on getting these postgres errors :
ERROR:  Index ???? is not a btree

I systematically reduced the shared buffers back down to 24415, and this kept
on happening. As soon as I reduced sort_mem back to under 10000,the problem
stopped. But the database is just as slow as before. (By slow I mean not as fast as it should
be on such a powerful machine compared to much worse machines running the same processes)

What can I do to make this database run faster on this machine.
Can anyone suggest how I would go about speeding up this database.

I need to prepare a database three times the size of this one, running the same processes,
and I don't know what improvements I can do on hardware to make this possible.

On the current machine I can easily get another 1GB  or 2GB of memory, but will that help at all?
Without going into the details of exactly the queries that run on this machine, what would be needed to
make postgres run very fast on this machine?

Please help.

Kind Regards
Stefan

Stef <svb@ucs.co.za> writes:
> It seems I was mistaking, as I started getting these kind of errors in dmesg :
> VM: killing process postmaster
> __alloc_pages: 0-order allocation failed (gfp=0x1d2/0)
> __alloc_pages: 0-order allocation failed (gfp=0x1d2/0)
> VM: killing process postmaster

This looks like the infamous OOM-kill kernel bug^H^H^Hfeature.  Turn off
memory overallocation in your kernel to get more stable behavior when
pushing the limits of available memory.

> But the database is just as slow as before. (By slow I mean not as
> fast as it should be on such a powerful machine compared to much worse
> machines running the same processes)

If your concern is with a single nightly process, then that quad Xeon is
doing squat for you, because only one of the processors will be working.
See if you can divide up the processing into several jobs that can run
in parallel.  (Of course, if the real problem is that you are disk I/O
bound, nothing will help except better disk hardware.  Way too many
people think they should buy a super-fast CPU and attach it to
consumer-grade IDE disks.  For database work you're usually better off
spending your money on good disks...)

            regards, tom lane

Tom Lane mentioned :
=> Turn off
=> memory overallocation in your kernel to get more stable behavior when
=> pushing the limits of available memory.

I think this will already help a lot.
Thanks!!

=> If your concern is with a single nightly process, then that quad Xeon is
=> doing squat for you, because only one of the processors will be working.
=> See if you can divide up the processing into several jobs that can run
=> in parallel.  (Of course, if the real problem is that you are disk I/O
=> bound, nothing will help except better disk hardware.  Way too many
=> people think they should buy a super-fast CPU and attach it to
=> consumer-grade IDE disks.  For database work you're usually better off
=> spending your money on good disks...)

Got 3 10000 rpm SCSI raid5 on here. I doubt I will get much better than that
without losing both arms and legs...

I think I'll try and even out the disk IO a bit and get 4 processes running in parallel.
At least I can move forward again.

Thanks again!

Kind Regards
Stefan

If your nightly process is heavily read-only, then raid5 is probably
fine. If however, there is a significant write component then it would
perhaps be worth getting another disk and converting to raid10
(alternatively - see previous postings about raid cards with on-board
cache). Are you seeing a lot of write activity?

Note that it is possible for a SELECT only workload to generate
significant write activity - if the resulting datasets are too large for
memory sorting or hashing. I'm *guessing* that with an 11G database and
1G (or was that 2G?) of ram that it is possible to overflow whatever
your sort_mem is set to.


cheers

Mark

Stef wrote:

>Got 3 10000 rpm SCSI raid5 on here. I doubt I will get much better than that
>without losing both arms and legs...
>
>
>