Benchmark shell to test performances of CTI read/write operations.

BenchmarkShell.php

<?php
/**
 * BEdita, API-first content management framework
 * Copyright 2017 ChannelWeb Srl, Chialab Srl
 *
 * This file is part of BEdita: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published
 * by the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * See LICENSE.LGPL or <http://gnu.org/licenses/lgpl-3.0.html> for more details.
 */

namespace BEdita\Core\Shell;

use BEdita\Core\Model\Entity\ObjectType;
use BEdita\Core\ORM\QueryFilterTrait;
use Cake\Cache\Cache;
use Cake\Console\Shell;
use Cake\Database\Expression\QueryExpression;
use Cake\Datasource\ConnectionManager;
use Cake\ORM\TableRegistry;
use Faker\Factory;
use Faker\Generator;

/**
 * Run benchmarks to test performances of CTI read/write operations.
 */
class BenchmarkShell extends Shell
{

    use QueryFilterTrait;

    /**
     * List of tables required for the benchmark.
     *
     * @var string[]
     */
    protected $tables = [
        'object_types',
        'relations',
        'objects',
        'profiles',
        'users',
        'locations',
        'relation_types',
        'object_relations',
    ];

    /**
     * List of object types to be included in the benchmark.
     *
     * @var string[]
     */
    protected $objectTypes = [
        'documents',
        'news',
        'profiles',
        'users',
        'locations',
    ];

    /**
     * {@inheritDoc}
     */
    public function getOptionParser()
    {
        return parent::getOptionParser()
            ->addSubcommand(
                'insert',
                [
                    'help' => 'Insert records for benchmarking.',
                    'parser' => [
                        'arguments' => [
                            'count' => [
                                'help' => 'Number of records to be inserted.',
                                'required' => true,
                            ],
                        ],
                        'options' => [
                            'connection' => [
                                'short' => 'c',
                                'help' => 'Connection to use.',
                                'default' => 'test',
                                'choices' => array_diff(ConnectionManager::configured(), ['default']),
                            ],
                            'locale' => [
                                'short' => 'l',
                                'help' => 'Locale of generated records.',
                                'default' => 'en_US',
                            ],
                        ],
                    ],
                ]
            )
            ->addSubcommand(
                'select',
                [
                    'help' => 'Run benchmarking on select queries.',
                    'parser' => [
                        'arguments' => [
                            'objectType' => [
                                'help' => 'Object type to run query against.',
                                'choices' => $this->objectTypes,
                            ],
                            'samples' => [
                                'help' => 'Number of samples.',
                                'default' => 100,
                            ],
                            'filter' => [
                                'help' => 'Filter to add to query, in the form of a query string.',
                            ],
                        ],
                        'options' => [
                            'connection' => [
                                'short' => 'c',
                                'help' => 'Connection to use.',
                                'default' => 'test',
                                'choices' => array_diff(ConnectionManager::configured(), ['default']),
                            ],
                        ],
                    ],
                ]
            );
    }

    /**
     * {@inheritDoc}
     *
     * @codeCoverageIgnore
     */
    public function startup()
    {
        // Disable cache, alias default connection and clear table registry.
        ini_set('memory_limit', -1);
        Cache::disable();
        ConnectionManager::alias($this->param('connection'), 'default');
        TableRegistry::clear();

        parent::startup();

        // Prepare datasource.
        $this->verbose('=====> Creating <info>tables</info>...');
        $this->ensureTables($this->tables);
        $this->verbose('=====> Creating <info>object types</info>...');
        $this->ensureObjectTypes($this->objectTypes);
        $this->verbose('=====> Creating <info>first user</info>...');
        $this->ensureFirstUser();
    }

    /**
     * {@inheritDoc}
     *
     * @codeCoverageIgnore
     */
    protected function _welcome()
    {
        parent::_welcome();

        // Output connection info.
        if ($this->param('connection')) {
            $info = ConnectionManager::get($this->param('connection'))->config();
            $info['vendor'] = explode('\\', $info['driver']);
            $info['vendor'] = strtolower(end($info['vendor']));

            if (isset($info['host'])) {
                $this->out(sprintf('<info>%8s</info>: %s', 'Host', $info['host']));
            }
            $this->out(sprintf('<info>%8s</info>: %s', 'Database', $info['database']));
            $this->out(sprintf('<info>%8s</info>: %s', 'Vendor', $info['vendor']));
            $this->hr();
        }
    }

