Re: Inadequate hosting for www.postgresql.org

Marc's gone for a bite to eat so I'll chip in...

> -----Original Message-----
> From: Mitch Pirtle [mailto:mitch(dot)pirtle(at)gmail(dot)com]
> Sent: 03 November 2004 21:59
> To: Marc G. Fournier
> Cc: Dave Page; Alexey Borzov; Oleg Bartunov; pgsql-www(at)postgresql(dot)org
> Subject: Re: [pgsql-www] Inadequate hosting for www.postgresql.org
>
> Hmm, seems to shave a second off of the results posted by
> Oleg. Progress?
>
> http://www.postgresql.org/index-test.php now reports a bunch
> of pg_result errors, so I cannot comment there.

Fixed. Marc commented out some stuff for testing.

> The thing that has my attention is the overall time reported
> by Webperf for www.alexey.beta.postgresql.org. I did the
> same tests on www.spacemonkeylabs.com, and receive timings
> 1/3rd, and this is a stock Mambo 4/5/1a with mmcache disabled
> (something provided by Plesk7 conflicts so I cannot load it
> on this machine). FWIW Mambo is all OO-based PHP, with
> several external libraries and 5 different RSS exports on the
> home page (something like 36 queries, the horror).
>
> From here I don't know what to suspect - network, hardware,
> or programming. *grumblegrumble*
>
> Marc, you say you got 15/30 set for apache, can you tell me
> how many procs are currently running?

56 5 seconds ago: http://www.postgresql.org/server-status

> Also, is the new website using pconnect or connect?

pconnect.

