Zend_Db::fetchAll() unacceptably slow

[umpirsky]

Hi.

I have mysql table:

CREATE TABLE `brand` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Car brand ID',
  `title` varchar(32) default NULL COMMENT 'Car brand title',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

with 93 records inserted.

I'm using Zend_Db with MYSQLI adapter. When I execute fetchAll(null, 'title') it takes 15 seconds to complete fetch!!! This is terrible slow, isn't it?

Profiler says:

 0.00114 SELECT `brand`.* FROM `brand` ORDER BY `title` ASC

which is ok and blazing fast.

Looks like fetchAll is wasting time somewhere. This is not unacceptable.

Any idea?

[Benjamin Eberlei-2]

Hello,

can you make a tracedump or a kcachegrind output using xdebug
for just your use-case?

Otherwise its impossible to tell what causes this.

greetings,
Benjamin

On Wed, 30 Sep 2009 05:03:22 -0700 (PDT), umpirsky <[hidden email]>
wrote:

> Hi.
>
> I have mysql table:
>
> CREATE TABLE `brand` (
>   `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Car brand ID',
>   `title` varchar(32) default NULL COMMENT 'Car brand title',
>   PRIMARY KEY  (`id`)
> ) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8 CHECKSUM=1
> DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
>
> with 93 records inserted.
>
> I'm using Zend_Db with MYSQLI adapter. When I execute fetchAll(null,
> 'title') it takes 15 seconds to complete fetch!!! This is terrible slow,
> isn't it?
>
> Profiler says:
>
>  0.00114 SELECT `brand`.* FROM `brand` ORDER BY `title` ASC
>
> which is ok and blazing fast.
>
> Looks like fetchAll is wasting time somewhere. This is not unacceptable.
>
> Any idea?

[umpirsky]

Looks like slow db connection http://www.screencast.com/users/umpirsky/folders/Jing/media/953fb733-e2e8-4456-a9cd-0e4570722110

What can I try? Maybe other server :P

Regards,
Saša Stamenković

On Wed, Sep 30, 2009 at 2:12 PM, Benjamin Eberlei <[hidden email]> wrote:

Hello,

can you make a tracedump or a kcachegrind output using xdebug
for just your use-case?

Otherwise its impossible to tell what causes this.

greetings,
Benjamin

On Wed, 30 Sep 2009 05:03:22 -0700 (PDT), umpirsky <[hidden email]>
wrote:

> Hi.
>
> I have mysql table:
>
> CREATE TABLE `brand` (
>   `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Car brand ID',
>   `title` varchar(32) default NULL COMMENT 'Car brand title',
>   PRIMARY KEY  (`id`)
> ) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8 CHECKSUM=1
> DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
>
> with 93 records inserted.
>
> I'm using Zend_Db with MYSQLI adapter. When I execute fetchAll(null,
> 'title') it takes 15 seconds to complete fetch!!! This is terrible slow,
> isn't it?
>
> Profiler says:
>
>  0.00114       SELECT `brand`.* FROM `brand` ORDER BY `title` ASC
>
> which is ok and blazing fast.
>
> Looks like fetchAll is wasting time somewhere. This is not unacceptable.
>
> Any idea?

[tfk]

On Wed, Sep 30, 2009 at 2:37 PM, Саша Стаменковић <[hidden email]> wrote:
> Looks like slow db
> connection http://www.screencast.com/users/umpirsky/folders/Jing/media/953fb733-e2e8-4456-a9cd-0e4570722110
> What can I try? Maybe other server :P

Is the database server not on the same server? If it's external (on
another server), there are multiple things:

a) Replace DNS host with an IP address, saves the lookup (or fix the DNS)
b) If it's not DNS, let your ISP review the issue.
c) Better server and/or connection between those.

You could also apply the cache to Zend_Db_Table to save the meta data
between requests. And maybe cache the response from the database
entirely to avoid the SQL calls.

Till

[umpirsky]

Thanks, it was the DNS problem, now it's blazing fast.

Regards,
Saša Stamenković

On Wed, Sep 30, 2009 at 2:46 PM, till <[hidden email]> wrote:

On Wed, Sep 30, 2009 at 2:37 PM, Саша Стаменковић <[hidden email]> wrote:
> Looks like slow db
> connection http://www.screencast.com/users/umpirsky/folders/Jing/media/953fb733-e2e8-4456-a9cd-0e4570722110
> What can I try? Maybe other server :P

Is the database server not on the same server? If it's external (on
another server), there are multiple things:

a) Replace DNS host with an IP address, saves the lookup (or fix the DNS)
b) If it's not DNS, let your ISP review the issue.
c) Better server and/or connection between those.

You could also apply the cache to Zend_Db_Table to save the meta data
between requests. And maybe cache the response from the database
entirely to avoid the SQL calls.

Till