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 26

Displaying posts with tag: mysql-proxy (reset)

Lua and mysql-proxy: how to send a query to an email
Employee +2 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):

1
2
3
4
5
6
7
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 ")
  end
end

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...]
Slapping MySQL-Proxy
+0 Vote Up -0Vote Down

I have old applications that need to read (and write) MyISAM tables that themselves receive lots of bulk updates. Time to try MySQL-Proxy.

MySQL Proxy is light on documentation and very few people written anything about working.   Most of what I have read says MySQL-Proxy is not ready for prim time.  I have hope so I had to give it a try.

I started with thee VMware servers. I setup one master and two read only slaves.   I tested the replication with mysqlslap from another independent server and it worked fine.  The slave never ran more then a second behind.

I downloaded

  [Read more...]
Catching erroneous queries, without MySQL proxy
+8 Vote Up -0Vote Down

MySQL Proxy is a really neat tool. I remember a few years back when I first saw Jan talking about it. Back in those days it was significantly different than it is now, but the concept remains the same: direct your database traffic through a man-in-the-middle. Chris Calender’s post on capturing erroneous queries with MySQL Proxy shows one use for Proxy. But wait. MySQL Proxy is just inspecting the MySQL protocol. And unless you’re using it for something else too, having a man in the middle to catch errors is like standing in the middle of the street and blocking traffic to count the cars on the street. Why don’t you stand on the sidewalk to count the cars instead?

Observing without interrupting

Maybe we can use tcpdump. If you search Google you’ll see lots of

  [Read more...]
