[RFC] Add Tarantool database support

[zilveer]

Summary

Ability to add support for Tarantool database support. Tarantool is a NoSQL database with support of SQL syntaxes.

Motivation

Tarantool is both in memory based database as well as in disc memory database. It is also blazing fast and beats Redis in many of the compared benchmarks.
The aim of Ubiquity is to be fast and Ubiquity together with support of Tarantool database would be awesome !

Compatibility with Ubiquity's philosophy

Indicate compatibility or improvements in:

• performances
• ease of use

Expected results

Tarantool has a very high RPS and will boost the performance additionally.

Additional context

There is a package , https://github.com/tarantool-php/client, which makes it easy to connect to Tarantool and is able to make both NoSQL as well as SQL requests out of the box.

There is also another php package for Tarantool php-mapper which is awesome and supports object models, like Eloquent models, for Tarantool database which could be awesome to use as well.

Regards

[jcheron]

Thank you @zilveer for this request that we had already discussed.

Ideally, the database query code and the use of ORM should remain unchanged for the developer. He will just have the possibility to switch from a classic PDO connection to a Tarantool connection (or another one afterwards), without changing a line of his code, so just a choice at the configuration level.

If this choice is confirmed, it will be inappropriate to use Tarantool php-mapper , at least initially.

Ubiquity has recently become multi-databases, it should on this occasion become multi-databases type.

All that remains now is to get organized and define priorities.
There are currently 3 RFCs open, unprocessed, 4 including this one:

• this one [RFC] Add Tarantool database support #64

From @gildonei

• Migrations [RFC] Migrations #51
• Cron tasks [RFC] Cron Tasks #55
• Email module [RFC] E-mail module #56

The documentation is not up to date, and some demos are in progress.

The discussion is open to define priorities, knowing that we must take into account 2 essential elements in our choices:

• The respect of users and the emerging community
• The orientation of the framework, its coherence

[zilveer]

@jcheron thanks for the reply.

I totally agree with you regarding the php mapper since it has to do with models and would conflict with the current model in Ubiquity.

Yes, by switching from any Database to a Tarantool connection the application should still be fine and fully working out of the box.

Let me know if there is any more questions or thoughts regarding Tarantool or it's implementation in Ubiquity .

Regards

[jcheron]

Concerning the architecture, I was planning to start with this implementation:

The Database class contains a Wrapper instance, which performs the operations specific to a database type.
The user will just have to make a choice of database type (which determines the Wrapper instance), when configuring a connection.

If you have another idea, feel free to submit it.

We already have to implement this with PDO, to see how it works.
I think there will be a slight impact on performance (pdo), as complexity increases.

[zilveer]

@jcheron this seems to be a good architecture of its implementation, yeah it will impact it but it won't even be noticed ;)

My idea is to actually also implement the php-mapper not for initiating models, but for actually be able to run/save procedures on the Tarantool instance which actually boosts the Tarantool dB requests even more.
With Tarantool and the great PHP Ubiquity framework I think Ubiquity will definitely be a unique php framework among others.

Regards

[jcheron]

Indeed, you're right, @zilveer , no significant difference.
On one Bench run, it's a little less good, on another it's better...
For once, I'm refraining from putting the graphs.

Next step: TarantoolWrapper

[zilveer]

@jcheron yes definitely you are right!

Any idea about adding php-mapper as well to the TarantoolWrapper so one can use the built in procedure storing in the Tarantool instance ? That would be great.

Here is a link to the Tarantool php-mapper:
https://github.com/tarantool-php/mapper

Regards

[jcheron]

The work of the TarantoolWrapper class is just to do the equivalent of what PDO was doing (PDOWrapper now).
But if we have a Tarantool connection instance, I guess we can then use php-mapper to do extra things.

You who know Tarantool better than I do, I didn't see any prepared statements, just enough to make parameterized requests. Can you confirm that?

Do you know the PECL version, maybe faster...
I don't know if the API is the same.

