I鈥檝e wanted to write a blog post for a long time about the tasks an Internet DBA needs to handle, but I never had the time while busy building platforms. Recently, as each module has begun to take shape, I finally have the time to settle in and introduce them.
As everyone knows, an Internet DBA differs greatly from a traditional industry DBA. We manage far more machines, face rapid new technology updates, deal with a large number of developers, operate in complex network environments, and must be on call 7*24. This adds layers of complexity on top of the traditional DBA role. We must consider how to deploy at scale, how to centralize monitoring, and how to solve single points of failure to guarantee 7*24 availability. Achieving this is not about stacking manpower; we need a complete platform as a foundation. So, what exactly should a database platform look like?
1. A Robust Monitoring System (Monitor + Control):
Monitoring is our eyes. We cannot stare at our DBs 7*24, so we need a monitoring system to watch over them for us. Once an anomaly occurs, monitoring should not only notify us but must also exercise control. For example: if a MySQL slave goes down, monitoring should automatically take it offline; if the slave replication status fails, it can automatically repair the synchronization; and so on. Furthermore, as the number of machines and instance daemons increases, we find that mobile phone alerts skyrocket. To ensure we can get a good night’s sleep, we need to figure out how to reduce these alarms, for example, deciding which alerts warrant an SMS and which are fine as an email; can disk space alarms for all machines be consolidated before being sent? These are all issues our monitoring system must consider.
2. Automatic Review System:
There are many developers and many projects, but their habits are inconsistent, which can make reviewing table structures a headache. To ensure online consistency, avoid being hurt by developers’ magical SQL, and prevent being crushed by numerous project reviews, we must have an automated table creation review system. We define some rules, such as: no reserved fields, primary keys must be INT or BIGINT, etc. Developers then fill in the table structure ready for launch, and the system automatically reviews it. Those that pass are automatically deployed online, and suggestions are given for those that fail.
3. Slow Query Log Analysis System:
With the automatic review system online, some SQL with suboptimal index usage might still slip through, so we need a slow query log analysis system to help us. When designing this system, we need to consider whether to capture slow logs in real-time or push them periodically on a daily basis. After capture, should the slow logs be immediately pushed to developers, or automatically analyzed first with suggestions provided? We also need to consider filtering some SQL, perhaps daily statistics queries. Of course, all this is automatic, requiring no manual intervention after the design is complete.
4. Statistics System:
We must clearly understand the overall operational state of our online DBs, changes in read volume, and changes in write volume. Charts are dead; they deceive no one. From traffic statistics, we can identify malicious access, determine if optimization is needed, or decide whether to add nodes to withstand greater pressure.
5. Backup System:
Whether you believe it or not, I am a believer: cold backups are always our lifeline. No matter how well we do, failures will happen, and `drop database` can occur too. Therefore, a complete backup system is imperative. Is our backup functioning correctly? Can the backed-up data be restored? How long does restoration take? These are all considerations for our backup system.
6. Management System:
We have hundreds of machines at least, potentially several thousand. How do we clearly know how many daemons run on each machine, which machines are under a DB Proxy, and how do we distribute scripts separately to master and slave machines? A management system is needed to help us complete these tasks.
7.