Updated mysql-proxy benchmarking script (for proxy 0.7)
+0 Vote Up -0Vote Down
My previous post contained a lua script for MySQL proxy that would generate benchmarking information. However, just days (or maybe hours?) after I published it, release 0.7 of mysql-proxy was published, making my script obsolete. I’ve fixed this (it needed just a minor tweak), so here‘s a tarball with the following: trace.lua, which is the […] Related posts:
  • Using MySQL Proxy to benchmark query performance By transparently sitting between client and server on each request,...
  • New release of MySQL Proxy GPL MySQL Proxy has a new release, just three days ago,...
  • Using
  •   [Read more...]
    Using MySQL Proxy to benchmark query performance
    +0 Vote Up -0Vote Down

    By transparently sitting between client and server on each request, MySQL Proxy offers many possibilities for query manipulation.

    Many are explored in the cookbook, and they even include a histogram recipe. Still, I wanted to learn more about the proxy while working on a script that would let me get some stats on the queries executed against a server (or group of servers).

    First things first, get a brief glimpse of the lua programming language since that’s what the proxy’s scripts are written in. Alternatively, you can jump straight into the sample scripts, extrapolate what you don’t

      [Read more...]
    MySQL Proxy is back
    +0 Vote Up -0Vote Down



    MySQL Proxy is back. After a long hiatus due to many deadlines in the Proxy team, the GPL version of MySQL Proxy has been updated.
    The most important news is the location. Proxy is now hosted on Launchpad, like many more MySQL related projects, and MySQL code itself.

    Kay and Jan announced and commented the changes. The repository is now on Bazaar, and there are rules for





      [Read more...]
    Current project status – HSCALE
    +0 Vote Up -0Vote Down

    A lot of people keep asking me about the status of HSCALE so I thought it is best to just write about it.
    Since I am waiting for the next GPL release of MySQL Proxy I concentrate on other things, both project-related and not project-related.

    Continuous integration and test strategy enhancements

    First of all there is a CI server up for almost 3 months now. Check out teamcity.hscale.org (Login as guest user). I also introduced a lint-process to discover bugs in my LUA code which arise mostly by mistyping variable names. By the way: You should definitely have a lint-like process in place if you are doing LUA programming or other languages of that type. It helps a lot.

    What’s in svn trunk?

    Multiple backends

    The current status is this:


      [Read more...]
    What is an “unauthenticated user”?
    +0 Vote Up -0Vote Down

    Every so often we have a client worrying about unauthenticated users. For example, as part of the output of SHOW PROCESSLIST they will see:

    +-----+----------------------+--------------------+------+---------+------+-------+------------------+
    | Id  | User                 | Host               | db   | Command | Time | State | Info             |
    +-----+----------------------+--------------------+------+---------+------+-------+------------------+
    | 235 | unauthenticated user | 10.10.2.74:53216   | NULL | Connect | NULL | login | NULL             |
    | 236 | unauthenticated user | 10.120.61.10:51721 | NULL | Connect | NULL | login | NULL             |
    | 237 | user                 | localhost          | NULL | Query   | 0    | NULL  | show processlist |
    +-----+----------------------+--------------------+------+---------+------+-------+------------------+
    
      [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:

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

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


      [Read more...]

    Version 0.3 of HSCALE is almost in the door
    +0 Vote Up -0Vote Down

    After working on build and test improvements (for example incorporating lualint and LuaCov) as well as other lua “side-projects” (i.e. Log4LUA) we are running towards HSCALE 0.3.

    The focus of the forthcoming version 0.3 of HSCALE is Dictionary Based Partition Lookup. Using this partition lookup module lets you take full control over how your partitions are created and where they are actually located.

    Update: Dictionary Based Partition Lookup is fully implemented. See this blog post and the wiki page about it.

    Please note: Due to

      [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 vs. HSCALE
    +0 Vote Up -0Vote Down

    Recently I added the first code to support multiple MySQL backends in HSCALE (see svn.hscale.org). As a “side note” I have to thank Giuseppe Maxia for MySQL Sandbox which made multi server testing a bliss!

    While coding this I started to feel that writing HSCALE on top of MySQL Proxy is no more as easy and clean as it started out to be. And finally I reached the point where I have to say:

    HSCALE (and maybe other advanced multi-server applications) and (current) MySQL Proxy don’t fit very well.

    Before I go into details please let me make clear that this is mostly due to the specific nature of MySQL Proxy being a connection and protocol based proxy. MySQL Proxy is great and there are a lot of cool applications that fit perfectly with it.

    Aside from

      [Read more...]
    OSCON 2008 - MySQL Proxy - from architecture to implementation
    +0 Vote Up -0Vote Down

    The presentation about MySQL Proxy at OSCON 2008 is over.

    Here are the slides.

    Thanks to my co-presenter Ronald Bradford and to all the participants. If you have more questions about the session, please use this blog's comments.
    Proxy webinar - slides, script, FAQ
    +0 Vote Up -0Vote Down
    We did it. Designing Scalable Architectures with MySQL Proxy (http://www.mysql.com/news-and-events/web-seminars/display-159.html) was delivered successfully, with over 150 attendees.
    There is a large number of questions that were asked during the session, and you can find them in MySQL Proxy FAQ.
    The slides, with the highly entertaining images used by John Loehrer to illustrate his point are also online
    Finally, John posted his Connection pooler Lua script in the Forge.
    Thanks to John Loehrer for his lively presentation, to Jimmy Guerrero and Rich Taylor for organizing the event, to Jan Kneschke, for answering questions online while we were



      [Read more...]
    Shaping the future of MySQL Proxy
    +0 Vote Up -0Vote Down

    Here is a chance for all users to influence the future development of MySQL Proxy.
    If you care about Proxy, you may want to check the current quickpoll in the Dev Zone, and vote for your favorite features.
    The developers have a truckload of ideas, of course, but only a finite amount of time. So they can't develop all the features at once. They must start somewhere, and your vote can help them decide which ones should get higher priority.


    In the meantime, don't forget the incoming webinar on Designing Scalable Architectures with MySQL Proxy (http://www.mysql.com/news-and-events/web-seminars/display-159.html). It's TODAY (tomorrow for some, depending on your time zone), July 8, 2008 at 10am PDT, 1pm EDT, 7pm CEST.
    mysql proxy 0.6.1 performance tests
    +0 Vote Up -0Vote Down

    The mysql proxy project has tremendous potential to make mysql administration and usage easier. I decided to throw some load at it to get a feel for how stable and performant it is.

    On EC2, I set up 6 “small” images in an example proxy setup:

    - One client machine to run sysbench
    - One machine to act as a mysql proxy machine, running 0.6.1 (FC4 binary)
    - Four identical database servers, running mysql 5.0.45

    The database configuration was largely default, with InnoDB configured for 64MB buffer pool (just enough to ensure the sysbench table was entirely in memory), 512MB log files, and 1024 max connections.

    mysql-proxy was run with the following command:

    mysql-proxy –proxy-backend-addresses=ip-10-251-66-63.ec2.internal:3306 –proxy-backend-addresses=ip-10-251-71-21.ec2.internal:3306











      [Read more...]
    HSCALE 0.2 released and new project web page
    +0 Vote Up -0Vote Down

    The main focus of version 0.2 was to improve handling of almost all of SQL. So now you can issue DESC TABLE tbl_name or RENAME TABLE tbl_name TO another_tbl_name on a partitioned table and you get correct results for SHOW TABLES etc.
    Other statements are rejected and we settled down for the feature set we want to provide for full partition scans.
    In addition to that there are some performance improvements.

    See the full list of changes.

    For the next release (0.3) we focus on the dictionary based partition lookup module and further performance improvements.

    Finally, there is a new project home page: http://www.hscale.org.

    Update: Benchmark HSCALE with MySQL Proxy 0.7.0 (svn) against 0.6.1
    +0 Vote Up -0Vote Down

    Earlier today I posted these benchmark results testing HSCALE and MySQL Proxy performance.

    As Jan Kneschke (the author of MySQL Proxy) pointed out there are quite some improvements in the current development version (svn trunk). So I gave revision 369 a try.

    Tests were all the same as mentioned in my previous post. And indeed we see quite dramatic improvements. While the performance of the Lua script stayed almost the same the footprint of the proxy itself sank to only 50 to 65%. Here are the numbers:

    Version / Concurrency MySQL MySQL Proxy Empty Lua Tokenizer QueryAnalyzer HSCALE w/o partitions HSCALE w/ partitions 0.6.1 / 40 217 1302 7667 7091 6162 7552 7577 0.6.1 / 20 217 557 2536 4532 4524 4325 4564 0.6.1 / 10 287 641 675 1179  [Read more...]
    Benchmark MySQL Proxy and HSCALE
    +0 Vote Up -0Vote Down

    As part of developing HSCALE, a partitioning / sharding solution, I set up a benchmark test suite. I made it scripted and thus repeatable to monitor the progress and performance regressions during the development.

    Test Suite

    The test suite uses mysqlslap to benchmark the overhead of MySQL Proxy itself in real life scenario as well as the different components of HSCALE – query analyzing and query rewriting. The complete test suite is available in the svn trunk at http://svn.hscale.org under hscale/test/performance/mysqlslap. There you find a build.xml – an Ant buildfile that is used to set up the test

      [Read more...]
    Presentation Slides: Introduction to HSCALE
    +0 Vote Up -0Vote Down

    No, these slides are not fresh from the User Conference in Santa Clara…

    Today, I held a presentation in front of all developers and support engineers of our technical department about database partitioning, MySQL Proxy, HSCALE and the progress we are making.

    Download the presentation slides here.

    HSCALE 0.1 released – Partitioning Using MySQL Proxy
    +0 Vote Up -0Vote Down

    As written here and here I’ve been working on a MySQL Proxy Lua module that transparently splits up tables into multiple partitions and rewriting all queries to go to the right partition.

    I finally got everything together to release a 0.1 version. Go on and download, try and read more about HSCALE 0.1.

    All this started out as a prototype just to see if it could be done. And after adopting parts of our main product to use partitions via HSCALE + MySQL Proxy (which was an easy task, we just had to rewrite a few out of hundreds of statements) I really think that this could work out in a larger scale.

      [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"
    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...]
    Showing entries 1 to 26

    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.