Key cache hit ratio

When we talk about key cache performance we usually look at these two status variables.

1. Key_read_requests
The number of requests to read a key block from the cache.

2. Key_reads
The number of physical reads of a key block from disk.

There is a good reason to examine Key_reads, because we know that disks are very slow relative to RAM but the Key_reads aren't always physical disk reads at all. If the requested block of data isn't in the operating system's cache, then a Key_read is a disk read, you will be lucky if it is cached, then it's just a system call. Having said that it is always good to minimize key_reads which may cause randon disk I/O.

The optimum solution is to keep the ratio Key_reads : Key_read_requests should be 1:100 and Key_writes / Key_write_requests should always be less than 1.

Finally I'll like to show you something partially useful you can do with Key_reads:

[xxxxxx ~]$ mysqladmin -uroot -p ext -ri10 | grep Key_reads
Enter password:
| Key_reads | 44605148 |
| Key_reads | 4 |
| Key_reads | 4 |
| Key_reads | 13 |
| Key_reads | 9 |
| Key_reads | 6 |
| Key_reads | 20 |
| Key_reads | 6 |
| Key_reads | 11 |

This server is doing approximately 7 Key_reads every ten seconds but it is hard to say that how many of them are random I/O and how many are just system calls (i.e. read from operrating system's cache)

upgrading to innodb plugin

As of MySQL 5.1.38, the InnoDB Plugin is included in MySQL 5.1 releases, in addition to the built-in version of InnoDB that has been included in previous releases. So all we need to do is just enable it, that is, add following options into my.cnf file

[mysqld]
..
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so

for further readings visit here http://dev.mysql.com/doc/refman/5.1/en/news-5-1-38.html

Assuming you have succesfully enabled Innodb Plugin, now what? would you automatically get benefits from all new Innodb plugin features?
The answer is yes/no, because Innodb Plugin supports new file format 'Barracuda', which introduces two such new data structures: compressed
, and long variable-length columns stored off-page. By default, the InnoDB Plugin does not create tables in a format that is incompatible with the built-in InnoDB in MySQL.
This means you won't get these two features automatically until you create new tables using new file format or convert exisiting tables to new format.

Innodb Plugin offers lots of other features too and these features are enabled by default, also you will automatically get benefit from these features such as fast index creation and lots of performance and scalability enhancments,
for full details click here
here http://www.innodb.com/doc/innodb_plugin-1.0/innodb-performance.html

Repair by keycache vs Repair by sorting

It is advised to disable keys on MYISAM/ARCHIVE tables before performing bulk insert operations should give a considerable speedup, especially when you have many indexes. And then enable keys to to re-create missing indexes, this task might take much longer than bulk insert operation, sometimes it may take days to finish.

One approach would be not to disable keys at first place but would this help at all? the answer is it may or may not. Personally I do not suggest this approach. If you find enable key task is taking longer then start new session with MySQL and check the state of enable keys command. If A SHOW PROCESSLIST reveal, not surprisingly, the dreaded "Repair with keycache", which seems to multiply indexing time by a factor of 20 to 30. If you want to see "repair by sort" to be used then theoretical max size of every single index must fit into myisam_max_sort_file_size.

Solution: increase myisam_max_sort_file_size

mysql> SET GLOBAL myisam_max_sort_file_size=100000 * 1024 * 1024

And restart enable keys

Similarly if your repair takes several hours then either use above solution or

you might think of using the utility 'myisamchk' with the -n (or --sort-recover) option.

Using SSL Connections with MySQL

It is very important to secure connection between MySQL Server and Clients. This can be achieved by using SSL connections between MySQL server and client program.

Assumptions:

1. MySQL that has been built with SSL support, and you are going to use OpenSSL.

2. Reader has basic Unix/MySQL Skills

3. Client program is running on windows machine (IP:192.168..0.5)

4. Server program is running on Centos/Redhat machine (IP:192.168.0.4)

Setting Up SSL Certificates for MySQL:

The following openssl commands will prompt you for several inputs. For testing, you can press Enter to all prompts.

1. Create new folder

shell> mkdir -p /etc/mysql

shell> chown -R mysql:mysql /etc/mysql/

shell> cd /etc/mysql/

2. Create CA certificate

shell> openssl genrsa 2048 > ca-key.pem

shell> openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

3. Create server certificate

shell> openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem

shell> openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

4. Create client certificate

shell> openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem

shell> openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

5. Add these lines into MySQL option file (i.e. /etc/my.cnf)

[mysqld]

..

ssl-ca=/etc/mysql/ca-cert.pem

ssl-cert=/etc/mysql/server-cert.pem

ssl-key=/etc/mysql/server-key.pem

6. Restart MySQL Instance

shell> /etc/init.d/mysql restart

7. Connect to MySQL server using mysql client program and check whether SSL support is enabled

shell> mysql -uroot -pxxxx

mysql> SHOW VARIABLES LIKE 'have_ssl'; 

+---------------+-------+ 

| Variable_name | Value | 

+---------------+-------+ 

| have_ssl      | YES   | 

+---------------+-------+

8. Create a user with REQUIRE SSL option

mysql> GRANT ALL ON test.* TO 'ssl_user'@'192.168.0.5' IDENTIFIED BY 'password' REQUIRE SSL;

9. Copy following files from /etc/mysql/ onto windows client on c:\ssl\

ca-cert.pem

client-cert.pem

client-key.pem

10. Test connectivity from windows O/S. From command prompt run this command

C:\>mysql -h192.168.0.4 -ussl_user  -ppassword --ssl-ca c:\ssl\ca-cert.pem --ssl-cert

c:\ssl\client-cert.pem --ssl-key c:\ssl\client-key.pem 

11. Once connected successfully, run this command to verify SSL connection

mysql> SHOW STATUS LIKE 'Ssl_cipher';

 +---------------+--------------------+ 

| Variable_name | Value              |

 +---------------+--------------------+ 

| Ssl_cipher    | DHE-RSA-AES256-SHA | 

+---------------+--------------------+

Done! 

Replication consistency check

How to verify that slaves are in sync with the master. There is an easy way to achieve this is to use mk-table-checksum. In this article I'll explain, how to verify slave database with master database.

Assumption:

=========

Master database server: 192.168.168.1

Slave database server: 192.168.168.2

Downloading

=========

we need to download mk-table-checksum onto slave database i.e. 192.168.168.2

wget http://www.maatkit.org/get/toolname/mk-table-checksum

chmod +x mk-table-checksum

Once downloaded, it is ready to use, no installation is needed.

Run this command: this will print connection information and exit

./mk-table-checksum 192.168.168.1 192.168.168.2 --user=root --ask-pass --chunk-size 500000 --databases test --explain-hosts

Output:

Server 192.168.168.1:

h=192.168.168.1,u=root

Server 192.168.168.2:

h=192.168.168.2,u=root

mk-table-checksum assumes the first server is the master and others are slave.

--user: user for login,

--ask-pass: prompt for a password when connecting to mysql servers, in this example it will prompt for both database servers

--chunk-size: Approximate number of rows or size of data to checksum at a time. In this example it's 50000

--database: checksum this comma-separated list of databases.In this example we decided to checksum only test database.

Now it's the time to run above command without --explain-hosts

./mk-table-checksum 192.168.168.1 192.168.168.2 --user=root --ask-pass --chunk-size 500000 --databases test

Output:

DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG

allsaints table_1 0 192.168.168.2 MyISAM 2 53fa3b06 0 0 NULL NULL

allsaints table_1 0 192.168.168.1 MyISAM 2 53fa3b06 0 0 NULL NULL

DATABASE

The database the table is in.

TABLE

The table name.

CHUNK

The chunk (see --chunk-size). Zero if you are not doing chunked checksums.

HOST

The server's hostname.

ENGINE

The table's storage engine.

COUNT

The table's row count, unless you specified to skip it.

CHECKSUM

The table's checksum, unless you specified to skip it or the table has no rows. some types of checksums will be 0 if there are no rows; others will print NULL.

TIME

The time the actual checksum and/or counting took.

WAIT

How long the checksum blocked before beginning.

STAT

The return value of MASTER_POS_WAIT().

LAG

How far the slave lags the master, as reported by SHOW SLAVE STATUS.

INNODB Full Table Scan

Full table scan is faster on MyISAM tables but it has been discourged to use with INNODB tables, the reason for which is that innodb buffer pool is not table scan resistant. A full table scan on INNODB can have the affects of evicting a large portion of the useful pages out of the INNODB buffer pool . However there are times when it is desirable to fill the buffer pool with full table scans. On other hand, if you use mysqldump to backup INNODB tables then you have experienced performance issues. Because it perfroms full table scans and thus blow out buffer pool cache.
INNOBASE has offered fix for it in innodb plugin 1.0.4

1) innodb_old_blocks_time
2) innodb_old_blocks_pct

