Lots of Indexes - MySQL vs MongoDB - Migrating

Question

So, I've never used MongoDB, I just read a lot about it and I think it's going to be good for my project. Also, I do not have lots of experience with MySQL and to be even more honest I have no clue of what I'm about to ask.

Scenario:

MySQL table profile:

• id = [pk, auto_increment, smallint]
• user_id = [pk, fk, varchar]
• category_id = [pk, fk, smallint]
• role_id = [pk, fk, tinyint]
• country_id = [pk, fk, smallint]
• state_id = [pk, fk, smallint]
• legal_document = [pk, varchar, ?unique]
• name = [pk, varchar, ?unique]
• type = [pk, boolean]
• last_activity = [pk, date]

All the fk you see there are MySQL tables of course. Then I'm thinking to use MongoDB to store the profile info, profile_info collection should contain documents like:

{
  '_id' : 1 (profile_id),
  'address': 'Some street in some state of some country :P',
  'phone': [5555555, 5555555],
  'email': '[email protected]',
   etc...
}

I'm planning to use mongodb because my project needs to be public ASAP and we may add several new profile info and other things and I do not want to do alter table and migrate from tools with lots of rows.

With that said, we may have to add a few more pks to the MySQL table, so I was thinking of migrating all the project to MongoDB and I do not know if that is a good move.

Questions:

• It's better to keep the pks in MySQL and trivial info in MongoDB, or would it be fine if I move all to MongoDB?

• May MongoDB be faster if I just migrate the whole project but I keep the structure like that? I mean like having profile and profile_info collections instead of just profile.

• I'm worried about how many resources MongoDB could use for a 'table/collection' with that many indexes, I want to keep disk space and ram at minimum use. Is there a critical difference between MySQL and MongoDB?

PS: SSDs are going to be used in the system.

PS II: All tables are just planned, nothing is written yet. I'm a bit of plan very ahead person so please be patient with me.

Answer 1

Given your description I strongly suggest against using MongoDB. Not because it would necessarily a bad choice (although I believe it is in your case for reasons other than pure technical ones).

Here are the points that caught my eye.

Data modeling

Trying to use MongoDB with relational data model with no adaptations almost always leads to tears and misery except for the most trivial use cases. And that is the better end of the story. The worse end is loosing money, potentially big time.

The reason for this is that with SQL, you identify your entities and their attributes and relations and then bang your head against the wall for a few hours to get your upper left above and beyond JOINS right to get your questions derived from your use cases answered. All while avoiding data redundancy like the devil holy water.

Data modeling with MongoDB works different. You identify the use cases and the questions derived from them and model your data in a way so that those questions can be answered in the most efficient way.

Since this is a bit abstract, let me give you an

Example

Let us assume you have a web application called "chirper", with users doing chirps. With SQL you would now model your data, coming up with something like a user table and a chirp table.

The first use case you encounter is that you want the latest 10 chirps to be displayed on the applications home page, together with the username of the chirper. With SQL it is easy enough, you do a join on chirps and users, sort the result descending by data and limit to 10 records.

With MongoDB, you'd have a careful look at what you need. For various reasons you do not want to embed the chirps into the user collection. But since you do need to relate the chirps to a user anyway, you decide to do so by username and come up with a "schema" for your chirps collection like this:

{
  _id: ObjectId("570b87a56931b8f21a8bf25c"),
  user: "jdoe",
  date: ISODate("2016-04-11T11:17:08.415Z"),
  text: "Chirp!!!"
}

and, miraculously enough, all you have to do is to do a

db.chirps.find({}).sort({date:-1}).limit(10)

which gives you the same result without a JOINS at the expense of relatively cheap disk space.

Resource limitation

I want to keep disk space and ram at minimum use

MongoDB is a lot, but surely not resource friendly. It was never meant to be a replacement for MySQL – which by the way was specifically designed to be a relatively lightweight general purpose RDBMS. I strongly advice against running MongoDB and anything else on the same server for production purposes. The reasons are manifold, but here are the most important ones:

