MySQL Key_Buffer_Size System Variable

MySQL MyISAM Key Cache: Key_Buffer_Size System Variable

Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache. For 32-bit system, the maximum allow amount is 4 GB. As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning).

You can increase the value to get better index handling for all reads and multiple writes; on a system whose primary function is to run MySQL using the MyISAM storage engine, 25% of the machine's total memory is an acceptable value for this variable. However, you should be aware that, if you make the value too large (for example, more than 50% of the machine's total memory), your system might start to page and become extremely slow. This is because MySQL relies on the operating system to perform file system caching for data reads,so you must leave some room for the file system cache. You should also consider the memory requirements of any other storage engines that you may be usingin addition to MyISAM.

Please note that the key cache is not used if the key_buffer_size value is too small to allocate the minimal number of block buffers (8).

1.  Key_Buffer_Size Status Varaiables and Monitoring:

The following global status variables should be checked periodically:
 
Key_read_requests: the number of requests that have been executed to read a key block from the cache.
Key_reads: the number of physical reads of a key block from disk.

     Key cache hit (read miss) ratio = (1-(Key_reads/Key_read_requests)) * 100.

The key cache hit ratio represents the proportion of keys that are being read from the key cache in memory instead of from disk. This should normally be greater than 99% for optimum efficiency.

Key_write_requests: the number of requests to write a key block to the cache.
Key_writes: the number of actual writes, logical and physical key block, to disk.

      Kewrite miss ratio = (1-(Key_writes/Key_write_requests)) * 100

The key write ratio is usually near 100% if you are using mostly updates and deletes, but might be much smaller if you tend to do updates that affect many rows at the same time or if you are using the DELAY_KEY_WRITE table option.

Key_blocks_unused: the number of used blocks in the key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time. The more blocks in the key cache, the less the server is using disk-based I/O to examine the index data, therefore, the value
of this variable should be high.
key_cache_block_size: the number of requests that have been executed to read key block from the cache.
The fraction of the key buffer in use can be determined using key_buffer_size in conjunction with the Key_blocks_unused status variable and the buffer block size, which is available from the key_cache_block_size system variable:

       1- ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)

This value is an approximation because some space in the key buffer is being allocated internally for administrative structures.

Key_block_not_flushed: the number of key blocks in the key caches that have changed but have not yet been flushed to disk. These can be considered ‘dirty” key blocks in the key cache. The index will be corrupted during a crash and will need to be repaired before it can be used again. Therefore, this value should be near zero (0).

Adjust the key_buffer_size variable and monitor the key cache hit ratio. Please don’t forget to put the corresponding value of key_buffer_size in your my.cnf; hence, the variable is set properly when the server is restarted.


References:

• MySQL Manual: The MyISAM Key Cache
• High Performance MySQL: Chapter 6, Optimzing Server Settings
• Other Reference On The Web

Related Post:

• MySQL Query Cache   
• MySQL Individual Thread Variables Setting And Tunning
• MySQL Server Connection System Variables 

SQL: SELECT THE FIRST N RECORDS

Assuming that we want to select the first 8 registrations from the following sample User table

Id	First_Name	Last_Name	City	Registration_Date
1	John	Smith	New York	2009-01-01
2	Adam	King	London	2008-12-01
3	Lin	Lin	Beijing	2009-12-01

Let's start with a MySQL query, which will use the LIMIT clause:

     Select * from user order by registration_date limit 8;

The above query will give the first 8 users by registration date.

Now, if we want to have the first 8 users from New York, you can change the query to this:

     Select * from user WHERE city=’New York’ ORDER BY registration_date limit 8;

Now let’s try the following query:

    SELECT * FROM
       ( SELECT * FROM user ORDER BY registration_date LIMIT 8 ) As  first_8
    WHERE city='New York';

This above query result actually returns the users from New York from the first 8 registrations. The number of records return will be between 0 and 8 in reality because the WHERE clause filter the records from the sub query result set.

