MySQL vs PostgreSQL Performance Benchmark (Latency - Throughput - Saturation)

Summary

In this benchmark video, the presenter compares PostgreSQL and MySQL through full CRUD operations in an e-commerce context. Key metrics like latency, throughput, disk operations, and resource usage are analyzed. Improvements from previous tests include optimized configurations for both databases on dedicated EC2 instances. The first test reveals PostgreSQL outperforms MySQL in insert, update, and delete operations, while the second test shows MySQL struggles under heavy read loads. Ultimately, PostgreSQL is deemed the better choice for CRUD-heavy applications, while MySQL may suit read-intensive use cases. Future tests will explore performance with larger datasets.

Takeaways

• 😀 Improvements were made to ensure a fair comparison between PostgreSQL and MySQL based on previous feedback.
• 😀 The benchmark includes full CRUD operations, measuring latency for INSERT, UPDATE, and DELETE.
• 😀 Throughput is evaluated by tracking the number of queries executed per second for both databases.
• 😀 The test simulates a real e-commerce scenario with six tables for customers, products, shopping carts, and orders.
• 😀 PostgreSQL outperforms MySQL in terms of INSERT, UPDATE, and DELETE latency during the tests.
• 😀 MySQL generates more disk operations and has higher CPU usage compared to PostgreSQL.
• 😀 Database configurations were optimized for both PostgreSQL and MySQL to improve performance.
• 😀 The second test involved a complex JOIN operation, with PostgreSQL also performing better despite a smaller difference.
• 😀 PostgreSQL holds its performance until around 420 queries per second, while MySQL starts degrading at 300.
• 😀 Overall, PostgreSQL is recommended for use cases with heavy updates and deletes, while MySQL may be suitable for read-heavy workloads.

Q & A

What improvements were made for this benchmark compared to the previous one?

The benchmark was improved by standardizing tests on i3en EC2 instances designed for databases, optimizing database configurations for PostgreSQL and MySQL, and simulating a more realistic e-commerce use case.

What types of operations were measured in the first test?

The first test measured the latency of INSERT, UPDATE, and DELETE operations, as well as the throughput by tracking how many queries each database could execute per second.

How does the e-commerce workflow impact database performance?

The workflow includes creating and updating a shopping cart, adding items, processing orders, and deleting items, all of which test the databases' performance in handling multiple concurrent CRUD operations.

What role do foreign keys play in the database tables?

Foreign keys establish relationships between tables, linking records and ensuring data integrity; MySQL automatically creates indexes for these keys, while PostgreSQL requires manual index creation.

What were the CPU and memory usage trends observed during the first test?

During the first test, PostgreSQL showed better performance with lower CPU usage, while MySQL experienced higher CPU usage and latency as the load increased. Both databases did not utilize much cache relative to the VM's overall memory capacity.

What is the significance of using transactions in database operations?

Using transactions groups multiple operations into one atomic action, ensuring that either all operations succeed or none do, which is critical for maintaining data consistency in real applications.

What differences in query performance were observed between PostgreSQL and MySQL?

PostgreSQL performed better in terms of latency for INSERT, UPDATE, and DELETE operations, while MySQL's performance degraded more rapidly under high load.

What was the maximum query rate achieved by PostgreSQL and MySQL during the tests?

PostgreSQL reached a limit of around 14,500 queries per second, while MySQL began to fail queries at around 12,500 queries per second.

How does disk usage and operations differ between the two databases?

MySQL performed more disk operations compared to PostgreSQL, which contributed to higher CPU usage and latency in MySQL during the tests.

What are the recommendations for choosing between PostgreSQL and MySQL based on the benchmark results?

For workloads with frequent updates and deletes, PostgreSQL is recommended due to its superior performance. However, if the workload primarily consists of read operations and complex joins, MySQL could be considered.