Re: [QUESTIONS] How to use memory instead of hd?

Bruce Momjian wrote:

Hi All

I just upgraded to 128MB RAM, and really don't need ALL of it MOST of the
time, and the main reason for it was to help with database speed.

But then I got to thinking, is there a way to just semi-permently put a
database into RAM? so that when a query is done it goes only to the image
of the database in RAM, never even touching the hard drive. This would be
enormasly faster on 50 ~ 100 (or what ever) MB databases, especially on
those new boxes with that 6? or 10? nano second RAM. Especially if one
could pick and chose which table(s) to put in RAM

You can tune your OS to use most of that as buffer cache. However,
writes will be flushed to disk by postgresql fync, or the OS syncing
every so often, but not too bad. You can also up your postgres shared
memory buffers, though some OS's have a limit on that.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^^^
Could we use mmap (instead of shmem) with MAP_ANON flag to get more memory
for shared buffer pool ?
I'm using FreeBSD, man mmap says:

MAP_ANON Map anonymous memory not associated with any specific file.
The file descriptor used for creating MAP_ANON regions is
used only for naming, and may be specified as -1 if no name
is associated with the region.

Vadim

Yes, we could. I don't think we do because we an anon-mapped region is
the same as shmem, but if the limit is higher for anon mmap, we could
used it.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Bruce Momjian wrote:

Bruce Momjian wrote:

Could we use mmap (instead of shmem) with MAP_ANON flag to get more memory
for shared buffer pool ?
I'm using FreeBSD, man mmap says:

Yes, we could. I don't think we do because we an anon-mapped region is
the same as shmem, but if the limit is higher for anon mmap, we could
used it.

It could be faster too,
and it API is cleaner too (IMHO).
If we resolve the fork/exec -> just fork problem in the server
mmap would make a good improvment in simplicity and probably
speed too.

regards,
--
---------------------------------------------
G�ran Thyni, sysadm, JMS Bildbasen, Kiruna

"maillist" == Bruce Momjian <maillist@candle.pha.pa.us> writes:

Bruce Momjian wrote: > > > > > Hi All > > > > I just upgraded
to 128MB RAM, and really don't need ALL of it MOST of the > >
time, and the main reason for it was to help with database
speed. > > > > But then I got to thinking, is there a way to
just semi-permently put a > > database into RAM? so that when a
query is done it goes only to the image > > of the database in
RAM, never even touching the hard drive. This would be > >
enormasly faster on 50 ~ 100 (or what ever) MB databases,
especially on > > those new boxes with that 6? or 10? nano
second RAM. Especially if one > > could pick and chose which
table(s) to put in RAM > > You can tune your OS to use most of
that as buffer cache. However, > writes will be flushed to
disk by postgresql fync, or the OS syncing > every so often,
but not too bad. You can also up your postgres shared > memory
buffers, though some OS's have a limit on that.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^^^ Could we use mmap (instead of shmem) with
MAP_ANON flag to get more memory for shared buffer pool ? I'm
using FreeBSD, man mmap says:

MAP_ANON Map anonymous memory not associated with any specific
file. The file descriptor used for creating MAP_ANON regions
is used only for naming, and may be specified as -1 if no name
is associated with the region.

Vadim

Yes, we could. I don't think we do because we an anon-mapped
region is the same as shmem, but if the limit is higher for anon
mmap, we could used it.

On FreeBSD the limit for mmap is basically the amount of swap vs a
predefined limit when a kernel is built. This was a project I was
thinking of doing but have not had the time yet. mmap may also be
faster due to tighter intergration in the kernel. In general it is
better to avoid all the SYSV IPC calls and use other methods of doing
the needed operations. Stevens book (either Advanced Unix Programming
or Unix Network Programming) has a good discussion of the pro/CONS of
the SYSV IPC interfaces.

-- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us |
Drexel Hill, Pennsylvania 19026 + If your life is a hard drive,
| (610) 353-9879(w) + Christ can be your backup. | (610)
853-3000(h)

--
Kent S. Gordon
Architect
iNetSpace Co.
voice: (972)851-3494 fax:(972)702-0384 e-mail:kgor@inetspace.com

Yes, we could. I don't think we do because we an anon-mapped
region is the same as shmem, but if the limit is higher for anon
mmap, we could used it.