    /**
     * Ensure that a list of tables is present in the datasource.
     *
     * Table definition is copied from `default` connection.
     *
     * @param string[] $tables List of tables that should be checked for presence.
     * @return void
     */
    protected function ensureTables(array $tables)
    {
        /* @var \Cake\Database\Connection $connection */
        $connection = ConnectionManager::get('default');
        $existing = $connection->getSchemaCollection()->listTables();
        if (count(array_diff($existing, $tables)) > 0) {
            $this->abort('Database has extra tables. Refusing to proceed.');

            return;
        }
        $tables = array_diff($tables, $existing);

        /* @var \Cake\Database\Connection $defaultConnection */
        $defaultConnection = ConnectionManager::get('default', false);
        $schemaCollection = $defaultConnection->getSchemaCollection();
        foreach ($tables as $table) {
            $table = $schemaCollection->describe($table, ['forceRefresh' => true]);
            $table->setOptions(['collation' => null]);
            foreach ($table->columns() as $column) {
                $options = $table->column($column);
                unset($options['collate']);
                $table->removeColumn($column);
                $table->addColumn($column, $options);
            }
            if ($table->name() === 'objects') {
                $table->dropConstraint('objects_createdby_fk');
                $table->dropConstraint('objects_modifiedby_fk');
            }
            foreach ($table->createSql($connection) as $statement) {
                $connection->query($statement);
            }
        }
    }

    /**
     * Ensure that a list of object types is present in the datasource.
     *
     * Object types definition is copied from `default` connection.
     *
     * @param string[] $objectTypes List of object types that should be present.
     * @return void
     */
    protected function ensureObjectTypes(array $objectTypes)
    {
        $table = TableRegistry::get('ObjectTypes');
        $objectTypes = array_diff($objectTypes, $table->find('list')->toArray());
        if (empty($objectTypes)) {
            return;
        }

        /* @var \Cake\Database\Connection $defaultConnection */
        $defaultConnection = ConnectionManager::get('default', false);
        $defaultTable = TableRegistry::get('SourceObjectTypes', [
            'connection' => $defaultConnection,
            'className' => 'BEdita/Core.ObjectTypes',
        ]);
        $objectTypes = $defaultTable
            ->find()
            ->where(function (QueryExpression $exp) use ($defaultTable, $objectTypes) {
                return $exp->in($defaultTable->aliasField('name'), $objectTypes);
            })
            ->map(function (ObjectType $objectType) {
                $objectType->isNew(true);

                return $objectType;
            })
            ->toArray();

        $table->saveMany($objectTypes);
    }

    /**
     * Ensure that first user is present in the database.
     *
     * This is required in order to insert new records.
     *
     * @return void
     */
    protected function ensureFirstUser()
    {
        $table = TableRegistry::get('Users');
        if ($table->find()->where(['Users.id' => 1])->count() === 1) {
            return;
        }

        $user = $table->newEntity([
            'created_by' => 1,
            'modified_by' => 1,
            'username' => 'bedita',
            'password' => 'bedita',
        ], ['accessibleFields' => ['*' => true]]);
        $user->set('type', 'users');

        $table->saveOrFail($user, ['checkRules' => false]);
    }

    /**
     * Get data for an entity.
     *
     * @param string $type Entity type.
     * @param \Faker\Generator $generator Faker generator.
     * @return array
     */
    protected function getData($type, Generator $generator)
    {
        $created = $generator->dateTimeThisYear;
        $modified = $generator->dateTimeThisYear;

        $data = [
            'type' => $type,
            'deleted' => $generator->boolean(10),
            'status' => $generator->randomElement(['on', 'off', 'draft']),
            'uname' => sprintf('%s-%s', $type, $generator->unique()->uuid),
            'title' => $generator->sentence,
            'description' => $generator->paragraph,
            'body' => implode(PHP_EOL, (array)$generator->paragraphs),
            'created_by' => 1,
            'modified_by' => 1,
            'created' => $created < $modified ? $created : $modified,
            'modified' => $created < $modified ? $modified : $created,
        ];

        switch ($type) {
            case 'users':
                $data += [
                    'username' => $generator->unique()->userName,
                    'password' => $generator->password,
                    'blocked' => $generator->boolean(10),
                ];
                // no break
            case 'profiles':
                $data += [
                    'name' => $generator->firstName,
                    'surname' => $generator->lastName,
                    'email' => $generator->unique()->email,
                    'birthdate' => $generator->dateTimeThisCentury,
                ];
                break;

            case 'locations':
                $data += [
                    'coords' => sprintf('POINT(%s %s)', $generator->longitude, $generator->latitude),
                    'address' => $generator->address,
                    'postal_code' => $generator->postcode,
                    'country_name' => $generator->country,
                ];
                break;
        }

        return $data;
    }

