Zend_Db really slow

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Zend_Db really slow

Razorblade
Hi everybody,
I've started a new application project using Zend Framework, unfortunatelly I am experiencing a really slow database query ( 6~8 sec for very simple queries ).
I'm not mantaining the server, the only thing I know that is unix.

Any advice in what should I have to look or configure?
Thanks

Sergio R.




Oltre 20 giochi per Messenger. Provali subito!
Reply | Threaded
Open this post in threaded view
|

Re: Zend_Db really slow

scss
Hi,
Could you write the sql sentence here?
How do you determine that the sql is slow and takes 6-8seconds?
Profiler is active?
Then,
. Did you run the sql directly from command line or via a db client?
. How many records are there in the main table?
. Are there any index fields defined?
. Any joined table?
. problem in join structure?
etc.

On Wed, Mar 24, 2010 at 12:39 PM, Sergio Rinaudo
<[hidden email]> wrote:

> Hi everybody,
> I've started a new application project using Zend Framework, unfortunatelly
> I am experiencing a really slow database query ( 6~8 sec for very simple
> queries ).
> I'm not mantaining the server, the only thing I know that is unix.
>
> Any advice in what should I have to look or configure?
> Thanks
>
> Sergio R.
>
>
>
> ________________________________
> Oltre 20 giochi per Messenger. Provali subito!
Reply | Threaded
Open this post in threaded view
|

Re: Zend_Db really slow

till
On Wed, Mar 24, 2010 at 12:01 PM, scs <[hidden email]> wrote:

> Hi,
> Could you write the sql sentence here?
> How do you determine that the sql is slow and takes 6-8seconds?
> Profiler is active?
> Then,
> . Did you run the sql directly from command line or via a db client?
> . How many records are there in the main table?
> . Are there any index fields defined?
> . Any joined table?
> . problem in join structure?
> etc.

You really need to look at it through a profiler (e.g. Xdebug).
Zend_Db may not be the fastest kid on the block, but I find it hard to
believe that you waste those 6 seconds in PHP code. ;-) It could be
something as simple as slow DNS resolution or who knows that.

Till

>
> On Wed, Mar 24, 2010 at 12:39 PM, Sergio Rinaudo
> <[hidden email]> wrote:
>> Hi everybody,
>> I've started a new application project using Zend Framework, unfortunatelly
>> I am experiencing a really slow database query ( 6~8 sec for very simple
>> queries ).
>> I'm not mantaining the server, the only thing I know that is unix.
>>
>> Any advice in what should I have to look or configure?
>> Thanks
>>
>> Sergio R.
>>
Reply | Threaded
Open this post in threaded view
|

RE: Zend_Db really slow

Razorblade
In reply to this post by scss
Hi, 
thank you for reply, answering to your questions

. Did you run the sql directly from command line or via a db client?

This is an example query in the bootstrap
$db->query("SET NAMES 'utf8'");
but also all the other queries are slow and make not difference if they are inside a controller or in an action helper.
I use pdo_mysql as adapter.

. How many records are there in the main table?

Not to many, but also if they are 10 is slow

. Are there any index fields defined?

Some table have primary key, some others not, but query one but the other is slow the same

. Any joined table?


I have join queries, but also simple query are slow

How do you determine that the sql is slow and takes 6-8seconds

I have to say that I am not sure that queries are slow.
The simpler test I've done is to put the line of code "die('test');" before and after the db call.
If it is before the output is generated in an instant, if it is after is slow.

BUT using ZFDebug plugin it says that all the queries are really fast ( 0.22 ms for example in front of a 10000.00 and more ms of page loading time )

Profiler is active?

Active or not active nothing change


And also I have Metadata cache ENABLED ( keep it disabled is the same )



Here it is a log with ZFDebug

Overall Time

default
index
index

Avg: 5900.1 ms / 1 requests
Min: 5900.1 ms

Max: 5900.1 ms

with 2 query ( 0.16 ms and 0.07 ms )


The version of Zend Framework that I'm using is 1.10.2.


I can hypothesize a problem with Zend_Db component in the environment I actually am.
What's your advices?

Thank You!




Sergio






--------------------

Hi,
Could you write the sql sentence here?
How do you determine that the sql is slow and takes 6-8seconds?
Profiler is active?
Then,
. Did you run the sql directly from command line or via a db client?
. How many records are there in the main table?
. Are there any index fields defined?
. Any joined table?
. problem in join structure?
etc.

