Moving from MySQL to MongoDB for my website backend

I finished migrating my blog/personal site over this afternoon and I really like working with Mongo so far (with PHP). NoSQL is really nice to work with, tables are a thing of the past for me!


Anyone else used it?

MongoDb is epic! It is so fast. For example; adding 1.000.000 new rows in a database, takes about 4 secs. Try that with mysql lol.

Im not using MongoDb on any of my websites yet, but planning to use it for some future websites.

Quote:

---

Originally Posted by Flowan

MongoDb is epic! It is so fast. For example; adding 1.000.000 new rows in a database, takes about 4 secs. Try that with mysql lol.

Im not using MongoDb on any of my websites yet, but planning to use it for some future websites.

---

In PHP/mysql it took about 07:50 (about 8 minutes) with a prepared statement without even binding data each time!


I truly expected better results from this,

PHP Code:

---

for ($i = 0; $i < 1000000; $i++)
{
    $stmt->execute();
} 


---

I'm interested.


The 1000000 loop (empty) by itself in PHP takes about 0-2 seconds.. let's call it 1 second, which is totally insignificant...

Quote:

---

Originally Posted by s-p-n

In PHP/mysql it took about 07:50 (about 8 minutes) with a prepared statement without even binding data each time!


I truly expected better results from this,

PHP Code:

---

for ($i = 0; $i < 1000000; $i++)
{
    $stmt->execute();
} 


---

I'm interested.


The 1000000 loop (empty) by itself in PHP takes about 0-2 seconds.. let's call it 1 second, which is totally insignificant...

---

