Tuning (database)
Under Tuning refers to the fine-tuning of parameters of a database installation, or be influenced properties of a database application with the goal of Performance Improvement, or an improvement in the throughput of the database.
A tuning of the database installation is possible and often necessary because database management systems ( DBMS ) usually offer an enormous, difficult to overlook variety of installation and operating options. So z. For example, you can set which parts of which database tables are physically stored in which memory areas. In addition, buffer sizes, the behavior of the database with regard to data changes that have not yet been released (see transaction ), index types and much more can be specifically influenced. It is obvious that such settings affect the performance.
Database tuning is usually carried out by highly qualified specialists and is a very cost-intensive measure with results that are difficult to predict. Often comparable improvements can be achieved more cost-effectively with hardware extensions. Tuning is therefore often limited to a few, simple measures or to high-end applications.
Alternatively, it is possible to optimize the data model rather than the database installation . Denormalization is typical for this . In doing so, the structures are changed in a way that deviates from the logical ideal structure, so that the SQL accesses actually required have to access fewer database tables and are consequently considerably faster. By optimizing the data structures, significantly greater performance improvements can usually be achieved, but with the disadvantage that this often requires changes to the programs that have already been completed. By using views , however, the data structure at the application level can be retained in many cases.
However, tuning at the application level offers even greater potential for performance improvements: While hardware expansions and tuning of the database installation can double the performance on average and even tenfold performance through optimization of the data model, by changing the algorithm of the application often accelerate them by several orders of magnitude if performance was not a design goal when they were designed .
Options for tuning access mechanisms
- Clustering
- Hashing
- Indexing
- Scanning
Individual evidence
- ↑ LMU: Database Tuning. Retrieved September 12, 2018 .
- ↑ Database Tuning and Self-Tuning. Retrieved September 12, 2018 .
- ↑ Index Tuning: Index Merging. Retrieved September 12, 2018 .