[zilveer]

@jcheron PECL version is older and slower and seems to be outdated. Php-client is the way to go.

Yeah there is only bindings in Tarantool which acts as prepared statements.

And yes if there is any Tarantool instance then using php-mapper is done though the initiated Tarantool instance.

Regards

[jcheron]

Hi @zilveer ,
If you want to test with Tarantool, you can use this composer.json file in a new project:

{
	"require": {
		"php": "^7.1",
		"twig\/twig": "^2.0",
		"phpmv\/ubiquity": "dev-multi-db-types",
		"phpmv\/php-mv-ui": "dev-master",
		"phpmv/ubiquity-tarantool": "^1.0@dev",
		"rybakit/msgpack": "^0.5.4"
	},
	"require-dev": {
		"monolog\/monolog": "^1.24",
		"mindplay\/annotations": "^1.3",
		"phpmv\/ubiquity-dev": "dev-multi-db-types",
		"phpmv\/ubiquity-webtools": "dev-multi-db-types",
		"czproject\/git-php": "^3.13"
	},
	"autoload": {
		"psr-4": {
			"": "app\/"
		}
	},
	"minimum-stability": "dev",
	"prefer-stable": true
}

And then do a composer install

I'll give you an example of a configuration (The dbName key is useless with Tarantool):

I haven't tested everything, so there must be some bugs left.
Tomorrow I'm going to see what it looks like in terms of performance, and certainly optimize...

[zilveer]

@jcheron
Wow you are just to good ;)
I will definitely test it out this week and see if I can find any bugs ;) !

Thx once again !! ;)))

Regards

[jcheron]

I'm relying on you to find some ;-)

Regards

[jcheron]

Some benchmarks results:
in reading, very little difference with pdo/Mysql, it is even slightly slower with Tarantool (pdo benefits from the persistent connection and prepared statements) => less than 1 ms difference

TechEmpower Multiple database queries benchmark (with 20 queries):

In writing, the difference is impressive (Tarantool is 8 times faster!)

TechEmpower Database updates benchmark (with 20 updates):

I didn't see any difference between Vinyl and MTX storage engines.

I am not a Tarantool specialist, there may be some adjustments to be made to optimize the database side.

[gildonei]

It is really impressive the difference in write operations

[jcheron]

Yes, it's impressive indeed.
And by using the PECL version of msgPack, we go from 8 to 9 times faster (17ms for Tarantool vs 156ms for pdo)

[zilveer]

It is good but the reading should be much higher then that, I will have a look at this as soon as I get time.

Regards

[jcheron]

Yes, I was also a little disappointed by the results in reading, so I made a benchmark between Tarantool and pdo/mysql without Ubiquity, still using the same Techempower test (20 consecutive readings).

The results are as follows:

The difference is even greater than with Ubiquity, which is partly explained by:

• the absence of persistent connection, and prepared statements
• the Loading of 22 Tarantool-php/client files

Tarantool

Server configuration

#!/usr/bin/env tarantool
-- Configure database
box.cfg {
   listen = 3302,
   background = true,
   log = '2.log',
   pid_file = '2.pid'
}
box.once("bootstrap", function()
   space = box.schema.space.create('world', {engine='vinyl'})
   space:format({
   {name = 'id', type = 'unsigned'},
   {name = 'randomNumber', type = 'string'}
   })
   box.schema.sequence.create('S',{min=1, start=1})
   space:create_index('primary', {
   type = 'tree',
   parts = {'id'},
   sequence= 'S'
   })
   box.schema.user.grant('guest', 'read,write,execute', 'space', 'world')
end)

Page source code:

\header('Content-type: application/json');
require_once 'vendor/autoload.php';

// Database connection
$client = \Tarantool\Client\Client::fromDsn ( 'tcp://127.0.0.1:3302' );

// Read number of queries to run from URL parameter
$query_count = 1;
if ($_GET['queries'] > 1) {
  $query_count = $_GET['queries'] > 500 ? 500 : $_GET['queries'];
}

// Create an array with the response string.
$arr = [];

// For each query, store the result set values in the response array
while (0 < $query_count--) {
  $query=$client->executeQuery('SELECT "id","randomNumber" FROM "world" WHERE "id" = ? limit 1', \mt_rand(1, 10000));
  // Store result in array.
  $arr[] = $query->getFirst();
}

// Use the PHP standard JSON encoder.
// http://www.php.net/manual/en/function.json-encode.php
echo \json_encode($arr);

pdo/mysql

Page source code:

\header('Content-type: application/json');

// Database connection
// http://www.php.net/manual/en/ref.pdo-mysql.php
$client = new \PDO('mysql:host=127.0.0.1;dbname=hello_world', 'userxxx', 'passwordxxx', [
    \PDO::ATTR_PERSISTENT => true,
    \PDO::ATTR_EMULATE_PREPARES => false
]);

// Read number of queries to run from URL parameter
$query_count = 1;
if ($_GET['queries'] > 1) {
  $query_count = $_GET['queries'] > 500 ? 500 : $_GET['queries'];
}

// Create an array with the response string.
$arr = [];

// Define query
$statement = $client->prepare('SELECT id,randomNumber FROM World WHERE id = ?');

// For each query, store the result set values in the response array
while (0 < $query_count--) {
  $statement->execute( [mt_rand(1, 10000)] );
  
  // Store result in array.
  $arr[] = $statement->fetch(PDO::FETCH_ASSOC);
}

// Use the PHP standard JSON encoder.
// http://www.php.net/manual/en/function.json-encode.php
echo \json_encode($arr);

Configuration for tests

• Host
  • System : Linux debian 9.0 64 bits
  • Processor : Intel 1x Intel® C2350 (Avoton) 2 C / 2T @1.7 Ghz x64, VT-x, AES-NI
  • RAM : 4 Ghz DDR3
  • HD : SSD 120 Go
• Network
  • bandwidth : Unmetered 1 Gbit/sec - 2500BaseX interface
  • network card : 2.5 Gb/s
• Servers
  • Http : Apache/2.4 (Debian)
  • php : PHP 7.3
  • Databases :
    • Ver 15.1 Distrib 10.1-MariaDB
    • Tarantool 2.2
    • 10 000 rows in DB

[jcheron]

Some news about Tarantool, Ubiquity and Swoole:
I tested it:

• ubi-swoole-tarantool => Ubiquity + Swoole + Tarantool + Coroutine
• ubi-tarantool => Ubiquity + Tarantool + Apache
• ubi-swoole-pool => Ubiquity + Swoole + Mysql coroutine + connection pool
• ubi-swoole => Ubiquity + Swoole +pdo
• ubi-pdo => Ubiquity + Apache + pdo
• ubi-swoole-mysqli => Ubiquity + Swoole + Mysqli + Coroutine

All tests are performed with 20 consecutive queries.

in writing

As expected, it's even faster with Swoole:

in reading

It's also better, but it seems less compared to the others:

Provisional conclusion:

• Tarantool outperforms the others in writing.
• In reading, it is as well as Swoole coroutine Mysql

Note that Ubiquity has now a new database wrapper for Swoole Coroutine Mysql, enough to make asynchronous Mysql queries with Swoole.

Do not compare these results with the previous ones (in absolute value), the test server is not the same.

[gildonei]

@jcheron I didn't know Swoole, it looks like ReactPHP. You already run tests with Ubiquity and ReactPHP didn't you? Comparing both, witch one of then should be better, Swoole or ReactPHP?

[jcheron]

Hi @gildonei
To have the equivalent of Swoole with React-php, you have to add php-pm, which has the role of process manager and load balancer.

React

React-php alone is the equivalent of a php server (not to be used in production), but faster.

To use it with Ubiquity in dev mode:

Ubiquity serve -t=react

php-pm

