Optimization of relational databases without downtime on the example of the most loaded database in Badoo

 
3r3-31. Optimization of relational databases without downtime on the example of the most loaded database in Badoo 3r33333.  
3r33333.  
Under highload conditions, the complexity of optimizing relational databases increases by an order of magnitude, since buying even more powerful hardware is expensive and it is no longer possible just to turn off the application at night for a long DB alter process and data migration. 3r33333.  
3r33333.  
We recently told how we are optimized the PHP code of our application . Now came the turn of the article about how we completely changed the internal structure of the most loaded and important database in Badoo, without losing a single query. 3r33333.  
backup tool works, for example. Percona XtraBackup . 3r33333.  
3r33333.  
After the new scheme caught up to the current record, the most crucial stage begins: you still need to pause the recording in the old scheme for a short period of time and, making sure that the entire available log is applied to the new scheme, which means that the data between the schemes is consistent, application level to enable recording immediately in both sources. 3r33333.  
3r33333.  
The main disadvantages of this approach are that you need to somehow store the log of operations, which in itself can create a load, in a complex switching process, and also in the probability of breaking the record, if for some reason the schemes turn out to be inconsistent. 3r33333.  
3r33333.  

Idempotent record

3r33333.  
The main idea of ​​this approach is to start writing to the new scheme in parallel with writing to the old one before complete synchronization of changes, and then complete the migration of the remaining data. Similarly, new columns are usually filled in large tables. 3r33333.  
3r33333.  
Synchronous recording can be implemented both on DB triggers and in source code. I advise you to do this in the code, since in any case you will eventually have to write code that will write data to the new scheme, and implementing the migration on the code side will provide you with more control. 3r33333.  
3r33333.  
An important point worth considering is that until the completion of the migration, the new scheme will be in inconsistent state. Because of this, a scenario is possible when updating a new table leads to a violation of the database database (foreign keys or a unique index), while from the point of view of the current scheme, the transaction is completely correct and must be completed. 3r33333.  
3r33333.  
This situation can lead to a rollback of good transactions due to the migration process. The simplest way to get around this problem is to add the IGNORE modifier to all requests to write data to a new scheme or intercept the rollback of such a transaction and run the version without writing to the new scheme. 3r33333.  
3r33333.  
The synchronization algorithm using idempotent writing in our case looks like this: 3r3333318.  
3r33333.  
 
3r33300. We include writing to the new scheme in parallel with writing to the old one in compatibility mode (IGNORE). 3r33333.  
3r3303.  
3r33300. Run the script, which gradually bypasses the new scheme and fix inconsistent data. After that, the data in both tables should be synchronized, but this is inaccurate due to possible conflicts in § 1. 3r33318.  
3r3303.  
3r33300. We launch the data consistency checker - we open a transaction and consistently read lines from the new and old schema comparing their correspondence. 3r33333.  
3r3303.  
3r33300. If there are conflicts, finish and return to p. 3.
 
3r3303.  
3r33300. After the checker showed that the data in both schemes are synchronized, then there should be no further discrepancies between the schemes, unless, of course, we missed a nuance. Therefore, we wait for some time (for example, a week) and run the control check. If it shows that everything is good, then the task is completed successfully and you can translate the reading. 3r33333.  
3r3303.  
3r3305. 3r33333.  
3r3308. Results
3r33333.  
As a result of changing the data format, we managed to reduce the size of the main table from 544 GB to 226 GB, thereby reducing the load on the disk and increasing the amount of useful data that fit in the RAM. 3r33333.  
3r33333.  
Totally from the beginning of the project, using all the described approaches, we managed to reduce the CPU usage of the database server from 80% to 35% at peak traffic. The results of the subsequent load test showed that at the current rate of growth of the load, we can remain on the existing gland for at least three more years. 3r33333.  
3r33333.  
Splitting one huge table into somewhat simplified the process of conducting future alters in the database, and also significantly accelerated some of the scripts that collected data for BI.
3r3323. ! function (e) {function t (t, n) {if (! (n in e)) {for (var r, a = e.document, i = a.scripts, o = i.length; o-- ;) if (-1! == i[o].src.indexOf (t)) {r = i[o]; break} if (! r) {r = a.createElement ("script"), r.type = "text /jаvascript", r.async =! ? r.defer =! ? r.src = t, r.charset = "UTF-8"; var d = function () {var e = a.getElementsByTagName ("script")[0]; e.parentNode.insertBefore (r, e)}; "[object Opera]" == e.opera? a.addEventListener? a.addEventListener ("DOMContentLoaded", d,! 1): e.attachEvent ("onload", d ): d ()}}} t ("//mediator.mail.ru/script/2820404/"""_mediator") () ();
+ 0 -

Add comment