CakePHP performance issue - 30-50 seconds of schema loading

Question

I'm developing an online sales management application for a bakery. I decided to use Cake PHP as a framework and Mochahost as hosting provider since I haven't heard any major drawbacks of both.

My application is currently under the development phase. The MySQL user it uses has only a single database available to it, the database has only 10-15 tables and currently maximum 50 rows in total (most tables empty yet).

I'm not sure, since debug kit shows only a single (or if session timeout 3) queries taking acceptable time (less that 0.2 of a second) but timers say Controller.shutdown event or Controller.startup event takes anywhere from 30 000 milisecs to 58 000 miliseconds (!!).

Each and every load of my page takes anywhere from 30 to 60 seconds(!!!). Found out that it is caused by getting schema information (getSchemaCollection(), typeMap()). Okay, I know, development settings makes cache only available for 2 mins or so, but every time I reload the page it takes 30-60 seconds with no table associations defined at all. No, I am not running anything unusually complex, I use Auth component and database Sessions...

With defined (deep) associations it runs for so long that page dies HTTP 504 Gateway Time-out or 502 Bad Gateway. With no response at all and according to cpanel the script is still runnning in background blocking one of my available entry processes and mysql user sign-ins. This means I can't even develop my page since I can't load it even once to have caches further on. Developing on a private development server couldn't also work: at the first production load, it would also try to build the cache.

Also disabling cache by calling Cache::disable() didn't help at all.

I also noticed that there is one query that takes 30-60 seconds to run on the mysql server included in the hosting plan, and that is SHOW SCHEMAS. I guess cakephp uses this query when getSchemaCollection() gets called for the first time.

How long this query runs for you? Am I wrong that this is totally inacceptable and I should ask for a refound or an immediate fix? I cant afford pageloadings this long since the bakery has 500 costumers daily, so every purchase would use heavy sql, etc.

Current sessions table:

Field   Type    Null    Key Default Extra
id  char(40)    NO  PRI NULL    
created timestamp   NO      CURRENT_TIMESTAMP   
updated timestamp   NO      CURRENT_TIMESTAMP   on update CURRENT_TIMESTAMP
data    blob    YES     NULL    
expries int(10) YES     NULL    
ipAdress    varchar(15) NO      NULL    
user_id char(36)    YES MUL NULL    

This table has user_id as foreign key to users table's PK (id).

SessionsTable.php:

class SessionsTable extends Table
{
    public static $uid = null;

    public function initialize(array $config)
    {
        parent::initialize($config);
        //$this->belongsTo('Users');
    }

    public function implementedEvents()
    {
        return array(
            'Model.beforeSave' => 'beforeSave',
        );
    }

    public function beforeSave(Event $event, EntityInterface $entity, $options = []) {
        $request = Router::getRequest();
        $request->trustProxy = true;
        $entity->set('ipAdress', $request->clientIp());
        if (!is_null(self::$uid)) $entity->set('user_id', self::$uid);
    }
}

Update I am not an expert at php profiling but if im correct the correspondig significant value is PDOStatement->execute() which gets called 5 times for 94.13 self(?)/incl.(?)

Update 2 I have recieved a quick and satisfying reply from mochahost saying show schemas query is disabled for various understandable performance reasons. They advised me to use SHOW SCHEMAS LIKE 'myuser_%' since all database names are in 'user_dbname' format. So my question is where and how should i impement this to be used by default in cakephp?

Answer 1

It sounds like you know the problem. The SHOW SCHEMAS should not be taking 30-60 seconds to load. That's a problem at the server level, assuming that you've tried running that query manually and yielded the same results (outside the context of your PHP application).

CakePHP needs access to your DB schema to work its magic, so I would suggest discussing this issue with your hosting provider.

Answer 2

I know this question is kind of old but since then I've been working on the application, managed to solve performance issues and it does a great job. For those out there, who came across a similar problem here are all my thought on the topic:

The Answer

• Use VPS. That way you dont have to share access to the same mysql server instance with other users (/apps). Note This solution also provides way better performance, when your app hits a treshold in user engagement per server. For more information on scaling development for beginners, chek out this post.

OR

• Build your DB Schema cache on the development machine and copy the cache files to the production (or live-to-be/staging) system. After, either set Configure::config('default', false) to enable production mode, thus disable quick cache invalidation (development default is 2 mins); or set cache expiration limits manually in your app.php config file. Note you may need to periodically update the cache files and their expiration to prevent the rebuilding process.

OR

• Use SHOW DATABASES LIKE '(your username)\_%'. This likely will not be disabled, however implementing such change in a framework could be quite hard. Supposing you are developing a small web app, this may not worth the time to work on it. Use the solution above in most cases.

BUT

• If you need a fix for another software environment or tool, such as MySQL Workbench or phpMyAdmin, see the workaround example later, using the third option. Note this works only, because all the schema names on the server use the following pattern: (username)_(actual db name).

Longer version, some additional thoughts

Provided the hosting was a live plan on a shared (linux) environment, and databases are also in shared environment, it seems like the provider (Mochahost) either disabled or restricted some queries (or at least some form of them) such as:

• SHOW DATABASES
• SHOW SCHEMAS
• queries on the information_schema

"Please, note that for optimum performance we use seperate remote MySQL database servers. (...) The access to our (shared plan) accounts' MySQL database is restricted and does not allow for the execution of the SHOW DATABASES or SHOW SCHEMAS command. In order to view a list of your databases, you should use a similar query: (see later). If you need to remotely access your MySQL and you are using (shared plan) - you can still do so however you will not be able to use the above command due to mysql privileges. The restriction is applied in order to improve the overall performance of our MyQL servers as well improve the uptime, and server security." - hosting provider's level 2 tech support. (Shoutout to them, this email was in fact very useful.)

Thinking about it, it is understandable and I consider it as a good practice. Consider a single query to describe all the available schemas on a shared server or a big cluster. This could mean dozens, hundreds or even more entries to iterate through, and return. Also note the security issue here, the results need to be filtered (using more resource) in order to not expose sensitive data about other users. Yes, database names are sensitive data.

Whereas tweaking the query a bit, to exclude non relevant results yields way better performance and obviously safer: SHOW DATABASES LIKE '(your username)\_%'. NOTE AGAIN this workaround is only doing its job because all schemas are named in the same pattern above. Also, your provider may disable this as well.

Getting phpMyAdmin (or other db management software) to work

$usr = 'YourUserName';
$cfg['Servers'][$i]['DisableIS'] = true;
$cfg['Servers'][$i]['ShowDatabasesCommand'] = "SHOW DATABASES LIKE '$usr\_%'";

Or such code in the configuration of phpMyAdmin should do the job. Note this is not guaranteed to work and is a workaround - but better give you an idea on solving your problem.

As far as other database management software goes, I only have experience with the MySQL Workbench. Unfortunately, this tool doesn't support tweaking but executes an initial query to fetch schemata. I'm going to open a feature request ticket or some sort and post updates on that here.

Make sure, the tool of your choice does not execute initial queries like the above, or - at least - there is an option to tweak your queries.

And last but not least

Never develop on public, production environments. Never.

Trying out (testing) a particular dev snapshot is fine. As long as you remove it right after. Do not leak unstable software. Use a local development server (best) or a standalone or VPS as a development server (with considered security), if you really need.