Because that's the wrong way to do it. If you insert 100 rows at a time, it'll be roughly 100 times faster (it's easier to add 100 rows at once than it is to add 1 row 100 times, since a lot of that overhead is shared).

If this is just for a personal website, you don't need MongoDB nor should you be using it. Contrary to popular belief, a hammer is not always the right tool for the job.

I love mengodb. I used it for few small backend projects and it's doing amazing job selecting, updating, etc.

Definitely good move.

Quote:

---

Originally Posted by jMerliN

Because that's the wrong way to do it. If you insert 100 rows at a time, it'll be roughly 100 times faster (it's easier to add 100 rows at once than it is to add 1 row 100 times, since a lot of that overhead is shared).

If this is just for a personal website, you don't need MongoDB nor should you be using it. Contrary to popular belief, a hammer is not always the right tool for the job.

---

I know that it's not 'required', but it's pleasurable to use, does the job fine, and I wanted to learn the technology.

Quote:

---

Originally Posted by jMerliN

Because that's the wrong way to do it. If you insert 100 rows at a time, it'll be roughly 100 times faster (it's easier to add 100 rows at once than it is to add 1 row 100 times, since a lot of that overhead is shared).

If this is just for a personal website, you don't need MongoDB nor should you be using it. Contrary to popular belief, a hammer is not always the right tool for the job.

---

Lol whoops, real benchmark:

Code:

---

Done in 00:23 (minutes:seconds)

---

using:

PHP Code:

---

for ($i = 0; $i < 1000000; $i++)
{
    $data .= $i;
    $stmt->bind_param('s', $data);
}
$stmt->execute(); 


---

Guy si am writing 160 variables to a Mysql DB within like 10ms?

MongoDB is supposed to be good with Node.js- if anyone is looking into that.

Quote:

---

Originally Posted by s-p-n

MongoDB is supposed to be good with Node.js- if anyone is looking into that.

---

MongoDB is for a different classification of data storage.

Mongo (and other NoSQL DBs) store data in 'collections' rather than 'tables'.

I guess the fundamental difference is that 'collections' are a group of 'things', and each group doesn't need to be the same format, have the same number of things or be in any way related. There are no tables.

I've been looking a -lot- into NoSQL databases lately, what they are, how they work, and the advantages and disadvantages of using one over a SQL database.

Without going too much into detail, the general consensus I get is that they are the "shiny new toy".

Why use NoSQL over MySQL? Because it's faster, right? The fact of the matter is that MySQL can be fast and efficient. It depends a lot on how your database is built, as well as making sure that your environment, i.e. server, is set up as to allow the database server to run as efficiently as possible. A lot of the times, one or both of these factors are not true.

Then there is the factor of NoSQL being simpler. The impression I got from what I read is that using NoSQL (efficiently) in an enterprise-esque manner is more difficult than using MySQL.

That said, NoSQL is built to scale very well across a cluster, or numerous servers, even if those servers have less than exceptional quality hardware.

After a lot of reading, I am going to continue using MySQL for an application I am building, which requires nothing less than 'enterprise-quality' efficiency.

There is a lot to know about NoSQL. I suggest anyone interested in it read up on it. You need to learn what it is, why it exists, what are the advantages and disadvantages of using it in general, as well as over SQL databases. There are a lot of informative and diverse opinions and articles about the two.

Also, Oracle has a NoSQL database out now (soon after they slandered it in comparison to MySQL, no doubt). Oracle NoSQL Database Technical Overview

Disclaimer: I am not an expert in either SQL or NoSQL. Hell, most people aren't an expert in NoSQL. Most of what I have said above comes from the memory of the last several articles I read about NoSQL over the past week. Don't hurt me; thanks.

It depends on the type of data you're storing, the magnitude of that data, and what you need to do with that data.

Quote:

---

Originally Posted by jMerliN

It depends on the type of data you're storing, the magnitude of that data, and what you need to do with that data.

---

Quite. But that doesn't deter from the fact that many peoples and small businesses and the like are using it more recently almost purely because it is a 'new shiny toy'. MySQL would work just as well, if not moreso, for many people and apps that are currently migrated to or in the process of migrating to MangoDB or similar.

There are a noticeable amount of businesses in my area that are hiring people who, amongst everything else, preferably have experience with NoSQL or/and MangoDB. I don't think it'll last, once people realize MySQL works fine in most scenarios that, at the very least, they will be facing.

Quote:

---

Originally Posted by s-p-n

Lol whoops, real benchmark:

Code:

---

Done in 00:23 (minutes:seconds)

---

using:

PHP Code:

---

for ($i = 0; $i < 1000000; $i++)
{
    $data .= $i;
    $stmt->bind_param('s', $data);
}
$stmt->execute(); 


---

---

You're still doing it wrong. Using PHP(or any backend web language) in the benchmark completely ruins the results due to the various drivers that can alter performance.

Proper procedure(in TSQL):

Code:

---

INSERT [dbo].[test2]([nVar]) SELECT [nVar] FROM [dbo].[test1]

---

The "test1" table had 1,000,000 rows of integers(could've done tinyint for even faster) and test2 had 0 rows.

Results:

Code:

---

(1000000 row(s) affected)

---

in 2 seconds.

Now note this is on a simple quad core(2.00ghz xeon) with 4GB RAM. This is a horrid benchmark test, but you said it took MongoDB 4seconds. If you want a slightly better test, try one billion rows of integers. Either way, an insertion query is hardly a measure of the overall performance of a database engine.

Twitter tried for years to transfer to a NoSQL database engine, and eventually gave up. Facebook only uses Cassandra for their message searching function, and I haven't heard of any other big companies using a NoSQL database. NoSQL database engines are still far too unstable and unpredictable to use, regardless of their better benchmarks and decentralized data storage. From what I've heard, they're also quite annoying to maintain.

Quote:

---

Originally Posted by xLethal

You're still doing it wrong. Using PHP(or any backend web language) in the benchmark completely ruins the results due to the various drivers that can alter performance.

Proper procedure(in TSQL):

Code:

---

INSERT [dbo].[test2]([nVar]) SELECT [nVar] FROM [dbo].[test1]

---

The "test1" table had 1,000,000 rows of integers(could've done tinyint for even faster) and test2 had 0 rows.

Results:

Code:

---

(1000000 row(s) affected)

---

in 2 seconds.

Now note this is on a simple quad core(2.00ghz xeon) with 4GB RAM. This is a horrid benchmark test, but you said it took MongoDB 4seconds. If you want a slightly better test, try one billion rows of integers. Either way, an insertion query is hardly a measure of the overall performance of a database engine.

Twitter tried for years to transfer to a NoSQL database engine, and eventually gave up. Facebook only uses Cassandra for their message searching function, and I haven't heard of any other big companies using a NoSQL database. NoSQL database engines are still far too unstable and unpredictable to use, regardless of their better benchmarks and decentralized data storage. From what I've heard, they're also quite annoying to maintain.

---

Are you on drugs?

The benchmark was for PHP coders

Quote:

---

I finished migrating my blog/personal site over this afternoon and I really like working with Mongo so far (with PHP). NoSQL is really nice to work with, tables are a thing of the past for me!

---

And the test given was adding 1,000,000 new rows. How is adding million new rows in MySQL with PHP to compare MongoDB adding a million rows with PHP wrong other than the fact I didn't use the exact same server and run the test 100 times?

Quote:

---

MongoDb is epic! It is so fast. For example; adding 1.000.000 new rows in a database, takes about 4 secs. Try that with mysql lol.

Im not using MongoDb on any of my websites yet, but planning to use it for some future websites.

---

I never claimed my benchmark was the almighty god of benchmarks, but it's distasteful that someone gave a mongo DB benchmark, said try this with MySQL, so I did, and he was right- it took longer.

If I cared enough I'd make a benchmark for Mongo and PHP too.


Why would I care about a benchmark for SQL in the command line when I use a programming language in real life?

Fill in the holes to your arguments before you say I'm doing something wrong. Your benchmark wasn't even for any of the right databases or languages in this discussion.


Who cares if the DB engine is good if the driver for my programming language of choice ruins performance? Programmers want the best database for their situation, not one that jumps ahead of the game in the command line but fails in real life. So I test for real life. Sue me.

One of the most notable downfalls of NoSQL is the lack of consistency. What one person sees may very well not be what a different person sees, despite the page/info/database being exactly the same. Because of the way information goes from node to node and server to server via NoSQL, you can not expect consistency - in fact, you should expect inconsistency. The only NoSQL database that even tries to claim ACID-compliance is Oracle's NoSQL database, and even that is questionable.

CouchDB has ACID semantics via MVCC.

Again, it entirely depends on the content of what you need in the DB as to whether or not a NoSQL database is better than a SQL database. And SQL databases are adding NoSQL features in recent versions, too.

Quote:

---

Originally Posted by s-p-n

Are you on drugs?

The benchmark was for PHP coders


And the test given was adding 1,000,000 new rows. How is adding million new rows in MySQL with PHP to compare MongoDB adding a million rows with PHP wrong other than the fact I didn't use the exact same server and run the test 100 times?


I never claimed my benchmark was the almighty god of benchmarks, but it's distasteful that someone gave a mongo DB benchmark, said try this with MySQL, so I did, and he was right- it took longer.

If I cared enough I'd make a benchmark for Mongo and PHP too.


Why would I care about a benchmark for SQL in the command line when I use a programming language in real life?

Fill in the holes to your arguments before you say I'm doing something wrong. Your benchmark wasn't even for any of the right databases or languages in this discussion.


Who cares if the DB engine is good if the driver for my programming language of choice ruins performance? Programmers want the best database for their situation, not one that jumps ahead of the game in the command line but fails in real life. So I test for real life. Sue me.

---

Seriously? I'm sorry, but you're not a coder in PHP or any language. Your little prepared statement example is shit because it does 1,000,000 far calls(on execution level) rather than ONE far call. I can do the exact same example in PHP-MSSQL and I would get the exact same result.

Code:

---

mssql_query("INSERT [dbo].[test2]([nVar]) SELECT [nVar] FROM [dbo].[test1]");

---

You should go back to school if you seriously would use the method you provided in the benchmark for ANYTHING.

The person that posted a MongoDB benchmark didn't say anything about PHP, so I'm left to assume he's testing the database engine itself, because testing with PHP completely ruins the benchmark regardless of intent. The only thing I would test with PHP in regards to databases is the PHP driver performance, which is completely irrelevant in most cases.

The reason I used MSSQL is because it's an SQL database, I'm not trying to compare MySQL to MongoDB, I'm saying the examples provided in this thread are shit and don't reflect on the actual database engine performance in the slightest.

You also should clean up your language, PHP isn't "real life", it's a very high level coding language designed for websites, not database benchmarks. PHP coding might be real life for you, but not everyone is a freelance outsourced web developer.

Quote:

---

Originally Posted by xLethal

Seriously? I'm sorry, but you're not a coder in PHP or any language. Your little prepared statement example is shit because it does 1,000,000 far calls(on execution level) rather than ONE far call. I can do the exact same example in PHP-MSSQL and I would get the exact same result.

Code:

---

mssql_query("INSERT [dbo].[test2]([nVar]) SELECT [nVar] FROM [dbo].[test1]");

---

You should go back to school if you seriously would use the method you provided in the benchmark for ANYTHING.

The person that posted a MongoDB benchmark didn't say anything about PHP, so I'm left to assume he's testing the database engine itself, because testing with PHP completely ruins the benchmark regardless of intent. The only thing I would test with PHP in regards to databases is the PHP driver performance, which is completely irrelevant in most cases.

The reason I used MSSQL is because it's an SQL database, I'm not trying to compare MySQL to MongoDB, I'm saying the examples provided in this thread are shit and don't reflect on the actual database engine performance in the slightest.

You also should clean up your language, PHP isn't "real life", it's a very high level coding language designed for websites, not database benchmarks. PHP coding might be real life for you, but not everyone is a freelance outsourced web developer.

---

You forget that this topic is about using PHP for MongoDB, not the command line. I'm not trying to test the database engines, I'm trying to test how it runs with PHP.

In "real life" coder's don't wait for requests and route them all themselves using the command line. Most people use these things called "http servers."

Popular "http servers" include this programming language called "PHP 5.3" for web developers to build the back-end to their web sites. Don't ask me why they choose such a horrible scripting language- they just do.


Now that you're caught up to speed. The OP posted about switching MySQL DB with MongoDB. The application layer he'll be using is that scripting language, PHP!

Get it?

That's real life.

Running a benchmark on the database daemon itself is a waste of time as it doesn't explain how the database will react in a real-life environment.

Get it? :ehh: I actually meant to compare PHP drivers. So you're arguing a moot point simply for the sake of arguing with me. Quit it.

Quote:

---

Originally Posted by s-p-n

You forget that this topic is about using PHP for MongoDB, not the command line. I'm not trying to test the database engines, I'm trying to test how it runs with PHP.

In "real life" coder's don't wait for requests and route them all themselves using the command line. Most people use these things called "http servers."

---

I stopped reading here, you're clearly not a coder and have no idea what the hell you're talking about. I told you I used PHP and it gave the exact same results. You just used PHP incorrectly. The PHP driver should never be a bottleneck anyways even for the crappiest database engines as long as you make the requests properly.

For you, "real life coding" is freelancing for people that get less than five requests per second on their websites. You shouldn't worry about performance of any driver or any database engine at all in this case. You should worry about your own code because its clearly not up to par.

Quote:

---

Originally Posted by xLethal

I stopped reading here, you're clearly not a coder and have no idea what the hell you're talking about. I told you I used PHP and it gave the exact same results. You just used PHP incorrectly. The PHP driver should never be a bottleneck anyways even for the crappiest database engines as long as you make the requests properly.

For you, "real life coding" is freelancing for people that get less than five requests per second on their websites. You shouldn't worry about performance of any driver or any database engine at all in this case. You should worry about your own code because its clearly not up to par.

---

Lol you can go on thinking that your entire life and it won't phase me a bit. For your convenience, the second php snippet sends 1 prepared query (2 requests) to MySQL. If your big problem with this is preparing queries, you're missing the point.

Most queries I send from PHP to MySQL contain data retrieved from the user. Therefore most queries I send are prepared first. PHP sends the query once, then it sends all the data second. Sure, if you sent 1 request including the query without the mysqli library and heck, even without PHP's for loop- it will be quite a bit faster. I never claimed prepared statements could scale up to par with multiqueries on terms of efficiency. However, I do believe an application using prepared statements has a better philosophy on security than those that clunk all the user data into the same text string and send it hoping it works.

 trying to pry me open
You are arguing the same thing over and over, and I am not disputing your claims, I'm just explaining to you that I'm doing it different. You aren't even giving valid points. I don't do freelance..? I don't even use PHP in the projects I'm currently involved in. So you're completely ignorant in your arguments. You have no idea who I am or how I code, you're just a troll.

That said, the way I insert MySQL rows with PHP- if I do- is often with prepared statements- so that's what I tested. You thought I was trying to find the most efficient way? That's blasphemy and I'm very insulted if you think that I thought prepared statements were efficient. That down-right hurts my feelings- in fact- I'm crying now. Thanks... Please do enlighten me on your wisdom of the TSQL- as we all know PHP5 and MySQLi is so bad and rotten, I'd like to see examples of what you use? (No sarcasm intended.. )

And lastly, (didn't think I'd get to this?) you can quit your script-kiddy attitude when you're talking to me. It doesn't impress me, nor does it make you sound smart. Rather, it makes you sound arrogant and ignorant. Please try to be polite and keep this light-hearted as you're hurting yourself, and giving me a headache.

Quote:

---

Originally Posted by s-p-n

Lol you can go on thinking that your entire life and it won't phase me a bit. For your convenience, the second php snippet sends 1 prepared query (2 requests) to MySQL. If your big problem with this is preparing queries, you're missing the point.

Most queries I send from PHP to MySQL contain data retrieved from the user. Therefore most queries I send are prepared first. PHP sends the query once, then it sends all the data second. Sure, if you sent 1 request including the query without the mysqli library and heck, even without PHP's for loop- it will be quite a bit faster. I never claimed prepared statements could scale up to par with multiqueries on terms of efficiency. However, I do believe an application using prepared statements has a better philosophy on security than those that clunk all the user data into the same text string and send it hoping it works.

 trying to pry me open
You are arguing the same thing over and over, and I am not disputing your claims, I'm just explaining to you that I'm doing it different. You aren't even giving valid points. I don't do freelance..? I don't even use PHP in the projects I'm currently involved in. So you're completely ignorant in your arguments. You have no idea who I am or how I code, you're just a troll.

That said, the way I insert MySQL rows with PHP- if I do- is often with prepared statements- so that's what I tested. You thought I was trying to find the most efficient way? That's blasphemy and I'm very insulted if you think that I thought prepared statements were efficient. That down-right hurts my feelings- in fact- I'm crying now. Thanks... Please do enlighten me on your wisdom of the TSQL- as we all know PHP5 and MySQLi is so bad and rotten, I'd like to see examples of what you use? (No sarcasm intended.. )

And lastly, (didn't think I'd get to this?) you can quit your script-kiddy attitude when you're talking to me. It doesn't impress me, nor does it make you sound smart. Rather, it makes you sound arrogant and ignorant. Please try to be polite and keep this light-hearted as you're hurting yourself, and giving me a headache.

---

Sigh, you can do the exact same query with prepared statements and it should give very similar results to a direct query. Binding 1million variables makes 1million calls to the MySQL/MySQLi driver, which is why the performance fails.

I'm saying you freelance because I assume with your activity on the coding forums, that you actually make some profit from development. I don't think you work for a corporation, so I naturally assumed you're a freelancer as many PHP-MySQL enthusiasts are.

Also, I don't think you understand what a "script-kiddy" is, they're hackers that use other people's exploit scripts without understanding them. The reason I'm acting aggressive is because your original response offended me.

Quote:

---

Are you on drugs?

The benchmark was for PHP coders

Why would I care about a benchmark for SQL in the command line when I use a programming language in real life?

Fill in the holes to your arguments before you say I'm doing something wrong. Your benchmark wasn't even for any of the right databases or languages in this discussion.

Who cares if the DB engine is good if the driver for my programming language of choice ruins performance? Programmers want the best database for their situation, not one that jumps ahead of the game in the command line but fails in real life. So I test for real life. Sue me.

---

This statement is so incredibly ignorant and bias, it makes me ashamed to even code. Let's see what's wrong with each of these statements that make me cringe.

Quote:

---

Are you on drugs?

---

Yes actually, I'm on a variety of hallucinogens quite obviously.

Quote:

---

The benchmark was for PHP coders

---

And PHP coders are affected by the database performance itself even more than the performance of the PHP-to-database driver.

Quote:

---

Fill in the holes to your arguments before you say I'm doing something wrong. Your benchmark wasn't even for any of the right databases or languages in this discussion.

---

I offered my benchmark to add some variety to the discussion. Just because the OP asked about MySQL and MongoDB specifically, it's still very relevant to compare another SQL database engine. Also, MSSQL and MySQL are a lot more similar than MongoDB and MySQL. There are no holes in my argument.

Quote:

---

Who cares if the DB engine is good if the driver for my programming language of choice ruins performance? Programmers want the best database for their situation, not one that jumps ahead of the game in the command line but fails in real life. So I test for real life. Sue me.

---

It's pretty hard to fuck up a PHP-to-database driver honestly. They're all going to preform similarly regardless of what you're doing. The database engine itself is what will cause overhead. If you want to test the PHP driver, you'll need to make a benchmark of how many queries/binds/fetches(driver requests) each driver can make within a certain period, then subtract the time of the actual query execution(because the query execution doesn't rely on the PHP driver, it relies on the database engine).

I feel that there is not only more hostility coming out of this conversation than need be, but also some possible misunderstandings, perhaps due to ambiguous terminology, or just misunderstandings on either the writer or reader's part.

First, let me say that I will agree that s-p-n's idea of benchmarking, even for what I assume he intended, was not very beneficial or helpful towards any statistics.

I think that I understand what s-p-n meant by 'real life' coding, albeit he did not seem to state it very well.

In the end, what matters is the experience that persons have upon viewing your website, right? When it comes to databases, we are, in the end, attempting to improve the efficiency of the application. One of the main reasons I see that moving from one database engine to another may be beneficial, developer learning curves put aside, is loading time.

If you correctly, for testing purposes, adapt two different databases to work with your application, and then load your website on a query-intensive page, you are looking for what puts less stress on your server, and thus loads faster for the end user. I believe that s-p-n's remarks of "real world" applications is indeed the loading time. Which is faster? This takes into account both the application language's driver and, even more importantly, the database engine itself.

Nonetheless, you obviously can not benchmark database A versus database B if you are benchmarking database A on computer C, and database B on computer D. It completely defeats the purpose of benchmarking, and makes any results you get meaningless. Now, if you test both databases or database engines on the same machine, you have more accurate results - at least in terms of percentage difference.

Then, as I said previously, there are different ways to benchmark anything - a database engine included - some which will produce more correct results than others, in terms of benchmark percentage differences (ex. One test shows that database A is 25% faster, but a more correct method of benchmarking shows that database A is only 16% faster).

Then there is the valid ideology that a correctly structured database will be faster than a poorly structured database, even with the same server and database engine. As I said in a previous post of mine, this is one reason why some moves from RDMS' to NoSQL databases are unnecessary, as you would do perfectly fine with a well structured SQL database.


/3cents

Quote:

---

Originally Posted by xLethal

The reason I'm acting aggressive is because your original response offended me.

---

Sowwy :(:

This is why I felt the way you were telling me I was doing it wrong was ridiculous:

Quote:

---

You're still doing it wrong. Using PHP(or any backend web language) in the benchmark completely ruins the results due to the various drivers that can alter performance.

Proper procedure(in TSQL):

---

I'm thinking, why is he using a completely different database engine without the scripting language I'm trying to test? And how is that more accurately testing PHP + MySQL vs PHP + Mongo?

As in, the fact I even used PHP was "wrong" yet the thread is about switching from MySQL to Mongo for use with PHP. Shouldn't the benchmark be in the language it's for? Sure, I agree if you want to strictly test the database u should test the database engine. There are plenty of other people doing that because it makes sense- it's a no brainer. But what that doesn't do is test how everything works together- which is very hard to do.

I wanted to see what I could get out of MySQL and PHP using prepared statements, so I did. I never claimed that's the fastest MySQL could possibly be, or even with PHP. Prepared Statements are the most common way I insert rows, so that's the way I tested. I even know that I didn't help anyone at all except myself. If anyone wants to try it, they can easily copy the code and compare MySQL prepared statements with MongoDB which sends data very similarly- afaik key-value pairs. But you acted as though I was born yesterday simply because I posted PHP.. I guess you kind of hit a nerve in me that made me treat you like a troll... and FYI I'm not a PHP enthusiast. I'm merely a guy who happens to know PHP. Anyway, squash it.