Regards, Dave (who's also off for food and then bed).

On Wed, 3 Nov 2004 22:18:35 -0000, Dave Page <dpage(at)vale-housing(dot)co(dot)uk> wrote:
> Marc's gone for a bite to eat so I'll chip in...

Super Dave!

> > Marc, you say you got 15/30 set for apache, can you tell me
> > how many procs are currently running?
>
> 56 5 seconds ago: http://www.postgresql.org/server-status

Looks okay to me.

> > Also, is the new website using pconnect or connect?
>
> pconnect.

Okay, hardware does not seem to be a constraint, apache/php seems
fine, mmcache is enabled; and so logically whatever is going on has to
be at the application level, either php or postgresql. Right?

> Regards, Dave (who's also off for food and then bed).

Hehe no rest for the wicked, I have to keep up with the germans and australians!

-- Mitch, eyes propped open with toothpicks

Just offering my 0.02... have you checked to see if the delays are due
to heavy io?

Gavin

Mitch Pirtle wrote:

>On Wed, 3 Nov 2004 22:18:35 -0000, Dave Page <dpage(at)vale-housing(dot)co(dot)uk> wrote:
>
>
>>Marc's gone for a bite to eat so I'll chip in...
>>
>>
>
>Super Dave!
>
>
>
>>>Marc, you say you got 15/30 set for apache, can you tell me
>>>how many procs are currently running?
>>>
>>>
>>56 5 seconds ago: http://www.postgresql.org/server-status
>>
>>
>
>Looks okay to me.
>
>
>
>>>Also, is the new website using pconnect or connect?
>>>
>>>
>>pconnect.
>>
>>
>
>Okay, hardware does not seem to be a constraint, apache/php seems
>fine, mmcache is enabled; and so logically whatever is going on has to
>be at the application level, either php or postgresql. Right?
>
>
>
>>Regards, Dave (who's also off for food and then bed).
>>
>>
>
>Hehe no rest for the wicked, I have to keep up with the germans and australians!
>
>-- Mitch, eyes propped open with toothpicks
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>

On Wed, 3 Nov 2004, Mitch Pirtle wrote:

> On Wed, 3 Nov 2004 22:18:35 -0000, Dave Page <dpage(at)vale-housing(dot)co(dot)uk> wrote:
>> Marc's gone for a bite to eat so I'll chip in...
>
> Super Dave!
>
>>> Marc, you say you got 15/30 set for apache, can you tell me
>>> how many procs are currently running?
>>
>> 56 5 seconds ago: http://www.postgresql.org/server-status
>
> Looks okay to me.
>
>>> Also, is the new website using pconnect or connect?
>>
>> pconnect.
>
> Okay, hardware does not seem to be a constraint, apache/php seems
> fine, mmcache is enabled; and so logically whatever is going on has to
> be at the application level, either php or postgresql. Right?

does anyone know of any issues with php 4.3.9?

I just recently upgraded to it from 4.3.8 (ie. a week ago) ... right now,
php seems to be the central issue ... I think I'm going to build an
Apache/PHP4.3.8 server tomorrow, and see if that makes a difference ... I
drop'd the # of processes on taht server by 1/2 this afternoon, the
loadavg on the server right now is <.5 ... and alexey's site *still* takes
2.5sec to load up ... and the CPUs are running ~80% idle:

last pid: 77489; load averages: 0.46, 0.35, 0.38 up 11+09:14:26 20:27:36
371 processes: 1 running, 368 sleeping, 2 zombie
CPU states: 11.5% user, 0.0% nice, 6.3% system, 0.8% interrupt, 81.3% idle
Mem: 1715M Active, 1388M Inact, 640M Wired, 203M Cache, 199M Buf, 10M Free
Swap: 8192M Total, 3824K Used, 8188M Free

So its gotta be something in mod_php itself ...

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

On Wed, 3 Nov 2004, Gavin M. Roy wrote:

> Just offering my 0.02... have you checked to see if the delays are due to
> heavy io?

From the server side of things, I've checked everything that I can think
of ... I ran a 'find . -mmin +1 -print | wc -l' on /usr/local/lib, it
reported back 28k files processed in ~45sec (641files/sec) ... I realize
that this doesn't simulate what Alexey is doing, but he's only reading in
a dozen files or so, according to him, for the index page ... is should be
able to handle that easily enough ...

I even grab'd a recursive directory script off of php.net, so taht I ran
it thorugh php ... did a stat on all 28k files to get mtime (again, not
reading in the whole file, only the meta data), but I was getting
~200files/sec off of that ...

So, the only thing I can think of is a problem with Alexey's code, which
he swears is impossible ... or a problem with mod_php4.3.9 itself ...

I even drop'd the # of processes on that server by 1/2 this afternoon, and
there is no noticeable improvement to Alexey's code ...

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

php 4.3.9 has no issues that I am aware of, and I am running 20
different sites on it. Some basic, some complex. Faster mmcache
performance with shm turned off would tell me that something was wrong
however. :-/

> I even grab'd a recursive directory script off of php.net, so taht I ran
> it thorugh php ... did a stat on all 28k files to get mtime (again, not
> reading in the whole file, only the meta data), but I was getting
> ~200files/sec off of that ...
>
> So, the only thing I can think of is a problem with Alexey's code, which
> he swears is impossible ... or a problem with mod_php4.3.9 itself ...
>
> I even drop'd the # of processes on that server by 1/2 this afternoon, and
> there is no noticeable improvement to Alexey's code ...

Hmm, is it possible to create a test script with a test schema, say
four or five tables that is portable? That way we could run it on
several machines to get a comparison. I have a couple SMP and
uniprocessor machines, all with 4.3.9 that I would happily run
benchmarks on.

-- Mitch

If it would help, I could give Alexey a shell and pgsql database to test
on my server with and see if he sees any significant differences.

Gavin

Mitch Pirtle wrote:

>php 4.3.9 has no issues that I am aware of, and I am running 20
>different sites on it. Some basic, some complex. Faster mmcache
>performance with shm turned off would tell me that something was wrong
>however. :-/
>
>
>
>>I even grab'd a recursive directory script off of php.net, so taht I ran
>>it thorugh php ... did a stat on all 28k files to get mtime (again, not
>>reading in the whole file, only the meta data), but I was getting
>>~200files/sec off of that ...
>>
>>So, the only thing I can think of is a problem with Alexey's code, which
>>he swears is impossible ... or a problem with mod_php4.3.9 itself ...
>>
>>I even drop'd the # of processes on that server by 1/2 this afternoon, and
>>there is no noticeable improvement to Alexey's code ...
>>
>>
>
>Hmm, is it possible to create a test script with a test schema, say
>four or five tables that is portable? That way we could run it on
>several machines to get a comparison. I have a couple SMP and
>uniprocessor machines, all with 4.3.9 that I would happily run
>benchmarks on.
>
>-- Mitch
>
>

On Wed, 3 Nov 2004, Mitch Pirtle wrote:

> php 4.3.9 has no issues that I am aware of, and I am running 20
> different sites on it. Some basic, some complex. Faster mmcache
> performance with shm turned off would tell me that something was wrong
> however. :-/

Here is how FreeBSD ports is building mod_php4 ... does anything look
wrong with this?

#!/bin/sh
./configure \
--enable-versioning \
--enable-memory-limit \
--with-layout=GNU \
--with-config-file-scan-dir=/usr/local/etc/php \
--disable-all \
--with-regex=php \
--with-apxs=/usr/local/sbin/apxs \
--prefix=/usr/local i386-portbld-freebsd4.10

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

Marc G. Fournier wrote:
<snip>
> So its gotta be something in mod_php itself ...

Is there any chance it's a name resolution, reverse lookup, or some
other kind of similar thing going wrong? (It's one of those things that
I always remember to check when weird delays happen for no apparent reason)

Hmmm, couldn't be any kind of firewalling or similar issue either?

Regards and best wishes,

Justin Clift

--enable-versioning: never did it, never saw the need for it, highly suspect it
--disable-all: what is this, not listed at php.net either?

Those are the only two that I think look fishy. Compiled into apache,
or as a dynamic module?

-- Mitch

On Wed, 3 Nov 2004 21:01:33 -0400 (AST), Marc G. Fournier
<scrappy(at)postgresql(dot)org> wrote:
> Here is how FreeBSD ports is building mod_php4 ... does anything look
> wrong with this?
>
> #!/bin/sh
> ./configure \
> --enable-versioning \
> --enable-memory-limit \
> --with-layout=GNU \
> --with-config-file-scan-dir=/usr/local/etc/php \
> --disable-all \
> --with-regex=php \
> --with-apxs=/usr/local/sbin/apxs \
> --prefix=/usr/local i386-portbld-freebsd4.10

On Thu, 4 Nov 2004, Justin Clift wrote:

> Marc G. Fournier wrote:
> <snip>
>> So its gotta be something in mod_php itself ...
>
> Is there any chance it's a name resolution, reverse lookup, or some
> other kind of similar thing going wrong? (It's one of those things that
> I always remember to check when weird delays happen for no apparent
> reason)

Nope, that's always been disabled ... unless its something in Alexey's
code that is doing it? I hadn't thought of that ... I only thought of the
apache server itself ... Alexey?

> Hmmm, couldn't be any kind of firewalling or similar issue either?

Nope, all my testing is from the VM itself ...

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

On Wed, 3 Nov 2004, Mitch Pirtle wrote:

> --enable-versioning: never did it, never saw the need for it, highly suspect it

k, I can get rid of that and try ...

> --disable-all: what is this, not listed at php.net either?

--disable-all Disable all extensions enabled by default.

extensions are build/installed seperately ...

> Those are the only two that I think look fishy. Compiled into apache,
> or as a dynamic module?

dynamic module, and the 'extensions' are seperate and installed into
/usr/local/lib/php/20020429:

# ls /usr/local/lib/php/20020429
Optimizer mcrypt.so
Optimizer_TS mhash.so
ZendExtensionManager.so mmcache.so
ZendExtensionManager_TS.so mnogosearch.so
bcmath.so mysql.so
bz2.so openssl.so
calendar.so overload.so
ctype.so pcre.so
curl.so pdf.so
domxml.so pgsql.so
exif.so posix.so
ftp.so pspell.so
gd.so session.so
gettext.so sockets.so
iconv.so tokenizer.so
imap.so wddx.so
interbase.so xml.so
ldap.so xmlrpc.so
mbstring.so zlib.so
mcal.so

>
> -- Mitch
>
> On Wed, 3 Nov 2004 21:01:33 -0400 (AST), Marc G. Fournier
> <scrappy(at)postgresql(dot)org> wrote:
>> Here is how FreeBSD ports is building mod_php4 ... does anything look
>> wrong with this?
>>
>> #!/bin/sh
>> ./configure \
>> --enable-versioning \
>> --enable-memory-limit \
>> --with-layout=GNU \
>> --with-config-file-scan-dir=/usr/local/etc/php \
>> --disable-all \
>> --with-regex=php \
>> --with-apxs=/usr/local/sbin/apxs \
>> --prefix=/usr/local i386-portbld-freebsd4.10
>

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

On Wed, 3 Nov 2004, Marc G. Fournier wrote:

> On Wed, 3 Nov 2004, Mitch Pirtle wrote:
>
>> --enable-versioning: never did it, never saw the need for it, highly
>> suspect it
>
> k, I can get rid of that and try ...

You might have hit it on the mark:

# time fetch http://wwwdevel.postgresql.org
fetch: http://wwwdevel.postgresql.org: size of remote file is not known
wwwdevel.postgresql.org 17 kB 8466 kBps
0.000u 0.014s 0:00.80 1.2% 48+556k 0+0io 0pf+0w

worst I've seen so far is 1.24sec ... I haven't recompiled the extensions
yet, only the mod_php itself ... will do those next without versioning and
see if that helps any ... definitely alot better then it was ...

I'm seeing as low as .78sec load times now on those pages ...

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

Now we are talking ;-)

Let us know how it looks after the extensions go through the build as well, ok?

-- Mitch

On Wed, 3 Nov 2004 22:22:16 -0400 (AST), Marc G. Fournier
<scrappy(at)postgresql(dot)org> wrote:
> On Wed, 3 Nov 2004, Marc G. Fournier wrote:
>
> > On Wed, 3 Nov 2004, Mitch Pirtle wrote:
> >
> >> --enable-versioning: never did it, never saw the need for it, highly
> >> suspect it
> >
> > k, I can get rid of that and try ...
>
> You might have hit it on the mark:
>
> # time fetch http://wwwdevel.postgresql.org
> fetch: http://wwwdevel.postgresql.org: size of remote file is not known
> wwwdevel.postgresql.org 17 kB 8466 kBps
> 0.000u 0.014s 0:00.80 1.2% 48+556k 0+0io 0pf+0w
>
> worst I've seen so far is 1.24sec ... I haven't recompiled the extensions
> yet, only the mod_php itself ... will do those next without versioning and
> see if that helps any ... definitely alot better then it was ...
>
> I'm seeing as low as .78sec load times now on those pages ...
>
>
>
> ----
> Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
> Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664
>

On Wed, 3 Nov 2004, Mitch Pirtle wrote:

> Now we are talking ;-)
>
> Let us know how it looks after the extensions go through the build as
> well, ok?

Nope, looks like the --enable version only matters in the main php binary,
as it hasn't improved load times ... but, using lynx on teh server itself,
its definitely not hanging on load anymore like it was before ...

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

On Wed, 3 Nov 2004 23:43:24 -0400 (AST), Marc G. Fournier
<scrappy(at)postgresql(dot)org> wrote:
> Nope, looks like the --enable version only matters in the main php binary,
> as it hasn't improved load times ... but, using lynx on teh server itself,
> its definitely not hanging on load anymore like it was before ...

Ok then. Looks to me like we've dealt with hardware, i/o, apache,
php, and the database ;-) I mean, if we can't tune postgresql than it
should be impossible LOL

