Optimization is a complex task because ultimately it requires understanding of the entire system to be optimized. Although it may be possible to perform some local optimizations with little knowledge of your system or application, the more optimal you want…
Overview of Partitioning in MySQL A partition is a division of a logical database or its constituting elements into distinct independent parts. Database partitioning is normally done for manageability, performance reasons. The partitioning can be done by either…
INFORMATION_SCHEMA Tables INFORMATION_SCHEMA provides access to database metadata. Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are…
MySQL Profiler: The SQL Profiler is built into the database server and can be dynamically enabled/disabled via the MySQL client utility. To begin profiling one or more SQL queries, simply issue the following command: mysql> set profiling=1; Two…
The MySQL Event Scheduler manages the scheduling and execution of events: Tasks that run according to schedule. Event support was added in MySQL 5.1.6. MySQL Events are tasks that run according to a schedule. Therefore, we sometimes refer…
MySQL uses several strategies that cache information in memory buffers to increase performance. Depending on your database architecture, you balance the size and layout of these areas, to provide the most performance benefit without wasting memory or exceeding…
The InnoDB Buffer Pool InnoDB maintains a buffer pool for caching data and indexes in memory. InnoDB manages the pool as a list, using a least recently used (LRU) algorithm incorporating a midpoint insertion strategy. When room is needed…
Locking Issues MySQL manages contention for table contents using locking: Internal locking is performed within the MySQL server itself to manage contention for table contents by multiple threads. This type of locking is internal because it is performed entirely…
How to Set Up Replication: This section describes how to set up complete replication of a MySQL server. There are a number of different methods for setting up replication, and the exact method to use depends on how you…
Replication: Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous – slaves need not be connected permanently to receive updates from the master. Depending…