Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 24

Displaying posts with tag: lua (reset)

Lua sysbench – crash course
+0 Vote Up -0Vote Down

This is a follow-up on my previous blog post about using Lua enabled sysbench. Today I will dive into how to write Lua scripts for sysbench. Look at this simple example:

function prepare ()
  local i
  print("creating table sbtest.t1 ...")
  db_query("create table t1 (c1 int unsigned primary key, c2 int)")
  for i= 1, 1000 do
    db_query("insert into t1 values (" .. i .. "," .. i .. ")")

function cleanup()
  db_query("drop table t1")

function help()
  print("sysbench Lua demo; no special command line options available")

function thread_init(thread_id)

function thread_done(thread_id)

  [Read more...]
Using Lua-enabled sysbench
+2 Vote Up -0Vote Down

A quite common benchmark for MySQL is sysbench. It was written nearly 10 years ago by Alexey Kopytov.

Sysbench has modes to benchmark raw CPU performance, mutex speed, scheduler overhead and file IO performance. The probably most often used sysbench mode is OLTP. This benchmark mimics a OLTP scenario with small transactions hitting an optimized database. There are many variables to play with, most important is the number of simulated application threads (option --num-threads). The OLTP benchmark can be run read-only, then it does 14 SELECT queries per transaction. Or it can be run read-write which adds 2 UPDATEs and one INSERT and DELETE.

The latest release of this official sysbench tree is 0.4.12. Many Linux distributions ship a package for this.

However there is also a newer version of

  [Read more...]
Lua and mysql-proxy: how to send a query to an email
Employee +3 Vote Up -0Vote Down

I recently got an inquiry on how to receive an email every time a query was executed via the MySQL proxy. This is very simple and you can achieve it by simply piping the query to the *nix mail command. Here is the sample code (in a file caled send_mail.lua):

function read_query(packet)
  if string.byte(packet) == proxy.COM_QUERY then
    print("Hello world! Seen the query: " .. string.sub(packet, 2))
    print("Sending query to email:")
    os.execute("echo " .. string.sub(packet, 2) .. "| mail -s'Lua test' your.email.here@example.com ")

To execute this, install proxy as per the instructions in the manual. Then simply run the proxy and point it at your script:

  [Read more...]
MockLoad on Launchpad - MySQL Proxy
Employee +1 Vote Up -0Vote Down
Several months ago, I started a little project at work (http://www.mysql.com/products/enterprise/monitor.html), called Mockload. It started as a fun way of using the MySQL Proxy, to test our monitoring agent, as well as the rules engine and graphs on the Service Manager.

I needed a tool that would be easy to use, and improve over time. And that it would allow our QA team to send custom values to the service manager. The goal was to pretend having some very busy MySQL servers.

And what better tool, than the MySQL Proxy itself to pretend being a busy MySQL Server!
The way our agent collects the MySQL related data, is by issuing different sql queries. So, I thought that I could have a

  [Read more...]
MySQL Proxy => proxydb + replication
Employee +4 Vote Up -0Vote Down
A couple of weeks ago I wrote a lua script to use with the MySQL Proxy that transforms the Proxy into a key=>value lookup dictionary.

But I couldn't just stop there. So I decided to add replication to it :).

The basic idea is that you have one proxy that acts like a master, it can handle all write/read operations. Once it receives a write query, it will send that query to the slave proxy instances, and after the last slave gets the query, the master will return a confirmation to the mysql client.
And of course,

  [Read more...]
MySQL Proxy - is it a MySQL server or what is it?
Employee +2 Vote Up -0Vote Down

There are times when you don't need a MySQL server to handle your queries, this could come handy when you are automating tests, or writing interesting Lua scripts.

Yes, you can use the MySQL Proxy for this as well. And have it pretend to be a MySQL server.
Today we'll focus on a simple implementation. We will handle an initial connection, a SHOW DATABASES query and the exit (QUIT) command.

You can see all the hooks

  [Read more...]
MySQL Proxy - proxydb
Employee +0 Vote Up -0Vote Down
What I really like about having Lua and MySQL Proxy together is that it turns out to be very flexible, you can have the proxy do all kinds of things. And the last thing I made the MySQL Proxy do is to act like memcached.

Well, maybe not, but it handles key => value pairs now :P

What does it do?

It handles 5 basic query types:

mysql> INSERT "key" = "value";

Very simple, insert a key => value pair on a proxy.global.db table, if there is already a value for that query, it will overwrite it.

mysql> SELECT "key";

  [Read more...]
How do I use the MySQL Proxy Admin plugin?
Employee +4 Vote Up -0Vote Down
We have an Admin plugin for the MySQL Proxy, but people started asking how to use it. I only found one example, thanks to Giuseppe, but people wanted more.

While the admin plugin is somehow limited, it already provides some nice features. One of the use cases is to give access to information to only authorized users. The Admin plugin uses its own username and password to authenticate users. This is not related to any user on your MySQL server.

The example.

I went ahead and put two scripts together in about an hour. They are basic, but should give you more of an idea of what you can

  [Read more...]
A feature, a bug, a new feature and a bug fix
Employee +0 Vote Up -0Vote Down
After I wrote about reading a master.info file using the MySQL Proxy, I went ahead and added the missing piece. Creating a master.info file using the MySQL Proxy.

A bug?
As I went back to lib/mysql-proto.c looking for a function that I could duplicate and modify to add the to_masterinfo_string() function, I realized that I missed a few master_ssl_* fields. It turned out that I was not exporting all the fields from the master.info file.

This time, the bug fix was easy enough. After modifying the test

  [Read more...]
Reading master.info files using the MySQL Proxy
Employee +0 Vote Up -0Vote Down
After I wrote about a new feature on MySQL Proxy that helps you read master.info files, I thought that showing an example could come handy.

You can find the complete file on the MySQL Forge and once I have a test case for this script, it will be available on Launchpad.

Explaining the code.
You can see at the top I have
local proto = assert(require("mysql.proto"))
This is important, as it makes the from_masterinfo_string() function available for use.

I also included a function called get_command(), which is a modification of code found

  [Read more...]
MySQL Proxy and master.info (contribution 2 of N)
Employee +0 Vote Up -0Vote Down
This time I'll write about a nice featured we now have on the MySQL Proxy.

Parsing master.info
The master.info is a file that the MySQL server creates when you are running a slave server, you can read the manual for more details.
And now you can use the MySQL Proxy to read that file and parse the data from it.

It all started when Jan emailed the proxy discuss mailing with the Changelog from 0.6.1 to 0.7.0. The part that got my attention was:
* added parser for master.info files"
... "
As I was working on a Lua script that did some simulation, I

  [Read more...]
Lua script repository for MySQL Proxy
Employee +0 Vote Up -0Vote Down

Doing QA on the MySQL Proxy and the Enterprise tools, I started writing a Lua script to use with the proxy. The goal was to tell our monitoring agent that the server it was monitoring was very busy. It basically intercepts queries like SHOW GLOBAL VARIABLES or SHOW GLOBAL STATUS among others and returns a custom resultset. (More details on a future post)

It is still a work in progress, but I wanted to give the community access to it. After some emails on the

  [Read more...]
MySQL Test Creation Tool Design Change
+0 Vote Up -0Vote Down
A few days ago the mysql proxy tool was released (link) and it does exactly what the name would suggest. But it does boast a few features that have changed the development of the test creation tool.
  • It has a Lua interpreter that allows user written scripts or modules to be run by the proxy. These scripts can intercept queries and responses between the client and the MySQL server, and also manipulate them in whatever way necessary. There are examples on the wiki of how to use the scripts.
  • Instead of writing an entirely new command line client, we can use the standard mysql client and focus on the scripts that will intercept the client-server communication and translate that into test cases.
For these reasons the test creation tool will not be written in C/C++ but instead as Lua modules
  [Read more...]
A Utility Library for MySQL Workbench
Employee_Team +0 Vote Up -0Vote Down

Here is a small library with various helpful Lua functions for scripts and plugins for Workbench, making it easier to write your own plugins for exporting to some external format, generating reports etc. The library is meant for Workbench 5.0, but it will be ported to 5.1 as well, which should make transitioning plugins from one to the other somewhat easier in the future. Currently, most functions are meant for iterating through various objects so you can, but new functions will be added every once in a while and contributions, bug fixes and comments are welcome. To “install” the library, place the file in your equivalent for C:\Documents and Settings\user\Program data\MySQL\Workbench\librariesQuick list of places to get useful information for plugin writing:

  • the Structs list will list the available

  [Read more...]
LuaSQL fetches results about 15% faster than MySQL Proxy?
+0 Vote Up -0Vote Down

While evaluating LuaSQL as backend connection replacement I came across this. I did a quick performance test using mysqlslap and it showed that just reading and copying the result can be significantly faster with LuaSQL.

Benchmark details

What I did was just sending the query to the backend and building up a new result-set in LUA.

This is the code for LuaSQL:

local _sqlEnv = assert(luasql.mysql())
local _con = nil

function read_auth(auth)
    local host, port = string.match(proxy.backends[1].address,

  [Read more...]

Log4LUA version 0.2 and project page
+0 Vote Up -0Vote Down

Everything is available at the project page

Please report issues and feature request here.

Version 0.2 is a bug fix release but with 2 significant changes in syntax:

  • Changed all constructor methods from create(...) to new. Seems to be more common in the LUA world.
  • The logger class is now returned by the module. So it is local logger = require("optivo.common.log4lua.logger")
    local LOG = logger.new(…)

    instead of

    local logger = require("optivo.common.log4lua.logger")
    local LOG = logger.Logger.create(…)
  • Some potential bugs have been spotted using (a slightly adopted version of)

      [Read more...]
    More fun with LUA - Introducing Log4LUA
    +0 Vote Up -0Vote Down

    UPDATE Log4LUA 0.2 released. Go to the project page.

    After the dust about backend connection handling in MySQL Proxy had settled, I begun working on other parts of HSCALE again. (I’ll return to the backend handling later after talking to Jan Kneschke about their plans.)

    One of the issues that bothered me the most was logging. Until now I just had used a simple debug function that could be enabled or disabled using an environment variable. The pretty straightforward approach a lot of people use. But there is more to logging than to print debug messages. So I searched the web and of course I found

      [Read more...]
    Building mysql-proxy-0.6.0 on CentOS-5.2
    +0 Vote Up -0Vote Down

    I recently needed to configure mysql failover on some of our test machines. Thanks to Sheeri’s helpful blog entry which provides a simple failover lua script, configuring failover is a simple matter. However, the machines are running centos-5.2 and centos doesn’t provide an rpm for mysql-proxy. This blog entry describes how to build your own.

    The latest mysql-proxy (0.6.1) is apparently not backward-compatible with 0.6.0 and earlier. It incorrectly handles the case when one of the backend machines is down. Instead of just marking it as down, it errors out completely. This makes it rather difficult to use it for failover scenarios. People have complained about this for a while. Bugs

      [Read more...]
    MySQL Proxy Lua scripts cannot be written as a module?
    +0 Vote Up -0Vote Down

    Yesterday I went through hell while testing the MySQL Proxy partition Lua scripts I am working on in a high concurrency environment. I am sending multiple queries to the server and build up a combined result set in read_query_result - something like this. The proxy returned weird results complaining about multiple result sets being sent from time to time at totally different places even though I was sure that for each query only one result set has been sent. And some of the results were just wrong, a lot of our tests failed unexpectedly.

    After long hours flicking almost every switch I simply removed the Lua module declaration from the main LUA script passed to the proxy via

      [Read more...]
    Playing arround with the MySQL-Proxy on Mayflower-barcamp
    +0 Vote Up -0Vote Down

    One of the workshops on our Barcamp two weeks ago had to do with the MySQL-Proxy from Jan Kneschke.

    Yet, we found out, that the proxy is rather unuseable for our task. Read here why.

    Continue reading "Playing arround with the MySQL-Proxy on Mayflower-barcamp"
    new responsibility
    +0 Vote Up -0Vote Down
    while we start to wind up development of connector/odbc 5.1, i will also be taking on responsibility for libmysql, the c library that defines the client interface to mysql, and the mysql command-line utilities. there are about 120 active bugs in those areas right now, so the first task will be getting that down to a more manageable number.

    after that, the field will open up for new development. i know that an asynchronous interface to libmysql is on some people’s wishlist, and there are other areas where i think that libmysql could be cleaned up in general.

    but the idea that i think is the most exciting is to

      [Read more...]
    MySQL Workbench: Lua Plugin Support
    Employee_Team +0 Vote Up -0Vote Down

    The Lua plugin support has been finally fixed and will be working in the next release. Together with a few other improvements, it’s now possible to write your own commands to do all kinds of tasks. Documentation for the plugin system is not yet written (and the previous one is outdated), but it should be straightforward to copy the supplied Lua plugin and change it to do something else. All you need is to know a little Lua (which is a simple scripting language) and explore Workbench internals using the GRT Shell (View -> Advanced -> GRT Shell).The sample Lua plugin is located in the modules folder. You can copy it to the custom plugins folder which is shown in the GRT Shell at startup and change things like the module name and add your own functions. I’ll write more about how to write such functions in a future post, like after the next WB is actually

      [Read more...]
    Get ready for the MySQL University lecture on Lua
    Employee +0 Vote Up -0Vote Down

    On December 13 (it means today for most of the readers) at 15:00 CET (14:00 UTC), the MySQL University lecture on Lua will start.
    The topic is quite extensive. Even though the lecture is limited to using Lua with MySQL Proxy, yet there is a lot of ground to cover. During a rehearsal session last week, I realized that the whole matter would need much more than one hour if I describe in detail all the introductory material that I originally planned.
    So I will reduce the time dedicated to MySQL Proxy architecture, which you can look on your own by reading Getting started with MySQL Proxy. I will cover this matter only briefly during the lecture, so if you know some background, you’ll enjoy the lecture

      [Read more...]
    Musings on MySQL Proxy
    Employee +0 Vote Up -0Vote Down
    When seeing that the MySQL Proxy was released, I decided to try to experiment with it since I see strong potential with this tool, both for replication and for other uses (recall that I'm a replication guy, so this is my primary focus). I'm actually on vacation, but this will of course not stop me from tinkering with things (I know, I'm just a hopeless case in this aspect ;) ).

    After reporting a minor bug, I managed to build and run it with some sample scripts. I'm using Kubuntu Feisty, and had some initial problems, but it was actually pretty straightforward. I'll repeat the steps anyway, in case anybody else have problems.

  • Get the source from the repository
    svn co http://svn.mysql.com/svnpublic/mysql-proxy/ mysql-proxy
  • Make sure you have all
  •   [Read more...]
    Showing entries 1 to 24

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.