On Wed, Mar 24, 2010 at 12:39 PM, Sergio Rinaudo
<[hidden email]> wrote:

> Hi everybody,
> I've started a new application project using Zend Framework, unfortunatelly
> I am experiencing a really slow database query ( 6~8 sec for very simple
> queries ).
> I'm not mantaining the server, the only thing I know that is unix.
>
> Any advice in what should I have to look or configure?
> Thanks
>
> Sergio R.
>
>
>
> ________________________________
> Oltre 20 giochi per Messenger. Provali subito!

Chiacchiera con i tuoi amici via Webcam su Messenger. Videochiamali!
Reply | Threaded
Open this post in threaded view
|

RE: Zend_Db really slow

David Muir
Where is the database located in relation to the web server? My guess is that it's slow because the database server is not on the same network as the web server.

Cheers,
David
Reply | Threaded
Open this post in threaded view
|

Re: Zend_Db really slow

weierophinney
Administrator
In reply to this post by Razorblade
-- Sergio Rinaudo <[hidden email]> wrote
(on Wednesday, 24 March 2010, 12:51 PM +0100):
> thank you for reply, answering to your questions
>
> . Did you run the sql directly from command line or via a db client?
>
> This is an example query in the bootstrap
> $db->query("SET NAMES 'utf8'");
> but also all the other queries are slow and make not difference if
> they are inside a controller or in an action helper.

If that's the case, then it's one of two things:

 * Network latency. This could be due to slow DNS lookups, bad network
   routing tables, etc.

 * A slow DB server.

It's likely *not* ZF in this particular case.

> I use pdo_mysql as adapter.
>
> . How many records are there in the main table?
>
> Not to many, but also if they are 10 is slow
>
> . Are there any index fields defined?
>
> Some table have primary key, some others not, but query one but the other is slow the same
>
> . Any joined table?
>
> I have join queries, but also simple query are slow
>
> How do you determine that the sql is slow and takes 6-8seconds
>
> I have to say that I am not sure that queries are slow.
> The simpler test I've done is to put the line of code "die('test');" before and after the db call.
> If it is before the output is generated in an instant, if it is after is slow.
>
> BUT using ZFDebug plugin it says that all the queries are really fast ( 0.22 ms for example in front of a 10000.00 and more ms of page loading time  )
>
> Profiler is active?
>
> Active or not active nothing change
>
>
> And also I have Metadata cache ENABLED ( keep it disabled is the same )
>
>
>
> Here it is a log with ZFDebug
>
> Overall Time
>
> default
>     index
>         index
>
>     Avg: 5900.1 ms / 1 requests
>             Min: 5900.1 ms
>
>      Max: 5900.1 ms
>
> with 2 query ( 0.16 ms and 0.07 ms )
>
>
> The version of Zend Framework that I'm using is 1.10.2.
>
>
> I can hypothesize a problem with Zend_Db component in the environment I actually am.
> What's your advices?
>
> Thank You!
>
>
>
>
> Sergio
>
>
>
>
>
>
> --------------------
>
> Hi,
> Could you write the sql sentence here?
> How do you determine that the sql is slow and takes 6-8seconds?
> Profiler is active?
> Then,
> . Did you run the sql directly from command line or via a db client?
> . How many records are there in the main table?
> . Are there any index fields defined?
> . Any joined table?
> . problem in join structure?
> etc.
>
> On Wed, Mar 24, 2010 at 12:39 PM, Sergio Rinaudo
> <[hidden email]> wrote:
> > Hi everybody,
> > I've started a new application project using Zend Framework, unfortunatelly
> > I am experiencing a really slow database query ( 6~8 sec for very simple
> > queries ).
> > I'm not mantaining the server, the only thing I know that is unix.
> >
> > Any advice in what should I have to look or configure?
> > Thanks
> >
> > Sergio R.
> >
> >
> >
> > ________________________________
> > Oltre 20 giochi per Messenger. Provali subito!
>
>
> ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
> Chiacchiera con i tuoi amici via Webcam su Messenger. Videochiamali!

--
Matthew Weier O'Phinney
Project Lead            | [hidden email]
Zend Framework          | http://framework.zend.com/
PGP key: http://framework.zend.com/zf-matthew-pgp-key.asc
Reply | Threaded
Open this post in threaded view
|

RE: Zend_Db really slow

Razorblade
Hi,
I want to thank everyone for all your kind answer,
expecially to Matthew Weier O'Phinney, reading your answer
I thought on a good solution to demonstrate that server was slow and not
ZF related, write a simple db test script only using simple php