At this point it would be good to get some profiling on one of the
slower pages - being able to time the database tasks, template
rendering and logical computation would go a long way to seeing just
where the tuning steps could be taken.

So we have shown that other PHP scripts on this server are quick, and
that the focus of the tuning is really pointed at the new website,
right?

-- Mitch

Marc G. Fournier wrote:
<snip>
> You might have hit it on the mark:

Um, now I'm lost. Recompiling the main PHP binary without
--enable-versioning was the winner?

Regards and best wishes,

Justin Clift

On Thu, 4 Nov 2004, Justin Clift wrote:

> Marc G. Fournier wrote:
> <snip>
>> You might have hit it on the mark:
>
> Um, now I'm lost. Recompiling the main PHP binary without
> --enable-versioning was the winner?

Ya ... I think what happens is that when the main php starts up, the first
thing it does is goes through its 'extensions' to make sure they are part
of the same version ... *why* it would do it each and ever connection, vs
just when apache starts up, I don't know ... but, I'm on a dial-up link at
home and I can actually load wwwdevel.postgresql.org now, where before I
had enough time to go to the kitchen for a drink waiting for it to load
...

But, I also don't believe it was *just* that option ... the database was
slow, due to the need for the vacuum full, which was apparent with the
load times on the current site ...

But, after upgrading and restarting apache on all the VMs, with the change
to PHP, even the servers loadavg are all back down to 'normal' ...