If you wants to use php-pm with Ubiquity:

composer require phpmv/ubiquity-php-pm:dev-master

And to start the server:

vendor/bin/ppm --bridge='\PHPPM\Ubiquity' --bootstrap='\PHPPM\Ubiquity' start --workers 32 --max-requests 1024 --host=127.0.0.1 --port=8080

The command is long and not integrated with dev-tools, but it is intentional (see reasons below)

Swoole

For Swoole, only under linux or mac for the moment:
Install Swoole:

pecl install swoole

Start the server from the project folder:

Ubiquity serve -t=swoole -p=8090

And now I come to your question:
React with php-pm is in php, which has advantages (easy to install with any OS), and disadvantages (it's slower).
But above all, PHP-PM still has some memory management problems, and a poor reaction to the intensive concurrent requests that developers have to solve.
For the moment, Swoole is therefore much more stable, much faster and above all it can be used in production (this is not recommended for react and php-pm, for the moment: dev only).
The results of Swoole and Ubiquity-swoole Benchmarks are very good on Techempower.

With php-pm, the results were not conclusive as soon as concurrency level was increased (see for example these results).
Since then, in consultation with the php-pm team, I have removed Ubiquity-React (php-pm) from the tests until solutions are found.

[gildonei]

Thank's for the explanation. I'll read more about swoole. Em seg, 2 de set de 2019 22:17, jcheron <notifications@github.com> escreveu:

…

Hi @gildonei <https://github.com/gildonei> To have the equivalent of Swoole with React-php, you have to add php-pm <https://github.com/php-pm/php-pm>, which has the role of process manager and load balancer. React React-php alone is the equivalent of a php server (not to be used in production), but faster. To use it with Ubiquity in dev mode: Ubiquity serve -t=react php-pm If you wants to use php-pm with Ubiquity: composer require phpmv/ubiquity-php-pm:dev-master And to start the server: vendor/bin/ppm --bridge='\PHPPM\Ubiquity' --bootstrap='\PHPPM\Ubiquity' start --workers 32 --max-requests 1024 --host=127.0.0.1 --port=8080 The command is long and not integrated with dev-tools, but it is intentional (see reasons below) Swoole For Swoole, only under linux or mac for the moment: Install Swoole: pecl install swoole Start the server from the project folder: Ubiquity serve -t=swoole -p=8090 And now I come to your question: React with php-pm is in php, which has advantages (easy to install with any OS), and disadvantages (it's slower). But above all, PHP-PM still has some memory management problems, and a poor reaction to the intensive concurrent requests that developers have to solve. For the moment, Swoole is therefore much more stable, much faster and above all it can be used in production (this is not recommended for react and php-pm, for the moment: dev only). The results of Swoole and Ubiquity-swoole Benchmarks are very good on Techempower <https://www.techempower.com/benchmarks/#section=test&runid=333c30b4-5501-4e74-a641-04a67eca266c&hw=ph&test=fortune&l=zik073-v&p=zik0zj-zik0zj-zik0zj-zik06n-f&c=2> . With php-pm, the results were not conclusive as soon as concurrency level was increased (see for example these results <https://www.techempower.com/benchmarks/#section=test&runid=52587667-bee1-4b9b-b34d-929b616d5e4c&hw=ph&test=query&c=6&d=e5&o=e> ). Since then, in consultation with the php-pm team, I have removed Ubiquity-React (php-pm) from the tests until solutions are found. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#64?email_source=notifications&email_token=AAKOWY77SGLBHENR6XDF5HTQHW3KJA5CNFSM4IJC673KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD5WXVWA#issuecomment-527268568>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAKOWY2H4P4ADAOJDY5DXMTQHW3KJANCNFSM4IJC673A> .

[jcheron]

After further adjustments on the Ubiquity-swoole connection pool.
It's 3x faster with than without: Yippi!

[zilveer]

@jcheron wow I am really impressed of your work !! :)
Well done !