Showing entries 1 to 3
Displaying posts with tag: table cache (reset)
How Triggers May Significantly Affect the Amount of Memory Allocated to Your MySQL Server

MySQL stores active table descriptors in a special memory buffer called the table open cache. This buffer is controlled by configuration variables table_open_cache that hold the maximum number of table descriptors that MySQL should store in the cache, and table_open_cache_instances that stores the number of the table cache instances. With default values of table_open_cache=4000 and table_open_cache_instances=16, MySQL will create 16 independent memory buffers that will store 250 table descriptors each. These table cache instances could be accessed concurrently, allowing DML to use cached table descriptors without locking each other.

If you use only tables, the table cache does not require a lot of memory because descriptors are lightweight, and even if you significantly increase the value of the table_open_cache, the required memory amount would not be so high. For example, …

[Read more]
Implications of Metadata Locking Changes in MySQL 5.5

While most of the talk recently has mostly been around the new changes in MySQL 5.6 (and that is understandable), I have had lately some very interesting cases to deal with, with respect to the Metadata Locking related changes that were introduced in MySQL 5.5.3. It appears that the implications of Metadata Locking have not been covered well, and since there are still a large number of MySQL 5.0 and 5.1 installations that would upgrade or are in the process of upgrading to MySQL 5.5, I thought it necessary to discuss what these implications exactly are.

To read what Metadata Locking exactly is please read this section here in the MySQL manual.

Let’s start off with having a look at the Meta Data Locking behavior prior to MySQL 5.5.3

Metadata Locking behavior prior to MySQL 5.5.3

Prior to MySQL 5.5.3 a statement that opened a …

[Read more]
Tuning MySQL Server Settings

The default configuration file for MySQL is intended not to use many resources, because its a general purpose sort of a configuration file. The default configuration does enough to have MySQL running happily with limited resources and catering to simple queries and small data-sets. The configuration file would most definitely need to be customized and tuned if you intend on using complex queries and when you have good amount of data. Most of the tunings mentioned in this post are applicable to the MyISAM storage engine, I will soon be posting tunings applicable to the Innodb storage engine. Getting started...

Showing entries 1 to 3