Since each database venders use different non-standard result limit syntax, the above query needs to rewrite to accommodate the particular syntax . For example, in Oracle, the query can be rewritten in follow form:

   Select * from user order by registration_date where rownum >= 8;

And in SQL Server, it can be written like this:

   Select TOP 8 * from user order by registration_date;

 Reference:

•  MySQMySQL Cookboook: Chapter 3, Selecting Data FromTables   
•  MySQL Cookbook: Chapter 13,Statistical Techniques.
•  ISO 2008-SQL
• Other Reference On the Web

Mysql Replication Slave Stopped Due to “Lock Wait Timeout Exceeded” Error

MySQL Replication: [ERROR] Slave: : Error 'Lock wait timeout exceeded; try restarting transaction' on query.

In a deadlock situation, the Mysql Replication server slave would let the transaction to retry up to the number of time which was set for the slave_transaction_retries system variable before stopping the slave and logging a message like the one bellow in the error log:

100419 0:03:47 [ERROR] Slave: : Error 'Lock wait timeout exceeded; try restarting transaction' on query.   Default database: 'mysqldb'. Query: 'UPDATE a_table SET colum_1 =a_value_colum + 1000,…,… WHERE id='12345'', Error_code: 1205
100419 0:03:47 [Note] Slave SQL thread exiting, replication stopped in log 'slave-1-log-bin.006034' at position 10470968

Refering to the MySQL manual; the 1205 error has the following description:

Error: 1205 SQLSTATE: HY000 (ER_LOCK_WAIT_TIMEOUT)
Message: Lock wait timeout exceeded; try restarting transaction.

Since the tables involved in the locks were InnoDB tables, we could check the InnoDB status by issuing the following command:
Mysql> show engine innodb status\G;
---------------------------------------------
LATEST DETECTED DEADLOCK
---------------------------------------------
*** (1) TRANSACTION:
TRANSACTION 0 2513168731, ACTIVE 0 sec, process no 17876, OS thread id 685890464 starting index read
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 8018, query id 317267495 user_id Updating
...
There were some useful information regarding the lock from the above output under the LATEST DETECTED DEADLOCK section which details the causes of the deadlock such as what kind of locks were used, and the query statements that involved in the lock.

To resolve the deadlock issues, we may need to debug the query statements and make code changes. In the mean time, we can check innodb_lock_wait_timeout and slave_transaction_retries to see if we can increase it a bit to avoid stopping the slave. To do so, issue the following statement from the mysql client:

Mysql> show variables;
innodb_lock_wait_timeout | 150
…
slave_transaction_retries | 10

The innodb_lock_wait_timeout variable set the length of time a transaction will wait for a row lock resource before “giving up”. The default value is 50 seconds. Please note that this variable applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks. InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.

The slave_transaction_retries set the number of time the replication slave thread to retry before stopping with an error due to any of the following situation (the default for this variable is 10):

· an InnoDB deadlock
· the transaction's execution time exceeded InnoDB's innodb_lock_wait_timeout
· the transaction’s execution time exceeded NDBCLUSTER’s TransactionDeadlockDetectionTimeout
· the transaction’s execution time exceeded NDBCLUSTER's TransactionInactiveTimeout,

Please also notice that, if the slave_transaction_retries server variable is set, the transaction will hold the lock for (slave_transaction_retries * innodb_lock_wait_timeout) seconds before giving up. In the example above, it means that a lock will be held for proximately 1,500 seconds (10*150). Therefore, try to find out what is causing this lock and fix it maybe a better solution.

In any case, please remember to restart the slave because deadlock error stopped the slave as it had indicated in the error log.

Reference:

• MySQL server Error Codes and Messages:
• innodb_lock_wait_timeout:
• slave_transaction_retries:
• High Performance MySQL (Second Edition), Appendix D, Debugging Locks
• High Performance MySQL (Second Edition), Chapter 8: Replication
• Other Reference On The Web