Shulou(Shulou.com)06/02 Report--

Editor to share with you how to choose the database, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

The method of selecting the database: 1, target; 2, function; 3, how many people use, understand the concurrency problem; 4, security and stability; 5, the operating system used.

How to select a database:

Overall, balance your needs in five aspects:

(1) subject matter

(2) function

(3) how many people use it (concurrency problem)

(4) Safety and stability

(5) operating system used (UNIX, linux, windows)

For used databases, there are:

DBase/FoxBase/FoxPro in the DOS/Novell/Windows 3.x world

MS SQL Server

Oracle

Sybase SQL/ASE Servers

Sybase IQ

Informix/Dynamic

MySQL

PostGreSQL/EnterpriseDB

Choices for some key requirements:

Mission critical (OLTP) and high performance systems

If we need the database to play a more important role in critical business systems and want to achieve the goals of time between failures / high availability / clustering, reliability (performance and data volume), disaster replication and automatic report generation, or if we need to achieve extremely high speed and low latency (HPC, high performance computing), then we may need the enterprise functionality of the database, but it is also more expensive.

HA (high availability) cluster function

The clustering functions of each database platform are as follows:

Oracle RAC is best suited for HA clustering, load balancing and expansion

MS SQL Server provides HA failover, but no load balancing

Sybase ASE 15 cluster is equivalent to Oracle RAC.

TeraData itself is a distributed database platform.

MySQL cluster claims that TPC-C performance benchmark is higher than Oracle RAC

IBM DB2/UDB runs well on clusters such as IBM P-Series / Power servers and Veritas VCS/Sun that run AIX HACMP (highly available Cluster Multiprocessing).

High performance / low latency / in-memory database

Oracle provides TimesTen in-memory database

MySQL clusters use memory storage engine

Sybase provides ASE in-memory database

In addition, we can also install the SQL Server database using RAMSAN/SSD (solid state drive). All the disks related to the database occur on the SSD, not on the traditional hard disk, so we can greatly reduce the disk Imax O latency, provide the Imax O throughput, and improve the overall performance of the database.

There are other in-memory databases, such as SQLite or eXtremeDB, but most of them either support only embedded systems or support only a single user or a single connection at the same time.

Scalability / performanc

When using Oracle RAC and Sybase ASE 15 clusters, if the existing hardware does not meet the performance requirements, we only need to add a more powerful server to the cluster and then gradually replace the other nodes

When using SQL Server cluster, because it only supports active / passive mode, there is no scalability at the instance level. If the existing hardware can not meet the needs, we must replace the entire cluster.

The MySQL cluster also supports adding nodes to a cluster where all nodes are active, but it is a shared cluster, and more nodes means more copies of the database, more storage, and more network traffic when replicating data between all nodes.

Copy

Sybase:Sybase has the best replication solution, surpassing Oracle's data Guardian and SQL Server's replication solution (that is, using its mirror / log transfer / transaction replication, SQL Server replication is still far worse than Sybase's replication server)

Heterogeneous replication and homogenous replication of Sybase is one of the main reasons why Sybase was excluded from Wall Street in the 1990s but was used by many large companies.

Sybase replication can be done at the database level (active / active replication, better than SQL Server mirroring), transaction level (similar to SQL Server transaction replication), table level, stored procedure level, function level, MSA (multi-site available), and through log shipping (solution for the poor in the replication world), its mirror activator combined with EMC/SRDF can truly guarantee ZDL (zero data loss) during planned and unplanned downtime.

SQL Server:SQL Server replication can be achieved through log shipping and database mirroring (active / passive, RCMW or read-only) and supports object-level (table) granularity transaction replication.

Oracle:Oracle 's data Guardian is one of the best solutions for database cloning and disaster recovery (using RMAN).

MySQL:MySQL provides no shared clusters, basically all active nodes are replicated synchronously, and its asynchronous replication also supports transaction and object-level replication.

Storage-level solution: using EMC's BCV,NetApp snapshot manager, Hitachi's snapshot, Veritas's volume replicator, data replication can occur at the disk block level and volume level.

Hybrid system

In general, the back-end database stores a large amount of data, and there will be performance problems when retrieving data (data sorting, grouping, summarization and calculation). When the running speed becomes slower, it will affect the data writing process.

Therefore, for these types of applications, we hope that the database can handle large amounts of data as well as read / write blocking.

Oracle

Oracle is the best choice for such a system, because in Oracle, readers / writers do not block each other, data is consistent throughout the read process / session / transaction, and the system overhead mainly occurs on memory locks and redo logs.

SQL Server

The new snapshot isolation for SQL Server 2005 uses the same line versioning capabilities of Oracle, but produces more IO in tempdb.

Sybase ASE

Sybase ASE's reader / writer programs block each other, but Sybase IQ solves this blocking problem by using snapshot versioning management.

Other databases

There are other databases that do not have blocking problems, but some of them do not even comply with the ACID (atomicity, consistency, isolation, and persistence) standards.

Data warehouse

Enterprises set up data warehouses to store / archive all historical data, as well as data they think need to be placed in the database. Usually, non-standardized data enters the data warehouse, and multi-dimensions provide fast retrieval for OLAP and business intelligence (BI) solutions.

The data warehouse is also used for new or enhanced business strategy backtracking testing, in addition, the data in the data warehouse is growing exponentially and nonlinearly.

Advantages of column-based relational database

Column-based relational databases have great advantages because they are inherently multidimensional and each column is self-indexed (B tree).

Typical column-based databases are Big Table,Sybase IQ,Vertica for Google and KickFire based on MySQL.

On average, these column-based databases are 60 to 100 times faster in data retrieval than traditional row-based relational databases such as Oracle,SQL Server,Sybase ASE,TeraData,DB2 and MySQL, but they are slower in updating data at the row level. So far, Sybase IQ seems to be the leader in this field, claiming that the world's largest data warehouse (more than 1p bytes) uses Sybase IQ.

Generally speaking, row-based relational data (SQL Server,Oracle,Informix,DB2/UDB,MySQL,Sybase ASE, etc.) is suitable for OLTP applications, while column-based relational databases (Sybase IQ,KickFire,Vertica, etc.) are more suitable for OLAP/DSS and data warehouse applications.

Note: those time series databases such as OneTick,kdb+ and Vhayu are not relational databases, their storage structure is file-based, each column of data is stored in a separate file, which is basically a column-based database, and column-based databases can also be used for backtracking testing. In addition, the data in the row-based data warehouse (cube) grows exponentially, but the data in the column-based data warehouse grows linearly.

Traditionally, Oracle has a leading advantage in the field of data warehouse, but now it has lost its advantage over column-based relational databases such as Sybase IQ,Vertica and KickFire. Big Table, which Google is using, is also a column-based database / storage system.

The above is all the contents of the article "how to choose a database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.