Shulou(Shulou.com)05/31 Report--

This article will explain in detail what the knowledge about the database is, and the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Most computer systems are stateful and may depend on storage systems. As time went on, I learned more about the database at the expense of data loss and interruption caused by our design errors. In the system with a large amount of data, the database is the core of the system design goal. Although it is impossible for developers to know nothing about databases, the problems they foresee and experience are often just the tip of the iceberg. I will share some insights that are very useful for developers who are not good at the database domain.

If there is nothing wrong with the network 99.999% of the time, you are lucky.

Nowadays, on the one hand, people think that the network is very reliable, on the other hand, the situation of system downtime caused by network outage is very common. There is not much research work in this area, and it is usually led by large companies, which use dedicated networks and dedicated staff equipped with custom hardware.

Google services have 99.999% availability, and they claim that only 7.6% of Spanner (Google's distributed database) problems are caused by the network, although they have always believed that private networks are the core support behind their usability. In 2014, a survey by Bailis and Kingsbury challenged one of the distributed computing fallacies proposed by Peter Deutsch in 1994: is the network really reliable?

We cannot conduct a comprehensive survey, and suppliers will not provide enough data to show how many customers' problems are caused by the network. We often experience network outages of large cloud providers, causing some of the networks to go down for hours, with a large number of visible affected customers and many that we can't see. Network outages may affect more, although not all events have a significant impact. Cloud computing customers may not be able to see these problems either. When problems arise, they are less likely to think it has something to do with the supplier's network error. For them, third-party services are black boxes. If you are not a supplier, it is impossible to estimate the true impact.

Compared with the vendor's report, you are lucky if only a small part of your system outage is related to network problems. The network is still affected by traditional problems, such as hardware failures, topology changes, management configuration changes, and power failures. But only recently have I learned that some newly discovered problems, such as sharks biting off submarine cables, have also become major influencing factors.

ACID is not as simple as it seems.

ACID stands for atomicity, consistency, isolation, and persistence. Even in the event of a crash, error, hardware failure, and so on, the database needs to ensure that these properties are valid. Most relational transaction databases provide ACID guarantees as much as possible, but many NoSQL databases do not have ACID transaction guarantees because of the high cost of implementation.

When I first entered the industry, our technical director wondered if ACID was an outdated concept. It can be said that ACID is regarded as a general concept rather than a strict enforcement standard. Now, I find it very useful because it provides a class of problems and a class of potential solutions.

Not every database is compatible with ACID, and the interpretation of ACID may vary among ACID-compatible databases. One of the reasons for the difference is the degree of tradeoff involved in implementing ACID. The database may claim to be ACID-compliant, but it is handled differently in some marginal cases or in the face of "unlikely" problems.

MongoDB's ACID performance has been controversial, even after the v4 release. MongoDB does not support logging for a long time. In the following case, the application writes twice (W1 and W2), and MongoDB can persist W1, but cannot persist W2 because of a hardware failure.

MongoDB crashes before writing data to the physical disk, resulting in data loss

Committing data to disk is an expensive process that claims good write performance at the expense of persistence at the expense of frequently committing data. Now, MongoDB has logging, but dirty writes still affect the persistence of the data, because logs are committed only once every 100ms by default. Even if the risk is greatly reduced, the persistence and changes of journals can still have the same problems.

Different databases have different consistency and isolation capabilities.

Among the attributes of ACID, consistency and isolation levels are implemented in the most ways, because the tradeoffs are the largest. In order to maintain data consistency, databases need to be coordinated, and contention for resources will increase. It becomes very difficult when you need to scale horizontally between multiple data centers, especially between different geographic areas. With the decline in availability and the frequent occurrence of network partitions, it is very difficult to provide a high level of consistency. For an in-depth explanation of this problem, see CAP Theorem. It is important to note, however, that the application can do something about data consistency, or the programmer may know enough about this to add additional logic to the application instead of relying heavily on the database.

Databases usually provide a variety of isolation levels, and application developers can choose the most cost-effective isolation level based on tradeoffs. Weaker isolation levels may be faster, but data race issues may be introduced. A stronger isolation level eliminates some potential data race problems, but is slow, and may introduce resource contention, making the database slow to downtime.

An overview of existing concurrency models and their relationships

The SQL standard defines only four isolation levels, although there are more available both in theory and in practice. If you want to learn more, jepson.io provides more introduction to existing concurrency models. Google's Spanner ensures external serialization of clock synchronization, even though this is a stricter isolation level, but it is not defined in the standard isolation level.

The isolation levels mentioned in the SQL standard are:

Serialization (the strictest and most expensive): the effect of serialization is the same as the serial execution of transactions. Serial execution means that each transaction is completed before the next transaction starts. It should be noted that serialization is usually implemented as "snapshot isolation" (such as Oracle) due to differences in interpretation, but there is no "snapshot isolation" in the SQL standard.

Repeatable: uncommitted reads in the current transaction are visible to the current transaction, but changes made by other transactions, such as newly inserted rows, are not visible.

Read committed: uncommitted reads are not visible to the transaction. Only committed writes are visible, but phantom reads may occur. If another transaction inserts and commits new rows, the current transaction can see them when querying.

Read uncommitted (least stringent, lowest cost): dirty reads are allowed, and transactions can see uncommitted changes from other transactions. In fact, this level is useful for returning approximate aggregations, such as COUNT (*) queries.

The serialization level minimizes the chance of data competition, although it is the most expensive and brings the most contention to the system. Other isolation levels are less expensive, but increase the likelihood of data competition. Some databases allow you to set isolation levels, and some databases do not necessarily support all isolation levels.

Support for isolation levels in various databases

Use optimistic locks

The cost of using database locks is very high, they not only introduce more contention, but also require a stable connection between the application server and the database. Exclusive locks are more affected by network partitions and can lead to deadlocks that are difficult to identify and resolve. In this case, consider using optimistic locks.

An optimistic lock is to write down its version number, recently modified timestamp, or checksum when reading a row of data. You can then check to see if the version has changed before modifying the record.

UPDATE products SET name = 'Telegraph receiver', version = 2 WHERE id = 1 AND version = 1

If a previous update operation modifies the products table, the current update operation will not modify any data. If it has not been modified before, the current update operation will modify a row of data.

In addition to dirty reading and data loss, there are other exceptions

When discussing data consistency, we focus on race conditions that can lead to dirty reads and data loss. But in addition to these, we should also pay attention to abnormal data.

An example of such an exception is write skew (write skew). Write skew does not occur when a dirty read or data loss occurs during a write operation, but when the logical constraints of the data are broken.

For example, suppose you have a monitoring application that requires at least one operator to be available on call.

In the above case, a write skew occurs if the two transactions are successfully committed. Even if there is no dirty read or data loss, the integrity of the data is lost because two people are assigned to be on call.

Serialization of isolation levels, schema design, or database constraints may help eliminate write skew. Developers need to identify these exceptions during development to avoid this problem in a production environment. Having said that, it is very difficult to identify write skew directly from the code. Especially in large systems, it is more difficult to find problems if different teams use the same tables but do not communicate with each other and do not check how to access the data.

Order problem

One of the core functions provided by the database is sequence assurance, but this is also a surprise for application developers. The database arranges the order in the order in which transactions are received, not in the order written in the code. The order in which transactions are executed is difficult to predict, especially in large-scale concurrent systems.

During development, especially when using non-blocking development libraries, poor readability can lead to problems where users believe that transactions are executed sequentially, but transactions may arrive at the database in any order. The following code looks like calling T1 and T2 sequentially, but if these functions are non-blocking and immediately return promise, the actual order of calls will be determined by the time they arrive at the database.

Result1 = T1 () / / returns promise result2 = T2 ()

If atomicity is required (commit completely or abort all operations) and order is important, then T1 and T2 should be included in a single database transaction.

Application-level fragmentation can be done outside the application

Sharding is a method of horizontally partitioning a database. Although some databases can automatically partition data horizontally, some databases will not do so or may not be good at doing so. When data architects or developers can predict data access patterns, they may partition horizontally on the client side rather than on the database side, which is called application-level fragmentation.

Application-level shredding usually gives the wrong impression that shards should exist in the application. In fact, the sharding function can be used as a layer in front of the database. As data grows and patterns iterate, sharding requirements may become more and more complex.

An example architecture of separation of application server and sharding service

Using sharding as a separate service can improve the iterative ability of the sharding strategy without redeploying the application. Vitess is a good example of this. Vitess provides horizontal sharding capability for MySQL, and the client can connect to Vitess,Vitess through MySQL protocol. The data will be sliced on each MySQL node.

Https://youtu.be/OCS45iy5v1M?t=204

Auto increment ID has "poison"

Automatic increment is a common way to generate primary keys. It is not uncommon to use a database as an ID generator and create tables with ID generation in the database. However, generating primary keys by automatically incrementing may not be ideal for the following reasons:

In distributed database system, automatic increment is a difficult problem. You need a global lock to generate ID, but if you can generate UUID, you don't need to coordinate database nodes. Using auto-increment with locks can introduce contention and may significantly degrade distributed write performance. A database such as MySQL may require a specific configuration and ensure the correctness of the primary primary replication. However, the configuration is error-prone and can cause write interruptions.

Some databases have partitioning algorithms based on primary keys. Sequential ID can cause unpredictable hotspots, causing some partitions to have too much data while others are idle.

The fastest way to access the database is to use the primary key. If you use other columns to identify records, sequential ID may become meaningless. So, whenever possible, choose a globally unique natural primary key (such as a user name).

Before deciding which method is better for you, consider the impact of automatically incrementing ID and UUID on indexes, partitioning, and sharding.

Unlocked stale data is very useful

Multi-version concurrency control (MVCC) can support many of the above consistency capabilities. Some databases (such as Postgres, Spanner) use MVCC to allow each transaction to view snapshots, that is, older versions of the database. These transactions can be serialized to maintain consistency. When you read data from an old snapshot, you read stale data.

It is also useful to read slightly older data, such as generating analysis reports based on the data or calculating approximate aggregate values.

The first benefit of reading stale data is latency (especially if the database is distributed in different geographic areas). The second advantage of the MVCC database is that it allows read-only transactions to be unlocked. If reading stale data is acceptable, this is a major advantage for applications that focus on reading large amounts.

The application server reads stale data from 5 seconds ago from the local copy, even if the latest version is available on the other side of the Pacific

Databases automatically purge older versions, and in some cases, they allow cleaning on demand. For example, Postgres allows users to clean up on demand, or automatically at regular intervals, while Spanner uses a garbage collector to clean up stale data for more than an hour.

Clock tilt occurs between any clock-related resources

The most secret of computing systems is that API "lies" all the time. Computers cannot accurately know the current time. They all have a quartz crystal that produces timing signals, but quartz crystals cannot time accurately, either faster or slower than the actual clock. The daily time drift can be as long as 20 seconds. To be accurate, the time on the computer needs to be synchronized with the actual time from time to time.

The NTP server is used to synchronize time, but the synchronization itself may be delayed due to the network. Synchronizing NTP servers in the same data center takes some time, while synchronizing with public NTP servers is likely to be more skewed.

Atomic and GPS clocks are better sources for determining the current time, but they are expensive and require complex settings that cannot be installed on each machine. Given these limitations, the data center uses a multi-tier approach. Although the atomic clock and the GPS clock provide the exact time, their time is broadcast to other machines through the secondary server. This means that each machine will tilt in some order of magnitude with the actual time.

Applications and databases are usually located on different machines, not only the database nodes distributed on multiple machines can not agree on the time, but also the application server clock and the database node clock.

Google's TrueTime takes a different approach. Most people attribute Google's progress in clocks to their use of atomic clocks and GPS clocks, but that's only part of the reason. TrueTime actually does these things:

TrueTime uses two different sources: GPS and atomic clocks. These clocks have different failure modes, so using them at the same time improves reliability.

TrueTime has an unconventional API that returns time as an interval, which can be any point between the lower limit and the upper limit. Google's distributed database, Spanner, can wait until it is determined that the current time has exceeded a certain time.

The Spanner component uses TrueTime,TT.now () to return a time interval, and Spanner can sleep to ensure that the current time has passed a specific timestamp.

The delay is not as simple as it seems.

If you ask 10 people in a room what "delay" means, they may have different answers. In a database, latency usually refers to "database latency" rather than the latency perceived by the client. The client can see the database latency and network latency. When debugging problems, it is important to be able to identify client latency and database latency. Always take both into account when collecting and displaying metrics.

Evaluate the performance requirements of each transaction

Sometimes, databases describe their performance characteristics and limitations in terms of read and write throughput and latency. However, when evaluating database performance, a more comprehensive approach is to evaluate each key operation (query or transaction). For example:

What is the write throughput and latency of inserting new rows into a table X (which already has 50 million rows) and updating the related table?

What is the delay when querying a user's friend's friend when the average number of friends is 500?

When a user subscribes to 500 accounts (with X updates per hour), what is the delay when querying the first 100 records in the user's timeline?

The performance assessment may include these situations until you are sure that the database can meet your performance requirements.

Be careful of high cardinality when collecting metrics. If you need high cardinality debug data, use logs or even distributed trace information.

Nested transactions are risky

Not every database supports nested transactions. Nested transactions can lead to unexpected programming errors that are not easy to identify until an exception is thrown.

Nested transactions can be detected and avoided on the client side. If it is unavoidable, care should be taken to avoid unexpected situations in which committed transactions are aborted unexpectedly due to subtransactions.

Encapsulating transactions at different layers can lead to unexpected nested transactions, which may be difficult to understand from a readability point of view. Look at the following example:

With newTransaction (): Accounts.create () with newTransaction (): Accounts.create () throw Rollback ()

What is the result of this code? Does it roll back two transactions or only internal transactions? What happens if we use multi-tier libraries to encapsulate transactions? Can we identify and improve this situation?

Assuming that a data layer already implements multiple operations (such as newAccount) in a transaction, what happens when you run them in a transaction of business logic? What are the characteristics of isolation and consistency at this time?

Function newAccount (id string) {with newTransaction (): Accounts.create (id)}

Instead of dealing with this problem, avoid using nested transactions. The data layer can still implement its own operations, but there is no need to create transactions. The business logic can then start, execute, commit, or abort the transaction.

Function newAccount (id string) {Accounts.create (id)} / / in the main program: with newTransaction (): / / read some configuration data from the database / / call ID service to generate ID Accounts.create (id) Uploads.create (id) / / create a user upload queue transaction should not depend on the application status

Application developers may use application state to update certain values or set query parameters in a transaction, so pay attention to the scope. When network problems occur, the client often retries the transaction. If the state of the transaction dependency is modified elsewhere, the wrong value is used.

Var seq int64 with newTransaction (): newSeq: = atomic.Increment (& seq) Entries.query (newSeq) / / other operations

Regardless of the end result, the above transaction increments the sequence number each time it runs. If the submission fails due to network reasons, it will be queried with a different serial number on the second retry.

The role of the query plan

The query plan determines how the database will execute the query. They also analyze and optimize the query before executing it. The query plan can only provide some possible estimates based on certain signals. For example, the following query:

SELECT * FROM articles where author = "rakyll" order by title

There are two ways to get the results:

Full table scan: we can traverse each record in the table and return articles that match the author's name, and then sort by title.

Index scan: we can use an index to find matching ID, get these rows, and then sort them.

The role of the query plan is to determine the best execution strategy. But the number of signals available for prediction is limited, so it can lead to wrong decisions. DBA or developers can use them to diagnose and tune poor performance queries. Slow query logs, latency issues, or execution time statistics can be used to identify queries that need to be optimized.

Some of the metrics provided by the query plan may not be very accurate, especially in terms of estimating latency or CPU time. As a supplement to query plans, tracking and execution path tools are more useful in diagnosing these problems, but not every database provides these tools.

Online migration is complex, but there are still signs to follow.

Online or real-time migration is migration from one database to another without downtime and without affecting the correctness of the data. It is easier to migrate to the same database or engine in real time, but it is much more complicated to migrate to new databases with different performance characteristics and schema requirements.

There are some patterns to follow for online migration:

Perform a double write operation on both databases. At this stage, the new database does not contain all the data, but will contain new data. After this step is safe, you can move on to the second step. Enable query paths for both databases.

Let the new database take on the main read and write tasks.

Stop writing to the old database, but you can continue to read from the old database. At this point, the new database still does not contain all the data, and to read the old data, you still need to get it from the old database.

At this point, the old database is read-only. Populate the missing data in the new database with the data from the old database. After the migration is complete, all read and write paths can use the new database, and the old database can be removed from the system.

Unpredictability brought about by the growth of database size

The growth of databases can cause unpredictable scalability problems.

As the database grows, previous assumptions or expectations about data size and network capacity may become obsolete, such as large scheme refactoring, large-scale operations and maintenance improvements, capacity issues, changes in deployment plans, or migration to other databases to avoid downtime.

Don't think it's enough to understand the internal structure of the database, because scalability brings new unknown problems. Unpredictable data hotspots, uneven data distribution, unexpected capacity and hardware problems, growing traffic, and new network partitions all force you to rethink your database, data model, deployment model, and deployment scale.

What about the database knowledge is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.