One of the main responsibilities of any database administrator is to keep a close eye on how database performance is impacting size and storage. Decisions will have to be made on whether or not to make changes within the database structure or application itself, or to make the changes on the storage and resource side instead.
Here are two key indicators that your MySQL Database is getting too large:
1 – The Number and Size of Indexes are getting out of Control
Indexes are normally associated with improving database speed, but this is a very general observation. In most cases, indexes will make accessing/reading data faster but can make writing data slower. As your database grows in size, this trade-off multiplies and becomes more complex to maintain. Not only must the DBA manage the indexing trade-off but disk space becomes an issue. Most people will find that as the database grows, so do the indexes. In return, this eats up precious space – many times at a faster rate than the actual database growth itself.
More disk resource used by indexes means less resource available for database growth – it becomes a matter-of-time before expensive upgrades creep into the picture. MySQL loads indexes into memory at execution time. If there is no room for your indexes, then the performance benefit is lost.
Possible Solution: Scalebase has the advantage of distributing the data across multiple databases. This way your company is not dependent on the disk space or memory of one server. With Scalebase, indexes and segments in each database are smaller and can be managed more efficiently by each MySQL instance. If a server gets maxed out, the Scalebase Data Traffic Manager will instantly switch to a faster responding server for processing.
2 – The Servers Consistently Run at Max Capacity
The situation is fairly common: several million to a few billion rows of data containing; reports, queries, Business Intelligence (BI), etc. – with employees requesting and expecting information instantaneously. You may have a BPM or ERP system that is a heavy draw on the database. No matter what the scenario is, as more data goes into a system, the reporting and business intelligence we want from it becomes more complex.
When you start to see your server(s) constantly running out of ram, CPU’s at 100%, and data moving to disk or temp tables, it’s a key indicator that MySQL is getting too large to handle. Consistency is the key as it is normal for peak periods of business to “run hot” for short periods of time. If, however, you see the servers maxing out for extended periods of time, or it happens frequently throughout the day, then it’s time to make some changes. Adding hardware is the quick fix, but can get very expensive, especially if your database experiences exponential growth over short periods of time.
Possible Solution – Scalebase allows your company to better manage these peak periods. By distributing the load across multiple database servers, the Scalebase Data Traffic Manager can utilize the MySQL servers that are operating the most efficiently and report the ones that aren’t. This can provide faster data and ultimately a better user experience.
Whatever you ultimately determine to be the optimal solution, experiencing either of these signs should trigger a response and plan of action in the near term. A database that is running at max capacity and causing the server to max out will have major performance consequences that can lead to serious compounding problems (within both the technology and business contexts) if not handled correctly.
Subscribe to the Scalebase blog to stay informed about the latest MySQL topics, industry news and developments.
Sign up for a free database scalability assessment to find out how your application will benefit from scaling out.