The reason I prefer this method is that upgrades are generally easier. You simply download the next 5.6.21, extract it, delete the existing MySQL_5620 service, create a new MySQL_5621 service using same command, and run mysql_upgrade once it's started.

Friday, January 4, 2013

Comparison of Protocols

A while ago I wrote a small random function tester to fuzz test native functions such as linestring, polygon, astext, etc. The queries it sends are generally small (100 bytes or less) and a totally CPU bound workload, since no data/tables are accessed.

As this was pretty much an open-ended test, simply pumping random data into the functions, I had planned to let it run for a few days and see if any problems arose.

5.5.29 client and server are used here in all tests. Roughly 345 million queries are sent via 8 threads. Below is a graph to show the QPS of each protocol for the run:

Averages:

The QPS taken every minute is here.
As we see, libmysqld can do nearly 8x the throughput of tcp/ip in this test. This matters, when you're running hundreds of billions of small fast queries.

Conclusion

Embedded speed is clearly superior. For this reason, I always try writing QA/testing code in C/C++ if I think it might need to be run billions of times. But you lose ability to monitor the embedded server status from a mysql client, which is annoying. However, distributing an embedded server application is far easier as it's self-contained. :)

Shared memory doesn't care to enforce wait_timeout, so you may want an idle-connection-killer script
looping in the background. Also, shared memory connection isn't very stable at high concurrency. This stability issue is already being dealt with so that's a plus.

Monday, September 10, 2012

Sometimes you want to just list all the currently executing statements, this is useful for diagnosing hangs or corruptions.

At least GDB 7 supports python macros, which can help us a lot here. I use a core file from 5.5.27, also a non-debug build but not "stripped". So it's a standard build made with -g allowing us to reference symbols.

I wrote a simplistic macro to iterate through mysqld's global "threads" variable.
This is what my ~./.gdbinit looks like:

set history filename gdb_history.txt
set history size 32000
set history save on
set pagination off
set logging overwrite on
set logging on
set print elements 1024
set print pretty on
set print object on
define print_thds
set $thd = ($arg0)->first
while ( $thd != 0 && $thd->next != 0)

It should be obvious that OPTIMIZE TABLE crashed here, since the function name is clear.

The reason I always use windows stack traces in bug reports is because they are readable. Most GDB and linux error log stack traces are not readable by the human brain at a glance, and therefore not memorable. One reason is excessive wrapping, another reason is offsets and arguments to the functions are irrelevant and not useful for search engine indexes or the average Joe trying to find a bug report matching a stack trace.

Which, on a side note is not always possible to do properly with optimized binaries so you get a partially bogus looking stack and you might be tempted to wrongly suspect faulty hardware, foul play, or bad binaries:

Using a technique that involves disassembling the mysqld binary into ASM and piecing together C/C++ source code/comments, it's quite possible to find those inlined functions, expanded macros, or functions that have no name in the symbols file. A nice topic for another posting. So, after manually inspecting the binary + numeric offsets, I could get a proper stack trace:

This is simply a bulk insert performing a 'repair by sort'. It crashed in the keycache when flushing blocks, perhaps due to a memory corruption or overrun of something. I remember fulltext indexes or large table having this problem..

Some Identifying Elements of a Stack Trace
If your 5.1. or 5.5. server ever crashes, please keep the stack trace as it can help identify exactly what the problem is, and you can search google for clues.

Wednesday, June 16, 2010

I'm pleased to report that so far the Soccer World Cup has been pulled off rather successfully, with only minor incidents reported. I have however noticed local news showing some 'feel-good' stories that are obviously written to give a false impression to the international media of the real situation here. Let's hope the unions and Eskom workers don't mess things up by holding a gun to the Country's head with protesting/striking too much in the international media's light.. Hold thumbs..

I thought I should share two important MySQL bugs with you today. In case you ever used YaSSL to establish SSL connections, you were at risk of hitting random crashes due to bug #34236 (Various possibly related SSL crashes) if more than one concurrent connection was ever made. The reason is the YaSSL code was built without mutexes as if for single threaded apps...

Next bug I think is widespread enough to mention is optimizer/query plan related. Examine the testcase on bug #48537 (difference of index selection between rpm binary and .tar.gz, windows vs linux..) And read the changeset notes:

On Intel x86 machines index selection by the MySQL query optimizer could sometimes depend on the compiler version and optimization flags used to build the server binary.

The problem was a result of a known issue with floating point calculations on x86: since internal FPU precision (80 bit) differs from precision used by programs (32-bit float or 64-bit double), the result of calculating a complex expression may depend on how FPU registers are allocated by the compiler and whether intermediate values are spilled from FPU to memory. In this particular case compiler versions and optimization flags had an effect on cost calculation when choosing the best index in best_access_path().

A possible solution to this problem which has already been implemented in mysql-trunk is to limit FPU internal precision to 64 bits. So the fix is a backport of the relevant code to 5.1 from mysql-trunk.

Now I'll get back to enjoying the public holiday and bugs reporting ;-)