Subject: Re: Inadequate hosting for www.postgresql.org

From: "Dave Page" <[email-masked]>
Date: Wed, 3 Nov 2004 22:18:35 -0000

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

> -----Original Message-----
> From: Mitch Pirtle [mailto:[email-masked]] 
> Sent: 03 November 2004 21:59
> To: Marc G. Fournier
> Cc: Dave Page; Alexey Borzov; Oleg Bartunov; [email-masked]
> 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).

---

From: Mitch Pirtle <[email-masked]>
Date: Wed, 3 Nov 2004 17:38:33 -0500

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

Super Dave!

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

Looks okay to me.

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

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

---

From: "Gavin M. Roy" <[email-masked]>
Date: Wed, 03 Nov 2004 14:47:14 -0800

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

Gavin

Mitch Pirtle wrote:

>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>  
>

---

From: "Marc G. Fournier" <[email-masked]>
Date: Wed, 3 Nov 2004 20:28:45 -0400 (AST)

> 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: [email-masked]           Yahoo!: yscrappy              ICQ: 7615664

---

From: "Marc G. Fournier" <[email-masked]>
Date: Wed, 3 Nov 2004 20:39:38 -0400 (AST)

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: Mitch Pirtle <[email-masked]>
Date: Wed, 3 Nov 2004 19:47:15 -0500

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

---

From: "Gavin M. Roy" <[email-masked]>
Date: Wed, 03 Nov 2004 17:00:07 -0800

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

---

From: "Marc G. Fournier" <[email-masked]>
Date: Wed, 3 Nov 2004 21:01:33 -0400 (AST)

> performance with shm turned off would tell me that something was wrong

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

----

---

From: Justin Clift <[email-masked]>
Date: Thu, 04 Nov 2004 12:22:01 +1100

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

---

From: Mitch Pirtle <[email-masked]>
Date: Wed, 3 Nov 2004 20:24:32 -0500

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

---

From: "Marc G. Fournier" <[email-masked]>
Date: Wed, 3 Nov 2004 21:54:18 -0400 (AST)

> I always remember to check when weird delays happen for no apparent 

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

----

---

From: "Marc G. Fournier" <[email-masked]>
Date: Wed, 3 Nov 2004 21:58:13 -0400 (AST)

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

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

----

---

From: "Marc G. Fournier" <[email-masked]>
Date: Wed, 3 Nov 2004 22:22:16 -0400 (AST)

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

----

---

From: Mitch Pirtle <[email-masked]>
Date: Wed, 3 Nov 2004 21:53:26 -0500

Now we are talking ;-)

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

-- Mitch

---

From: "Marc G. Fournier" <[email-masked]>
Date: Wed, 3 Nov 2004 23:43:24 -0400 (AST)

> Let us know how it looks after the extensions go through the build as 

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

----

---

From: Mitch Pirtle <[email-masked]>
Date: Wed, 3 Nov 2004 22:50:28 -0500

> 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

---

From: Justin Clift <[email-masked]>
Date: Thu, 04 Nov 2004 15:32:20 +1100

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

---

From: "Marc G. Fournier" <[email-masked]>
Date: Thu, 4 Nov 2004 00:55:11 -0400 (AST)

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

----

---

From: Justin Clift <[email-masked]>
Date: Thu, 04 Nov 2004 16:26:33 +1100

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