Phalcon 3.2.0 on PHP 7.0 getting slow on raw query

Im using latest phalcon 3.2.0 on php 7.0 with nginx and php opcache enabled this basic raw query code and I am only getting around 15 req per second on ab benchmark

     $sql = "SELECT * FROM table
                    WHERE field = 'something here'
                    ORDER BY product_id DESC LIMIT 12 
                    OFFSET 0";

    $di = $this->DI;
    $db = $di['db'];
    $data = $db->query($sql);
    $result = $data->setFetchMode(\Phalcon\Db::FETCH_OBJ);
    $results = $data->fetchAll();
    return $results;

While on my python flask program running on the same vagrant machine(ubuntu/trusy32) and same query using gunicorn and nginx I am getting around 1300 req per second.

my computer is windows 7 running on intel i7 7700 with 8gb of ram.

I thought phalcon would be faster, is there something that I should do? thanks

edited Jul '17

How big is this resultset? Are you sure that on python you fetch ALL DATA?

From php docs:

https://secure.php.net/manual/en/pdostatement.fetchall.php#refsect1-pdostatement.fetchall-returnvalues

Using this method to fetch large result sets will result in a heavy demand on system and possibly network resources. Rather than retrieving all of the data and manipulating it in PHP, consider using the database server to manipulate the result sets. For example, use the WHERE and ORDER BY clauses in SQL to restrict results before retrieving and processing them with PHP.

Your code is using simply just pdo, nothing fancy really. Just make sure that in python you have really whole data returned too. You didn't provide any info really.

edited Jul '17

I manage to increase the phalcon and just use the bare setup but still 80req/s and 1k+req/s difference. is there any setup that should be followed?

Here is my basic setup in phalcon

        $routes = new MicroCollection();
        $routes->setHandler("App\Controllers\Frontend\HomeController", true);
        $routes->get("/test", "get_test");
        $app->mount($routes);


        =================================
        <?php

        namespace App\Controllers\Frontend;

        use App\Models\Users;

        class HomeController extends \Phalcon\Mvc\Controller
        {

            public function get_test(){
                $usr = new Users();
                $usr = $usr->get_test();
                var_dump($usr); 
                exit();
            }
        }

        ==================================

        <?php

        namespace App\Models;
        use Phalcon\DI;

        class Users extends \Phalcon\Mvc\Model {

            public function getSource() {
                return "users";
            }

            public function get_test(){

                $sql = "SELECT * FROM users
                        ORDER BY user_id DESC LIMIT 12 
                        OFFSET 1";

                $di = $this->DI;
                $db = $di['db'];
                $data = $db->query($sql);
                $result = $data->setFetchMode(\Phalcon\Db::FETCH_OBJ);
                $results = $data->fetchAll();
                return $results;
            }

        }

===========================================================

