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}