Zend_Db_Table time overhead - about 25% | Building Sample Web Site Based On Zend Framework (examples for user registration and login)

{ 2008 08 28 }

Zend_Db_Table time overhead - about 25%

Here I will represent the results of a test, that I ran recently to determine how much time overhead does Zend_Db_Table brings to typical web application. The details follow, but the rough results are that it took about 25% more time for the test cases with Zend_Db_Table to run compared to using directly Zend_Db_Adapter.

My intend was to test this in as close to live enviroment as possible, so the test case that I ran was standart MVC architecture. We have some overhead also from the setup of the front controller and the dispatch process, loading configuration files, setting up Zend_Translate, loading some simple front controller plugin. The only thing that I have disabled is the layout and the viewRenderer action helpers by running

$this->_helper->layout->disableLayout();
$this->_helper->viewRenderer->setNoRender();

in the init() function of my test controller.

I’m running this with PHP 5.2.6 with APC, Zend Framework 1.5.2, MySQL 5.0.67, MySQLi DB connection adapter.

I have the following DB structure:
- table `users` with standart fields for this - username, password, email address, birthday, real name etc, filled in with 2000 users
- table `posts` with fields: id (primary key, autoincrement), userId (int), title (varchar 64), content (text) with index on userId column - filled in with 20000 posts with random generated text and random generated titles. Text is between 250 and 3000 bytes long.
- table `tags` with fields: id (primary key, autoincrement), tag (varchar 32) with index on tag column. Here we have 35000 tags, each tag is md5 hash of some value, so it is 32 bytes long.
- table `tag2posts` which links the two tables - tags and posts. So we have many-to-many relationship between these two. Fields: tagId (int) and postId (int). Here the primary key is on two columns (tagId, postId), and we have extra index on postId column, because we will search “give me posts for this tag” queries. Each post is linked with between 3 and 7 tags. The size of this table is 99896 rows.

My test case does the following actions:
1) Generates name of a test user. All test users have usernames in the form (test_xxx) where xxx is number between 1 and 2000. Then loads all the info about this user.
2) Loads last 5 post titles from the selected user.
3) Random selects one of these posts and loads it - with the text and all linked tags id. (Without loading the actual tag text, as I found later, but this is not crucial for the result of all this)
4) Random selectes one of the tags for this post and loads the titles of the last 20 posts tagged with it.
5) Prints these titles on the screen

I ran the test in 4 different variants. These 4 variants proved to distinguish one from another in the time they took to run.
These 4 variants are:
1) using Zend_Db_Table without metadata cache
2) using Zend_Db_Table with APC as metadata cache
3) writing the SQL queries directly in the controller, using direct Zend_Db_Adapter_Mysqli
4) writing the SQL queries in Model classes, using direct Zend_Db_Adapter_Mysqli

Fastest method is the 3rd one - where we write the SQL queries directly in the controller. This is not good practice and is error prone. The second place with only small difference is for the 4th method - where we hide the queries in Model classes, but do not use Zend_Db_Table. As for the last 2 places - the performance here drops significantly. When we use APC as metadata cache for Zend_Db_Table compared to the case when we do not use metadata cache at all the difference is about 10%.

I ran several times the benchmark about each of the methods, removed the slowest runs and averaged the other numbers to get the final results. Here are the exact numbers:

I tested with level of concurency 5 with 100 requests (ab -c 5 -n 100)
Requests per second:
method 3 - writing the SQL queries directly in the controller, using direct Zend_Db_Adapter_Mysqli: 12.93 rps
method 4 - writing the SQL queries in Model classes, using direct Zend_Db_Adapter_Mysqli: 12.66 rps
method 2 - using Zend_Db_Table with APC as metadata cache: 10.2 rps
method 1 - using Zend_Db_Table without metadata cache 9.2 rps

here is the code of the test controller class:

< ?php
 
class TestController extends Zend_Controller_Action 
{
    /**
     * DB Connection
     *
     * @var Zend_Db_Adapter_Abstract
     */
    protected $db;
 
    protected $cache;
 
    public function init()
    {
        $this->db = Zend_Registry::get('db');
        $this->_helper->layout->disableLayout();
        $this->_helper->viewRenderer->setNoRender();
 
        //setup cache for DB metadata:
 
        // First, set up the Cache
        $frontendOptions = array(
            'automatic_serialization' => true
            );
 
        $backendOptions  = array();
 
        $this->cache = Zend_Cache::factory('Core', 'APC', $frontendOptions, $backendOptions);
        //$this->cache->setOption('caching', false);
    }
 
