Database tuning describes a group of activities used to optimize and homogenize the performance of a database. A Computer Database is a structured collection of records or data that is stored in a computer system It usually overlaps with query tuning, but refers to configuration of the database files, the database management system (DBMS), and the operating system and hardware the DBMS runs on. Query languages are Computer languages used to make queries into Databases and Information systems Broadly query languages can be classified A database management system ( DBMS) is Computer software designed for the purpose of managing Databases DBMSes may use any of a variety of Data models An operating system (commonly abbreviated OS and O/S) is the software component of a Computer system that is responsible for the management and coordination Typical PC hardware A typical Personal computer consists of a case or chassis in a tower shape (desktop and the following parts Motherboard
The goal is to maximize use of system resources to perform work as efficiently and rapidly as possible. Most systems are designed to manage work efficiently, but it is possible to greatly improve performance by customizing settings and the configuration for the database and the DBMS being tuned.
I/O tuning
I/O tuning is placing database transaction logs, files associated with temporary work spaces, and table and index file storage to optimize and balance reads and writes against these files. In Computing, input/output, or I/O, refers to the communication between an Information processing system (such as a Computer) and the outside In Relational databases and Flat file databases a table is a set of data elements (values that is organized using a model of vertical columns (which are I/O is generally the most expensive operation in database work, and is typically the first bottleneck in database performance encountered. In Computing, optimization is the process of modifying a system to make some aspect of it work more efficiently or use fewer resources
Hardware and software configuration of disk subsystems are examined: RAID levels and configuration, block and stripe size allocation, and the configuration of disks, controller cards, storage cabinets, and external storage systems such as a SAN. RAID — which stands for Redundant Array of Inexpensive Disks,or alternatively Redundant Array of Independent Disks (a less specific name and thus now the In Computing (specifically data transmission and data storage) block is a sequence of Bytes or Bits having a nominal length (a block size An expansion card (also expansion board, adapter card or accessory card) in Computing is a Printed circuit board that can be inserted In Information technology, a storage area network ( SAN) is an architecture to attach remote computer storage devices (such as Disk arrays tape libraries Transaction logs and temporary spaces are heavy consumers of I/O, and affect performance for all users of the database. Placing them appropriately is crucial.
Frequently joined tables and indexes are placed so that as they are requested from file storage, they can be retrieved in parallel from separate disks simultaneously. A SQL JOIN clause combines records from two tables in a Relational database, resulting in a new temporary table sometimes called a "joined table" Frequently accessed tables and indexes are placed on separate disks to balance I/O and prevent read queuing. GAURAV
DBMS tuning
DBMS tuning refers to tuning of the DBMS and the configuration of the memory and processing resources of the computer running the DBMS. This is typically done through configuring the DBMS, but the resources involved are shared with the host system. Host system is any networked computer that provides services to other systems or users
Tuning the DBMS can involve setting the recovery interval (time needed to restore the state of data to a particular point in time), assigning parallelism (the breaking up of work from a single query into tasks assigned to different processing resources), and network protocols used to communicate with database consumers. Parallel computing is a form of computation in which many instructions are carried out simultaneously operating on the principle that large problems can often In the field of Telecommunications, a communications protocol is the set of standard rules for data representation signaling authentication and error detection required to
Memory is allocated for data, execution plans, procedure cache, and work space. A query plan (or query execution plan) is a set of steps used to access or modify information in a SQL Relational database management system. It is much faster to access data in memory than data on storage, so maintaining a sizable cache of data makes activities perform faster. In Computer science, a cache (kæʃ like "cash") is a collection of data duplicating original The same consideration is given to work space. Caching execution plans and procedures means that they are reused instead of recompiled when needed. It is important to take as much memory as possible, while leaving enough for other processes and the OS to use without excessive paging of memory to storage. In Computer Operating systems that have their Main memory divided into pages, paging (sometimes called swapping) is a transfer
Processing resources are sometimes assigned to specific activities to improve concurrency. In Computer science, concurrency is a properties of system in which several Computational processes are executing at the same time and potentially interacting On a server with eight processors, six could be reserved for the DBMS to maximize available processing resources for the database. A server is a Computer dedicated to providing one or more services over a computer network typically through a request-response routine
Database maintenance
Database maintenance includes backups, column statistics updates, and defragmentation of data inside the database files. In Information technology, backup refers to making copies of Data so that these additional copies may be used to restore the original after a
On a heavily used database, the transaction log grows rapidly. Transaction log entries must be removed from the log to make room for future entries. Frequent transaction log backups are smaller, so they interrupt database activity for shorter periods of time.
DBMS use statistic histograms to find data in a range against a table or index. In Statistics, a histogram is a Graphical display of tabulated frequencies, shown as Bars It shows what proportion of cases fall into each of Statistics updates should be scheduled frequently and sample as much of the underlying data as possible. Accurate and updated statistics allow query engines to make good decisions about execution plans, as well as efficiently locate data.
Defragmentation of table and index data increases efficiency in accessing data. The amount of fragmentation depends on the nature of the data, how it is changed over time, and the amount of free space in database pages to accept inserts of data without creating additional pages. An SQL INSERT statement adds one or more records to any single table in a Relational database.
Dapyx Software network: MP3 Explorer | Ebook Manager | Zenithic