Performance Comparison Between MySQL and PostgreSQL

The amount of data will be huge in the future/you cannot predict the size of the data. We recommend that you change to PostgreSQL.

After a certain amount of data is stored in MySQL (generally, the performance of a single MySQL table is the best-, and the performance starts to decline when there are more data), the performance decreases rapidly, and in the case of large data volumes, mySQL stability/data reliability is a problem.

Official descriptions of PostgreSQL 8.2 are as follows:

Http://www.postgresql.org/about/

Quote:
Limit Value
Maximum database size Unlimited
Maximum table size 32 TB
Maximum row size 1.6 TB
Maximum field size 1 GB
Maximum rows per table Unlimited
Maximum columns per table 250-1600 depending on column types
Maximum indexes per table Unlimited
Currently, MySQL is only suitable for running web applications and is not suitable for massive data storage and data warehouses.

PostgreSQL has already been used for large-scale commercial applications in China.

Quote:
PostgreSQL is suitable for large applications. He Weiping laser, the author of the PostgreSQL Chinese official manual, replied to a large application:

Quote:
In fact, I have a huge database at hand,
The Data + index has exceeded 3 billion GB, and the total data volume exceeds rows,
It takes about 12 hours every day.

Basically, I think:
Check your Io investment and do not mean anything on your hard disk.
Second, carefully analyze what is your bottleneck,
After many events, we do not need database replicate.
Third, use data cutting as appropriate.

One simple sentence:
Appropriate hardware investment and planning with appropriate software structures.
Specific things need to be analyzed.

Let's take a look at our 500g Library:
Single-host HP dl385, 16 GB memory and 6 SCSI disks, 20 SATA disk disks,
The disk array is HP dl320s (msa1500), which is quite cheap.
We construct SCSI to run XFS, SATA, RAID5, and ext3,
At present, the performance is very satisfactory (from our perspective), some update statements, once
Millions of rows of data will be updated, so some of our programs will be updated dozens of times a day,
It can also be completed within S. Vacuum once a day, at low load
Time period, which may take about minutes ~ 200 min, use slony for data backup and backup
To a large hard drive ide machine (Tb hard drive, don't be surprised, now GB hard drive
Only 3500 yuan .).
This machine is used for data mining and has a small number of concurrent jobs. Therefore, we have not implemented load.
.

If you have any questions, continue the discussion.
Test: MySQL vs PostgreSQL

Quote:
MySQL does not adapt to massive data, intensive computing, and heavy-load applications.

At least currently, the quality of open source databases cannot compete with commercial data.

A foreign 3rd-party server (tweakers.net) conducted MySQL 4.1.20, MySQL 5.1.20a and PostgreSQL 8.2 performance tests (run on machines with different levels of configuration) on 2006-11-29 ). you can refer.
Posted by fsluiter@gmail.com

Tweakers.net, a Dutch community of online tweakers, benchmarked their potential new server with PostgreSQL 8.2 vs several versions of MySQL 4.1.20 and MySQL 5.1.20a

Graph and text test statistical report:
Http://tweakers.net/reviews/657/6

Quote:
If the requirements are higher, we recommend that you use the bizgres cluster with excellent performance.

Refer:
Http://bbs.chinaunix.net/viewthr. .. Page % 3d1 & page = 1

Quote:
0.2 billion of a single table, slect crount (*) from table; to scan the entire table.

Same configuration single host hardware: 8 GB memory, each machine is two dual-core amd86. disk RAID 0 + 1

It took 50 seconds for Oracle 10 Gb, and PostgreSQL's common cluster was used for 1 minute 40 seconds.

The speed of using the PostgreSQL High-Performance cluster bizgers is four times faster than that of oracle.

More than 0.1 billion lines of MySQL are as slow as snails.