	public function indexAction()
    {
 
    }
 
    public function generatetestdbAction()
    {
        //$this->insertTags();
        //$this->insertUsers();
        //$this->insertPosts();
        //$this->linkPostsToTags();
    }
 
    public function runtestcaseAction()
    {
//1) Generates name of a test user. All test users have usernames in the form (test_xxx) where xxx is number between 1 and 2000. Then loads all the info about this user.
//2) Loads last 5 post titles from the selected user. 
//3) Random selects one of these posts and loads it - with the text and all linked tags id. (Without loading the actual tag text, as I found later, but this is not crucial for the result of all this)
//4) Random selectes one of the tags for this post and loads the titles of the last 20 posts tagged with it. 
//5) Prints these titles on the screen
//        
        $mode = $this->getRequest()->getParam('mode');
 
        if (!isset($mode)) $mode = 1;
 
        $startUser = 'test_' . mt_rand(1, 2000);
 
        if ($mode == 1)
        {
            //mode 1 is with Zend_Db_Table
            //$users = new Users();
            $users = new Users(array('metadataCache' => $this->cache));
 
            $u = $users->selectUserByUsername($startUser);
            if (!$u) throw new My_Exception('not such start user');
 
            //load last 5 post titles for $u
            //$posts = new Posts();
            $posts = new Posts(array('metadataCache' => $this->cache));
 
            $select = $posts->select();
            $select->from($posts, array('id', 'title'))
                //->where($posts->getAdapter()->quoteInto('userId = ?', $u->id))
                ->where('userId = ?', $u->id)
                ->order('id DESC')
                ->limit(5);
 
            $rows = $posts->fetchAll($select);
 
            //load full info for random post of these:
            $cnt = $rows->count();
            $rows->seek(mt_rand(0, $cnt-1));
            $p = $rows->current();
            $p = $posts->find($p->id);            
            $p = $p->current();                                    
 
            //$t2p = new Tag2Posts();
            $t2p = new Tag2Posts(array('metadataCache' => $this->cache));
            $select = $t2p->select();
            //$select->where($t2p->getAdapter()->quoteInto('postId = ?', $p->id));
            $select->where('postId = ?', $p->id);
            $rows = $t2p->fetchAll($select);
 
            $cnt = $rows->count();
            $rows->seek(mt_rand(0, $cnt-1));
            $tag = $rows->current();
            $tagId = $tag->tagId;
 
            $select = $posts->select();
            $select->from($posts, array('id', 'title'))
                ->join('tag2posts', 'tag2posts.postId=posts.id', array())
                ->where('tag2posts.tagId = ?', $tagId)
                ->order('id DESC')
                ->limit(20);
 
            $rows = $posts->fetchAll($select);
 
            foreach ($rows as $r)
            {
                echo $r->title . "<br />";
            }
        }
        else if ($mode == 2)
        {
            $stmt = $this->db->query("SELECT * FROM users WHERE username = ?", $startUser);
            $u = $stmt->fetch();            
            if (!$u) throw new My_Exception('not such start user');
 
            //load last 5 post titles for $u
            $stmt = $this->db->query("SELECT id, title FROM posts WHERE userId = ? ORDER BY id DESC LIMIT 5", $u['id']);
            $rows = $stmt->fetchAll();                
 
 
            //load full info for random post of these:            
            $key = array_rand($rows);
            $p = $rows[$key];
            $stmt = $this->db->query("SELECT * FROM posts WHERE id = ?", $p['id']);
            $p = $stmt->fetch();                                                
 
            $stmt = $this->db->query("SELECT * FROM tag2posts WHERE postId = ?", $p['id']);
            $rows = $stmt->fetchAll();
 
            $key = array_rand($rows);
            $tag = $rows[$key];
            $tagId = $tag['tagId'];
 
            $stmt = $this->db->query("SELECT id, title FROM posts JOIN tag2posts t2p ON t2p.postId=posts.id " . 
                "WHERE t2p.tagId = ? ORDER BY id DESC LIMIT 20", $tagId);
 
            $rows = $stmt->fetchAll();
 
            foreach ($rows as $r)
            {
                echo $r['title'] . "<br />";
            }                        
        }
        else
        {
            $users = new Users2();
            $u = $users->selectUserByUsername($startUser);
            if (!$u) throw new My_Exception('not such start user');
 
            //load last 5 post titles for $u
            $posts = new Posts2();
            $rows = $posts->getLastPostsForUser($u['id'], 5);
 
            //load full info for random post of these:
            $key = array_rand($rows);
            $p = $rows[$key];
            $p = $posts->getPostById($p['id']);
 
            $tags = $posts->getTagsForPost($p['id']);
            $key = array_rand($tags);
            $tag = $tags[$key];
 
            $rows = $posts->getLastPostsForTag($tag['tagId'], 20); 
 
            foreach ($rows as $r)
            {
                echo $r['title'] . "<br />";
            }
        }
    }
 
