|Showing entries 1 to 12|
In one of my previous posts, “How to resize InnoDB logs?”, I gave the advice on how to safely change the size of transaction logs. This time, I will explain why doing it may become necessary.
The transaction logs handle REDO logging, which means they keep the record of all recent modifications performed by queries in any InnoDB table. But they are a lot more than just an archive of transactions. The logs play important part in the process of handling writes. When a transaction commits, InnoDB synchronously makes a note of any changes into the log, while updating the actual table files happens asynchronously and may take place much later. Each log entry is assigned a Log Sequence Number[Read more...]
Initially I just intended for the tool to be able to dump the general log to standard output, from any machine capable to connect to MySQL. Quick enough, I realized the power it brings.
With this tool, one can dump to standard output all queries using temporary tables; or using a specific index; or doing a full index scan; or just follow up on connections; or… For example, the following execution will only log queries which make for filesort:
[Read more...]oak-hook-general-log --user=root --host=localhost --password=123456
I’m further developing a general log hook, which can stream queries from the general log.
A particular direction I’m taking is to filter queries by their type of actions. For example, the tool (oak-hook-general-log) can be instructed to only stream out those queries which involve creation of a temporary table; or those which cause for a filesort, or full index scan, etc.
This is done by evaluating of query execution plans on the fly. I suspect the MySQL query analyzer (http://www.mysql.com/why-mysql/white-papers/mysql_wp_queryanalyzer.php) roughly does the same (as a small part of what it does).
It’s almost nothing one cannot do with sed/awk. However, I bumped into a couple of problems:
I’m seeking input on a new openark kit utility I’ve started to implement.
The tool, oak-hook-general-log, will hook up to a MySQL (>= 5.1) server, and stream the general log into standard output. It looks like this:
[Read more...]bash$ python src/oak/oak-hook-general-log.py --socket=/tmp/mysql.sock --user=root 2010-03-21 10:18:42 root[root] @ localhost  79 1 Query SELECT COUNT(*) FROM City 2010-03-21 10:18:48 root[root] @ localhost  79 1 Query DELETE FROM City WHERE id=1000 2010-03-21 10:18:54 root[root] @ localhost  79 1 Query SHOW PROCESSLIST 2010-03-21 10:19:06 root[root] @ localhost  79
$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.2 (Tikanga)
In a nutshell: What’s New in MySQL 5.1.
Release notes: Changes in release 5.1.x (Production).
And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.
To play the video directly, go to http://technocation.org/node/663/play. To download the 146 Mb video to your computer for offline playback, go to http://technocation.org/node/663/download. The slides can be downloaded as a[Read more...]
|Showing entries 1 to 12|