Re: Need help with postgresql/apache/php optimisation - Mailing list pgsql-general

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?

>     For information, i have a Celeron 1.2Ghz with 256Mb, IDE drive, enough
> bandewitch, and about 3000 hit per day. Its postgresql 7.2.lastone,
> apache 1.3.lastone, linux (redhat), and the last 2.4 kernel after the
> exploit problem. Its not 'my' server, so i cant upgrade anything of it
> (RAM is very short i think). Im hosting a web site with apache/php. The
> table are not huge, the biggest is aroung 3000rows and only 25-30
> tables.

Not a very big machine, but it's not a big database.

>     The problem is that on "fire time", the load go to > 1 and stay long
> time. But with top (i use top -d 1 to have "real" load average) i can
> see that the CPU is more than 50% idling.
>
> For exemple, i have this kind of stat :
>
> 0s - load 1.5 - cpu idling 0%
> 5s - load 1.6 - cpu ilding 50%
> 6s -> 60s - load around 1.2 - cpu idling around 50%-100%
>
> (Dunno if its very easy to understand).

I would guess disk activity is the problem.

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

> In fact, i hosted the old site with mysql/apache and i was very happy to
> see the load going from 0.90 to 0.40 but the population growing up and
> the problem came.
>
> I made the common optimisation with VACUUM ANALYZE and some from the
> documentation.

OK - you have obviously done the basics.

> Maybe i dont understand what load average mean, but i dont understand
> why with more than 50% cpu idling, the load average dont grow down. So i
> thought i lose cpu from somewhere ... but the probleme is what is this
> somewhere :).

Load average is a measure of how many processes are waiting. They might be
waiting for CPU time, or disk I/O or network, or ...

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?

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

--
  Richard Huxton
  Archonet Ltd