This is the 28th day of my participation in the August Text Challenge.More challenges in August

  • 📢 welcome to like: 👍 collect ⭐ message 📝 if there are mistakes please correct, give people rose, hand left lingering fragrance!
  • 📢 This article was originally written by Webmote and originally published by Nuggets.
  • 📢 author’s motto: life is toss about, when you don’t toss about life, life will start to toss about you, let us come on together! 💪 💪 💪

1. Compare database performance

BenchaMark, long ago, companies were keen to do all kinds of read and write tests, and it seemed that the best thing to do was to test Oracle’s performance. Then gradually everyone died down, especially in recent years, few people did database performance tests directly.

Perhaps with the popularity of distributed applications, people are no longer interested in testing database performance on a standalone basis.

According to the search, before 2010, a foreigner did the performance test of SQL Server and MySQL. According to the test report, THE performance of SQL Server is more than 10 times that of MySQL, but such poor performance does not affect MySQL to become one of the most popular databases in the world.

Looking at DB popularity, MySQL is second, SQL Server is third, and all the other databases are below.

Therefore, a simple conclusion can be drawn that looking at database performance alone does not make sense.

2. How to test database performance

Regardless of whether the performance of the database determines the architecture selection, we still need to test the performance of the database against a certain benchmark since the company values performance comparison.

For Aurora AWS generates it 5 times faster than MySQL of the same type and 3 times faster than PosgreSQL. AWS tests it all to sell Aurora, so it’s not really very scientific. Of course, there is no more scientific solution in the industry, so SysBench is the only option in this regard.

So let’s use SysBench to test the performance of individual databases.

For SqlServer, I don’t see scripts supporting SqlServer tests, but lua scripts are written for MySQL and PostgreSQL, so we don’t have to worry about scripts.

3. Prepare the environment

We need a standard test environment, and the hardware configuration should be consistent across databases.

  • Three EC2 test environments were used as clients to initiate read and write operations
  • Three servers, one for each database, configured with 4-core 32 GB SSD disks as standard.

Install the SysBench test environment on each EC2 virtual machine.

Connect to the various databases and start filling in the test data.

sudo sysbench --db-driver=mysql --oltp-table-size=100000 --oltp-tables-count=24 --threads=1 --mysql-host=aaa.amazonaws.com --net=host --mysql-port=3306 --mysql-user=admin --mysql-password=12345678  --mysql-db=db1 /usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua prepare

Copy the code

Then to read and write operations test, and record the TPS and QPS, and so the test finished, it is not enough to do single table tests should be, but what does it matter, this is just the rough test, the advantages of each database is different, have to take a ruler measurement, estimation is each have their advantages, so we certainly a standard.


sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=aaa.amazonaws.com  --mysql-user=admin --mysql-password="12345678" --mysql-port=3306  --mysql-db=db1 --max-requests=0 --oltp-simple-ranges=0 --oltp-distxinct-ranges=0 --oltp-sum-ranges=0 --oltp-order-ranges=0 --time=300 --oltp-read-only=on --threads=1000 --report-interval=10 run

Copy the code

4. Compare test results

To test for fairness, we need to keep the CPU utilization of each database server at a high load.

Here are the AWS test results:

Well, that’s the smell.

All the environment is ready, I thought I was working overtime in the district office today, but I slept until 11:00. Anyway, I will continue the test on weekdays. I will summarize first, mysql and postgreSQL are predictably smooth, and SQLserver may need to write lua test scripts.

But that’s okay, scripts are no problem for small architects, so let’s wait for my test results!

5. Summary

PostgreSQL is rich in functionality, but not widely used. Many third-party components are not supported by postgreSQL. How do you convince your company to switch from postgreSQL to MySQL?

Routine summary, rational view!

Knot is what ah, knot is I want you to praise but can not get lonely. 😳 😳 😳

👓 have seen this, but also care about a thumbs-up?

👓 has been liked, but also care about a collection?

👓 are collected, but also care about a comment?