What's slowing things down?

[lawpoop]

Hello all -

I have a problem with a php page. I have a setup with Apache 2.0, PHP
5 and Postgres 8.1 on Debian 4.0. My script uses simplexml to parse
large xml files ( 8 files at 2-15 MB ea. ) and do inserts into the
database.

The problem is twofold. One, output on the parsing page seems to come
in starts and fits. I have a message to the user that gets echoed each
time parsing a file is finished; but these messages come all at once,
like two or three at a time. In other words, the message doesn't come
when the parsing is finished; they seem to build up. It's a simple
loop so I don't think the problem is in the program

Also, when I am going through the parsing loop for a set of files, the
rest of the site doesn't respond until the looping is over. The
parsing seems to basically stop all web serving.

How can I find out if the problem is with Apache, PHP, or Postgres?

[Michael Fesser]

..oO(la*****@gmail.com)

>The problem is twofold. One, output on the parsing page seems to come
in starts and fits. I have a message to the user that gets echoed each
time parsing a file is finished; but these messages come all at once,
like two or three at a time. [...]

Try flush() to force a flushing of the output buffer.

http://www.php.net/flush

Micha

[NC]

On Jul 10, 12:26 pm, [e-mail] wrote:

>
I have a problem with a php page. I have a setup with Apache 2.0, PHP
5 and Postgres 8.1 on Debian 4.0. My script uses simplexml to parse
large xml files ( 8 files at 2-15 MB ea. ) and do inserts into the
database.

The problem is twofold. One, output on the parsing page seems to come
in starts and fits. I have a message to the user that gets echoed each
time parsing a file is finished; but these messages come all at once,
like two or three at a time. In other words, the message doesn't come
when the parsing is finished; they seem to build up. It's a simple
loop so I don't think the problem is in the program

This is normal behavior; either your server :

http://www.php.net/flush

Also, when I am going through the parsing loop for a set of files,
the rest of the site doesn't respond until the looping is over. The
parsing seems to basically stop all web serving.

How can I find out if the problem is with Apache, PHP, or Postgres?

All of the above. When you start parsing and writing the results into
the database, PHP and Postgres lean very heavily on both memory and
CPU, so there is almost nothing left for the rest of your
application. Run top during parsing and see for yourself.

Cheers,
NC

[lawpoop]

On Jul 10, 7:42 pm, NC <[e-mail]:

On Jul 10, 12:26 pm, [e-mail] wrote:

I have a problem with a php page. I have a setup with Apache 2.0, PHP
5 and Postgres 8.1 on Debian 4.0. My script uses simplexml to parse
large xml files ( 8 files at 2-15 MB ea. ) and do inserts into the
database.

The problem is twofold. One, output on the parsing page seems to come
in starts and fits. I have a message to the user that gets echoed each
time parsing a file is finished; but these messages come all at once,
like two or three at a time. In other words, the message doesn't come
when the parsing is finished; they seem to build up. It's a simple
loop so I don't think the problem is in the program

This is normal behavior; either your server :

http://www.php.net/flush

Also, when I am going through the parsing loop for a set of files,
the rest of the site doesn't respond until the looping is over. The
parsing seems to basically stop all web serving.

How can I find out if the problem is with Apache, PHP, or Postgres?

All of the above. When you start parsing and writing the results into
the database, PHP and Postgres lean very heavily on both memory and
CPU, so there is almost nothing left for the rest of your
application. Run top during parsing and see for yourself.

Cheers,
NC

I tested Apache serving a static HTML webpage, and that got served up
in a timely manner during parsing.

I understand Postgres getting bogged down with selects and inserts,
but there's only one instance of the PHP parser than can parse web
pages? That if one PHP script is bogging down on the server, all PHP
scripts will be bogged down?

[lawpoop]

On Jul 11, 1:40 pm, [e-mail] wrote:

>
I tested Apache serving a static HTML webpage, and that got served up
in a timely manner during parsing.

I understand Postgres getting bogged down with selects and inserts,
but there's only one instance of the PHP parser than can parse web
pages? That if one PHP script is bogging down on the server, all PHP
scripts will be bogged down?

Actually, I did some more testing, and it looks like postgres is the
culprit.

I can serve static HTML pages while parsing, I can spit out data from
infinte loops in php while parsing, but I can't seem to get data from
other queries when doing a bunch of inserts.

[Jerry Stuckle]

la*****@gmail.com wrote:

On Jul 11, 1:40 pm, [e-mail] wrote:

>I tested Apache serving a static HTML webpage, and that got served up
in a timely manner during parsing.

I understand Postgres getting bogged down with selects and inserts,
but there's only one instance of the PHP parser than can parse web
pages? That if one PHP script is bogging down on the server, all PHP
scripts will be bogged down?

Actually, I did some more testing, and it looks like postgres is the
culprit.

I can serve static HTML pages while parsing, I can spit out data from
infinte loops in php while parsing, but I can't seem to get data from
other queries when doing a bunch of inserts.

Each new connection to the server starts a new process or thread
(depending on your OS and Apache version) to handle the request. So you
have multiple PHP parsers running.

What you're probably running into is locking in PostGres. When you're
inserting or updating rows, PostGres can lock rows or tables so that
queries don't get partially updated data or data which hasn't been
committed.

The result is SELECT statements will wait until you COMMIT or ROLLBACK
the INSERT or UPDATE transaction (or close the connection).

This is standard for virtually all databases.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Andy Hassall]

On Wed, 11 Jul 2007 18:11:35 -0400, Jerry Stuckle <js*******@attglobal.net>
wrote:

>What you're probably running into is locking in PostGres. When you're
inserting or updating rows, PostGres can lock rows or tables so that
queries don't get partially updated data or data which hasn't been
committed.

The result is SELECT statements will wait until you COMMIT or ROLLBACK
the INSERT or UPDATE transaction (or close the connection).

This is standard for virtually all databases.

Virtually all databases now implement (or have the option to implement)
multiversioning. For example, Oracle, MySQL with InnoDB table handlers, and I
believe recent versions of SQL Server, and from a quick search, PostgreSQL has
done for ages.

Multiversioning means that readers don't necessarily get blocked by
uncommitted writers.

The resources used for maintaining the "old" view of the data can be exhausted
of course, but in Oracle that produces an error for the reader ("snapshot too
old") rather than a lock - other databases may react differently.

Concurrent writers for the same data will still obviously contend with each
other.

--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool