Author | Jaana Dogan, Google Software Engineer Translator | Kenyue, Editor | Produced by Xiyan | CSDN Most computer systems have some state that needs to be saved to the storage system.

Author | Jaana Dogan, Google software engineer

translator | Kenyue, editor | Xiyan

Produced by | CSDN (ID: CSDNnews)

Most computer systems have some state that needs to be saved to the storage system. Over the years I have accumulated a lot of database knowledge, mostly lessons learned from design errors that lead to data loss and website offline. In a data-based system, the database is the core of the entire system design and the focus that needs to be weighed. Although the working principle of databases cannot be ignored, the problems many application developers have seen and experienced are still just the tip of the iceberg. In this post, I want to share some knowledge that I find particularly useful.

99.999% of the cases, the network time is not a problem, it is just luck

People often say that the network is very stable today, and some people argue that many systems are downtime due to network failures. Research in this area is now limited, and most of the research focuses on large organizations with independent networks, dedicated hardware and dedicated personnel.

Google's Spanner (Google's distributed database) implements 99.999% of the services online, and their research shows that only 7.6% of the problems are caused by the network, although they attribute high availability to dedicated networks. The 2014 Bailis and Kingsbury survey (https://cacm.acm.org/magazines/2014/9/177925-the-network-is-reliable/fulltext) challenged the "Train of Distributed Computing" published by Peter Deutsh in 1994 (https://en.wikipedia.org/wiki/Fallacies_of_distributed_computing). Is the network really reliable?

Apart from these giants, there is no complete investigation on the Internet. Large vendors also did not provide enough data on customer network failures. Failures caused by cloud providers’ networks can also cause some of the Internet to go down for hours, but this is only a small number of failures that many people can notice. Sometimes, many network downtimes that we don’t know can have a huge impact. Customers of cloud services may not be able to notice the problems they encounter. When the network goes down, the customer is not sure that the failure is caused by the vendor's network failure. For them, third-party services are black boxes. Only those suppliers themselves can estimate the scope of impact.

Compared with those systems of large enterprises, if the downtime caused by network problems in your system only accounts for a small proportion, then you can only say that you are lucky. The network will still be affected by various traditional problems, such as hardware failures, topology changes, management configuration changes, and power outages. But I recently heard that sharks can also cause network failures (https://twitter.com/rakyll/status/1249891472993693696).

ACID has many meanings

ACID refers to atomicity, consistency, isolation and durability. These are several features that database transactions need to be guaranteed. Only by ensuring these features can the data be guaranteed in extreme situations such as crashes, errors, hardware failures, etc. Without ACID or similar guarantees, application developers cannot determine which databases should be responsible for and which developers themselves should be responsible for. Most relational transactional databases try to follow ACID, but new standards such as NoSQL have spawned a large number of databases that do not guarantee ACID, because ACID implementation costs are quite expensive.

When I first entered the industry, I once argued with the technical leader whether ACID is outdated. It can be said that ACID is just a loose description, not a strict implementation standard. Today, I think ACID is very useful because it provides a large category of problems (and a large category of possible solutions).

Not every database supports ACID, and even databases that implement ACID have different interpretations. One of the reasons is that there are many compromises that need to be made to implement ACID. Databases usually claim to be ACID compliant, but in some extreme cases there will be different interpretations, or they will also handle those "unlikely" situations. Developers can at least understand how databases are implemented at a higher level, understand when they will have problems, and design trade-offs.The most controversial thing about

is MongoDB's ACID implementation (that debate remains even after the release of the fourth edition.) MongoDB has not supported logs for a long time, although it saves data to disk every 60 seconds (or even longer). Consider the following situation: The application issues two write requests (w1 and w2). Mongo can persist the first write request, but persistence of w2 will fail due to hardware failure.

This figure demonstrates the data situation when MongoDB fails before writing to disk.

submitting data to disk is an expensive operation, avoiding commits can be exchanged for performance improvements at the expense of durability. Today, MongoDB already supports logs, but dirty writes may still affect the persistence of data, because by default, MongoDB submits data every 100 milliseconds. Therefore, even with log support, similar situations can still occur, although the loss of changes in the event of a failure will be much less.

The consistency and isolation capabilities of each database are different

In the ACID attribute, consistency and isolation are the biggest difference between different implementations because there are more compromises in implementations. Consistency and isolation are both very expensive features. Both require coordination, and in order to ensure data consistency, both lead to competition. This problem becomes increasingly serious when it is necessary to scale horizontally at multiple data centers (especially multiple data centers spanning different geographical areas). For a more general explanation of this phenomenon, see CAP theory. It is worth pointing out that applications can handle some of the inconsistencies, and experienced programmers can also add extra logic, so they don't have to rely entirely on the database. The

database usually provides different isolation layers, so that application developers can choose the best cost-effective isolation layer to use. Weaker isolation is faster, but may trigger data competition. Strong isolation eliminates possible data competition, but it is slower and may introduce data conflicts, which slows down the database and may even lead to downtime.

Existing concurrency model and its relationship overview

SQL standard only defines four layers of isolation, although more isolation layers are also possible from both theoretical and practical perspectives. jepson.io (https://jepsen.io/consistency) provides another view of later concurrency models that you can read. For example, Google's Spanner guarantees external serialization with clock synchronization, and this is a stronger isolation layer that is not defined in the standard. The isolation level mentioned in the

SQL standard is as follows:

• serializable (strictest and most expensive): In the same transaction, sequential execution and parallel execution can produce the same effect. Serial execution refers to the execution and completion of each transaction before the next transaction begins. It should be noted that serialization is usually implemented as "snapshot isolation" (such as Oracle), and snapshot isolation is not the content of SQL standard.

• repeatable read operations: Read operations not submitted in the current transaction are only visible to the current transaction, while read operations caused by other transactions are invisible.

• read commit: Uncommitted read operation is not visible to other transactions. Only the submitted write operations are visible, but shadow reading may occur. If another transaction inserts and commits a new row, the current transaction may see new data.

• uncommitted read operation (not too strict, but relatively cheap): allow dirty reading, and transactions can see changes that other transactions have not yet committed. In practice, this level can be used to return approximate aggregate results, such as performing a COUNT(*) operation on a table. The serializable level of

allows the least data competition to occur, but it is the most expensive to implement and will introduce the most data competition to the system. Other isolation levels are cheaper, but will increase the possibility of data competition. Some databases allow themselves to set isolation levels, while others do not support certain isolation levels.

even if it claims to support certain isolation levels databases, it is necessary to carefully check its behavior and understand its actual operations:

implementations of different isolation levels of different databases.The implementation of

can be optimistic about locking

locks when it cannot keep the lock can be very expensive, not only because it introduces data competition, but also requires a stable connection between the application and the database server. Exclusive locks can be more severely affected by network partitions and lead to hard-to-identify and resolve deadlocks. If you cannot hold an exclusive lock, you can choose an optimistic lock.

optimistic lock refers to the record of the version number and the recently modified timestamp or its checksum when reading a row. You can then check if the version has no atomic changes before changing the record.

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

If another update changed the row before it is updated, the update to the product table will affect 0 rows. If there is no earlier update, it will affect 1 row and we can tell that our update has been successful.

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

When discussing data consistency, we usually focus on data competition that may lead to dirty reads and data loss. However, there are more than two abnormalities in the data. An example of an exception like

is writing skew. Write skews are difficult to identify because we are not looking for them specifically. The reason for write skew is not that dirty reads or data loss occurs, but that the logical constraints of the data are corrupted.

For example, suppose a monitoring application requires an operator to be on duty at any time.

In the above case, if both transactions are successfully committed, write skew occurs. Although no dirty reads occur, no data loss occurs, data consistency is also corrupted because both operators are assigned on-duty tasks.

serializable isolation, structural design or database constraints can help eliminate write skew problems. Developers should be able to identify this exception during development and avoid such data exceptions in production environments. That being said, it is very difficult to identify possible write skews in the code. This problem can occur even in large systems if different teams are responsible for different functions on the same table and lack communication with each other. The

database is inconsistent with my understanding of the order and one of the core functions of the

database is to ensure the order, but the order of the database understanding may be inconsistent with the order seen by the application developers. The order of transactions seen by the database is sorted by the time of reception, not the order the developer believes. In high concurrency systems, the order of execution of transactions is difficult to predict.

During development, especially when using non-blocking libraries, poor code style and readability can lead to problems: users think that transactions can be executed sequentially, even if they can reach the database in any order. The following program makes it appear that T1 and T2 will be called sequentially, but if these functions are non-blocking and are returned immediately with promises, the order of calls will depend on how long they are received in the database.

If atomicity is required (full commit or abandon all operations), and the order is important, operations T1 and T2 should run in the same database transaction. sharding of the

application layer may require the implementation of

sharding outside the application. It is a way to extend the database horizontally. Although some databases can automatically achieve horizontal scaling, some databases cannot or are not good at this function. If data architects and developers can predict how data points are accessed, horizontal sharding can be implemented at the user level instead of handing over the task to the database. This is called sharding of the application layer.

The name "application layer sharding" usually brings the wrong impression that shards exist between application services. However, the sharding function can be implemented as a layer above the database. Depending on how data grows and data structures iterate, sharding requirements can become very complex. It is ideal if certain strategies can be implemented iteratively without redeploying the application server.

Example of decoupling application servers from shard services

Using shards as a separate service can improve the ability to iterate sharding strategies without having to redeploy applications. One such example of an application-level sharding system is Vitess. Vitess provides horizontal sharding for MySQL and allows clients to connect to it through the MySQL protocol, and it shards data on various MySQL nodes that they do not know each other.

AUTOINCREMENT may be harmful

AUTOINCREMENT is a common way to generate primary keys. There are countless cases where it generates IDs, but some databases also use special ID generation methods. Here are some advantages and disadvantages of using self-increase and using dedicated primary key generation methods:

• In a distributed database system, self-increase may be difficult. A global lock is required to generate an ID. If a UUID is generated, no cooperation is required between the individual nodes of the database. Self-increasing with locks may lead to data conflicts and may lead to severe performance degradation in the distributed insertion operation. Some databases such as MySQL may require special configurations to correctly achieve self-increase in a dual-main architecture. This configuration is easy to make mistakes, resulting in write failures.

• Some databases have primary key-based partitioning algorithms. Sequential IDs can cause unpredictable hotspots and can overwhelm some partitions while others remain idle.

• The fastest way to access rows in a database is through its primary key. If there is a better way to identify records, the sequential ID may make the most important columns in the table meaningless values. Please select a globally unique natural primary key (such as username) where possible.

When using self-increment ID or UUID as an index, partition or shard, it is necessary to seriously consider which method is the most suitable.

outdated data may be useful and lock-free

Multi-version Concurrency Control (MVCC) introduces many of the consistency features we have discussed above. Some databases (such as Postgres, Spanner) use MVCC to ensure that each transaction sees a snapshot (a previous version of the database). Transactions and snapshots can still be serialized to ensure consistency. When reading from an old snapshot, you read outdated data.

can be useful to read slightly outdated data, such as generating analysis reports from the data, or calculating approximate aggregate values. The first benefit of

reading outdated data is delay (especially when the database is distributed in different physical regions). The second benefit of MVCC database is that it allows read-only transactions to be lock-free. If outdated data can be accepted, lock-free is the biggest advantage in applications where there are a large number of reads.

Even if the latest version exists in the database on the other side of the Pacific, the application server still reads old data from the local copy 5 seconds ago. The

database automatically cleans up old versions, and in some cases you can clean up as needed. For example, Postgres allows users to use VACUUM commands on demand, and also automatically performs cleaning every once in a while. Spanner performs garbage collection regularly to clean up older versions for more than an hour.

As long as the clock exists, clock skew occurs

The most secret secret in calculation is that the API of all time is a lie. Our machines cannot know exactly the current time. Our computers all contain a quartz crystal that produces a timing signal. But quartz crystals cannot accurately calculate time and will always be faster or slower than the actual clock. The drift every day can last up to 20 seconds. For accuracy, time on our computers needs to be synchronized with the actual time from time to time.

We use an NTP server to synchronize, but the synchronization itself may experience network delay. Synchronizing with an NTP server in the same data center can take some time, and syncing with a public NTP server may cause more bias.

atomic clock and GPS clock are better sources for determining current time, but they are too expensive and require complex setups to be installed on each machine. With these limitations in mind, data centers often use multi-layer approaches.Atomic clocks or GPS clocks can provide accurate timing, and their time will be broadcast to other machines through another server. This means that each machine will deviate to a certain extent from the actual current time.

has complex situations. Applications and databases are often located in different computers (and maybe even in different data centers). Not only can the database nodes distributed on several computers not reach a consensus on time, but the application server clock and database node clock cannot reach a consensus.

Google's TrueTime takes a different approach here. Most people think Google's advances in clocks can be attributed to the use of atomic clocks and GPS clocks, but that's only part of the reason. TrueTime actually works as follows:

• TrueTime uses two different sources: GPS and atomic clock. These clocks have different failure modes, so using both simultaneously can improve reliability.

• TrueTime uses a non-traditional API. It returns a time range where the actual time lies anywhere between the lower and upper bounds. Google's distributed database Spanner waits until it can be sure that the current time has exceeded a certain time. This method introduces some delay to the system, especially when the uncertainty of the main server broadcast is high, but can still provide correctness even in global distribution. The component of

Spanner uses TrueTime, where TT.now returns a time range, so that Spanner can insert sleep to ensure that the current time exceeds a certain time.

's confidence in the current time decreases, meaning that the Spanner operation may take more time. Therefore, while it is impossible to have an accurate clock, high reliability is still important for performance.

Delay has many meanings

Everyone has different understandings of the word "delay". In a database, latency is often called "database latency" rather than client-aware latency. The latency seen by the client is the sum of the database latency and network latency. It is crucial to be able to identify client and database latency when debugging escalating issues. Always consider using both when collecting and displaying metrics.

evaluation performance requires

for each transaction Sometimes, the database uses read and write throughput and latency as metrics when promoting its performance and limitations. In fact, when evaluating the performance of a new database, a more comprehensive approach is to evaluate critical operations (per query and/or per transaction) separately. For example:

• inserts a row in table X (including 50 million rows of data with constraints) and fills the write throughput and latency of the associated data.

• When the average number of friends per user is 500, query the delay of friends of friends of a given user.

• When each user subscribes to 500 accounts and each account has X messages per hour, the delay of obtaining the latest 100 records in the timeline.

reviews and experiments should include such extreme use cases to be sure whether the database can meet your performance requirements. A similar rule of thumb is to use this kind of nuanced use case when collecting latency data and setting service level goals.

nested transactions do more harm than good

Not every database supports nested transactions, but when nested databases support nested transactions, nested transactions can lead to unexpected programming errors, which are usually difficult to detect until you find data exceptions.

If you want to avoid nested transactions, the client library can usually help you detect and avoid nested transactions. If it is not possible to avoid, care must be taken to avoid unexpected situations in which sub-transactions can cause unexpected aborts of committed transactions.

encapsulating transactions in different layers can lead to surprisingly nested transaction cases, and its intentions will be difficult to understand from a readability standpoint.Take a look at the following program:

What is the result of the above code? Will it roll back two transactions, or will it only roll back inner transactions? What if this code depends on multiple layers in the library, each encapsulating transactions? How can we identify and improve this situation?

Imagine a data layer containing multiple operations (such as newAccount) that has implemented its own transaction processing. The data layer can implement high-level operations without creating transactions by itself. The business logic can then initiate a transaction, perform operations in a transaction, and then commit or abandon.

transactions should not maintain application state

Application developers may want to use application state in transactions to update specific values, or modify query parameters. A key factor to consider is to use the correct scope. Clients usually retry the entire transaction when a network problem occurs. If a transaction depends on a state that may be modified elsewhere, the transaction may read the wrong value when the data race occurs. Transactions should pay attention to data competition within the application.

The above transaction will add sequence numbers each time it runs, regardless of the actual execution result. If a network failure causes a submission to fail, then the second attempt will be queryed using a different sequence of numbers.

query planner can report database information

query planner can give the way of execution of queries in the database. It also analyzes the queries and optimizes them before execution. The planning period can only provide some rough estimates. How can I get the following query results?

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

There are two ways to get the results:

• Full table scan: Scan every record in the table, return all articles that match the author's name, and then sort

• Index scan: Use the index to find the matching ID, get these rows, and then sort

The responsibility of the query planner is to judge which method is the most suitable. The query planner gets a limited signal, which can lead to undesirable decisions. DBAs or developers should use this information to diagnose and tune inefficient queries. The new version of the database can adjust the query planner and perform self-diagnosis. Slow query reports, delayed problem reports, execution time reports, etc. also help optimize queries. Some metrics provided by the

query planner can be confusing, especially those regarding latency or CPU time. As an aid to the query planner, the tracking and execution path tools also help diagnose these problems. But not every database provides these tools.

online migration is very complicated, but it is not impossible

real-time online migration means migrating from one database to another, without stopping the service in the middle, and ensuring the correctness of the data. If you migrate between the same databases, online migration will be easier, but if you use a new database, coupled with different performance requirements and table structures, migration will become more complex. There are different models for online migration of

, and the following is one of them:

• starts to double write to both databases. At this stage, the new database will not have all the data, but the new data will appear in the new database. Once you have confidence in this step, you can continue with the second step.

• simultaneously enables the read path on both databases.

• mainly uses the new database for read and write operations.

• stops writing operations on the old database, but the read operations are still on the old database. At this time, the new database does not contain all new data, and when reading the old data, the old database may still be needed.

• At this time, the old database is read-only. Fill in the missing data in the new database. After the migration is completed, all read and write paths can use the new database, and the old database can be removed.

If you need more case studies, you can read this detailed article by Stripe (https://stripe.com/blog/online-migrations). The significant growth of the

database shows that uncertainty

database growth will bring unpredictable scale problems. The more you understand the principles of the database, the less we have foresee the scale, but some things are unpredictable. The growth of the

database will cause previous assumptions about data size and network capacity requirements to become invalid. At this time, large-scale structural modifications, large-scale operation improvements, capacity issues improvements, reconsideration of deployment, migration to other databases, etc. are needed to avoid service interruptions.

Never assume that you just need to understand the internal principles of the current database, because scale will cause new problems. Unpredictable hotspots, unbalanced data distribution, unpredictable capacity and hardware issues, growing traffic and new network partitions will force you to reconsider issues such as databases, data models, deployment models and deployment scale.

Original link:

https://medium.com/@rakyll/things-i-wished-more-developers-knew-about-databases-2d0178464f78

This article is a CSDN translated article. Please indicate the source when reprinting.

new medal, new prize, high traffic, and more benefits are waiting for you~

☞360 Finance new chief scientist: Don’t expect AI Lab to make middle-end

☞ Understand microservices, start with catching a wild boar

☞AI images intelligently repair old photos, and the effect is amazing! | Attached code

☞ Surveyed 10,975 Go language developers, and we made these discoveries!

☞The senior architect tells you: How can you write the code easily by yourself? It is not painful for others to read