    /**
     * Insert batch of records.
     *
     * @param int $count Number of records to insert.
     * @return void
     */
    public function insert($count)
    {
        $this->out(sprintf('=====> Generating <info>%d</info> records...', $count));

        /* @var \Cake\Shell\Helper\ProgressHelper $progress */
        $progress = $this->helper('Progress');
        $this->out();
        $progress->init([
            'total' => $count,
        ]);

        // Insert records.
        $generator = Factory::create($this->param('locale'));
        $totalTime = $peakTime = $errors = 0;
        for ($i = 1; $i <= $count; $i++) {
            $type = $generator->randomElement($this->objectTypes);
            $table = TableRegistry::get($type);

            $entity = $table->newEntity($this->getData($type, $generator), ['accessibleFields' => ['*' => true]]);

            $start = microtime(true);
            $result = $table->save($entity);
            $delta = microtime(true) - $start;
            if ($result === false) {
                $errors += 1;
            }

            $totalTime += $delta;
            $peakTime = max($peakTime, $delta);

            $progress->increment(1);
            $progress->draw();
        }
        $this->out();
        $this->out();

        // Output stats.
        $this->out(sprintf('=====> <success>%15s</success>: %7d', 'Saved entities', $count - $errors));
        $this->out(sprintf('=====> <error>%15s</error>: %7d', 'Save errors', $errors));
        $this->out(sprintf('=====> <info>%15s</info>: %.05fs', 'Avg. save time', $totalTime / $count));
        $this->out(sprintf('=====> <info>%15s</info>: %.05fs', 'Peak save time', $peakTime));
    }

    /**
     * Run sample queries to load data.
     *
     * @param string $objectType Name of object type to use.
     * @param int $samples Number of samples to measure.
     * @param string|null $filter Query filter.
     * @return void
     */
    public function select($objectType, $samples = 100, $filter = null)
    {
        $table = TableRegistry::get($objectType);

        parse_str($filter, $options);
        if (!array_key_exists('filter', $options)) {
            $options = [
                'filter' => $options,
            ];
        }

        /* @var \Cake\Shell\Helper\ProgressHelper $progress */
        $progress = $this->helper('Progress');
        $this->out();
        $progress->init([
            'total' => $samples,
        ]);

        $totalTime = $peakTime = 0;
        for ($i = 1; $i <= $samples; $i++) {
            $query = $table->find();
            if (!empty($options['filter'])) {
                $query = $this->fieldsFilter($query, $options['filter']);
            }

            $start = microtime(true);
            $query->all();
            $delta = microtime(true) - $start;

            $totalTime += $delta;
            $peakTime = max($peakTime, $delta);

            $progress->increment(1);
            $progress->draw();
        }
        $this->out();
        $this->out();

        // Output stats.
        $this->out(sprintf('=====> <success>%15s</success>: %7d', 'Found results', $query->count()));
        $this->out(sprintf('=====> <info>%15s</info>: %.05fs', 'Avg. query time', $totalTime / $samples));
        $this->out(sprintf('=====> <info>%15s</info>: %.05fs', 'Peak query time', $peakTime));
    }
}

Place this file in BEDITA4/plugins/BEdita/Core/src/Shell/BenchmarkShell.php.

Run bin/cake benchmark --help for help.

Available commands:

• bin/cake benchmark insert <count>: insert <count> records.
• bin/cake benchmark select <objectType> <samples> [<filter>]: run <samples> queries to select objects of type <objectType>, with an optional filter <filter> (a string with the same format of ?filter query string).