    protected function insertTags() {
        //generate 35000 tags
        $this->db->query("TRUNCATE TABLE tags");
        $stmt = $this->db->prepare("INSERT INTO tags SET tag = ?");
        $stmt->bindParam(1, $newTag);
        for ($i = 1; $i < = 35000; $i++)
        {
            $newTag = md5(microtime());
            $stmt->execute();                        
        }
 
        echo '35000 tags inserted';
    }
 
    protected function insertUsers()
    {
        //insert 2000 users
        $this->db->query("DELETE FROM users WHERE username LIKE ?", array(1=>'test_%'));
        $stmt = $this->db->prepare("INSERT INTO users SET username = ?, password = ?, email = ?, active = 1");
        $stmt->bindParam(1, $username);
        $stmt->bindParam(2, $password);
        $stmt->bindParam(3, $email);
 
        $password = Users::computePasswordHash('123456');
        for ($i = 1; $i < = 2000; $i++)
        {
            $username = 'test_'.$i;
            $email = 'test_'.$i.'@example.com';
 
            $stmt->execute();
        }
 
        echo '2000 users inserted';
    }
 
    protected function insertPosts()
    {
        //insert 20000 posts 
        //$this->db->query("TRUNCATE TABLE posts");
        //$this->db->query("TRUNCATE TABLE tag2posts");
 
        $stmt = $this->db->prepare("INSERT INTO posts SET userId = ?, title = ?, content = ?");
        $stmt->bindParam(1, $userId);
        $stmt->bindParam(2, $title);
        $stmt->bindParam(3, $content);
 
        //@todo: increase your time limit, because this one may take some time
        for ($i = 1; $i < = 20000; $i++)
        {
            $userId = mt_rand(16, 2015);
            $title = $this->getRandomText(rand(30, 60));
            $content = $this->getRandomText(rand(250, 3000));
 
            $stmt->execute();
        }
 
        echo '20000 texts inserted';                
    }
 
    protected function linkPostsToTags()
    {
        //link each post to about 5 tags
        //posts are from 1 to 20000
        //tags are from 1 to 35000
 
        $stmt = $this->db->prepare("INSERT INTO tag2posts SET tagId = ?, postId = ?");
        $stmt->bindParam(1, $tagId);
        $stmt->bindParam(2, $postId);
 
        for ($i = 1; $i < = 20000; $i++)
        {
            $postId = $i;
 
            //@todo: better testing for duplicate tag id should be implemented. 
            $numTags = mt_rand(3, 7);            
            for ($j = 1; $j <= $numTags; $j++)
            {
                do
                {
                    $newTagId = mt_rand(1, 35000);                
                }
                while ($newTagId == $tagId);
                $tagId = $newTagId;
 
                $tagId = mt_rand(1, 35000);
                $stmt->execute();
            }            
        }
 
        echo 'tags links inserted';
 
 
    }
 
    public static function getRandomText($numCharacters)
    {
        //algorithm: generate words (2 to 15 characters long) then space until $numCharacters is reached
        $text = '';
        while ($numCharacters > 0)
        {
            $currentWord = '';
 
            $charset = "abcdefghijklmnopqrstuvwxyz";
            $chl = strlen($charset)-1;
 
            $length = mt_rand(2, 15);
            for ($i=0; $i< $length; $i++) $currentWord .= $charset[(mt_rand(0,$chl))];            
 
            $text .= $currentWord;
            $text .= ' ';
            $numCharacters -= ($length + 1);
        }
 
        return $text;
    }
 
 
 
}

Classes Users, Posts and Tag2Posts are very simple, they just extends Zend_Db_Table_Abstract and define the $_name property. For variant 4 - here are example classes Users2 and Posts2:

< ?php
 
class Users2
{
    protected $db;
 
