Uptime is assumed. Performance is the new black. You can’t hope for performance, you need to be certain of the performance of the system. You need to know what drives performance and how any changes to the system change (or could change) the performance profile. This is, in summary, what performance certainty means. It’s a new world, performance matters Not […]
Read More
With the added complexity of growing data volumes and ever-changing workloads, database performance tuning is now necessary to maximize resource utilization and system performance. However, performance tuning is often easier said than done. Let’s face it, tuning is difficult for a number of reasons. For one thing, it requires a significant amount of expertise in […]
Read More
When it is not possible to eliminate an SQL statement to improve performance, it might be possible to simplify the statement. Consider the following questions: Are all columns required? Can a table join be removed? Is a join or WHERE restriction necessary for additional SQL statements in a given function? Column Improvement An important requirement […]
Read More
When it’s not possible to remove SQL statements that are unnecessary and the rate of change of common data is relatively low, caching SQL results can provide a significant performance boost to your application and enable additional scalability of your database server. MySQL Caching The MySQL query cache can provide a boost in performance for […]
Read More
Eliminating overhead that adds unnecessary load to database servers when SQL statements are unnecessary can improve MySQL performance, including removing duplicate, repeating or unnecessary statements. Removing Duplicate SQL Statements Capture of all SQL statements for a given function or process will highlight any duplicate SQL statements that are executed to complete a specific request. The […]
Read More
Adding indexes can provide significant performance benefits. However, the most effective SQL optimization for a relational database is to eliminate the need to execute the SQL statement completely. For a highly tuned application, the greatest amount of time for the total execution of the statement is the network overhead. Removing SQL statements can reduce the […]
Read More
In addition to creating new indexes to improve performance, you can improve database performance with additional schema optimizations. These optimizations include using specific data types and/or column types. The benefit is a smaller disk footprint producing less disk I/O and results in more index data being packed in available system memory. Data Types Several data […]
Read More
The management of indexes—how they are created and maintained—can impact the performance of SQL statements. Combining Your DDL An important management requirement when adding indexes to MySQL is the blocking nature of a DDL statement. Historically, the impact of an ALTER statement required that a new copy of the table be created. This could be […]
Read More
For companies that live and die by their databases, a one-shot backup isn’t really the perfect solution. Typically for such companies (think Yahoo! or Google), database access is a near-constant process, and database content changes continually, often on a second-by-second basis. Data replication, which involves continual data transfer between two (or more) servers to maintain a […]
Read More
Replication in MySQL is the dynamic process of synchronizing data between a primary (main) database server and one or more secondary (replica) database servers in near-real time. Using this process, it’s possible to create copies of one or more databases so that even if the primary server fails, data can still be recovered from one […]
Read More
Equally important as backing up data in a MySQL database is the ability to restore data. Most books on the subject emphasize the importance of backing up your data regularly (and rightly so), but restoring the data is an often-overlooked aspect of this process. Backed-up files are useless if they can’t be accessed. Accordingly, you should […]
Read More
One essential task of any database administrator is to make sure the data is protected from loss. This is accomplished by regular backup and test restorations of your database. When disaster strikes (and it will, make no mistake about that), you will be better equipped to deal with it if you perform these backup and restore […]
Read More