The latest from the MySQL community

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 understand of the syntax by making paralelizations against other …

Scenario: Grow from two data nodes to four using ONLINE ADD NODE and REORGANIZE PARTITION of a 2GB table, and UPDATE traffic

Setup for each data node:
* Dual cpu - quad core running at 2.33GHz.
* 8GB of RAM
* DataMemory=4096MB
* MaxNoOfExecutionThreads=8
* Configuration generated by Configurator
* Bound execution threads to cores (LockExec/Maint....Thread..)
* Completely distributed setup.

Created the following table and filled it with 2M rows which gives a table (different of last time to simplify further tests) of 2GB in size:

CREATE TABLE `t1` (
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`b` int(10) unsigned DEFAULT NULL,
`c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`d` varbinary(768) DEFAULT NULL,
`e` varbinary(256) DEFAULT NULL,
…

I want to measure the time it takes to do various ONLINE ADD NODE operations in MySQL Cluster 7.0 and especially how long time it takes to repartition the data onto the newly added nodes. So here comes the first post on the subject.

First off: Go from two data nodes to four using ONLINE ADD NODE and REORGANIZE PARTITION of 2GB tables, and no traffic system.

Setup for each data node:

• Dual cpu - quad core running at 2.33GHz.
• 8GB of RAM
• DataMemory=4096MB
• MaxNoOfExecutionThreads=8
• Configuration generated by Configurator
• Bound execution threads to cores (LockExec/Maint....Thread..)
  
• Completely distributed setup.
  
…

bencher is a test program that allows you to benchmark requests on MySQL Cluster. I have used this utility a lot of customers, because it lets me:

• specify a simple query that I want to benchmark on the command line
  
• implement more complex use cases.
  
• implement NDBAPI requests

and I don't have to reinvent the wheel every time. It is all there: connectivity, multi-threading support, timers, and some basic statistics, and it compiles on most platforms. I just have to focus on the queries I want to optimize or benchmark.

The simple use case is to specify the SQL query you want to benchmark, the number of threads, and how many times. You can also customize this very easily to benchmark more elaborate SQL requsts, and NDBAPI requests.

…

Not to rehash an old argument, but I am watching a recent  video on performance work being done by Sun hosted by Allan Packer (a recorded MySQL University session) and one of his bullet points was “Scale out when it makes sense, rather than just because there is no alternative”. That is what they are working on in his group at Sun.

You mean I will be able to buy 8 and 16 cores boxes and be able to use all the cores?

Hallelujah and Amen brother

Allan, thanks to you and all other who are working on this both inside and outside Sun.

Giving a bunch of mysql instances something you do everyday and you might think ….. how should I do it? Write a bunch of selects and inserts manually? nahh that takes s**tload of time, should I run binlogs collected from a live system on my test server? nahh thats not practical nor is it real since it doesn’t contain selects, should I gather the general query log and try that out? nahhh  …..

MySQL has been kind enough to supply us with their mysql_slap which does the job for us and given I needed to do a proof of concept on monitoring a group of 4 circular replicated servers I wrote a small script which does the job of slapping them with a varying level of concurrancy, iterations, number of queries and connections for as long as you like.

Here it is and I hope some of you might find it useful for slapping their own test servers :).

#!/bin/bash

NumberOfConcurrentLoads=4
…