I am currently in the process of building a new API that will get requested in three steps and perform three actions in a row.
- A product will be listed (select based upon user input).
- The QTY will be updated upon ordering.
- A customer name and address will be inserted.
No relation is needed.
I have run extensive testing (sharing only the summary here) on both HHVM (didn't work with inserts on MongoDB) and native PHP (using mod_php on Apache2 and php-fpm on Nginx).
The testing was done on a i5 650 with 4 GB of Ram.
Each test was run in a loop of 1.000, 10.000 and finaly 100.000 (when possible).
At first I tested with InnoDB and was surprised to see how slow MariaDB was in comparison to MongoDB, but that of course if because of the ACID. Since we do not (strictly speaking) require transactions I decided to test against MyISAM instead and that was a huge improvement over InnoDB making MariaDB much faster than MongoDB in our setup.
I then added a dynamic table structure in JSON for both inventory and customers in MariaDB and tested that with COLUMN_CREATE and COLUMN_ADD in which we replicated the MongoDB setup for testing with MariaDB. However, this is not real JSON support. Everything is stored in a BLOB. As of MySQL 5.7.8, MySQL supports a native JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents with automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error. But we did not test against MySQL 5.7.8. as we want the packages following Debian.
PostgreSQL already had native JSON format from before version 9.4. Now it also got JSONB which turns the JSON document into a hierarchy of key/value data pairs. All the white space is discarded, only the last value in a set of duplicate keys is used and the order of keys is lost to the structure dictated by the hashes in which they are stored.
Select speeds of MariaDB are extremely fast, but updating and inserts are extremely slow on InnoDB, but extremely fast on MyISAM.
PostgreSQL performs better than MariaDB with InnoDB.
HHVM didn't work with MongoDB as inserts into MongoDB isn't supported by the driver.
HHVM with MariaDB didn't show any improvements over normal PHP. We made sure to warm up the Jit thoroughly, however HHVM isn't optimized for every kind of operation, especially not for loops, so I suspect that is why.
HHVM is also famous for memory leaks, crashes and other problems, so we decided against deploying it, but tested it anyway.
At first I tested against the older version of MongoDB from Debian, namely 2.4.10, but afterwards upgraded to 3.0.6 in order to benefit from the speed improvements that should have been made.
At one time during our test MongoDB 3.0.6 crashed and it wasn't possible to get it up and running again. The log was silent so I started it manually with "-vvvv" option to get some output.
The output I got was:
2015-10-13T04:28:36.903+0200 D COMMAND [PeriodicTaskRunner] task: DBConnectionPool-cleaner took: 0ms
2015-10-13T04:28:36.903+0200 D - [PeriodicTaskRunner] cleaning up unused lock buckets of the global lock manager
2015-10-13T04:28:36.903+0200 D COMMAND [PeriodicTaskRunner] task: UnusedLockCleaner took: 0ms
2015-10-13T04:28:36.903+0200 D COMMAND [PeriodicTaskRunner] task: DBConnectionPool-cleaner took: 0ms
2015-10-13T04:28:36.905+0200 D INDEX [TTLMonitor] TTLMonitor thread awake
2015-10-13T04:28:36.945+0200 D JOURNAL [durability] groupCommit begin
2015-10-13T04:28:36.946+0200 D JOURNAL [durability] Processing commit number 1757
2015-10-13T04:28:36.946+0200 D JOURNAL [durability] groupCommit end
2015-10-13T04:28:37.048+0200 D JOURNAL [durability] groupCommit begin
2015-10-13T04:28:37.048+0200 D JOURNAL [durability] Processing commit number 1758
2015-10-13T04:28:37.048+0200 D JOURNAL [durability] groupCommit end
I then flushed the journal and deleted a lot of data, but I was never able to get MongoDB up and running again. I also could not find any specific data (except from the C code that printed the messaged) about what was going on.
I whish I could how located information on what exactly went wrong and why. I tried repaired the databases as well, that went fast, but the problem persisted.
I had to completely remove MongoDB and purge all files to get it going again. Maybe I did something wrong, but getting stuck in a situation like this, with no information on how to proceed, is not okay.
Testing on an i5 650 with 4GB Ram.
One new DB connection is opened and closed upon each loop, this is done on purpose. With InnoDB and PostgreSQL, transactions are implemented in the PDO code (not shown in the scripts below).
| Setup | CPU | MEM |
|---|---|---|
| Apache, php5-mod, php5-mongo, MongoDB | mongod 55%, apache 45% | mongod 7.5%, apache 0.8% |
| Nginx, php5-fpm, php5-mongo, MongoDB | mongod 55%, php5-fpm 45%, nginx 1.7% | mongod 5.9%, php5-fpm 0.9%, nginx 0.3% |
| Apache, php5-mod, php5-mysql, MariaDB (InnoDB) | mysqld 2.7%, apache 0.7% | mysqld 4.1%, apache 1.4% |
| Nginx, php5-fpm, php5-mysql, MariaDB (InnoDB) | Cancelled | Canceled |
| Apache, php5-mod, php5-mysql, MariaDB (MyISAM) | mysqld 72.8%, apache 36.2% | mysqld 4.2%, apache 0.9% |
| Nginx, php5-fpm, php5-mysql, MariaDB (MyISAM) | mysqld 74.4%, php5-fpm 35.5%, nginx 0.7% | mysqld 4.0%, php5-fpm 1.4%, nginx 0.2% |
| Apache, php5-mod, php5-mysql, MariaDB (MyISAM,JSON) | mysqld 73.1%, apache 35.2% | mysqld 4.4%, apache 1.7% |
| Nginx, php5-fpm, php5-mysql, MariaDB (MyISAM,JSON) | mysqld 73.8%, php5-fpm 33.6%, nginx 0.7% | mysqld 4.5%, php5-fpm 1.4%, nginx 0.2% |
PostgreSQL showed similar or a little less CPU and memory usage than MariaDB.
| Setup | 1.000 | 10.000 | 100.000 |
|---|---|---|---|
| Apache, php5-mod, php5-mongo, MongoDB (2.4.10) | 1.443 s. | 13.532 s. | 137.022 s. |
| Nginx, php5-fpm, php5-mongo, MongoDB (2.4.10) | 1.235 s. | 12.483 s. | 125.143 s. |
| Apache, php5-mod, php5-mongo, MongoDB (3.0.6) | 1.383 s. | 10.920 s. | 109.417 s. |
| Nginx, php5-fpm, php5-mongo, MongoDB (3.0.6) | 1.034 s. | 10.396 s. | 102.754 s. |
| Apache, php5-mod, MariaDB (InnoDB) | 64.030 s. | Canceled | Canceled |
| Nginx, php5-fpm, MariaDB (InnoDB) | 64.030 s. | Canceled | Canceled |
| Apache, php5-mod, MariaDB (MyISAM) | 0.910 s. | 9.567 s. | 94.838 s. |
| Nginx, php5-fpm, MariaDB (MyISAM) | 0.858 s. | 8.860 s. | 89.980 s. |
| Apache, php5-mod, MariaDB (MyISAM,JSON) | 1.067 s. | 8.134 s. | 104.966 s. |
| Nginx, php5-fpm, MariaDB (MyISAM,JSON) | 0.693 s. | 10.318 s. | 101.217 s. |
| Apache, php5-mod, php5-pgsql, PostgreSQL | 59.968 s. | Cancelled | Cancelled |
| Nginx, php5-fpm, php5-pgsql, PostgreSQL | 58.852 s. | 601.694 s. | Cancelled |
In this setup I skipped all selects and updates and only inserted a new customer in order to test pure write speed.
I wanted to compare pure write speeds between MariaDB, MongoDB and PostgreSQL in a single table/collection setup.
| Setup | 1.000 | 10.000 | 100.000 |
|---|---|---|---|
| Apache, php5-mod, php5-mongo, MongoDB (2.4.10) | 0.774 s. | 7.776 s. | 78.623 s. |
| Nginx, php5-fpm, php5-mongo, MongoDB (2.4.10) | 0.779 s. | 7.562 s. | 76.083 s. |
| Apache, php5-mod, php5-mongo, MongoDB (3.0.6) | 0.709 s. | 6.596 s. | 63.519 s. |
| Nginx, php5-fpm, php5-mongo, MongoDB (3.0.6) | 0.644 s. | 6.274 s. | 62.844 s. |
| Apache, php5-mod, MariaDB (InnoDB) | 62.381 s. | Cancelled | Cancelled |
| Nginx, php5-fpm, MariaDB (InnoDB) | 61.240 s. | 622.151 | Cancelled |
| Apache, php5-mod, MariaDB (MyISAM) | 0.358 s. | 3.823 s. | 37.284 s. |
| Nginx, php5-fpm, MariaDB (MyISAM) | 0.372 s. | 3.487 s. | 37.657 s. |
| Apache, php5-mod, MariaDB (MyISAM,JSON) | 0.368 s. | 3.605 s. | 39.236 s. |
| Nginx, php5-fpm, MariaDB (MyISAM,JSON) | 0.401 s. | 3.923 s. | 38.875 s. |
| Apache, php5-mod, php5-pgsql, PostgreSQL | 46.999 | Cancelled | Cancelled |
| Nginx, php5-fpm, php5-pgsql, PostgreSQL | 46.723 s. | 601.694 s. | Cancelled |
| Apache, php5-mod, php5-sqlite, Sqlite3 | 161.858 | Cancelled | Cancelled |
Sqlite3 was just for fun :)
Changed the "write concern" on MongoDB 3.0.6 to "0", but that didn't improve the write speeds by much:
| Apache, php5-mod, php5-mongo, MongoDB (3.0.6) | 0.545 s. | 6.063 s. | 59.964 s. | | Nginx, php5-fpm, php5-mongo, MongoDB (3.0.6) | 0.0591 s. | 5.927 s. | 59.210 s. |
Choosing between MongoDB and other RDBMS should NEVER have anything to do with speed, but the fact is that when it comes to data storage MongoDB isn't as fast as advertised. However, much improvement has been made from 2.4.x to 3.0.x.
The choice between MongoDB and the other RDBMS should be made upon whether or not you need relationships between the data and upon the other major differences between MongoDB and the others.
I however would never use MongoDB for important data! MongoDB is best suited for data that isn't important like a logs, web scraping (search engine) and similar stuff.
Here is a very good example of making the mistake of not understanding your needs before you select the right tool for the job: http://www.sarahmei.com/blog/2013/11/11/why-you-should-never-use-mongodb/
The title should not be, "Why you should never use MongoDB", but rather, "Knowing when not to use MongoDB".
Researching and usecase experience is very important!
http://ryanangilly.com/post/1091884265/3-reasons-to-use-mongodb
https://blog.serverdensity.com/mongodb-benchmarks/
https://aphyr.com/posts/322-call-me-maybe-mongodb-stale-reads
MongoDB is best suited for pure documents with no relations to other documents!
MySQL has made some progress in the standards area, but the philosophy behind MySQL is that they’ll support non-standard extensions if the customers need them. With PostgreSQL, the standards were built into the platform from the start, whereas MySQL added them later.
PostgreSQL has one storage engine. MySQL has nine, but only two of those really matter to most users: MyISAM and InnoDB.
MyISAM, and the improved Aria (MariaDB), is build for speed, but they lack transactions. InnoDB has transactions. Both MySQL’s InnoDB and PostgreSQL are fully ACID compliant, but PostgreSQL performed much better in this setup.
I did try various performance enhancements and tried tailoring the configurations for MariDB, MongoDB and PostgreSQL to match my box, but they didn't show much improvement.
I think that the performance of PostgreSQL over MariaDB InnoDB is impressive. I did another benchmark some years ago before we started another project. Back then we needed serious speed with lots of inserts, but we ended up using MariaDB in connection with Sphinx because MariaDB was much faster on inserts than PostgreSQL. Clearly PostgreSQL has been improved.
PostgreSQL has always been strict about ensuring data is valid before inserting or updating it, and it will throw an error if you try to insert a string into an integer field, whereas with MySQL/MariaDB, you need to set the server to a strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES), otherwise adjusted values will be inserted or updated.
In this setup I didn't use strict SQL mode, but PostgreSQL still outperform MariaDB with InnoDB tables.
The same script has been used with PostgreSQL with the change to the PDO connection also using TCP and added support for transactions.
<?php
$start_time = microtime(true);
date_default_timezone_set('Europe/Copenhagen');
for ($i = 0; $i < 1000; $i++) {
try
{
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '1234',
array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br>";
die();
}
echo '<p>STEP 1</p>';
$sql = 'SELECT * FROM inventory WHERE iso3166 = :country_iso_code';
$country_iso_code = 'DK';
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':country_iso_code', $country_iso_code, PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo '<pre>';
print_r($result);
echo '</pre>';
echo '<p>STEP 2</p>';
$sql = 'UPDATE inventory SET qty = qty - 1 WHERE id = :id';
$id = 4;
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
echo '<p>STEP 3</p>';
$sql = 'INSERT INTO customer
(first_name, last_name, street_line_1, street_line_2, zip, country, phone, email)
VALUES
(:first_name, :last_name, :street_line_1, :street_line_2, :zip, :country, :phone, :email)';
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':first_name', 'Foo', PDO::PARAM_STR);
$stmt->bindValue(':last_name', 'Bar', PDO::PARAM_STR);
$stmt->bindValue(':street_line_1', 'Foobar 23', PDO::PARAM_STR);
$stmt->bindValue(':street_line_2', '', PDO::PARAM_STR);
$stmt->bindValue(':zip', '2400', PDO::PARAM_STR);
$stmt->bindValue(':country', 'Sweden', PDO::PARAM_STR);
$stmt->bindValue(':phone', '+4612345678', PDO::PARAM_STR);
$stmt->bindValue(':email', 'foo@bar.com', PDO::PARAM_STR);
$stmt->execute();
$pdo = null;
echo '<p>Done.</p>';
}
$end_time = microtime(true);
echo '<p>Script executed in: <br>'
. round( (($end_time - $start_time)/60), 3) . ' minutes. <br>'
. round( ($end_time - $start_time), 3) . ' seconds. <br>'
. round( (($end_time - $start_time)*1000), 3) . ' milliseconds. <br>'
. round( (($end_time - $start_time)*1000000), 3) . ' microseconds.</p>';
<?php
$start_time = microtime(true);
date_default_timezone_set('Europe/Copenhagen');
for ($i = 0; $i < 1000; $i++) {
try
{
$m = new MongoClient(); // connect
}
catch ( MongoConnectionException $e )
{
echo '<p>Could not connect to mongodb!</p>';
exit(1);
}
echo '<p>STEP 1</p>';
$db = $m->selectDB("test");
$collection = $db->inventory;
$query = array(
'iso3166' => (string)'DK'
);
$cursor = $collection->find($query);
foreach($cursor as $result) {
echo '<pre>';
print_r($result);
echo '</pre>';
}
echo '<p>STEP 2</p>';
$db = $m->selectDB("test");
$collection = $db->inventory;
$id = new MongoId('561867e84b8fae68f09074da');
$cursor = $collection->update(
array('_id' => $id),
array('$inc' => array(
'qty' => -1
)
)
);
echo '<p>STEP 3</p>';
$db = $m->selectDB("test");
$collection = $db->customer;
$collection->insert(array(
'first_name' => 'Foo',
'last_name' => 'Bar',
'street_line_1' => 'Foobar 23',
'street_line_2' => '',
'zip' => '2400',
'country' => 'Sweden',
'phone' => '+4612345678',
'email' => 'foo@bar.com',
'date_time' => date('Y-m-d h:i:s', time())
)
);
$pdo = null;
echo '<p>Done.</p>';
}
$end_time = microtime(true);
echo '<p>Script executed in: <br>'
. round( (($end_time - $start_time)/60), 3) . ' minutes. <br>'
. round( ($end_time - $start_time), 3) . ' seconds. <br>'
. round( (($end_time - $start_time)*1000), 3) . ' milliseconds. <br>'
. round( (($end_time - $start_time)*1000000), 3) . ' microseconds.</p>';