After having worked for two years and one quarter of a year with MySQL AB (now Sun), I have decided to resign and pursue another career.
Before I joined MySQL AB I was working as a database consultant and application developer. I knew that joining MySQL would mean giving that up, but I was happy to do so because I really liked the MySQL product. I was also attracted by the company itself, the flexible working hours, working from home, travelling abroad, and, very important, being one of the few companies that is capable creating FOSS software on a commercial basis.
I have had a splendid time at MySQL AB, and later Sun. I met lots of smart people and travelled to a bunch of places I would probably not've had the chance to visit otherwise. I learned a lot about the MySQL server, and a lot about the challenges involved in making an open source software company profitable. I've been given the chance to co-author a book on MySQL Cluster, and authored the cluster and associate certification exams. Finally, I created a training course that learns you how to write MySQL UDFs.
Looking back, I feel quite satisfied, and I'm thankful for having had this opportunity. However, slowly but surely I have noticed that I have been less involved in technical stuff, at least professionally. I have been spending so much time on hobby hacking in my spare time, that I reconsidered my position, and decided that I would really like to pick up application development again. At some point, a good opportunity came by, and I decided to seize it. So, that's what I did.
As of the 1st of October, next Wednesday, I will be working for Strukton Rail. I will be working for a department that's responsible for business process improvement and re-engineering. I will be doing a bit of business process analysis and consultancy, but the larger part of my job will consist of developing a Web application to model business processes and to support project management. These applications will have a distinctly graphical nature, and I have a pretty clear idea where I want that to end up. In addition I will be adding business intelligence to these applications.
I will be in the lucky position to be able to decide for a large part which technologies and products we will be using, and I think I will be able to build all of it on open source/free software. I am pretty certain that I will be using MySQL, Pentaho, PHP, ExtJs and Google Gears.
My intention is to keep blogging about MySQL. Most likely I will write more about all these other technologies too.
I'd like to finish by saying again what a wonderful time I had with MySQL/Sun! I wish MySQL/Sun the best of luck, and I want to thank all of my colleagues there. Thank you! I have learned a lot from you, and I'm looking forward to meeting again some place, some time.
Roland
Friday, September 26, 2008
Tuesday, August 26, 2008
Webinar: Introducing MySQL Workbench
September 16, 10:00 am PST, 1:00 pm EST, 18:00 GMT
I'm proud to inform you that I will be hosting a MySQL Workbench Webinar, September 16. You can find the announcement right here.
I have spent quite a deal of time testing MySQL Workbench. Thanks to the hard work of the MySQL Developer Tools Team, the tool just becomes better and better each new build. Personally, I think it is high time to present its features and benefits in a web seminar, and when Giuseppe asked me to do it, I was more than happy to oblige.
I'll shortly post an outline of some of the key features I will be presenting. Most likely, I'll address a number of issues I've seen recurring on the MySQL Workbench Forums, but I also intend to demonstrate a number more advanced features that I hope will benefit DBAs as well as Developers.
Stay tuned ;)
Tuesday, July 29, 2008
MySQL aggregate UDF Performance
At the MySQL User's conference 2008 I did a tutorial on MySQL User-defined functions. At the same conference, Brian Miezejewski and Mark Atwood did a talk on aggregate UDFs.
In relation their upcoming talk, Mark wrote a blog post reporting that MySQL Aggregate UDFs are three times slower than equivalent native functions:
I just tested it on Linux and Windows. For a simple aggregate UDF that is equivalent to the built-in
If you are interested in trying this on your own system, download these instructions. Would be cool if you could leave a comment here of your results, TIA
In relation their upcoming talk, Mark wrote a blog post reporting that MySQL Aggregate UDFs are three times slower than equivalent native functions:
I just proved to myself via testing and benchmarking that user defined aggregate functions are about three times slower than the call interface to the built-in functions.Later on he posted an update, explaining that it looks like this result is probably "a MacOS thing".
It turns out to be a MacOS thing. On Linux, the performance penalty is less than 10%. Further research will surely be done.
I just tested it on Linux and Windows. For a simple aggregate UDF that is equivalent to the built-in
COUNT() function, I found that the UDF is about 5% slower than the built-in function. Personally I think that's not too bad.If you are interested in trying this on your own system, download these instructions. Would be cool if you could leave a comment here of your results, TIA
Thursday, July 24, 2008
Inspect the Query Cache using MySQL Information Schema Plug-ins
A while ago I wrote about MySQL 5.1 information schema plug-ins.
At the time, I wrote a plug-in to report the contents of the query cache, but for all kinds of reasons, I never found the time to write a decent article about it, nor to release the code.
I am not sure if I'll ever find the time to write that article, but I just tidied up the code, and installed it in a new MySQL 5.1.26-rc server. It seems to work there, so I put the code up on the web.
Inside the source file, there's instructions to build and deploy it. If all goes well, you can do things like:
Are you interested in this plugin-in? Do you have any suggestions? Download it, and put your comments here.
At the time, I wrote a plug-in to report the contents of the query cache, but for all kinds of reasons, I never found the time to write a decent article about it, nor to release the code.
I am not sure if I'll ever find the time to write that article, but I just tidied up the code, and installed it in a new MySQL 5.1.26-rc server. It seems to work there, so I put the code up on the web.
Inside the source file, there's instructions to build and deploy it. If all goes well, you can do things like:
mysql> select * from information_schema.mysql_cached_queries\G
*************************** 1. row ***************************
STATEMENT_ID: 1
SCHEMA_NAME: test
STATEMENT_TEXT: select count(*) from world.city
RESULT_BLOCKS_COUNT: 1
RESULT_BLOCKS_SIZE: 512
RESULT_BLOCKS_SIZE_USED: 106
1 row in set (0.00 sec)
Are you interested in this plugin-in? Do you have any suggestions? Download it, and put your comments here.
Building MySQL from Source: There's a fine line...
There's a fine line between love and hate, you see
Can't wait to late, but baby I'm on it
(From: "Liberation" by Outkast)
Suppose you need to build a MySQL server with the same options as a pre-built one, distributed through the MySQL download site.
Sounds strange?
Well, maybe, but that's what you need if you want to compile certain MySQL 5.1 plug-ins so that they'll will play nice with such a pre-built server.
Some plug-ins depend on nothing more but the
plugin.h header file, but for example storage engine plug-ins require things way beyond that. If you want to compile those yourself, you are required to first build a MySQL server from source, even though you will install the plug-in in another, pre-built, server. (If you want to see how this works in practice, check out the excellent documentation for building and installing the PBXT storage engine plug-in.)
Now, the snag is, the server you need to build must ideally be built in exactly the same way as the deployment server. So, the question is, how do you find out how your server was built?
As it turns out, there is a very simple method to obtain the original compile line. You can find it in the
mysqlbug script. On line 28 you see the following line:
CONFIGURE_LINE="./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community Server (GPL)' '--with-server-suffix=' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--with-pic' '--with-fast-mutexes' '--with-client-ldflags=-static' '--with-mysqld-ldflags=-static' '--with-zlib-dir=bundled' '--with-big-tables' '--with-ssl' '--with-readline' '--with-embedded-server' '--with-partition' '--with-innodb' '--without-ndbcluster' '--with-archive-storage-engine' '--with-blackhole-storage-engine' '--with-csv-storage-engine' '--without-example-storage-engine' '--with-federated-storage-engine' '--with-extra-charsets=complex' 'CC=ccache gcc -static-libgcc' 'CFLAGS=-g -O3' 'CXXFLAGS=-g -O3' 'CXX=ccache gcc -static-libgcc'"
Are you curious what will happen if you compile your plug-in against a server that is not built the same as the deployment server? Well, the plug-in won't be "playing nice" with your server, and it is likely that something nasty will happen, such as a crash, or worse: the plugin may unintentionally change the behavior of the server, with unwelcome results such as data corruption.
So, find the
mysqlbug script, (it's normally located in the MySQL bin directory) and find that fine line...
Friday, July 11, 2008
MySQL: DIVide and Conquer
Everybody that has had to do some numeric calculations in SQL will have encountered this simple problem: divide an integer by another integer and round down the outcome to the nearest integer. In many cases people write it like this:
Simple enough, right? First we do the division
Update: My claim that
Thanks Kai!
However, there is a better way.
We can use the integer division operator
This approach has a number of advantages:
To prove the last point, take a look at the results of a simple benchmark. I simply used the
I repeated this two more times and averaged the time spent, and then made this little graph of the results:
The results show that
Now, I don't think the performance benefit is of much practical significance. You may see a slight improvement for large datasets using multiple division operations, but in many cases the ordinary query processing will probably have a much larger part in the total time spent. But still,
FLOOR(a/b)
Simple enough, right? First we do the division
a/b, then we round down using the function FLOOR(). Update: My claim that
TRUNCATE(a/b, 0) is equivalent to FLOOR(a/b) is false! It maybe true when the outcome of the division is a positive number, but in case of a negative number, TRUNCATE() will only lose the decimals (resulting in a higher negative number) and FLOOR() will still round down.Thanks Kai!
However, there is a better way.
We can use the integer division operator
DIV instead of the ordinary division operator /. Because this is an integer division, there is no need for an extra function to lose the decimals, and the expression is simply:
a DIV b
This approach has a number of advantages:
- It is explicit. By looking at the expression we know immediately that the result will be an integer, and that
aandbare meant to be integers too. - It is easier to read. Because we don't need another function and parenthesis, this expression is easier on the eyes, something that you will appreciate if the expression is not simply
FLOOR(a/b)but something likeFLOOR(SUM(a)/SUM(IFNULL(b,0))) - It is fast! The
DIVoperation does not have to deal with complex floating point math, and will be much faster on most microprocessors
To prove the last point, take a look at the results of a simple benchmark. I simply used the
BENCHMARK() function and executed:
mysql> SELECT BENCHMARK(10000000,1234567 DIV 7) ;
+-----------------------------------+
| BENCHMARK(10000000,1234567 DIV 7) |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (0.83 sec)
mysql> SELECT BENCHMARK(10000000,1234567 / 7) ;
+---------------------------------+
| BENCHMARK(10000000,1234567 / 7) |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (7.26 sec)
mysql> SELECT BENCHMARK(10000000,FLOOR(1234567 / 7)) ;
+----------------------------------------+
| BENCHMARK(10000000,FLOOR(1234567 / 7)) |
+----------------------------------------+
| 0 |
+----------------------------------------+
1 row in set (8.80 sec)
I repeated this two more times and averaged the time spent, and then made this little graph of the results:
DIV is about 9 to 10 times faster than the ordinary division operator, and that adding FLOOR() function makes the entire expression another 10% slower.Now, I don't think the performance benefit is of much practical significance. You may see a slight improvement for large datasets using multiple division operations, but in many cases the ordinary query processing will probably have a much larger part in the total time spent. But still,
DIV is faster, easier to read and more explicit if you want to solve this type of problem.
Wednesday, July 09, 2008
A fast, single pass method to calculate the median in MySQL
After stepping off of the
I previously wrote on how to calculate the median using
Apart from not relying on setting the buffer size for
At the heart of the solution are the user-defined variables:
These are initialized in the inmost subquery:
Note that this yields one row, initializing the row number
You see, first we divide the total number of rows by two. If there is an even number of rows,
The calculation of the left median is along the same lines:
We just have to take care that in case we do have an odd number of rows, we need to pick the true median position here too.
To do that we first find out if there is an odd number of rows using
Now that we have these values, we can use
And this will give us at most two rows in case of an even number of rows, and one row for an odd number of rows.
The final step is to average the values from the rows, which is required to calculate the median in case of an even number of rows:
GROUP_CONCAT() solution for calculating quantiles I figured it would be nice to find a better way to calculate the median too. Solution
I previously wrote on how to calculate the median using
GROUP_CONCAT(), but I think that this is a better way:
SELECT AVG(length) AS median -- take the average of left and right median
, MIN(length) AS left_median --
, MAX(length) AS right_median --
, @l AS left_median_position --
, @r AS right_median_position --
FROM (
SELECT @n, length -- @n is just here to facilitate debug
FROM film
CROSS JOIN (
SELECT @n:=0
, @r := COUNT(*) DIV 2 + 1 -- right median or true median
, @l := COUNT(*) DIV 2 -- left median, or true medain
+ IF(
COUNT(*) % 2 -- even or odd?
, 1 -- odd, need next value
, 0 -- even, need true left median
)
FROM film
) ``
WHERE (@n:=@n+1) -- row number
BETWEEN @l AND @r -- select two middle ones
ORDER BY length -- need to sort to get middle values
) ``
Apart from not relying on setting the buffer size for
group_concat_max_len, this solution is also faster than the GROUP_CONCAT() solution. For example, calculating the median amount from the payment table in the sakila database takes some 0.12 seconds using the GROUP_CONCAT() solution and about 0.03 seconds with this method.Explanation
At the heart of the solution are the user-defined variables:
@n: the row number@l: the position of the 'left median' that is the row number with the highest value in the lower half of all rows@r: the position of the 'right median', that is the row number with the lowest value in the higher half of all rows
These are initialized in the inmost subquery:
SELECT @n:=0
, @r := COUNT(*) DIV 2 + 1 -- right median or true median
, @l := COUNT(*) DIV 2 -- left median, or true medain
+ IF(
COUNT(*) % 2 -- even or odd?
, 1 -- odd, need next value
, 0 -- even, need true left median
)
FROM film
Note that this yields one row, initializing the row number
@n to zero. We can calculate the position for the right median immediately by doing
COUNT(*) DIV 2 + 1
You see, first we divide the total number of rows by two. If there is an even number of rows,
COUNT(*) DIV 2 will give us the left median, and adding 1 is then by definition the right median. If COUNT(*) is an odd number, it still holds: the integer division rounds down to the nearest integer value, and adding 1 then gives us the position of the true median in that case.The calculation of the left median is along the same lines:
@l := COUNT(*) DIV 2 -- left median, or true medain
+ IF(
COUNT(*) % 2 -- even or odd?
, 1 -- odd, need next value
, 0 -- even, need true left median
)
We just have to take care that in case we do have an odd number of rows, we need to pick the true median position here too.
To do that we first find out if there is an odd number of rows using
COUNT(*) % 2. This calculates the remainder of dividing the total number of rows by two. In case of an odd number of rows, the remainder is 1 which is considered to be TRUE by the IF function. In this case, 1 is added, effectively making @l equal to @r (that is, both hold the position of the true median). In case of an even number of rows, we add 0 as COUNT(*) % 2 is already the desired position of the left median.Now that we have these values, we can use
@n to generate a rownumber, which we can compare against the calculated @l and @r values:
WHERE (@n:=@n+1) -- row number
BETWEEN @l AND @r -- select two middle ones
ORDER BY length -- need to sort to get middle values
And this will give us at most two rows in case of an even number of rows, and one row for an odd number of rows.
The final step is to average the values from the rows, which is required to calculate the median in case of an even number of rows:
SELECT AVG(length) AS median -- take the average of left and right median
...
FROM (
...
) ``
Subscribe to:
Posts (Atom)
DuckDb Performance: min, max, and median vs quantile
I was playing with some classical statistics in DuckDB and I ran into something I'd like to share. It's about the measures minimum,...
-
Every now and then, people are puzzled by the precise status and extent of MySQL support for dynamic SQL. Statement Handling MySQL support ...
-
Some time ago, I announced the MySQL UDF Repository . In short, the MySQL UDF Repository tries to be a one stop place to obtain high qualit...
-
Handling cursor loops in MySQL keeps puzzling people . Single Cursor Loops The common case is to have a simple cursor loop. Each record is ...