1. MongoDB will take up to 85% - 90% of your available, physical RAM. This is because the working set (the indices and a subset of the data) are kept in RAM.
2. Depending on the storage engine and its configuration you use for MongoDB, even CPU utilization can be considerable.
3. A heavily loaded MongoDB needs a lot of disk IO. I have seen systems with SSDs in a RAID0 in which the IO rate was at its limit.

Leaving out the petty details for the moment, this would mean that during situations where you do not want it the most (your application is taking off and has a lot of users), MongoDB would battle with the other parts of the application over resources.

Do not get me wrong: MongoDB is not a resource eating monster per se. But if it does what it is intended to do, namely dealing with huge amounts of data and data changes, you do not want to have the parts of your application battle for resources.

That being said: There are ways to limit MongoDBs resource allocation and make sure those limits are obeyed. Probably the most well known as of today is Docker. You have to decide wether it is worth it to run MongoDB in a Docker container.

As for the data file size: There is transparent compression available for the default wiredTiger storage engine. You can choose from either snappy(default) or the better known zlib compression. Both come at the expense of a higher CPU utilization, as mentioned above.

Time to market

Forgive me, but you demonstrate a lack of basic knowledge about MongoDB. Since you are not sure what to do, the best you can do is to research every step carefully, make a decision, rinse and repeat. And I am not even taking into account that you would need to remodel your data and most likely make massive changes to your applications persistence layer. If I were you and wanted/needed a fast time to market, I'd go with what I have as of now. In case problems occur during production, I'd get myself a consultant specializing on NoSQL to find out wether one of the various NoSQL databases suits your needs, identify said DBMS (which may or may not be MongoDB), get myself a specialist for that and do the migration only then. With a specialist on your side.

Administration

A mistake commonly made by people is that they think it is as easy to manage a MongoDB deployment as it is to getting it to work. It is not. Any DBA, Sysadmin or even DevOps (even when used in the wrongest sense of the word) should be able to get a sharded cluster running. Choose the proper dimensions, identify problems, decide when to scale, deal with non-trivial problems and errors? Not so much. And note that the latter is highly subjective and prone to the skills of the DBA in question. Do you really want to store your production data on a system you barely understand?

Conclusion

Imho, MongoDB does not fit the requirements you stated. Changing now will most likely increase your time to market, and very much so since you do not understand the intricacies and pitfalls of MongoDB. In the worst case you built up technical debts while simultaneously diminishing the performance of your application.

MongoDB is less well suited for your resource limitation requirements, and finding a sweet spot for resource limitations that give you an acceptable performance requires an experienced DBA and quite some time, during which your application might run with sub-par performance.

Let me put it in a bit of context: MongoDB might very well be suitable for your use cases, and even excel. But I do not think it is feasible to change your persistence technology as of now, given your level of knowledge and the stated requirements.

hth

Answer 2

I deem that there are multiple (very important) aspects that you need to consider before deciding which tool you are going to employ to develop your project.

The primary objective should be to manage the pertinent data as it is, a quite valuable organizational asset, and a reliable manner to achieve said objective is by way of technical means that are supported on sound theory.

In this regard, it is worth mentioning that the success of a determined database does not only depend on the database management system (DBMS) of choice but also on a number of factors, such as:

• Its logical model
• Its physical implementation settings
• Its qualified administration

Since you are considering a SQL platform as a tentative DBMS, this fact suggests the intention to implement a relational database, so I will focus on this respect throughout the present answer.

Although Dr. E. F. Codd (a Turing Award recipient) published his seminal paper A Relational Model for Large Shared Data Banks back in 1970, I really consider that his exceptional work remains unparalleled and state-of-the-art because, e.g., it is solidly based on first-order logic and set theory.

When implemented in a SQL platform, a well designed database permits obtaining many of the advantages proposed by relational theory. In contrast, a poorly designed database can easily become inoperative. Having said that, it is important to be aware that the development of a relational database demands a firm understanding of the specific business domain of interest. Therefore, you have to analyze and classify all the things of concern, and these tasks require strong data modeling skills. In this way, if you have a clear knowledge of the business context and good modeling abilities, you will be able to create a strong logical database structure which represents the bussines context with precision and can be easily extended and modified.