1) Just before performing table scans and dumps to prevent eviction of a large portion of usefull pages from buffer pool. You can set this variable at runtime

mysql>SET GLOBAL innodb_old_blocks_time=1000;
.. perfrom table scans
mysql> SET GLOBAL inndob_old_blocks_time=0;


2) Similarly we can decide how much space should be available for old blocks, the default value for which is 37% (approximatley i.e. 3/8 of the pool). This behavior can be controlled with innodb_old_blocks_pct runtime variable. The value for this variable should be altered carefully, as you might have lots of unwanted old pages in buffer pool.

Customized Logging Using MySQL Proxy

If you are using MySQL 5.0, it is not possible to enable general query log without restarting mysql instance. However there are number of ways to capture what is being sent to mysql server. For instance
a) DTrace- this is the option available if you are using Solaris.
b) MySQL Proxy- you can use this on all major platforms including microsoft windows

Today I'll focus on MySQL Proxy.

Assumptions:
=========
1) Reader has basic skills of unix
2) Reader will carry out these steps on Redhat5/Centos5 only.
3) Download location is /root/
4) MySQL Server is running on 127.0.0.1 ( i.e. localhost)

Installation:
========

1. Download latest version of MySQL Proxy from here
wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.0-linux-rhel5-x86-64bit.tar.gz/from/http://mirrors.dedipower.com/www.mysql.com/
2. Decompress it
tar -xvzf mysql-proxy-0.8.0-linux-rhel5-x86-64bit.tar.gz
3. rename it
mv mysql-proxy-0.8.0-linux-rhel5-x86-64bit myqsql-proxy

Setup configuration file
=======================
Create text file using using your faviroute editor e.g. vi
shell# vi /etc/mysql-proxy

copy following text into /etc/mysql-proxy.conf

[mysql-proxy]
daemon=true
pid-file=/tmp/mysql-proxy.pid
log-file=/var/log/mysql-proxy.log
log-level=debug
proxy-backend-addresses=127.0.0.1:3306
proxy-lua-script=/root/mysql-proxy/include/custom_logging.lua

Permissions (Make it sure that mysql-proxy script should be able to read it)
=====================================================

shell# chmod 650 /etc/mysql-proxy.conf

Setup Lua script
================
copy following text into /mysql-proxy/include/customer_logging.lua

--------------------------------------------------------------------
local log_file = '/var/log/mysql_query.log'

local fh = io.open(log_file, "a+")

local tokenizer = require("proxy.tokenizer")

function read_query( packet )
if string.byte(packet) == proxy.COM_QUERY then
query = string.sub(packet, 2)
proxy.queries:append(1, packet, {resultset_is_needed = true} )
return proxy.PROXY_SEND_QUERY
else
query = ""
end
end

function read_query_result (inj)
local row_count = 0
local res = assert(inj.resultset)

local num_cols = string.byte(res.raw, 1)

if num_cols > 0 and num_cols < 255 then
for row in inj.resultset.rows do
row_count = row_count + 1
end
end

local error_status =""

if not res.query_status or res.query_status == proxy.MYSQLD_PACKET_ERR then
error_status = "[ERROR]"
end

if (res.affected_rows) then
row_count = res.affected_rows
end
--
-- write the query, adding the number of retrieved rows
--
local tokens = tokenizer.tokenize(inj.query:sub(2))
local norm_query = tokenizer.normalize(tokens)

fh:write( string.format("%s %6d --%s --%s query-time(%d) rows{%d} %s\n",
os.date('%Y-%m-%d %H:%M:%S'),
proxy.connection.server.thread_id,
proxy.connection.client.default_db,
query,
inj.query_time,
row_count,
error_status))
fh:flush()
end
---------------------------------------------------

Starting MySQL Proxy Server
=====================
Shell# /root/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.conf

Testing
=====
shell# mysql -h127.0.0.1 -uroot -pxxx -P 4040 mysql> SHOW DATABASES;
mysql> SHOW TABLES FROM TEST;
mysql> exit;

See what appears in log file
===================
shell# tail -f /var/log/mysql_query.log
2010-03-16 16:14:38 58123 -- --SELECT DATABASE() query-time(128) rows{1}
2010-03-16 16:15:11 58123 --test --show tables query-time(252) rows{5}