On FreeBSD the limit for mmap is basically the amount of swap vs a
predefined limit when a kernel is built. This was a project I was
thinking of doing but have not had the time yet. mmap may also be
faster due to tighter intergration in the kernel. In general it is
better to avoid all the SYSV IPC calls and use other methods of doing
the needed operations. Stevens book (either Advanced Unix Programming
or Unix Network Programming) has a good discussion of the pro/CONS of
the SYSV IPC interfaces.

OK, mmap added to FAQ.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Yes, we could. I don't think we do because we an anon-mapped
region is the same as shmem, but if the limit is higher for anon
mmap, we could used it.

On FreeBSD the limit for mmap is basically the amount of swap vs a
predefined limit when a kernel is built. This was a project I was
thinking of doing but have not had the time yet. mmap may also be
faster due to tighter intergration in the kernel. In general it is
better to avoid all the SYSV IPC calls and use other methods of doing
the needed operations. Stevens book (either Advanced Unix Programming
or Unix Network Programming) has a good discussion of the pro/CONS of
the SYSV IPC interfaces.

I meant, mmap() added to TODO.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

G�ran Thyni wrote:

Bruce Momjian wrote:

Bruce Momjian wrote:

Could we use mmap (instead of shmem) with MAP_ANON flag to get more memory
for shared buffer pool ?
I'm using FreeBSD, man mmap says:

Yes, we could. I don't think we do because we an anon-mapped region is
the same as shmem, but if the limit is higher for anon mmap, we could
used it.

It could be faster too,
and it API is cleaner too (IMHO).
If we resolve the fork/exec -> just fork problem in the server
mmap would make a good improvment in simplicity and probably
speed too.

MAP_INHERIT
Permit regions to be inherited across execve(2) system calls.

- exec isn't problem...

Vadim

Vadim B. Mikheev wrote:

G�ran Thyni wrote:

Bruce Momjian wrote:

Yes, we could. I don't think we do because we an anon-mapped region is
the same as shmem, but if the limit is higher for anon mmap, we could
used it.

It could be faster too,
and it API is cleaner too (IMHO).
If we resolve the fork/exec -> just fork problem in the server
mmap would make a good improvment in simplicity and probably
speed too.

MAP_INHERIT
Permit regions to be inherited across execve(2) system calls.

- exec isn't problem...

Vadim

It is on non-BSD systems (I checked Linux 2.0.33 and DG/UX, no go).
And how do you keep the handles after an exec.

BTW, to get rid of exec to more easily replace shmem with mmap is only
part of it,
to use mmap instead of shmem to easier remove the exec is the other
side. :-)

best regards,
--
---------------------------------------------
G�ran Thyni, sysadm, JMS Bildbasen, Kiruna

Bruce Momjian wrote:

Hi All

I just upgraded to 128MB RAM, and really don't need ALL of it MOST of the
time, and the main reason for it was to help with database speed.

But then I got to thinking, is there a way to just semi-permently put a
database into RAM? so that when a query is done it goes only to the image
of the database in RAM, never even touching the hard drive. This would be
enormasly faster on 50 ~ 100 (or what ever) MB databases, especially on
those new boxes with that 6? or 10? nano second RAM. Especially if one
could pick and chose which table(s) to put in RAM

You can tune your OS to use most of that as buffer cache. However,
writes will be flushed to disk by postgresql fync, or the OS syncing
every so often, but not too bad. You can also up your postgres shared
memory buffers, though some OS's have a limit on that.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^^^
Could we use mmap (instead of shmem) with MAP_ANON flag to get more memory
for shared buffer pool ?
I'm using FreeBSD, man mmap says:

MAP_ANON Map anonymous memory not associated with any specific file.
The file descriptor used for creating MAP_ANON regions is
used only for naming, and may be specified as -1 if no name
is associated with the region.

Vadim

I am going through my mailbox and I believe we never found a portable
way to allocate shared memory other than system V shared memory.
Increasing the amount of buffers beyond a certain amount requires a
kernel change.

Has anyone come up with a better way?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

I would not worry too much about the kernel parameter change.
Systems with such low values will need those parameter changes for most
other databases too (since they all use SYS V shm).
Installations where the administrator is not involved will most likely be test/university
installations, where memory is usually sparse anyway (buffer increase not wanted).

I would not change to mmap if that would actually require a file on some systems.
(systems with Gb of RAM are starting to gain ground is only one argument)

Andreas