[Re] Need help with postgresql/apache/php optimisation

[Froggy / Froggy Corp.]

Thx for your quick answer too :)

Richard Huxton wrote:

On Wednesday 18 February 2004 20:18, Froggy / Froggy Corp. wrote:

Hello,

I asked one time for more "benchmark" soft to know where is the cpu
average, and read the post about optimising the postgresql.conf (and use
them), but i allways get a load > 1 on fire time (dunno the right name,
"coup de feu" in french (10h00 -> 14h00, 18h00 -> 21h00).
This is the period when you get the most hits, yes?

yes

With different software, i dont see anything wrong (or i dont understand
how to use them), the problem is the memory which make some nice road
around 12Mo Free and 3Mo Free, but the swap dont really grow up (but
linux make a lot of cache).

Not sure I understand you fully, but if swap isn't active you should be OK.
See below for a good test.

Swap is active, but the size don't change a lot. (12Mb common time,
maybe 50Mb on rush time).
There are three things to look at while testing this:
1. The output of "vmstat 1" - this will show memory usage, swap activity disk
blocks in/out, cpu usage etc.
2. The output from "top", press "M" to sort by memory usage - that way we can
see how much memory is being used.
3. How many requests are you processing at the same time?

I will use vmstat 1 tomorrow (22:24 here, so too late)

Around 4/5 postgresql thread and around 20 apache thread with top.
With mod-status from apache, i get same information, 20 requests in
process, but only some in activity.

If someone could help me, i need to put a new feature which will add
more than 2000 hit per day and im afraid about the life of the server

Only 5000 hits per day? I'm sure we can get that working.

I hope :D.

Another question, i put on postgresql.conf a limited number of
postgresql max_connections , and use permanent link beetween apache and
postgresql. The problem is that a lot of apache thread is used for
sending data (gfx). Is there a way to say that some thread are only here
for php process ?

thx in advance,
regards,

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

[scott.marlowe]

On Wed, 18 Feb 2004, Froggy / Froggy Corp. wrote:

Another question, i put on postgresql.conf a limited number of
postgresql max_connections , and use permanent link beetween apache and
postgresql. The problem is that a lot of apache thread is used for
sending data (gfx). Is there a way to say that some thread are only here
for php process ?

No, PHP's "connection pooling" is more of an accident looking for a place
to happen than useful sometimes. you might want to try reverting to just
plain pg_connects and see how the machine behaves.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Froggy / Froggy Corp.]

In fact, under php4.?, the connexion is not save under session, so i
allways need to make a pg_connect. But under documentation it say that
the socket stay open, so i save some cpu.

"scott.marl owe" wrote:

On Wed, 18 Feb 2004, Froggy / Froggy Corp. wrote:

Another question, i put on postgresql.conf a limited number of
postgresql max_connections , and use permanent link beetween apache and
postgresql. The problem is that a lot of apache thread is used for
sending data (gfx). Is there a way to say that some thread are only here
for php process ?

No, PHP's "connection pooling" is more of an accident looking for a place
to happen than useful sometimes. you might want to try reverting to just
plain pg_connects and see how the machine behaves.

---------------------------(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

[Richard Huxton]

On Wednesday 18 February 2004 21:35, Froggy / Froggy Corp. wrote:

Another question, i put on postgresql.conf a limited number of
postgresql max_connections , and use permanent link beetween apache and
postgresql. The problem is that a lot of apache thread is used for
sending data (gfx). Is there a way to say that some thread are only here
for php process ?

Given your lack of memory, I'd drop the permanent (persistent) connections
between php and postgresql - connect for every page you need to. That should
free up some memory.

Once we have the vmstat figures, we can look at how to tune postgresql.conf
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html