Out of the box performance: PHP + PDO + MariaDB

Backstory:

Another week, another evening at the Pub with some friends and colleges. Somehow or the other we got on the topic of database insert performance and how long it would take to reach the 32bit max integer.  That being 2.14somethingsomethingsomething billion. I wagered that the the max signed int could be a reached relatively quickly, my college on the other hand said ‘no no no; it  would take hours. Days even’. And so, a wager was born.

The requirements:

PHP + PDO + a SQL database; default configurations. No editing php.ini to allow higher memory usage, no disabling *SQL disk_flush in my.cnf, etc. Raw install, logic, go for the gold.

The process:

On the local development machines we limit the container service manager to limit hardware usage to 7 of the 2.4Ghz CPUs and 15Gb of memory. For the disk we run 256GB SSD, desktop models; nothing fancy.

On that hardware I setup a PHP 7:latest service and a MariaDB:latest service; then linked them. From there it was a matter of connection credentials and increasing the batch insert count until it was close, but not over, the default memory usage per thread. Then how to start up multiple threads, easy enough, bash helped out there. So using bash I spun up 10 threads and let the process run for 1 to 2 minutes.

Getting the max value after the given time frame I was able to extrapolated out how long  it would take to fill the 2.14 billion rows.

The Result:

At current the fastest time requirement would take 1.526 hrs to go from 0 to 2.14 billion row inserts. I know we can get faster but ran out of time today.

The Source:

If you are interested in the code / stats /etc the repo is here https://github.com/davidjeddy/full-up-the-db. Feel free to fork / PR the repo if you can get a faster speed. It would be really awesome to show 32 bit max int can be reached in 5 minutes or less. (Remember, no editing of configurations.)

PSA: Do not use Codeception DB and Yii2 modules together…

…specifically the Yii2: ORM and DB module and transactions. The Yii2 $I->seeRecord() & related methods do NOT use the same connection ID as the DB module. So doing actions such as importing fixtures and executing actions via the ActiveRecord abstraction happen on the frameworks connection.

Trying, then, to do actions like $I->seeInDatabase() and related DB modules actions will fail, almost always. Why? The Db module uses a separate connection, as defined in the suite.yml (acceptance/functional/unit) files.

So, from here out I will not be using the two modules together. Either use Yii2+Fixtures or use the DB+dump.sql. Both, together, is problematic at best.

Did a thing today, ‘nother new package.

New Package: Stripe + CommandBus + Yii2

I got tired of writing out the entire Stripe class requirements coupled with the need to trigger stripe events from different areas of the application  motivated me to create this package. It is by far NOT production ready but lays the ground work and ideas for the package. Take a look, leave a comment, contribute.

Also have another one in the works that combines Google Chrome, Codeception, headless browser testing, and replacing PhantomJs (since the maintainers are deprecating the project).

Yii2 hasOne vs hasMany param order

hasOne()

* 
* public function getCountry()
* {
*     return $this->hasOne(Country::className(), ['id' => 'country_id']);
* }
*

hasMany()

*
* public function getOrders()
* {
* return $this->hasMany(Order::className(), ['customer_id' => 'id']);
* }
*

Notice the opposite order? The docs explain why but it’s still confusing.

Ticket submitted.

Yii2 and bower/asset: jquery.inputmask AfterAction report…

So last week Im working along and I run composer install … to get a new framework installation to while trying to track down a bug. The install fails. I am all like ‘what did you say to me!?’ bower-asset/jquery.inputmask package was not being found. I look through the composer.json of the framework and sure enough it is required. A frontend client asset is required to install a framework. While some would say ‘thats a terrible idea’ in this case it makes sense. The framework is a full MVS stack, not just a middleware, or just a ORM, or just a client rendering engine.

Anyways, after looking in Packagist and following the link to Github the repo itself was basically empty. Apparently GirtHub repo alias’s have a life span limit. This limit had expired and thus caused the package URI to no longer resolve correctly. So I submitted a couple tickets on the respective projects and went home.