<?php
function getmicrotime()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
$time_start = getmicrotime();

$link = mysql_connect('host', 'user', 'pass');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_select_db("mydb");


$result = mysql_query("SELECT * FROM mytable");

while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
    printf ("<p>ID: %s  Name: %s</p>", $row[0], $row["myfield"]);
}

$time_end = getmicrotime();
$time = $time_end - $time_start;
echo "<p>Page generated in ".$time." </p>";

?>


Result was 5 seconds in the current server, 0.02 seconds in another one.
Thank you!

Sergio Rinaudo





> Date: Wed, 24 Mar 2010 10:02:45 -0400
> From: [hidden email]
> To: [hidden email]
> Subject: Re: [fw-general] Zend_Db really slow
>
> -- Sergio Rinaudo <[hidden email]> wrote
> (on Wednesday, 24 March 2010, 12:51 PM +0100):
> > thank you for reply, answering to your questions
> >
> > . Did you run the sql directly from command line or via a db client?
> >
> > This is an example query in the bootstrap
> > $db->query("SET NAMES 'utf8'");
> > but also all the other queries are slow and make not difference if
> > they are inside a controller or in an action helper.
>
> If that's the case, then it's one of two things:
>
> * Network latency. This could be due to slow DNS lookups, bad network
> routing tables, etc.
>
> * A slow DB server.
>
> It's likely *not* ZF in this particular case.
>
> > I use pdo_mysql as adapter.
> >
> > . How many records are there in the main table?
> >
> > Not to many, but also if they are 10 is slow
> >
> > . Are there any index fields defined?
> >
> > Some table have primary key, some others not, but query one but the other is slow the same
> >
> > . Any joined table?
> >
> > I have join queries, but also simple query are slow
> >
> > How do you determine that the sql is slow and takes 6-8seconds
> >
> > I have to say that I am not sure that queries are slow.
> > The simpler test I've done is to put the line of code "die('test');" before and after the db call.
> > If it is before the output is generated in an instant, if it is after is slow.
> >
> > BUT using ZFDebug plugin it says that all the queries are really fast ( 0.22 ms for example in front of a 10000.00 and more ms of page loading time )
> >
> > Profiler is active?
> >
> > Active or not active nothing change
> >
> >
> > And also I have Metadata cache ENABLED ( keep it disabled is the same )
> >
> >
> >
> > Here it is a log with ZFDebug
> >
> > Overall Time
> >
> > default
> > index
> > index
> >
> > Avg: 5900.1 ms / 1 requests
> > Min: 5900.1 ms
> >
> > Max: 5900.1 ms
> >
> > with 2 query ( 0.16 ms and 0.07 ms )
> >
> >
> > The version of Zend Framework that I'm using is 1.10.2.
> >
> >
> > I can hypothesize a problem with Zend_Db component in the environment I actually am.
> > What's your advices?
> >
> > Thank You!
> >
> >
> >
> >
> > Sergio
> >
> >
> >
> >
> >
> >
> > --------------------
> >
> > Hi,
> > Could you write the sql sentence here?
> > How do you determine that the sql is slow and takes 6-8seconds?
> > Profiler is active?
> > Then,
> > . Did you run the sql directly from command line or via a db client?
> > . How many records are there in the main table?
> > . Are there any index fields defined?
> > . Any joined table?
> > . problem in join structure?
> > etc.
> >
> > On Wed, Mar 24, 2010 at 12:39 PM, Sergio Rinaudo
> > <[hidden email]> wrote:
> > > Hi everybody,
> > > I've started a new application project using Zend Framework, unfortunatelly
> > > I am experiencing a really slow database query ( 6~8 sec for very simple
> > > queries ).
> > > I'm not mantaining the server, the only thing I know that is unix.
> > >
> > > Any advice in what should I have to look or configure?
> > > Thanks
> > >
> > > Sergio R.
> > >
> > >
> > >
> > > ________________________________
> > > Oltre 20 giochi per Messenger. Provali subito!
> >
> >
> > ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
> > Chiacchiera con i tuoi amici via Webcam su Messenger. Videochiamali!
>
> --
> Matthew Weier O'Phinney
> Project Lead | [hidden email]
> Zend Framework | http://framework.zend.com/
> PGP key: http://framework.zend.com/zf-matthew-pgp-key.asc


Chiacchiera con i tuoi amici via Webcam su Messenger. Videochiamali!