Will the base break if the server is pulled out of the socket, or DB ORACLE giblets for dummies
I wrote for colleagues - programmers, far from the subject area, who really, sincerely did not understand what is so complex in the database. They wanted to store critical data in simple files. I asked them tricky questions about reliability, speed and simultaneous access, they tried to come up with clever solutions “on the go”. In the end, they soberly estimated the required amount of code and realized that they would have to write their little ORACLE or, at least, MySQL. Then I told them how these problems were solved in DB ORACLE, they were struck by the elegance of some algorithms. I liked the lecture, and I decided to post it in open access.
Who does not want to read the theory - at the end of a couple of key points that every developer working with ORACLE should know. He may not own the SQL language, but he has to know the features of a COMMIT.
For DBA and other experts (in a good way) - the material is consciously simplified to the maximum, due to this there are some inaccuracies. The goal was to convey the basic principles to an unprepared person.
')
Initially, there was a file and there were tables with fixed-length records.
Everything is simple - new records were added to existing “holes” in the middle of the file instead of deleted ones, if there are no “holes” - at the end.
Fast, but you have to reserve a lot of space just in case for rare, unique data. So, one famous Spaniard has the name “Pablo Diego Jose Francisco de Paula Juan Nepomucheno Crispin Crispiano de la Santisima Trinidad Ruiz and Picasso” - respectively, the full name field will have to be done with a large margin.
And no long descriptions or notes from several sentences.
When adding such lines to the end of the file, there are no problems, but when inserting into the middle of the file instead of deleted and changing the data inside the lines (a couple of sentences were added to the product description), there are problems. The solution was invented a long time ago by developers of file systems and memory managers - to store data in blocks, when data “crawl away” across several blocks - to defragment it. In addition, ORACLE allows for creating a block to leave there empty space for future changes, in case the length of the new record increases.
When added functionality - the speed dropped dramatically. Defragmentation takes a lot of resources, it has to be limited. As a result, many of the records turned out to be “smeared” into blocks, and, worst of all, these blocks can be physically located in different parts of the hard disk. The obvious solution — caching in the buffer cache — saves the reading situation. When committing changes, all data must be written to disk (when it comes to critical applications, you cannot rely on UPS), and not one, but a few, preferably for redundancy. Moreover, the disks must be logical, the redundancy solely by means of RAID will not work, because, in the event of the death of the controller, the entire array may be sent to the dump. And users, in addition, do not want to duplicate data files on the server - server drives are very expensive.
A solution was found - created - redo log, redo log. After changing the data in the cache, all new and changed data is sequentially written to this file. If you select a separate disk for this task (or rather several on different controllers), the head will not jump, and the sequential write speed of the disks is high. Let's pay attention to the elegance of the solution - all changes are written to the log immediately, without waiting for the transaction to be committed. COMMIT only puts a key marker, which takes a split second. In the case of rollback traction, information about the changes remains in the log file, but it ends with a red mark.
What about data files? Periodically, a process is started that finds changed, so-called “dirty” blocks in the buffer cache and writes them to disk. Due to the "wholesale" processing increases the speed and response time of the system.
So that the size of the rerun logs does not increase to infinity, the log rotation is used. ORACLE ensures that during the cyclic change of the log files, all the “dirty” blocks get into the data files.
From this scheme follows a feature that is not obvious at first glance, which struck me in its time to the depths of the soul. What to do with modified blocks that are not confirmed by COMMIT ”, but no longer fit into the buffer cache? Write changes to a temporary file? No, ORACLE writes these changes immediately to the data files. Yes, in a system that was initially focused on enhanced reliability, data files may contain inconsistent, uncommitted COMMIT data, and this is normal, it is part of the workflow. Instead, the DBMS backs up the modified blocks in special UNDO files. Here, this reserve is maintained until a transaction is committed, and allows other transactions to read data while another transaction changes it. Yes, in ORACLE changes do not block reading. These are the basics, but many application developers will be surprised to learn about this feature.
And - all UNDO changes are also recorded in the redo log.
Thus, the redo log contains information about all added data, as well as data before and after the change. These logies are very important, only with the help of them you can bring the data files into a correct, consistent state. ORACLE recommends duplicating redo logs on different disks.
If before cyclic rewriting of the next log file of repeated execution to copy it to another medium or server, you can get the following buns.
1. A simple backup - it is enough to make a complete copy of the data files, for example, once a day, and only save logs for the rest of the time.
2. Backup server - further development of the idea. Logs are rolled onto a copy of the database deployed on the backup server as they arrive. When the time comes to X, the data processing switches to the backup server.
3. Requests to the past - you can see these tables as they were, for example, an hour and a half ago. And this can be done on a working base, no need to “raise” any backups.
Returning to the headline: what happens when a sudden power outage? After all, the data in the files will remain in an inconsistent state? Nothing wrong.
Consider an example: after the last successful write to the database file (time 1), the system confirmed 1 tanzaktion (time 2), then started to perform the 2nd, the power is turned off (time 3) while writing to the database file.
So, the server is working again. The system reads the redo log since the last successful write to the disk (since time 1) and rolls back all changes. To do this, the log has all the necessary information. Then the system consistently repeats all operations (roll forward). Finds the COMMIT label (made in time 2) and commits the changes. Then continues to roll. It reaches the end of the redo log (at this time point -3 - power is turned off) and does not find the COMMIT label. The rollback will be executed again, but to the point (time 2).
Data again in a consistent state!
Yes, all this information is useful, but what should the developers pay attention to?
• The first unobvious moment - unlike other DBMSs in ORACLE, a frequent COMMIT is harmful. Upon receipt of this command information, albeit in small quantities, should be recorded on disk. This process takes place as quickly as possible, but if you try to execute it after changing each entry for a 10 millionth table ... The system is designed to do COMMIT when it is necessary from the point of view of business logic, and not more often.
Rollback of a transaction (ROLLBACK) should be avoided as much as possible, as a rule, the time for rollback is often comparable to the time of changes. UPDATE was executed for an hour, we decided to roll back, ROLLBACK will also run for comparable time. A commit would “pass” in a fraction of a second.
• The second point, for some reason not obvious to many developers - changing data never blocks reading. It needs to be remembered, but in general the implementation of simultaneous access to data from the point of view of the developer requires a separate article.
• The third point - do not be afraid of joins, this is not a bad MySQL. Properly configured database will allow you to maintain data integrity and performance. HASH JOUN, MERGE JOIN, INDEX ORGANIZED TABLES, CARDINALITY, QUERY REWRITING and MATRIALIZED VIEW UPDATE ON COMMIT settings allow a knowledgeable admin to achieve good speed. But maintaining an inconsistent denormalized system in order in a couple of years will be an order of magnitude more difficult.
• And finally - use the bound variables! This will make it possible to significantly increase productivity at once, often several times and, as a free bonus, avoid SQL!
Conclusion - ORACLE has created a reliable DBMS, but how fast it will work depends on the developers. Non-optimal solutions will force any server to ask for mercy.
Who does not want to read the theory - at the end of a couple of key points that every developer working with ORACLE should know. He may not own the SQL language, but he has to know the features of a COMMIT.
For DBA and other experts (in a good way) - the material is consciously simplified to the maximum, due to this there are some inaccuracies. The goal was to convey the basic principles to an unprepared person.
')
Initially, there was a file and there were tables with fixed-length records.
Everything is simple - new records were added to existing “holes” in the middle of the file instead of deleted ones, if there are no “holes” - at the end.
Fast, but you have to reserve a lot of space just in case for rare, unique data. So, one famous Spaniard has the name “Pablo Diego Jose Francisco de Paula Juan Nepomucheno Crispin Crispiano de la Santisima Trinidad Ruiz and Picasso” - respectively, the full name field will have to be done with a large margin.
And no long descriptions or notes from several sentences.
When adding such lines to the end of the file, there are no problems, but when inserting into the middle of the file instead of deleted and changing the data inside the lines (a couple of sentences were added to the product description), there are problems. The solution was invented a long time ago by developers of file systems and memory managers - to store data in blocks, when data “crawl away” across several blocks - to defragment it. In addition, ORACLE allows for creating a block to leave there empty space for future changes, in case the length of the new record increases.
When added functionality - the speed dropped dramatically. Defragmentation takes a lot of resources, it has to be limited. As a result, many of the records turned out to be “smeared” into blocks, and, worst of all, these blocks can be physically located in different parts of the hard disk. The obvious solution — caching in the buffer cache — saves the reading situation. When committing changes, all data must be written to disk (when it comes to critical applications, you cannot rely on UPS), and not one, but a few, preferably for redundancy. Moreover, the disks must be logical, the redundancy solely by means of RAID will not work, because, in the event of the death of the controller, the entire array may be sent to the dump. And users, in addition, do not want to duplicate data files on the server - server drives are very expensive.
A solution was found - created - redo log, redo log. After changing the data in the cache, all new and changed data is sequentially written to this file. If you select a separate disk for this task (or rather several on different controllers), the head will not jump, and the sequential write speed of the disks is high. Let's pay attention to the elegance of the solution - all changes are written to the log immediately, without waiting for the transaction to be committed. COMMIT only puts a key marker, which takes a split second. In the case of rollback traction, information about the changes remains in the log file, but it ends with a red mark.
What about data files? Periodically, a process is started that finds changed, so-called “dirty” blocks in the buffer cache and writes them to disk. Due to the "wholesale" processing increases the speed and response time of the system.
So that the size of the rerun logs does not increase to infinity, the log rotation is used. ORACLE ensures that during the cyclic change of the log files, all the “dirty” blocks get into the data files.
From this scheme follows a feature that is not obvious at first glance, which struck me in its time to the depths of the soul. What to do with modified blocks that are not confirmed by COMMIT ”, but no longer fit into the buffer cache? Write changes to a temporary file? No, ORACLE writes these changes immediately to the data files. Yes, in a system that was initially focused on enhanced reliability, data files may contain inconsistent, uncommitted COMMIT data, and this is normal, it is part of the workflow. Instead, the DBMS backs up the modified blocks in special UNDO files. Here, this reserve is maintained until a transaction is committed, and allows other transactions to read data while another transaction changes it. Yes, in ORACLE changes do not block reading. These are the basics, but many application developers will be surprised to learn about this feature.
And - all UNDO changes are also recorded in the redo log.
Thus, the redo log contains information about all added data, as well as data before and after the change. These logies are very important, only with the help of them you can bring the data files into a correct, consistent state. ORACLE recommends duplicating redo logs on different disks.
If before cyclic rewriting of the next log file of repeated execution to copy it to another medium or server, you can get the following buns.
1. A simple backup - it is enough to make a complete copy of the data files, for example, once a day, and only save logs for the rest of the time.
2. Backup server - further development of the idea. Logs are rolled onto a copy of the database deployed on the backup server as they arrive. When the time comes to X, the data processing switches to the backup server.
3. Requests to the past - you can see these tables as they were, for example, an hour and a half ago. And this can be done on a working base, no need to “raise” any backups.
Returning to the headline: what happens when a sudden power outage? After all, the data in the files will remain in an inconsistent state? Nothing wrong.
Consider an example: after the last successful write to the database file (time 1), the system confirmed 1 tanzaktion (time 2), then started to perform the 2nd, the power is turned off (time 3) while writing to the database file.
So, the server is working again. The system reads the redo log since the last successful write to the disk (since time 1) and rolls back all changes. To do this, the log has all the necessary information. Then the system consistently repeats all operations (roll forward). Finds the COMMIT label (made in time 2) and commits the changes. Then continues to roll. It reaches the end of the redo log (at this time point -3 - power is turned off) and does not find the COMMIT label. The rollback will be executed again, but to the point (time 2).
Data again in a consistent state!
Yes, all this information is useful, but what should the developers pay attention to?
• The first unobvious moment - unlike other DBMSs in ORACLE, a frequent COMMIT is harmful. Upon receipt of this command information, albeit in small quantities, should be recorded on disk. This process takes place as quickly as possible, but if you try to execute it after changing each entry for a 10 millionth table ... The system is designed to do COMMIT when it is necessary from the point of view of business logic, and not more often.
Rollback of a transaction (ROLLBACK) should be avoided as much as possible, as a rule, the time for rollback is often comparable to the time of changes. UPDATE was executed for an hour, we decided to roll back, ROLLBACK will also run for comparable time. A commit would “pass” in a fraction of a second.
• The second point, for some reason not obvious to many developers - changing data never blocks reading. It needs to be remembered, but in general the implementation of simultaneous access to data from the point of view of the developer requires a separate article.
• The third point - do not be afraid of joins, this is not a bad MySQL. Properly configured database will allow you to maintain data integrity and performance. HASH JOUN, MERGE JOIN, INDEX ORGANIZED TABLES, CARDINALITY, QUERY REWRITING and MATRIALIZED VIEW UPDATE ON COMMIT settings allow a knowledgeable admin to achieve good speed. But maintaining an inconsistent denormalized system in order in a couple of years will be an order of magnitude more difficult.
• And finally - use the bound variables! This will make it possible to significantly increase productivity at once, often several times and, as a free bonus, avoid SQL!
Conclusion - ORACLE has created a reliable DBMS, but how fast it will work depends on the developers. Non-optimal solutions will force any server to ask for mercy.
Source: https://habr.com/ru/post/164447/
All Articles