What I suspect is that before the PHP maintainer "split up" teh extensions
to seperate ports each, instead of one built binary, he didn't have the
versioning enabled ... it was only because of the splits that he added it,
so it never affected us before ...

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

Hi Marc,

Ok, I've CC'd the listed maintainer of the FreeBSD PHP port on this so
that he finds out the --enable-versioning option may be causing some
performance problems.

Wonder if he's aware of it already at all?

:-/

Regards and best wishes,

Justin Clift

Marc G. Fournier wrote:
> On Thu, 4 Nov 2004, Justin Clift wrote:
>
>> Marc G. Fournier wrote:
>> <snip>
>>
>>> You might have hit it on the mark:
>>
>> Um, now I'm lost. Recompiling the main PHP binary without
>> --enable-versioning was the winner?
>
>
> Ya ... I think what happens is that when the main php starts up, the
> first thing it does is goes through its 'extensions' to make sure they
> are part of the same version ... *why* it would do it each and ever
> connection, vs just when apache starts up, I don't know ... but, I'm on
> a dial-up link at home and I can actually load wwwdevel.postgresql.org
> now, where before I had enough time to go to the kitchen for a drink
> waiting for it to load ...
>
> But, I also don't believe it was *just* that option ... the database was
> slow, due to the need for the vacuum full, which was apparent with the
> load times on the current site ...
>
> But, after upgrading and restarting apache on all the VMs, with the
> change to PHP, even the servers loadavg are all back down to 'normal' ...
>
> What I suspect is that before the PHP maintainer "split up" teh
> extensions to seperate ports each, instead of one built binary, he
> didn't have the versioning enabled ... it was only because of the splits
> that he added it, so it never affected us before ...
>
> ----
> Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
> Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664