Once you have developed a stable database (taking into account the particulars of the DBMS you decided to use) and launched your system, it is time to concentrate your efforts in managing the server and, as one would expect, the data, and here is where database administration skills are particularly critical.

So, as you know, all of these requires a certain amount of experience, which you can only gain by embarking on several projects, preferably under the supervision of a specialized colleague or team.

Aspects to take into account

So, in order to make an informed decision, you should:

• Continue asking good questions.
• Take the time to learn about relational theory.
  • In this regard, I highly recommend Dr. Codd bibliography, so that you learn directly from the originator of the relational paradigm.
• Enhance your data modeling skills.
  • You might find IDEF1X of interest. It is a powerful and expressive technique that was defined as a standard in 1993 by the United States National Institute of Standards and Technology (NIST).
  • In this meta post I discuss some elementary modeling points, and in this answer I deal with a basic database structure, in case you are interested.
• Evaluate the capabilities and limitations that pertain to MySQL.
• Asses other SQL systems and compare them with MySQL.
  • It is worth noting that the major platforms have been heavily optimized over the years (or even decades).
  • There are different open source alternatives that are very interesting.
• Study the different SQL dialects available.
• Get SQL hands-on experience following the theoretical stipulations so that you can see their value in action.
• Find out the theory that serves as a base for MongoDB.
• Study the tools that are similar to MongoDB.
• Compare MongoDB (and other resembling tools) with SQL software (and also with pre-relational technology).

The Profile table Primary Key definition and Indexes

One part of your question that called my attention in a particular way was the fact that you defined all the columns of the profile table as the PRIMARY KEY, which you explained in the following comment:

[…] yeah they are primary keys, I make them pk because as far as I know that makes them index and can speed the SELECT operation on that table. And I meant we may have to add more indexes (pk) to the profile table. I have read that doing ALTER table with lots of rows can last very long and can be complicated, also I will like to do sharding or whatevery technique may help on performance.

Thus, there are some fundamental (and very relevant) points about relational keys and index structures that need to be clarified.

Primary Keys

A PRIMARY KEY (PK) represents a logical element, and it is a column (or a combination of columns) that holds values that uniquely identify a given row in the respective table. A table cannot be set with more than one PK.

At the physical level, a PK usually has a subordinate INDEX that, apart from speeding up the data retrieval (as you have rightly mentioned), also helps to ensure the uniqueness of a determined row (so said INDEX is, in fact, UNIQUE).

Alternate Keys

A table can have one or more ALTERNATE KEYs (AK), which are logical constituents as well. An AK is a column (or combination of columns) that retains values that uniquely identify a certain row in the corresponding table, but was not chosen as the PK.

An AK can be established via a UNIQUE CONSTRAINT, which is commonly assisted by a physical INDEX that enhances retrieval speed and, naturally, protects row uniqueness.

Indexes on columns that are not (or are not part of) Primary or Alternate Key definitions

Columns that are not (or are not part of) PKs or AKs can also be INDEXed if such approach accelerates some of your queries. As a consequence, you do not need to add new columns to a PK in order to obtain the physical advantages, you just have to incorporate them to a composite non-unique INDEX (or create a non-unique INDEX for each corresponding column, when necessitated) without adding them to the PK, since by doing so you would devoid the PK definition of its contextual meaning.

Entity types, Keys and meaning

If the people involved in a given context has determined that a certain kind of thing, i.e., an entity type has organizational significance, then each instance of said entity type must be differentiated by the value (or values) of one (or more) of its attribute(s), hence PKs and AKs are essential qualities of the data and they depend exclusively on semantic aspects. Each entity type should be set as a table in a database structure; every entity type instance should be INSERTed as a row in the appropriate table.

So, I deem relevant to state that, just like creating a database and tables inside a server does not necesarily mean that such database and tables are relational, labeling columns as keys does not necesarily mean that they are, in fact, keys. Thus, since keys are an intrinsic characteristic of the data, their identification depends on the modeler competence, and their correct implementation in a server depends on the modeler proper declaration.

Logical and physical

