From: Simon Waters <simonw@zynet.net>
Organization: ZyNet Limited
To: pgsql-performance@postgresql.org
Subject: Re: [GENERAL] Drupal and PostgreSQL - performance issues?
Date: Mon, 13 Oct 2008 15:49:33 +0100
User-Agent: KMail/1.9.9
References: <2434A118-CEFA-43A9-BA88-006EC1F80326@hoegh.org>
	<06DDE07E-3CF9-4B3E-8536-7C164BAB8FC6@hoegh.org>
	<dcc563d10810130719v799e0601rdcb7234bdbe61fea@mail.gmail.com>
In-Reply-To: <dcc563d10810130719v799e0601rdcb7234bdbe61fea@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200810131549.33863.simonw@zynet.net>

On Monday 13 October 2008 15:19:07 Scott Marlowe wrote:
> 
> > shared_buffers = 24MB
> > max_fsm_pages = 153600
>
> Well, 24MB is pretty small.  See if you can increase your system's
> shared memory and postgresql's shared_buffers to somewhere around 256M
> to 512M.  It likely won't make a big difference in this scenario, but
> overall it will definitely help.

I noted after reading earlier messages in the thread, that my distro documents 
that the values it default to for shared_buffers is rather small.

One of our servers is fairly pressed for memory (some of the time). Is there 
any way to measure the amount of churn in the shared_buffers, as a way of 
demonstrating that more is needed (or at this moment more would help)?

A few very small databases on this server, and one which is 768M (still pretty 
small but a lot bigger than the rest, most of which is logging information). 
The only "hot" information is the session table, ~9000 lines, one index on 
the session id. Can I ask Postgres to tell me, or estimate, how much memory 
this table would occupy if fully cached in memory?

Half the problem in modern computing is knowing what is "slow". In this case, 
counting the rows of the session table takes about 100ms. Deleting expired 
session rows about 120ms, more if it hasn't done it for a while, which is I 
guess evidence that table isn't being cached in memory as efficiency as it 
could be.

In this case the server thinks the system I/O is zero for half the tools in 
use, because of the RAID hardware, so most of the Linux based tools are 
useless in this context.

At the risk of thread hijacking, for the session table I wonder if we are 
handling it the most efficient way. It is just a regular table, indexed on 
session_id. Each request of note to the server requires retrieval of the 
session record, and often updating the expiry information. Every N requests 
the application also issues a:

DELETE FROM sessions WHERE expires<NOW() OR expires IS NULL;

Since there is no index on the table, it sequentially scans, and deletes the 
stale records. I'm thinking since it is indexed for regular queries, making N 
larger has almost no obvious penalty except we accumulate a small number of 
stale records for longer. I'm not sure if an index on expires is worth it, 
probably too small to make much difference either way.

As for Drupal on Postgres, it might be worth the effort for big 
implementations, I did it for a while, but doing it again I'd go with MySQL. 
Nothing to do with the database, everything to do with support for 3rd party 
add-ins. Till Drupal gets to the automated testing of these things routinely 
against different backends and configs...... Perhaps that is all that is 
needed, a service for Drupal authors that tries their plugins against 
Postgres automatically and complains if it doesn't work?