Re: [PHP-DB] msql perfomance: fast through mysql, slow through php
Try putting a separate network connection between your webserver box and your db, specifically for data between PHP and your DB. Don't route that traffic through the same network as you are using to retrieve the webpage, as the DB connection will eat up all of your bandwidth, leaving none available for sending the data to the browser. On Tue, 2004-02-10 at 23:17, jde wrote: Aha!!! In preparing to give more info, as requested by Sebastian, I have discovered that: You are right, but not exactly for the reasons you describe. The problem is the fact that I was interleaving the mysql_fetch with echo. However, I do not believe it is simply an 'adding the latency'. The two reasons this is probably not the case are because 1) the timing measures I made where from within the code, not by watching the browser, so I knew exactly the time it was taking. Secondly, the mysql server and the web client are co-located (i.e. in my house) and the latency between web server + mysql server would be the same as between web server + web browser, of which I know with high confidence is never as long as the timings would indicate. And actually a third reason for knowing it isn't simply latency is because, when I did perform all the data fetching before doing any output, the times decreased by a factor of 1000: from on order 1 to 6 seconds per fetch down to around .0001 second per fetch. This order of magnitude of difference is too much for simply latency. Nevertheless, clearly the mixing of I/O between the mysql connection and the web client creates a HUGH impact on the php efficiency. Thanks for the discussion!!! This got the job done!!! Question is now: is this a mysql library issue or a php/mysql interaction issue...will make sure the php and mysql folks know this (which they may already know about, I just didn't). Thank you Thank you! jde Brent Baisley wrote: You're also writing out to screen as you get the data, so you may be adding the latency from the web server to the client on top of your data fetching. You can probably get a boost by getting all the data into PHP first and then formatting and sending it to the client. At a minimum you should be able to determine if the client is the big slow down. Matthew Vos Systems Analyst Product Development Techcom Software Solutions Inc. 416.398.5764 [EMAIL PROTECTED] * * * * * * * * * * This message contains information from Techcom Software Solutions Inc. which is confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify [EMAIL PROTECTED]. This message and any attachments have been scanned for viruses. This message has been digitally signed to ensure authenticity. Key ID: CFEA28EC Fingerprint: 009F E13D 0450 55AF DC1C 4D77 1FE9 0DBB CFEA 28EC signature.asc Description: This is a digitally signed message part
Re: [PHP-DB] msql perfomance: fast through mysql, slow through php
There are couple of things that look like they may be slowing you down.
The first one being the east coast/west coast thing. There's a big
possibility for latency issues going that distance no matter how fast
your connection is. You can do a quick test using ping.
You're also writing out to screen as you get the data, so you may be
adding the latency from the web server to the client on top of your
data fetching. You can probably get a boost by getting all the data
into PHP first and then formatting and sending it to the client. At a
minimum you should be able to determine if the client is the big slow
down.
This is the standard code (simplified a bit) I use for getting data
from MySQL.
$query = select acolumn from atable where bcolumn=0;
$result = mysql_query($query,$db);
if($result) {
$num_rows = mysql_num_rows($result);
while ($row = mysql_fetch_array ($result,MYSQL_ASSOC)) {
$result_arr[] = $row;
}
}
Now all your data is being held by PHP in the array variable
$result_arr, MySQL is out of the picture and the client is not yet part
of the picture. You then cycle through the array, formatting the output
for the client. Ideally, you would also use output buffering in PHP and
then deliver the final formatted output in one big chunk to the client
rather than streaming it a little bit at a time. And if you are using
output buffering, you can then using compression to deliver the data to
the client for an even bigger speed boost, especially if the client is
connected over a modem.
Look into ob_start(ob_gzhandler) for details on using output
buffering.
Hope some of those suggestions speed things up.
On Feb 8, 2004, at 10:47 PM, jde wrote:
Greetings,
I have search the archives over the last two years and have not found
any help for this question.
The basic setup: An apache/php server exists on the west coast of the
US, and a linux/mysql server on the east coast.
My PHP snippet looks like this:
$result = mysql_query(select acolumn from atable where bcolumn=0,
$db);
while ($row = mysql_fetch_assoc($result)) echo 'some stuff from the
row';
The problem is, that the above snippet, to return seven rows, takes
about 15 seconds. Putting timing statements around the query and
around the fetch calls shows that the query call takes only 0.03
seconds. However, each fetch call takes anywhere from 0.5 to 5
seconds each to complete.
However, if I am logged onto the apacher server and from the user
prompt run the 'mysql' client, and perform the same exact query, the
'mysql' client can peform the query and return all the results, all 7
rows, in 0.1 second.
This is apache 1.3/php 4.3.4 and mysql 4.0.17-standard.
I have tried all the mysql_connect flags without any change.
Should I expect such a performance difference? Is there something else
I need to look for?
Thank you for your time,
jde
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] msql perfomance: fast through mysql, slow through php
Hi jde, this sounds very strange. Please post send the results of an traceroute and ping to the list. Perhaps, the bandwidth could be the problem. We have a constellation like you with our boxes in germany. The webservers are located in karslruhe, the mysql-boxes in frankfurt. Everything works fine... Regards, Sebastian jde said: Greetings, I have search the archives over the last two years and have not found any help for this question. The basic setup: An apache/php server exists on the west coast of the US, and a linux/mysql server on the east coast. My PHP snippet looks like this: $result = mysql_query(select acolumn from atable where bcolumn=0, $db); while ($row = mysql_fetch_assoc($result)) echo 'some stuff from the row'; The problem is, that the above snippet, to return seven rows, takes about 15 seconds. Putting timing statements around the query and around the fetch calls shows that the query call takes only 0.03 seconds. However, each fetch call takes anywhere from 0.5 to 5 seconds each to complete. However, if I am logged onto the apacher server and from the user prompt run the 'mysql' client, and perform the same exact query, the 'mysql' client can peform the query and return all the results, all 7 rows, in 0.1 second. This is apache 1.3/php 4.3.4 and mysql 4.0.17-standard. I have tried all the mysql_connect flags without any change. Should I expect such a performance difference? Is there something else I need to look for? Thank you for your time, jde -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Sebastian Mangelkramer IMAC - Information Management Consulting Blarerstraße 56, D-78462 Konstanz Tel. +49 (0)7531 - 90 39-11 Fax +49 (0)7531 - 90 39-47 E-Mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] msql perfomance: fast through mysql, slow through php
Greetings, I have search the archives over the last two years and have not found any help for this question. The basic setup: An apache/php server exists on the west coast of the US, and a linux/mysql server on the east coast. My PHP snippet looks like this: $result = mysql_query(select acolumn from atable where bcolumn=0, $db); while ($row = mysql_fetch_assoc($result)) echo 'some stuff from the row'; The problem is, that the above snippet, to return seven rows, takes about 15 seconds. Putting timing statements around the query and around the fetch calls shows that the query call takes only 0.03 seconds. However, each fetch call takes anywhere from 0.5 to 5 seconds each to complete. However, if I am logged onto the apacher server and from the user prompt run the 'mysql' client, and perform the same exact query, the 'mysql' client can peform the query and return all the results, all 7 rows, in 0.1 second. This is apache 1.3/php 4.3.4 and mysql 4.0.17-standard. I have tried all the mysql_connect flags without any change. Should I expect such a performance difference? Is there something else I need to look for? Thank you for your time, jde -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
