PGSQL GENERAL 12 POSTGRES NOT USING SHARED MEMORY
From: kop@no-spam ("Karl O. Pinc")
Subject: Postgres not using shared memory
Date: Wed, 8 Dec 2004 17:53:51 -0600


Hi,

I can't seem to get postgresql to use shared memory and performance is terrrible.

PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-42)

$ uname -a Linux artsdata 2.4.21-20.0.1.ELsmp #1 SMP Wed Nov 24 20:34:01 EST 2004 i686 i686 i386 GNU/Linux
$ free total used free shared buffers cached Mem: 2055336 1614408 440928 0 4240 1488104
-/+ buffers/cache: 122064 1933272
Swap: 2096440 1216 2095224

$ /sbin/sysctl -a | grep shm ...
kernel.shmall = 1073741823
kernel.shmmax = 1073741823
(I had both above 1 larger, and tried cutting it down by one out of endpoint paranoia. That should be 1GB.)

postgresql.conf:
#shared_buffers = 126976 # 1GB - 32MB (just to leave some below kernel limit)
shared_buffers = 6000 # testing to get _some_ shared memory
=> select * from pg_settings where name = 'shared_buffers';
name | setting | context | vartype | source | min_val | max_val ----------------+---------+------------+---------+--------------------+---------+------------

shared_buffers | 6000 | postmaster | integer | configuration file | 16 | 2147483647
(1 row)

I've see my setting in pg_settings all along, but free never shows me any shared memory used.

I've tried shutting down all the other daemons and restarting postgresql and nothing changes.

What am I doing wrong here?

Thanks.

Karl <kop@no-spam>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

From: doug@no-spam (Doug McNaught)
Subject: Re: Postgres not using shared memory
Date: Fri, 10 Dec 2004 16:30:56 -0500

"Karl O. Pinc" <kop@no-spam> writes:

> Hi,
>
> I can't seem to get postgresql to use shared memory and performance is > terrrible.

1) Linux doesn't track shared pages (which is not the same as shared memory) anymore--the field the in 'free' output is just there to avoid breaking software. Use the 'ipcs' command to list shared memory segments--you'll find Postgres is using what you told it to (otherwise it woudn't start up at all). 2) The -performance list is a good place to find out why queries are running slowly.

-Doug
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@no-spam

From: jd@no-spam ("Joshua D. Drake")
Subject: Re: Postgres not using shared memory
Date: Fri, 10 Dec 2004 13:39:32 -0800

Karl O. Pinc wrote:

> Hi,
>
> I can't seem to get postgresql to use shared memory and performance is > terrrible.
>
> PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) > 3.2.3 20030502 (Red Hat Linux 3.2.3-42)
>
> $ uname -a > Linux artsdata 2.4.21-20.0.1.ELsmp #1 SMP Wed Nov 24 20:34:01 EST 2004 > i686 i686 i386 GNU/Linux >
> $ free > total used free shared buffers cached > Mem: 2055336 1614408 440928 0 4240 1488104
> -/+ buffers/cache: 122064 1933272
> Swap: 2096440 1216 2095224
>
> $ /sbin/sysctl -a | grep shm > ...
> kernel.shmall = 1073741823
> kernel.shmmax = 1073741823

free will not correctly show shared memory usage if you are allocating more than 512 megs (I think). I don't recall the exact amount but this is a limitation of free.

What type of hard drives do you have? What does a sar 1 or iostat report?

Sincerely,

Joshua D. Drake
> (I had both above 1 larger, and tried cutting it down by one out of > endpoint paranoia. That should be 1GB.)
>
> postgresql.conf:
> #shared_buffers = 126976 # 1GB - 32MB (just to leave some > below kernel limit)
> shared_buffers = 6000 # testing to get _some_ shared memory >
> => select * from pg_settings where name = 'shared_buffers';
> name | setting | context | vartype | source > | min_val | max_val > ----------------+---------+------------+---------+--------------------+---------+------------
>
> shared_buffers | 6000 | postmaster | integer | configuration file > | 16 | 2147483647
> (1 row)
>
> I've see my setting in pg_settings all along, but free never shows me > any shared > memory used.
>
> I've tried shutting down all the other daemons and restarting > postgresql and nothing > changes.
>
> What am I doing wrong here?
>
> Thanks.
>
> Karl <kop@no-spam>
> Free Software: "You don't pay back, you pay forward."
> -- Robert A. Heinlein >
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if > your > joining column's datatypes do not match
-- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@no-spam - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:jd@no-spam title:Consultant tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1
end:vcard
[plaintext jd.vcf]

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

From: kleptog@no-spam (Martijn van Oosterhout)
Subject: Re: Postgres not using shared memory
Date: Sat, 11 Dec 2004 00:14:14 +0100

On Wed, Dec 08, 2004 at 05:53:51PM -0600, Karl O. Pinc wrote:
> Hi,
>=20
> I can't seem to get postgresql to use shared memory and performance is > terrrible.

The shared column in free doesn't represent the amount of SysV shared memory used. If the system can't allocate the amount you ask it'll tell you.

Running ipcs as root will show you the segment.

Perhaps you should tell us what the actual problem is...
--=20
Martijn van Oosterhout <kleptog@no-spam> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQFBui3BY5Twig3Ge+YRAq9DAJ98Xt1df3O+zibTmeaQGobaDlrwxwCgtsn/
bHZ+lj645W6ic4u17yeQxjo=
=birz -----END PGP SIGNATURE-----


From: kop@no-spam ("Karl O. Pinc")
Subject: Re: Postgres not using shared memory
Date: Thu, 16 Dec 2004 12:48:59 -0600

On 2004.12.10 15:30 Doug McNaught wrote:
> "Karl O. Pinc" <kop@no-spam> writes:
> > > Hi,
> >
> > I can't seem to get postgresql to use shared memory and performance > is > > terrrible.
> > 1) Linux doesn't track shared pages (which is not the same as shared > memory) anymore--the field the in 'free' output is just there to > avoid breaking software. Use the 'ipcs' command to list shared > memory segments--you'll find Postgres is using what you told it to > (otherwise it woudn't start up at all).
> 2) The -performance list is a good place to find out why queries are > running slowly.

Thank you all for your replies and your help. I found out hours after sending the original post that Linux does not report shared memory in 'free', and for some reason the first time I tried 'ipcs' (before I posted) I must have done something wrong because I wasn't seeing it there either.

Moments after I posted the network crashed, and hilarity ensued.
It's taken me until now to get back to everybody, and determine that my problems are not postgresql related.

Thanks again for the help and sorry for the delay in my response.

Karl <kop@no-spam>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend