Data Storage Solution: MySQL/MongoDB Performance Pressure Test Report

When proxy IPs hit databases: what to choose between MySQL and MongoDB?

做代理IP服务的老铁都知道，每天要处理海量IP的状态更新、地域标记、可用性检测数据。最近帮客户做系统升级时，发现他们用MySQL存了3000万条IP记录，结果查询经常飙到5秒以上。今天就拿这个真实案例，带大家看看不同数据库在代理IP场景下的表现差异。

I. Three major data characteristics of proxy IP services

Figure out the business characteristics before you can choose the right storage solution. Proxy IP data has theseThe Devil's Detail::

1. The amount of data is like a snowball (new IPs added daily + status updates)
2. Query patterns are convoluted (both by country and by response rate)
3. Write operations are busier than a delivery boy (IP status updates every 5 minutes)

II. List of equipment for real-world pressure testing

The test environment is a two-machine cluster with the following configuration:

As a special note, using ipipgo's Dynamic Residential Proxy for pressure testing traffic canPerfectly simulate the real sceneThis is essential for testing the concurrent processing capability of the database for globally distributed requests.

III. Announcement of the results of the performance ring competition

Comparison of key metrics after 48 hours of continuous pressure testing:

Write performance:

• MySQL: Processing 1200 Status Updates Per Second
• MongoDB: Eats 3,800 writes per second

Compound queries:

When checking for "US + response <100ms + HTTPS support" IPs:

• MySQL takes 800ms to go indexing
• MongoDB's Aggregation Pipeline Only Uses 210ms

Fourth, hybrid storage program recommendations

Based on the measured results, it givesgolden portfolio program::

1. Storing dynamic data (status, inspection records) with MongoDB
2. MySQL Stores Static Attributes (Locale, Operator, etc.)
3. Real-time synchronization of IP libraries via ipipgo's APIs

V. Guide to avoiding pitfalls and practical skills

These are a few potholes to avoid when it comes to concrete implementation:

• Indexing Traps:Don't index all fields, MongoDB's indexes take up 1.5 times more space than MySQL's
• Connection pool settings:It is recommended that the initial number of connections = number of CPU cores × 2, this parameter is adjusted to directly double the performance
• Data slicing:When the IP magnitude exceeds 50 million, horizontal splitting must be done. Using ipipgo's IP segment attribution data as a split key is particularly appropriate

VI. Frequently Asked Questions Q&A

Q: What should I do if the number of database connections is always insufficient?
A: In addition to enlarging the connection pool, it is recommended to access the intelligent routing function of ipipgo, which can reduce the repeated queries of 30%.

Q: What can I do about historical data that inflates too quickly?
A: According to our real test experience, the pressure on MongoDB is directly halved by transferring the test records of 3 months ago to the time series database.

Q: To support multi-tenancy how to design?
A: Add tenant label field in IP data model to do data segregation with ipipgo's whitelist function.

Seeing this, I guess some old timers are going to ask:Where can I find an off-the-shelf solution?Straight to ipipgo's Enterprise Edition, which already has the hybrid storage solution we're talking about built in, with auto-scaling. In particular, theirIP Quality Monitoring ModuleIt can automatically trigger the optimization strategy of the database, which is much more hassle-free than manual maintenance.

As a final reminder, there is no silver bullet in database selection. Like some customers need real-time statistical reports, we added another layer of caching in front of MongoDB. Specifically how to match, depends on the actual traffic pattern of the business. Can't decide, you can find ipipgo's technical team to do free architecture consulting, they have dealt with a variety of odd scenarios, can take a lot less detours.