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

The property MySQLPostgreSQL instance starts the instance by executing the MySQL command (mysqld). An instance can manage one or more databases. A server can run multiple mysqld instances. An instance manager can monitor individual instances of mysqld. Start the instance by executing the Postmaster process (pg_ctl). An instance can manage one or more databases that form a cluster. A cluster is an area on disk that is initialized at installation time and consists of a directory in which all data is stored. Create the first database using initdb. Multiple instances can be started on a single machine. A database database is a named collection of objects, entities that are separate from other databases in the instance. All databases in an MySQL instance share the same system catalog. A database is a named collection of objects, and each database is an entity separate from other databases. Each database has its own system catalog, but all databases share pg_databases. The data buffer sets the data buffer through the innodb_buffer_pool_size configuration parameter. This parameter is the number of bytes in the memory buffer that InnoDB uses to cache table data and indexes. On a dedicated database server, this parameter can be set to up to 80% of the machine's physical memory. Shared_buffers cache. 64 buffers are allocated by default. The default block size is 8K. You can update the buffer cache by setting the shared_buffers parameter in the postgresql.conf file. When the database connection client connects to the database using CONNECT or USE statements, you can specify the database name, as well as the user id and password. Use roles to manage users and user groups in the database. When the client connects to the database using the connect statement, you can specify the database name, as well as the user id and password. Use roles to manage users and user groups in the database. Authentication MySQL manages authentication at the database level. Basically only password authentication is supported. PostgreSQL supports a wealth of authentication methods: trust authentication, password authentication, Kerberos authentication, Ident-based authentication, LDAP authentication, PAM authentication encryption can specify a password at the table level to encrypt the data. You can also use the AES_ENCRYPT and AES_DECRYPT functions to encrypt and decrypt column data. Network encryption can be achieved through a SSL connection. Columns can be encrypted / decrypted using functions in the pgcrypto library. Network encryption can be achieved through a SSL connection. The audit can grep the querylog. You can use PL/pgSQL triggers on the table for auditing. Query interpretation uses the EXPLAIN command to view the interpretation plan of the query. Use the EXPLAIN command to view the interpretation plan for the query. Backup, recovery, and logging InnoDB uses pre-write (write-ahead) logging. Full online and offline backups as well as crash and transaction recovery are supported. Third-party software is required to support hot backup. Maintain pre-write logs in a subdirectory of the data directory. Support for online and offline full backups as well as crash, point-in-time, and transaction recovery. Hot backup can be supported. The JDBC driver can download the JDBC driver from Resources. You can download the JDBC driver from Resources. The table type depends on the storage engine. For example, the NDB storage engine supports partitioned tables and the memory engine supports memory tables. Temporary tables, regular tables, and partitioned tables of scope and list types are supported. Hash partition tables are not supported. Because PostgreSQL's table partitioning is accomplished through a system of table inheritance and rules, a more complex partitioning approach can be implemented. The type of index depends on the storage engine. MyISAM:BTREE,InnoDB:BTREE . B-tree, hash, R-tree, and Gist indexes are supported. Constraints support primary key, foreign key, unique and non-null constraints. Check constraints are resolved, but not enforced. Primary key, foreign key, unique, non-null, and check constraints are supported. Stored procedures and user-defined functions support CREATE PROCEDURE and CREATE FUNCTION statements. Stored procedures can be written in SQL and C++. User-defined functions can be written in SQL, C, and C++. There are no separate stored procedures, they are all implemented through functions. User-defined functions can be written in PL/pgSQL (proprietary process language), PL/Tcl, PL/Perl, PL/Python, SQL, and C. Triggers support pre-row triggers, post-row triggers and statement triggers, and trigger statements are written in procedural language compound statements. Supports pre-row trigger, post-row trigger and statement trigger, and the trigger process is written in C. System profile my.confPostgresql.conf database configuration my.confPostgresql.conf client connection file my.confpg_hba.confXML supports limited XML support. Limited XML support. Data access and management server OPTIMIZE TABLE-reclaims unused space and removes fragmentation of data files

Myisamchk-analyze-updates the statistics used by the query optimizer (MyISAM storage engine)

Mysql-Command Line tool

MySQL Administrator-- client GUI tool Vacuum-- reclaims unused space

Analyze-updates the statistics used by the query optimizer

Psql-Command Line tool

PgAdmin-client GUI tools concurrently control supporting table-level and row-level locks. The InnoDB storage engine supports READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, and SERIALIZABLE. Use the SET TRANSACTION ISOLATION LEVEL statement to set the isolation level at the transaction level. Support table-level and row-level locks. The supported ANSI isolation levels are Read Committed (the default-- you can see a snapshot of the database when the query starts) and Serialization (similar to Repeatable Read-- you can only see the results committed before the transaction starts). Use the SET TRANSACTION statement to set the isolation level at the transaction level. Use SET SESSION to set up at the session level.

