Goodbye MongoDB, Hello, PostgreSQL
startup Olery was founded almost 5 years ago. We started with a single product, Olery Reputation, which was created by the Agency, engaged in the development in Ruby. All this has increased the range of different products. Today we have Olery Feedback, Hotel Review API Data, widgets to embed on your sites and much more.
We are 25 apps (all in Ruby) – some of them in the web (Rails or Sinatra), but basically it is a background app for processing.
Although we can be proud, we have one problem that's always hung somewhere in the background database. Initially, we used MySQL for important data (users, contracts, etc.) and MongoDB to store profiles and other data that could be easily restored in case of loss. At first everything worked well, but the growth we began to experience problems, especially with MongoDB. Some of them arose in the sphere of interaction of database applications, some directly from the database itself.
For example, at some point we had to delete a million documents from MongoDB, and later paste. As a result, the database is stalled for hours. Then we had to run repairDatabase. And also fix itself took several hours.
Another time we noticed the brake and determined that the cause was a cluster of MongoDB. But we have been unable to figure out what slows down our database. No matter what tools for debugging and statistics gathering we had. We had (here I do not know how to translate, tell me in the comments: "until we replaced the primaries of the cluster "), so the performance is back to normal.
This is just two examples, but there were many. The problem was not only that the database was capricious, but that we could not understand why this is happening.
Another fundamental problem with which we are faced is a Central feature of MongoDB, namely, the absence of the scheme. In some cases, this provides advantages. But in many cases this leads to the problem of implicit schemes. They are not determined by the engine data storage, on the basis of the behavior of applications and projections.
For example, you can have a set of pages in which the application expects to find the title field of string type. Scheme is, although you don't explicitly set. The problems start if the data structure changed over time, and the old data is not transferred to the new structure (which is quite hard to do in the case of schemaless databases). Let's say you have code like this:
the
This will work for all documents that have a title field that returns a String. If the documents have a field with a different name, or no string fields, it will break. To handle such cases you need to rewrite the code:
the
Another way is to specify the schema in the database. For example, Mongoid, a popular MongoDB ODM for Ruby allows you to do it. But why set the schema using these tools, if it is possible to specify the schema in the database itself? It would be wise and for re-use. If you only have one app running against the database, it's not scary. But if they were a dozen, then it all quickly turns into a mess.
Storing data without a schema designed to facilitate your life. You don't need to come up with a scheme. In fact, in this case, you shift the responsibility for maintaining the data connectivity. In some cases it works, but I'm willing to bet that in most cases, this only causes more difficulties.
And we come to the question of what should be a good database. We at Olery value the following:
— connectivity
data and the behavior of the system seen from the outside
— correctness and nedvosmisleno
— scalability
The connectivity helps the system to guarantee implementation of what is expected of it. If the data is always stored in a certain way, the system becomes simpler. If a field is required, then the application should not check for it. BB needs to guarantee the completion of certain operations even under load. There's nothing more frustrating than to insert the data and waiting for them to appear in the database within a few minutes.
The visibility from the outside describes how the system itself, and how from it just retrieve the data. If the system is buggy, it should be easy to debug. Data requests should also be easy.
Correctness implies that the system lives up to expectations. If the field is defined as numeric, you should be able to paste the text there. MySQL in this respect is extremely weak. You can paste the text into a numeric field and get some nonsense data.
Scalability is important not only for performance but also from a financial point of view and from the point of view of how the system responds to changing requirements. The system should work well without unreasonable financial costs and does not slow down the system development life cycle who depend on it
Considering all this, we went looking for a replacement for MongoDB. Since our queries clearly fit the traditional relational database, we drew eyes on two candidates: MySQL and PostgreSQL.
MySQL was the first, in particular because we have already used in some cases. But it has its problems. For example, setting a field as int(11), you can paste the text there, and MySQL will try to convert. Examples:
the
Although MySQL and issue a warning, warnings are often simply ignored.
The second problem is any change in the table causes it to salochki on reading and writing. So, after each operation changes have to wait for its completion. For large tables, this can take hours, which will then slow down the entire project. Companies like SoundCloud have developed this special type tools lhm.
So we started to look at PostgreSQL. She has a lot of advantages. For example, you cannot insert text into a numeric field:
the
Have PostgreSQL also has the ability to change tables, which does not lead to their salacca. For example, adding a column that has no default values, and which can be filled in NULL, not selecive table.
There are other interesting features, namely: index and search based on trigrammic, full text search, support for JSON requests, support requests and storage of pairs a key-value, support for pub/sub and more.
But most importantly, PostgreSQL has a balance between performance, reliability, correctness and coherence.
So, we decided to stay in PostgreSQL. The process of migrating from MongoDB was a difficult task. We broke it into three stages:
— prepare the PostgreSQL database, migrate small parts of the data
— updating apps that work with MongoDB for PostgreSQL, including any refactoring
— migrate production to the new database and placement on the new platform
Although there are tools to migrate, because of the peculiarities of our data, we had to make such instruments. It was a one-off Ruby scripts, each doing a separate task – the transfer of related, Erasure encodings edit main keys and other things.
Most time was spent on updating applications, especially those heavily dependent on MongoDB. It took a few weeks. The process consisted of the following:
— replacement driver/code/model code for MongoDB to PostgreSQL
run tests
— fix tests
— repeat step 2
For applications running not on Rails, we decided to use Sequel. Took for Rails ActiveRecord. Sequel – a handy set of tools that support almost all the special features of PostgreSQL. Build the queries he wins ActiveRecord, although sometimes it turns out too much text.
For example, you need to count the number of users who use a certain locale as a percentage of the total. In plain SQL, the query might look like this:
the
In our case, we get the following result:
the
Sequel allows you to write the query in pure Ruby without a string of fragments (which are sometimes required for ActiveRecord)
the
If you do not want to use a Sequel.lit('*'), you can do this:
the
Text too much, but they can be easily redone in the future.
In plans we want to translate the application running with Rails on a Sequel. But it's unclear if it's worth the time spent
For this there are two ways:
— to stop the whole project, to migrate, to raise the project
— to migrate in parallel with a working project
The first option implies that the project will not work for a while. The second is complicated in execution. You need to consider all the new data that will be added during migration, in order not to lose them.
Fortunately, Olery all so cleverly set up, the transactions with the database occur in approximately equal intervals of time. Data that changes often, move easier, because very few of them.
Here's the plan:
— to transfer critical data users, contracts, etc.
— move less critical data (which you can then convert or restore)
— check that everything works on a set of separate servers
— to transfer production to the new server
— to transfer all the critical data that appeared with the first step
The second step took the day. The first and the fifth for 45 minutes.
It's been almost a month since we moved and we were all happy. Changes occurred only for the better. Increased application responsiveness. Time reviews the API has decreased.
We moved on January 21. Peak on chart – restart the application which led to a temporary increase in response time. After the 21st, the response time is almost halved.
Where we have noticed a serious increase in performance, so it is in the application that saved data from reviews and ratings.
Our data collectors also accelerated.
The difference came not so strong, but collectors and not so much use the database.
And finally, the application that distributes the timetable data collector (“scheduler”):
Because it works after a certain period of time, the graph is difficult to read, but overall there is a clear decrease in response time.
So, we are quite satisfied with the results of the move, and missing MongoDB is not going to. The performance is great tools for working with the database very comfortable, the requests for to do has become much easier in comparison with MongoDB. The only service that still uses it, it's Olery Feedback. It runs on a separate cluster. But its in the future we also intend to translate to PostgreSQL.
Article based on information from habrahabr.ru
We are 25 apps (all in Ruby) – some of them in the web (Rails or Sinatra), but basically it is a background app for processing.
Although we can be proud, we have one problem that's always hung somewhere in the background database. Initially, we used MySQL for important data (users, contracts, etc.) and MongoDB to store profiles and other data that could be easily restored in case of loss. At first everything worked well, but the growth we began to experience problems, especially with MongoDB. Some of them arose in the sphere of interaction of database applications, some directly from the database itself.
For example, at some point we had to delete a million documents from MongoDB, and later paste. As a result, the database is stalled for hours. Then we had to run repairDatabase. And also fix itself took several hours.
Another time we noticed the brake and determined that the cause was a cluster of MongoDB. But we have been unable to figure out what slows down our database. No matter what tools for debugging and statistics gathering we had. We had (here I do not know how to translate, tell me in the comments: "until we replaced the primaries of the cluster "), so the performance is back to normal.
This is just two examples, but there were many. The problem was not only that the database was capricious, but that we could not understand why this is happening.
the Problem of lack of schema
Another fundamental problem with which we are faced is a Central feature of MongoDB, namely, the absence of the scheme. In some cases, this provides advantages. But in many cases this leads to the problem of implicit schemes. They are not determined by the engine data storage, on the basis of the behavior of applications and projections.
For example, you can have a set of pages in which the application expects to find the title field of string type. Scheme is, although you don't explicitly set. The problems start if the data structure changed over time, and the old data is not transferred to the new structure (which is quite hard to do in the case of schemaless databases). Let's say you have code like this:
the
post_slug = post.title.downcase.gsub(/\W+/, '-')
This will work for all documents that have a title field that returns a String. If the documents have a field with a different name, or no string fields, it will break. To handle such cases you need to rewrite the code:
the
if post.title
post_slug = post.title.downcase.gsub(/\W+/, '-')
else
# ...
end
Another way is to specify the schema in the database. For example, Mongoid, a popular MongoDB ODM for Ruby allows you to do it. But why set the schema using these tools, if it is possible to specify the schema in the database itself? It would be wise and for re-use. If you only have one app running against the database, it's not scary. But if they were a dozen, then it all quickly turns into a mess.
Storing data without a schema designed to facilitate your life. You don't need to come up with a scheme. In fact, in this case, you shift the responsibility for maintaining the data connectivity. In some cases it works, but I'm willing to bet that in most cases, this only causes more difficulties.
Requirements for a good database
And we come to the question of what should be a good database. We at Olery value the following:
— connectivity
data and the behavior of the system seen from the outside
— correctness and nedvosmisleno
— scalability
The connectivity helps the system to guarantee implementation of what is expected of it. If the data is always stored in a certain way, the system becomes simpler. If a field is required, then the application should not check for it. BB needs to guarantee the completion of certain operations even under load. There's nothing more frustrating than to insert the data and waiting for them to appear in the database within a few minutes.
The visibility from the outside describes how the system itself, and how from it just retrieve the data. If the system is buggy, it should be easy to debug. Data requests should also be easy.
Correctness implies that the system lives up to expectations. If the field is defined as numeric, you should be able to paste the text there. MySQL in this respect is extremely weak. You can paste the text into a numeric field and get some nonsense data.
Scalability is important not only for performance but also from a financial point of view and from the point of view of how the system responds to changing requirements. The system should work well without unreasonable financial costs and does not slow down the system development life cycle who depend on it
Care and MongoDB
Considering all this, we went looking for a replacement for MongoDB. Since our queries clearly fit the traditional relational database, we drew eyes on two candidates: MySQL and PostgreSQL.
MySQL was the first, in particular because we have already used in some cases. But it has its problems. For example, setting a field as int(11), you can paste the text there, and MySQL will try to convert. Examples:
the
mysql> create table example ( `number` int(11) not null );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into example (number) values (10);
Query OK, 1 row affected (0.08 sec)
mysql> insert into example (number) values ('wat');
Query OK, 1 row affected, 1 warning (0.10 sec)
mysql> insert into example (number) values ('what is this nonsense 10');
Query OK, 1 row affected, 1 warning (0.14 sec)
mysql> insert into example (number) values ('10 a');
Query OK, 1 row affected, 1 warning (0.09 sec)
mysql> select * from example;
+--------+
| number |
+--------+
| 10 |
| 0 |
| 0 |
| 10 |
+--------+
4 rows in set (0.00 sec)
Although MySQL and issue a warning, warnings are often simply ignored.
The second problem is any change in the table causes it to salochki on reading and writing. So, after each operation changes have to wait for its completion. For large tables, this can take hours, which will then slow down the entire project. Companies like SoundCloud have developed this special type tools lhm.
So we started to look at PostgreSQL. She has a lot of advantages. For example, you cannot insert text into a numeric field:
the
olery_development=# create table example ( number int not null );
CREATE TABLE
olery_development=# insert into example (number) values (10);
INSERT 0 1
olery_development=# insert into example (number) values ('wat');
ERROR: invalid input syntax for integer: "wat"
LINE 1: insert into example (number) values ('wat');
^
olery_development=# insert into example (number) values ('what is this nonsense 10');
ERROR: invalid input syntax for integer: "what is this nonsense 10"
LINE 1: insert into example (number) values ('10 what is this nonsen...
^
olery_development=# insert into example (number) values ('10 a');
ERROR: invalid input syntax for integer: "10A"
LINE 1: insert into example (number) values ('10 a');
Have PostgreSQL also has the ability to change tables, which does not lead to their salacca. For example, adding a column that has no default values, and which can be filled in NULL, not selecive table.
There are other interesting features, namely: index and search based on trigrammic, full text search, support for JSON requests, support requests and storage of pairs a key-value, support for pub/sub and more.
But most importantly, PostgreSQL has a balance between performance, reliability, correctness and coherence.
PostgreSQL
So, we decided to stay in PostgreSQL. The process of migrating from MongoDB was a difficult task. We broke it into three stages:
— prepare the PostgreSQL database, migrate small parts of the data
— updating apps that work with MongoDB for PostgreSQL, including any refactoring
— migrate production to the new database and placement on the new platform
Migration of a small portion of data
Although there are tools to migrate, because of the peculiarities of our data, we had to make such instruments. It was a one-off Ruby scripts, each doing a separate task – the transfer of related, Erasure encodings edit main keys and other things.
Updating apps
Most time was spent on updating applications, especially those heavily dependent on MongoDB. It took a few weeks. The process consisted of the following:
— replacement driver/code/model code for MongoDB to PostgreSQL
run tests
— fix tests
— repeat step 2
For applications running not on Rails, we decided to use Sequel. Took for Rails ActiveRecord. Sequel – a handy set of tools that support almost all the special features of PostgreSQL. Build the queries he wins ActiveRecord, although sometimes it turns out too much text.
For example, you need to count the number of users who use a certain locale as a percentage of the total. In plain SQL, the query might look like this:
the
SELECT locale
count(*) AS amount,
(count(*) / sum(count(*)) OVER ()) * 100.0 AS percentage
FROM users
GROUP BY locale
ORDER BY percentage DESC;
In our case, we get the following result:
the
locale | amount | percentage
--------+--------+--------------------------
en | 2779 | 85.193133047210300429000
nl | 386 | 11.833231146535867566000
it | 40 | 1.226241569589209074000
de | 25 | 0.766400980993255671000
EN | 17 | 0.521152667075413857000
| 7 | 0.214592274678111588000
fr | 4 | 0.122624156958920907000
ja | 1 | 0.030656039239730227000
ar-AE | 1 | 0.030656039239730227000
eng | 1 | 0.030656039239730227000
zh-CN | 1 | 0.030656039239730227000
(11 rows)
Sequel allows you to write the query in pure Ruby without a string of fragments (which are sometimes required for ActiveRecord)
the
star = Sequel.lit('*')
User.select(:locale)
.select_append { count(star).as(:amount) }
.select_append { ((count(star) / sum(count(star)).over) * 100.0).as(:percentage) }
.group(:locale)
.order(Sequel.desc(:percentage))
If you do not want to use a Sequel.lit('*'), you can do this:
the
User.select(:locale)
.select_append { count(users.*).as(:amount) }
.select_append { ((count(users.*) / sum(count(users.*)).over) * 100.0).as(:percentage) }
.group(:locale)
.order(Sequel.desc(:percentage))
Text too much, but they can be easily redone in the future.
In plans we want to translate the application running with Rails on a Sequel. But it's unclear if it's worth the time spent
Migration operating data
For this there are two ways:
— to stop the whole project, to migrate, to raise the project
— to migrate in parallel with a working project
The first option implies that the project will not work for a while. The second is complicated in execution. You need to consider all the new data that will be added during migration, in order not to lose them.
Fortunately, Olery all so cleverly set up, the transactions with the database occur in approximately equal intervals of time. Data that changes often, move easier, because very few of them.
Here's the plan:
— to transfer critical data users, contracts, etc.
— move less critical data (which you can then convert or restore)
— check that everything works on a set of separate servers
— to transfer production to the new server
— to transfer all the critical data that appeared with the first step
The second step took the day. The first and the fifth for 45 minutes.
Opinion
It's been almost a month since we moved and we were all happy. Changes occurred only for the better. Increased application responsiveness. Time reviews the API has decreased.
We moved on January 21. Peak on chart – restart the application which led to a temporary increase in response time. After the 21st, the response time is almost halved.
Where we have noticed a serious increase in performance, so it is in the application that saved data from reviews and ratings.
Our data collectors also accelerated.
The difference came not so strong, but collectors and not so much use the database.
And finally, the application that distributes the timetable data collector (“scheduler”):
Because it works after a certain period of time, the graph is difficult to read, but overall there is a clear decrease in response time.
So, we are quite satisfied with the results of the move, and missing MongoDB is not going to. The performance is great tools for working with the database very comfortable, the requests for to do has become much easier in comparison with MongoDB. The only service that still uses it, it's Olery Feedback. It runs on a separate cluster. But its in the future we also intend to translate to PostgreSQL.
Комментарии
Отправить комментарий