How to Choose the Database for Audit Storage: A Performance Analysis
In the intricate realm of database management, the importance of selecting the right database type cannot be overstated, particularly when it comes to high-load scenarios. At DataSunrise, we understand the significance of this decision, especially when it comes to high-loaded environments. Here, we present a performance analysis of different database types on AWS and offer insights to guide your selection process.
Testing Environment
We conducted thorough performance tests on PostgreSQL, MySQL 5.7, MSSQL, Aurora MySQL, and Aurora PostgreSQL. Employing pgbench as our benchmarking tool, each database was tested on m5.2xlarge instances with a boosted storage of 12000 IOPS to gauge its peak performance.
DataSunrise Tuning for Visibility
To ensure the performance of our Audit Storage system was clear, we fine-tuned DataSunrise. We set Additional Parameters like SingleThreadProcessing, AuditHighWaterMark, and AuditLowWaterMark just right, and adjusted AuditPutThreadQueueWait. Our DataSunrise instance, residing on an EC2 m7i.4xlarge has ReactiveProxyThreads=16 and AuditThreads=32, optimizing the use of every vCPU.
For the test we used the following command:
pgbench -h 127.0.0.1 -p 5435 -U postgres -c 512 -j 1024 -t 10000 -S postgres
And get the following results in TPS (Transaction Per Second)
Report Generation Performance
Beyond transactional throughput, we delved into the critical aspect of report generation. The results are as follows:
Results
The data provides a vivid picture. RDS databases, especially RDS PostgreSQL, showcase superior performance compared to Aurora databases. If you’re anticipating a high database load, our recommendation leans towards RDS PostgreSQL or Aurora PostgreSQL. The flexibility in storage size that Aurora offers might be enticing for larger databases, but remember, performance is key.
Choosing the right database is more than a checkbox on your to-do list; it’s a strategic decision that impacts your entire system’s performance. Let these performance metrics guide you in the complex world of databases with DataSunrise at your side.
Dive into the specifics of your workload, understand your scaling needs, and let these insights inform your decision-making process. In the world of databases, precision matters. Choose wisely. If you have questions or need further guidance, our team at DataSunrise is here to assist.