As you can see, it is very important to distinguish logical from physical elements. Summing up, a logical (or abstract) component depends directly on the meaning of the data; in contrast, a physical (or concrete) construct is a mechanism that is used “under the hood” so that a DBMS can —for instance— facilitate data retrieval, support the logical definitions made by a database creator, or both.

Base and derived tables (or relations)

With a SQL system, you can define base tables (via the DDL CREATE TABLE statements) that shape the structure of the database, but that is not all there is to it, since you can as well obtain multiple derived tables once you need to retrieve result sets which combine columns from different tables, e.g., by virtue of a SELECT statement that JOINs said tables. You can define said derived tables as VIEWs, and also query them directly if necessary. This is just one good example of the versatility that is offered by SQL platforms, since you would always be working with the same kind of structure, a table (or relation).

Of course, you can also make use of the built-in server functions in order to make different kinds of calculations, create computed and concatenated columns, obtain statistiscs and keep on creating queries that you did not even imagine at design time.

If, as time passes, the data users define new contextual things of interest, you can perfectly cover their needs by adding new tables to your database and, yes, you can combine the previously existing ones with the fresh tables and produce brand new derived relations.

As you can see, the possibilities offered by a relational approach are huge.

Joins

Since JOINs might seem a bit cumbersome, in case you face a problem with a specific query, you can come to DBA.SE and ask for help. There is a good amount of users that are very skilled and experienced, and quite probably more than one might like to offer their valuable assistance.

In this regard, I should say that this kind of operation has been highly optimized at the physical level by multiple SQL vendors. So, in the suitable conditions (i.e., performed in a well designed database) JOINs are decidedly fast.

Redundancy

A relational database stores assertions about real world facts, and an exact fact happens one single time. So from a logical perspective, storing the same fact more than once is unreasonable and unnecesary.

Redundancy eventually leads to inconsistencies. For example, suposse that:

• Someone has retained the same piece of information twice in a certain database.
• Later, someone else comes and UPDATEs only one occurrence of the duplicates. As a consequence, the other occurrence is not up to date anymore.
• Successively, another person UPDATEs the occurrence that had not been modified so far. In this manner, both duplicates have undergone different changes at distinct points in time.
• Then, when someone is interested in retrieving the piece of information in question, he or she can find two different versions of it.

So:

• Which version can be considered the correct, reliable one?
• Which one reflects the real world accurately?

As you know, this phenomenon can even have legal implications, a situation that surely is of enormous importance.

Furthermore, the time and effort that has to be employed to handle such inconsistencies (perhaps by some kind of update synchronization) should be better devoted to tasks that actually produce value for your organization. So, I recommend avoiding their storage by design and keeping the logical consistency of your database intact.

Tables with big amounts of rows

There are multiple database instances retaining billions of rows across numerous tables that serve their users at really high speeds, but this, again, is a result of a proper design made by qualified practitioners. So, the problem is not the amount of information stored, but the way in which said information is managed.

Multiple applications working with the same database

A relational database is meant to serve multiple application programs at the same time. So you can have, e.g, one or more web apps, one ore more desktop apps and one or more mobile apps, all working toghether with your database simultaneously.

So —using programming jargon— one must make sure not to couple the database with the code of any of the apps; keep each software component separated from the others but, at the same time, connected.

Answer 3

Agree with Mark mostly, but Mongo is not that bad. The chirp table would be better with the userid instead of the name, and then using the right calls, you could populate the data from the user collection/table, that is a lookup in an indexed field, pretty fast in Mongo.

The indexes themselves are not to worry about, as long as you keep them flat and only for the purpose where you need them. Our typical query is one collection and populating data from three different ones and it is super fast and with the async processing of Node it costs the server maybe 5% performance for the IO when we have a lot of hits. I haven't seen a SQL server run that fast ever. Might get there with a lot of tweaking and optimizations, but here you get it for free from the start.

And your tables are fine size-wise, all flat data that is per-user is no problem. Even if every person on the planet created 10 accounts, you will only get a few Gigabytes probably.

The big thing is relations between tables, so one person following many, or many people liking one, the logs for timelines of those things, and so on. When you deal with those, you will run into space problems soon. Also, always put them in own collections, do not embed the likes and followers! Sooner or later you will run into weird things where your document in the database can't hold more than X bytes or whatever.

