How to choose a database

Methods for selecting a database: 1. Target; 2. Functions; 3. How many people use it and understand concurrency issues; 4. Security and stability; 5. Operating system used.

##Related free learning recommendations: mysql video tutorial

How to choose a database:

Overall balance your needs from 5 aspects:

(1) Target

(2) Function

(3) How many people use it (concurrency issues)

(4) Security and stability

(5) Operations used System (UNIX, linux, windows)

The databases used 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 key business systems and want to achieve goals such as downtime/high availability/clustering, reliability (performance and data volume), disaster replication and automatic report generation, or need to achieve For extreme high-speed and low-latency (HPC, high-performance computing) effects, then we may need enterprise-class capabilities of the database, but the cost is also higher.

HA (High Availability) Cluster Function

The cluster functions of each database platform are as follows:

• Oracle RAC is most suitable for HA cluster, load balancing and expansion;

　• MS SQL Server provides HA failover function, but no load balancing;

　• Sybase ASE 15 cluster has equivalent functions to Oracle RAC;

　•TeraData itself is a distributed database platform;

　•MySQL Cluster claims TPC-C performance benchmark is higher than Oracle RAC;

　•IBM DB2/UDB on IBM P Series/Power running AIX HACMP (High Availability Cluster Multiprocessing) It runs very well on servers and clusters such as Veritas VCS/Sun.

　High performance/low latency/in-memory database

　• Oracle provides TimesTen in-memory database;

　•MySQL cluster uses in-memory storage engine;

　•Sybase Provide ASE memory database;

　• In addition, we can also use RAMSAN/SSD (solid state drive) to install the SQL Server database. All disk I/O related to the database occurs on the SSD instead of the traditional hard disk. , so we can greatly reduce disk I/O latency, improve I/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 are either Only supports embedded systems, or only supports single user or single connection at the same time.

　Scalability/Performance

　• When using Oracle RAC and Sybase ASE 15 cluster, if the existing hardware cannot meet the performance requirements, we only need to add a Use a more powerful server, and then gradually replace 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 cannot meet the If required, we must replace the entire cluster;

　• MySQL Cluster also supports adding nodes to the cluster where all nodes are active, but it is a shared-nothing cluster, and more nodes means more database copies. More storage and more network traffic required to replicate data across all nodes.

Replication

• Sybase: Sybase has the best replication solution, surpassing Oracle's Data Guard and SQL Server replication solutions (i.e. using its mirroring/log shipping/transactional replication , SQL Server's replication is still far worse than Sybase's replication server); one of the reasons.

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

　• SQL Server: SQL Server replication can be implemented through log shipping and database mirroring (active/passive, R/W or read-only), and supports object-level (table) granular transaction replication.

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

　• MySQL: MySQL provides shared-nothing clusters. Basically all active nodes are replicated synchronously. Its asynchronous replication also supports transaction and object-level replication.

　• Storage-level solutions: Using EMC's BCV, NetApp's Snapshot Manager, Hitachi's Snapshot, and Veritas' Volume Replicator, data replication can occur at the disk block level and volume level.

Hybrid system

Usually, the back-end database stores a large amount of data, and there will be performance problems when retrieving the data (data sorting, grouping, aggregation and calculation) Problem, when the running speed slows down, it will affect the data writing process.

Therefore, for these types of applications, we expect the database to handle large amounts of data well and also handle read/write blocking issues well.

　• Oracle

Oracle is the best choice for this kind of system because in Oracle, the read/write programs do not block each other and the data is kept throughout the read process/session/transaction. They are all consistent, and the system overhead mainly occurs in memory locks and redo logs.

　• SQL Server

SQL Server 2005's new snapshot isolation uses the same row versioning feature of Oracle, but will generate more IO in tempdb.

　 • Sybase ASE

The read/write programs of Sybase ASE will block each other, but Sybase IQ uses snapshot versioning management to solve this blocking problem.

　• 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 Durability) standards.

Data Warehouse

Businesses build data warehouses to store/archive all historical data, as well as the data they think needs to be placed in the database. Often, non-normalized data goes into the data Warehouse, multi-dimensional provides fast retrieval for OLAP and business intelligence (BI) solutions.

Data warehouses are also used for back-testing new or enhanced business strategies. In addition, the data in the data warehouse grows exponentially rather than linearly.

Advantages of column-based relational databases

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

Typical column-based databases include Google's Big Table, Sybase IQ, Vertica and KickFire built 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 when updating data based on the row level. So far, Sybase IQ seems to be the leader in this field. It is said that the largest data warehouse in the world (more than 1P bytes) uses Sybase IQ.

Generally speaking, row-based relational data (SQL Server, Oracle, Informix, DB2/UDB, MySQL, Sybase ASE, etc.) are 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 cannot be regarded as relational databases. Their storage structure is based on files. The data of each column is stored in a separate file, which is basically column-based. Databases, column-based databases can also be used for backtesting. Furthermore, data in row-based data warehouses (cubes) grows exponentially, but data in column-based data warehouses grows linearly.

Traditionally, Oracle has been a leader in the field of data warehouses, but now it has lost its advantage in the face of column-based relational databases (such as Sybase IQ, Vertica and KickFire). The Big Table that Google is using is also a Column-based database/storage system.

The above is the detailed content of How to choose a database. For more information, please follow other related articles on the PHP Chinese website!