Not sure how long it took exactly but when I returned to work the next morning not only GitHub restored the alias but the framework group was going to updated the dependency list to point to the (newer) repository in the next point release. While this does not seem like a big thing to some; I felt happy I was able to find, isolate, and identify an issue that effected a number of people; and did so very quickly. Then provided a solution. #problem-solver

 

Related Links:

Packagist: jquery-inputmask
Yii2 GitHub ticket

Yii2 AR SQL Verbs; Y-U-SO-WEIRD?

Yii2 being an active record style DB abstraction AND needing to support a wide range of database technologies facilitated creating the standard insert / select / update / delete functionality inside the Active Record model layer. While most of implementation makes sense some of it is not intuitive. Herein is a TL;DR of the 4 major commands and the *All() version if applicable.

 

select / selectAll (in Yii2 this is termed ‘find’)

\Class::find([{array of criteria}])->one(); OR \Class::findOne([{array of criteria}]);

\Class::find([{array of criteria}])->all(); OR \Class::findAll([{array of criteria}]);

 

insert / insertAll (termed ‘save’)

$model = new \Class({column values as array});

$model->save();

No saveAll() implementation by default

 

update / updateAll

$model = \Class::find({column values as array});

$model->setAttribute(s)({string or array of SQL SET keyed values})

$model->save();

 

Class::updateAll([{array of criteria}], {string to Key = Value pairs for SQL SET})

 

delete / deleteAll

\Class::delete({string of criteria})

\Class::deleteAll({string of criteria})

 

Bonus: Yii2 also has the ability to create database commands and bypass the Active Record abstraction altogether: Yii::app()->db->createCommand({string  of SQL command}).

 

Now, lets look at MySQL / Maria / MsSQL / Postgra default immplimentation of the same actions:

select/all

Select {string of keyed values} FROM {string of source} WHERE {string of keyed values}

 

insert/All

Insert Into {string of source} VALUES {string of keyed values}

 

update/All

Update {string of source} Set {string of keyed values}

 

delete/All

Delete From {string of source} Where  {string of keyed values}

 

See a pattern there? string source, key/value data sets. And minus Select all start with the data source followed by the command, the the key/value criteria.  And even select makes sense when you treat the field names as a string substitution for `select {needle(s)} from {source|`.

 

It is not that AR is bad, nor that Yii2 had a lack of effort. Trying to support the feature set of multiple database technologies, active record, createQuery, best practices, security, and community requests is daunting, hands down. Maybe I’ll write a package to normalize the base 4 verbs for MySQl/MariaDB…

 

 

Shameless Self Promotion: Presenting during Tampa Bay PHP’s May meetup!

Using Codeception for Acceptance testing

Tuesday, May 30, 2017, 6:30 PM

Sourcetoad’s new location
2901 W Busch Blvd #1018 Tampa, FL

9 Members Went

David Eddy will present “Using Codeception for Acceptance testing: the crash course.”

Check out this Meetup →

 

Update: Video is up on youtube at https://youtu.be/QXSP0bEpF4Y .

Refactoring array index magic number with class constant.

During a code review my peer Michal Mazur turned me onto the follow example; I have to say I am really digging it: Array Index as constant .

In my specific case here is the usage:

if (!empty($paramData[19]) && is_string($paramData[19])) {
    $paramData = $paramData[19];
} elseif (empty($paramData[19]) && !empty(isset($paramData[17])) && isset($paramData[17])) {
    $paramData = $paramData[17];
}

…and the refactored logic:

if (!empty($rowData[$this::FULL_SOURCE]) && is_string($rowData[$this::FULL_SOURCE])) {
    $imageSource = $rowData[$this::FULL_SOURCE];
} elseif (empty($rowData[$this::FULL_SOURCE]) && !empty(isset($rowData[$this::SQUARE_SOURCE])) &&
    isset($rowData[$this::SQUARE_SOURCE])
) {
    $imageSource = $rowData[$this::SQUARE_SOURCE];
}

While a bit more verbose the logic is much easier to read using the contextually named constants.