Re: [GENERAL] Drupal and PostgreSQL - performance issues?

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?