Phalcon result

      vagrant@vagrant-ubuntu-trusty-32:~$ ab -n 200 -c 100 http://phalcon.dev/test/
      This is ApacheBench, Version 2.3 <$Revision: 1528965 $>
      Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
      Licensed to The Apache Software Foundation, http://www.apache.org/

      Benchmarking phalcon.dev (be patient)
      Completed 100 requests
      Completed 200 requests
      Finished 200 requests


      Server Software:        nginx
      Server Hostname:        phalcon.dev
      Server Port:            80

      Document Path:          /test/
      Document Length:        0 bytes

      Concurrency Level:      100
      Time taken for tests:   2.469 seconds
      Complete requests:      200
      Failed requests:        0
      Total transferred:      26000 bytes
      HTML transferred:       0 bytes
      Requests per second:    81.00 [#/sec] (mean)
      Time per request:       1234.613 [ms] (mean)
      Time per request:       12.346 [ms] (mean, across all concurrent requests)
      Transfer rate:          10.28 [Kbytes/sec] received

      Connection Times (ms)
                    min  mean[+/-sd] median   max
      Connect:        0    1   1.3      2       3
      Processing:   267  994 289.1   1081    1474
      Waiting:      267  994 289.1   1081    1474
      Total:        269  995 288.8   1081    1477

      Percentage of the requests served within a certain time (ms)
        50%   1081
        66%   1156
        75%   1183
        80%   1199
        90%   1251
        95%   1360
        98%   1447
        99%   1460
       100%   1477 (longest request)
      vagrant@vagrant-ubuntu-trusty-32:~$

Flask

route

  app.add_url_rule('/<name>', view_func=LazyView('controllers.frontend.homepage.homename'))

user model

    from config.database import db

    class Users(db.Model):
        __tablename__ = 'users'
        user_id = db.Column(db.Integer, primary_key=True)
        user_avatar = db.Column(db.String(255))
        user_name = db.Column(db.String(255))

        def __repr__(self):
            return '<User %r>' % self.user_id


        def raw_insert_sample():
            db.engine.execute("""\
                INSERT INTO users (user_avatar, user_name) 
                VALUES ("Test", "Test")""")

homepage

  from flask import render_template


  def homename(name):

      # import users model  
      from models.users import Users


      Users.raw_insert_sample()

      result = Users.query.all()
      res = ''
      for row in result:
          res = res + '<br>' + str(row.user_id)

      return "<h1 style='color:blue'>Welcome %s</h1>" % res

======================================== ab test

  vagrant@vagrant-ubuntu-trusty-32:~$ ab -n 200 -c 100 http://flask.dev/asdfsdfgsdfg/
  This is ApacheBench, Version 2.3 <$Revision: 1528965 $>
  Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
  Licensed to The Apache Software Foundation, http://www.apache.org/

  Benchmarking flask.dev (be patient)
  Completed 100 requests
  Completed 200 requests
  Finished 200 requests


  Server Software:        nginx/1.4.6
  Server Hostname:        flask.dev
  Server Port:            80

  Document Path:          /asdfsdfgsdfg/
  Document Length:        233 bytes

  Concurrency Level:      100
  Time taken for tests:   0.158 seconds
  Complete requests:      200
  Failed requests:        0
  Non-2xx responses:      200
  Total transferred:      78200 bytes
  HTML transferred:       46600 bytes
  Requests per second:    1263.46 [#/sec] (mean)
  Time per request:       79.147 [ms] (mean)
  Time per request:       0.791 [ms] (mean, across all concurrent requests)
  Transfer rate:          482.44 [Kbytes/sec] received

  Connection Times (ms)
                min  mean[+/-sd] median   max
  Connect:        0    8   7.8     11      18
  Processing:    11   56  14.7     54      86
  Waiting:        7   56  14.9     54      86
  Total:         22   64  16.9     57     105

  Percentage of the requests served within a certain time (ms)
    50%     57
    66%     65
    75%     74
    80%     80
    90%     93
    95%     98
    98%    103
    99%    104
   100%    105 (longest request)
  vagrant@vagrant-ubuntu-trusty-32:~$
edited Jul '17

Maybe you have xdebug installed and enabled? This can affect performance much.

Notice you are doing var_dump - this is huge difference i guess.

Also you are creating new model instance for no reason - another difference.

Also overall more objects/classes etc this is stupid to compare really.

edited Jul '17

ok i just check xdebug is not enabled.

I tried to change the get_test from homecontroller to this so that I will not have to use the Users model, but I am now getting blank results, any idea why? tia

  public function get_test(){
        $sql = "SELECT * FROM users 
                LIMIT 12 
                OFFSET 1";
        $di = \Phalcon\DI::getDefault();
        $db = $di['db'];
        $data = $db->query($sql);
        $result = $data->setFetchMode(\Phalcon\Db::FETCH_OBJ);
        $results = $data->fetchAll();
        foreach($results as $result){
            echo $result->user_id . "<br>";
        }
        exit();
  }
edited Jul '17

Idk, check logs. Tbh im not sure what is your problem. You are comparing diffrent codes and diffrent languages.

edited Jul '17

Actually no problem whatsoever and Just tried it out for curiosity reasons. Anyway, I have probably messed up with my phalcon setup and everything and yeah, this is not probably the right way to compare the two

I have my page based on phalcon and angular rendered in below one second, all requests are around ~50-80ms(ajax) so it's performing very fine.

edited Jul '17

Don't you cache the result set in Python Code?

If you serve cached content then it will be always fast in every language compared to situation when you are fetching the whole result set for every request.

Also check your MySQL configuration - maybe for PHP the MySQL Query Cache is disabled but on other server when you are using Python it's enabled?

Also - excuse me, but 1300 req per second seems unrealistic, it means below 1 ms per request, you wont get such performance on "naked" MySQL using where and searching on moderately sized dataset, not to mention MySQL + scripting language. It's possible using CPython and "hello world" page but not with conjunction with fetching dataset from DB on your machine.

Something's wrong here or you just serving cached content in every request.



3.8k

Do you have indexes on "field" and "product_id"? The query will be slow without indexes. With indexes, the speed depends on the database configuration. There are several database memory allocation settings that all interact to help cache indexes and queries. The slightest difference can produce a big difference.

Then the speed depends on what memory is available. When you run two different applications, one could be pushing your machine into paging. Check overall memory than the MySQL memory usage stats.

You can also ask MySQL to show you the internal query processing. There might be a slight difference that makes one query work well and tips the the other query over into a slower approach.

MySQL tuning is about a three day workshop. :-(

Hi guys,

After double checking the route on my flask, it is routing to a 404 page thats why it is faster. and correcting the memory on the machines, both perform well on my end around 90-100req/s. But you can also try it out as I maybe messing up some other factors like mysql tuning, nginx etc. Just curious, not really master of the two.

MySQL or MariaDB? There are few things to set there as well.... which is whole another story.

Also while var_dump returns entire raw data it will surely slow down things - on ab/any front fetching program that will make difference as you're measuring data transfer too - so just imagine w/o any framework to load huge text file from filesystem and to echo it on the page - that will make ab and similar tests crawl as large amouts of data is being transferred.

Remember, here we're talking about framework performance, not DB underlying driver. Sure, framework has support for (data) models, but in general take into account that RDBMS will be the slowest factor in a chain.



203

Hi,guys, What languages are you developing in Web programs now? im from china learning Phalcon ..

Php Phalcon and JS Angular.



3.8k

One approach to separating the db time from the code is to log the SQL generated by the code and run the SQL in PHPMyAdmin or direct in mysql.

You can also add explain to the SQL to see the retrieval strategy. Slight differences in settings and memory usage can completely change requests for multiple rows. Plus MariaDB expands the relevant settings from about 16 to about 60. :-(

Interesting, maybe thats why because I am using mariadb on this one.

Plus MariaDB expands the relevant settings from about 16 to about 60. :-(