    public function __construct()
    {
        $this->db = Zend_Registry::get('db');
    }
 
    public function selectUserByUsername($username)
    {
        $stmt = $this->db->query("SELECT * FROM users WHERE username = ?", $username);
        $u = $stmt->fetch();            
        return $u;
    }
}

< ?php
 
class Posts2 
{
    protected $db;
 
    public function __construct()
    {
        $this->db = Zend_Registry::get('db');
    }
 
 
    public function getPostById($postId)
    {
        $stmt = $this->db->query("SELECT * FROM posts WHERE id = ?", $postId);
        $p = $stmt->fetch();
 
        return $p;
    }
 
    public function getTagsForPost($postId)
    {
        $stmt = $this->db->query("SELECT * FROM tag2posts WHERE postId = ?", $postId);
        $rows = $stmt->fetchAll();
        return $rows;
    }
 
    public function getLastPostsForUser($userId, $num)
    {
        $stmt = $this->db->query("SELECT id, title FROM posts WHERE userId = ? ORDER BY id DESC LIMIT 5", $userId);
        $rows = $stmt->fetchAll();
        return $rows;
    }
 
    public function getLastPostsForTag($tagId, $num)
    {
        $num = (int) $num;
        $stmt = $this->db->query("SELECT id, title FROM posts JOIN tag2posts t2p ON t2p.postId=posts.id " . 
                "WHERE t2p.tagId = ? ORDER BY id DESC LIMIT $num", $tagId);
 
        $rows = $stmt->fetchAll();
        return $rows;
    }
}

conclusion: Zend_Db_Table is not good for heavy loaded web applications. My personal choice is to go with something like variant 4. The time overhead here is minimal and the code in the controller is most readable.

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.

{ 5 }

Comments

Zend_Db_Table erzeugt 25% Overhead sagt Andrei » Ralfs PHP und Zend Framework Blog | 28-Aug-08 at 8:22 pm | Permalink

[...] Nikolov hat in seinem Blog einen Artikel über ein paar Performance-Messungen mit Zend_Db_Table veröffentlicht und kommt zu dem Schluss, dass Zend_Db_Table einen Overhead von bummelig 25% [...]
Iczi | 16-Sep-08 at 10:01 am | Permalink

Well, Zend_Db_Table is “fast” enough for any website and any load. Today, developers like to scale PHP horizontally, means spreading your code to many servers and use a load-balancer to distribute the requests equally.

Even if Zend_Db_Table would add 50% overhead, that’s really no problem. Thanks to cheap servers!

I think, when talking about performance, we also have to think of “developer-performance”, means how fast a developer can finish his task. Developing and software-engineering are goind to be the most expensive parts of your projects, so it gets cheaper, if they are really fast and write mantainable and well tested code.

Zend_Db_Table provides tools to be faster, when it comes to solve database tasks and is a transparent and easy understable implementation.

So, who cares, if a PHP webapp can handle 150 or 200 request/s? I add a new server and then it’s going to be much faster than before and I saved time and/or money when i developed the application.
viperx | 18-Sep-08 at 3:07 pm | Permalink

Ok Iczi, this is your point of view
And if your tareget is 2 or 3 servers OK, you are right. But if the application needs 100 servers then 20% is 20 more servers. Not that this is too much, but it is a difference indeed.
fabrizio | 03-Oct-08 at 6:20 pm | Permalink

What about using Zend_Db_Table_Abstract::setDefaultMetadataCache($cache);
as written in the reference guide?

” By default, Zend_Db_Table_Abstract queries the underlying database for table metadata upon instantiation of a table object. That is, when a new table object is created, the object’s default behavior is to fetch the table metadata from the database using the adapter’s describeTable() method.

In some circumstances, particularly when many table objects are instantiated against the same database table, querying the database for the table metadata for each instance may be undesirable from a performance standpoint. In such cases, users may benefit by caching the table metadata retrieved from the database.

There are two primary ways in which a user may take advantage of table metadata caching:

*

Call Zend_Db_Table_Abstract::setDefaultMetadataCache() - This allows a developer to once set the default cache object to be used for all table classes.
*

Configure Zend_Db_Table_Abstract::__construct() - This allows a developer to set the cache object to be used for a particular table class instance.
“
viperx | 17-Oct-08 at 5:04 pm | Permalink

Hi fabrizio,
if you read the article you will see that I have included the case with metadata cache

Building Sample Web Site Based On Zend Framework (examples for user registration and login)