The main advantages of PostgreSQL:

1. PostgreSQL is completely free, and it is a BSD agreement. If you change the PostgreSQL and then sell it for money, no one cares about you, which shows that the PostgreSQL database will not be controlled by other companies. Needless to say, oracle database is a commercial database, not open. Although the MySQL database is open source, but now as SUN is acquired by oracle, it is now basically controlled by oracle. In fact, before SUN was acquired, the most important InnoDB engine in MySQL was also controlled by oracle, while a lot of important data in MySQL are placed in the InnoDB engine, anyway, our company is like this. So if the market scope of MySQL conflicts with the market scope of oracle database, there is no doubt that oracle will sacrifice MySQL.

two。 There are many open source software cooperating with PostgreSQl, and there are many distributed cluster software, such as pgpool, pgcluster, slony, plploxy and so on. It is easy to do read-write separation, load balancing, data level splitting and other solutions, but this is more difficult under MySQL.

3. The PostgreSQL source code is written clearly and is much more readable than MySQL. It is suspected that the MySQL source code has been confused. So many companies are the basic PostgreSQL to do secondary development.

4. PostgreSQL is better than MySQL in many aspects, such as complex SQL execution, stored procedures, triggers, indexes. At the same time, PostgreSQL is multi-process, while MySQL is linear. Although the processing speed of MySQL is fast when concurrency is not high, when concurrency is high, the overall processing performance of MySQL is not as good as PostgreSQL on a single machine with multiple cores. The reason is that MySQL threads can not make full use of the power of CPU.

That's all I think about at present. I'd like to add it again in the future. Welcome to clap bricks.

Differences between PostgreSQL and multiple versions of oracle or InnoDB

The biggest difference between PostgreSQL and the multi-version implementation of oracle or InnoDB is whether the latest version and the historical version are stored separately, PostgreSQL is not separated, while oracle and InnoDB are separated, and innodb only separates the data, and the index itself is not separated.

The main advantages of PostgreSQL are:

1. PostgreSQL has no rollback segment, while oracle and innodb have rollback segment, and both oracle and Innodb have rollback segment. For oracle and Innodb, the rollback segment is very important. The damage of the rollback segment can lead to data loss and even serious problems that the database cannot be started. In addition, because PostgreSQL does not have a rollback segment, and the old data is recorded in the original file, when the database is abnormal crash, the recovery will not be as complex as that of oracle and Innodb database, because redo and undo are needed synchronously when oracle and Innodb are restored. Therefore, after the occurrence of abnormal crash in PostgreSQL database, the probability of database failure is less than that of oracle and mysql.

two。 Because the old data is recorded directly in the data file, not in the rollback segment, ora-01555 errors are not reported as often as oracle does.

3. Rollback can be completed quickly, because rollback does not delete data, while oracle and Innodb, rollback is very complex, the changes made by the transaction must be cleaned up when the transaction is rolled back, the inserted records must be deleted, and the updated records will be updated back (see the row_undo function). At the same time, the rollback process will also generate a large number of redo logs again.

4. WAL log is simpler than oracle and Innodb. For oracle, you need to record not only changes in data files, but also changes in rollback segments.

The main disadvantages of PostgreSQL are:

1. The latest version and the historical version are not stored separately, resulting in more scanning needed to clean up the old version, which is expensive, but the general database has a peak period. If we arrange VACUUM reasonably, this is not a big problem, and VACUUM is further strengthened in PostgreSQL9.0.

2. Because there is no version information in the index, Coverage index scan cannot be implemented, that is, the query only scans the index, returns the desired properties directly from the index, and needs to access the table. While oracle and Innodb can

Features in PostgreSQL9.0:

Hot Standby function in PostgreSQL

That is, when standby applies log synchronization, it can also provide read-only service, which is very useful for read-write separation. This function is only available in oracle11g.

Features of PostgreSQL Asynchronous commit (Asynchronous Commit):

This feature is also available in oracle to oracle11g R2. Because in many application scenarios, a small amount of data is allowed to be lost during downtime, this feature is particularly appropriate in such scenarios. Setting synchronous_commit to false in PostgreSQL9.0 turns on this feature. It is important to note that although it is set to commit asynchronously, PostgreSQL will only lose a small amount of data when the host goes down, and asynchronous commit will not lead to data corruption and database failure. This feature has not been heard of in MySQL.

Features of indexes in PostgreSQL:

There can be partial indexes in PostgreSQL, that is, only part of the data in the table can be indexed, and create index can be indexed with where conditions. At the same time, indexes in PostgreSQL can be scanned in reverse, so there is no need to build special descending indexes in PostgreSQL.

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.