For a smaller network with maybe 100000 users, your current combination should run fine, as soon as it gets larger, you might run into performance problems.

On a larger scale network, Node+Mongo will run a lot faster than PHP+Mysql, so you might invest some time now to get it right. But if everything is already done in MySQL you can also wait for a later moment. Rewriting the table definitions should take you 1-2 days, or you could maybe even use MySQL from Node if you only have the database so far.

Whatever you decide, don't use relational data in Mongo as embedded fields, Mark has a blog post about it. Just put every MySQL table in an own flat collection and use proper indexes and the populate mechanism from the node framework, that's something like LEFT JOIN in SQL.

If you haven't already coded all views, I would also recommend creating a small API for all calls, so you can easier develop and change or add front-ends later in the process.

If you are feeling insecure, always consult someone who knows how to work with those things. Would even pay someone to do the first steps and get you up to speed. This of course depends on the project, but you could start with nice blogs if you are worried.

In my experience it is easier to cut down early what you have than to get people to change it later, so would really recommend spending a few days to try to get the node+mongo thing running. There are nice tutorials out there, and it is really just simple JavaScript coding, both Mongo and Node. Nothing to be afraid of.

You already know where to ask questions, so you will do fine!

Answer 4

This depends on so much stuff. What are you building and how complex will your DB queries be? Hobby or professional use? Is the data critical? Do you need transaction safety? Do you already have all the data, in what format? or do you start from scratch? how many users do you expect? how many requests? Are you bound to PHP or can you use Node.JS? Is it a website or an app? Are you building an Api or use direct DB access?

Side note: In mongo you usually have an ObjectId (like a timestamp) instead of auto_increment as primary key, so you don't start at 1.

I would not mix databases, use one of them, both are able to handle large datasets. If you are afraid of performance problems, consider renting a powerful server for a little more money, it's worth it. But usually with moderate numbers the servers are bored, especially with SSDs.

In MySQL smallint is pretty low, use bigint and don't worry about the extra bytes: http://dev.mysql.com/doc/refman/5.7/en/integer-types.html

Answer 5

Looks like you have set up so that Mongo will win. The MySQL schema that you have sketched out has a number of inefficiencies. Please provide HOW CREATE TABLE profile.

Does your PK have 10 columns? I hope not.

Also, to help with the conversion to Mongo, please provide some of the typical queries.

Normalizing every column is overkill and inefficient.

Countries have a perfectly good 2-letter standard abbreviations that take just as much space as a SMALLINT. That is, there is no need to normalize them. And I generally prefer to put the entire "location" in another table, not because of "normalization", but so it can be one lookup instead of three.

How much data do you have (or will you have)? Once the data is bigger than can be caches (in any database), performance suffers in various ways. Some are predictable; some are avoidable.

"All tables are planned by not written" -- Are you referring to MySQL? Or Mongo? Or both? If you have no familiarity with either, then it will be a learning curve. Also, Mongo (I think) is lower level -- that is, you have to write more code to get the same effect.

570b87a56931b8f21a8bf25c smells like some kind of hash? Beware. If the data becomes bigger than RAM, operations, even "simple" ones will become I/O-bound due to the randomness of hashes as IDs.

You need to better understand indexing before embarking on any database activity. It is probably the most important aspect.

A PRIMARY KEY (in MySQL) is a unique identifier of the rows. It should contain no more than is necessary for such. Adding extra columns is counter-productive.

"ASAP" -- Plan on a complete rewrite in a couple of months. I do mean 'complete'. Rushing into a design and implementation without sufficient background will lead to a mess. But if you plan for a rewrite, you will be thinking in that direction, so it won't be so painful to throw away this prototype.

Answer 6

Fill the SQL tables with the worst case, a looooooot of happy users! Then see how performant it is with a lot of queries and then try to copy the data into Mongo. Might help you decide when the best for switching is. I usually go with NOW! ;-)

Also not sure what SQL offers these days, but in Mongo you can do sharding across multiple servers if the collections get too full, replica sets if one server cannot handle all the queries, ... best talk with your doctor or someone who does this for a living.