Via SpamExperts:

SpamExperts has their own anti-spam filtering cloud which is provided as a software-as-a-service (SaaS) model. In addition they have an e-mail security product which they install, update and monitor on-site. Most of SpamExperts’ anti-spam technology has been developed in-house and makes extensive use of the MySQL database. All clients contribute in real-time to their filtering effectiveness, so they have many different data flows that are handled by MySQL. Replication is used to synchronize the data between the systems in a cluster and to push data feeds in real-time. The SaaS cloud is replicated across four countries for redundancy, whereas client installations spread the data retrieval around the globe.

After extensive testing, SpamExperts managed the efficient migration of approximately three hundred servers from MySQL to MariaDB within three hours. “In the past we have run into various MySQL bugs. Despite the fact that we reported them, the bugs were still not solved after a year,” says Dreas van Donselaar Chief Technology Officer for SpamExperts B.V. “There were bugs in the upgrade procedure from MySQL 5.0 to MySQL 5.1, thus preventing us from using the entire new 5.1 feature-set”.

“MariaDB had the same bugs that we ran into with MySQL. However the big difference was that when we reported these bugs, they were quickly resolved within 48 hours!” exclaimed Dreas. The quick turnaround time bolstered confidence in the quality of the MariaDB product and its support and development teams.

During the testing phases, SpamExperts discovered a few bugs during the conversion that affected about 5% of their systems: long table names were not correctly converted, the thread_stack was too low on certain systems for MariaDB 5.1 which resulted in some crashes, newly created tables during the conversion resulted in table name conflicts and replication breakage due to table name changes. These bugs were all present in MySQL and have since been resolved in the MariaDB codebase. This process was resolved generally using Internet Relay Chat (IRC) on the #maria channel on irc.freenode.net.

Asked how SpamExperts is enjoying the use of MariaDB after a couple months of usage, Dreas responds: “Its running great! We have not encountered any major new issues. Its very nice to work with a team that is so passionate about its software and we believe it should allow MariaDB to quickly become the next relational database standard.”

Baby fur seal, South Georgia

As Hakan mentioned previously, the full text of SQL-99 Complete, Really by Peter Gulutzan and Trudy Pelzer, is now in the AskMonty.org Knowledgebase. Importing the text and formatting it for the Knowledgebase was a major project and I’m glad that it’s done.

Having the full text of this book freely available is a great thing for anyone who uses SQL because the book is about the SQL-99 standard and not about any particular database implementation. They do talk about different implementations, but those sections are clearly marked as such, and serve as examples of  how some databases implement (or diverge from) the standard.

The question now is: What’s next? Both for the SQL-99 section, and for the Knowledgebase in general?

First, we want to integrate the SQL-99 text into the rest of the Knowledgebase. At present, it is largely isolated from the other main section (the MariaDB section). We’ll start by cross-linking between SQL-99 and MariaDB articles. Going forward we will incorporate the SQL-99 text into new and existing MariaDB-related articles, where appropriate.

Beyond SQL-99, we also want to grow the Knowledgebase into a repository of all sorts of database-related information. To that end, we’re on the lookout for more ready-made content to add to the Knowledgebase. If you own or control the rights to a database-related book, and would like to see it added, please contact us. We’re even willing to purchase the rights, if necessary. Bonus points if the book is directly about MariaDB or MySQL, but we’re also interested in other databases.

Speaking of the Knowledgebase, we just rolled out some updates to make it easier for anyone who wants to to participate in creating and editing content to get started. If you’d like to help out, please do! If you have questions on how to get started you can almost always find me and other Knowledgebase admins in #maria on Freenode (my Freenode nick is dbart) or you can join the maria-docs mailing list and ask there.

In our upcoming MariaDB 5.3 release Monty optimized the internal string append code for performance. I tested his patch with a plain MariaDB 5.2 vs. a patched MariaDB 5.2 with sql-bench, which showed an overall performance gain around 3%.

The details of the patch Monty describes like this

Patch to optimize string append:

While examining a trace output from a mysql-test-run case, I noticed a
lot of reallocation calls.  This was strange as MariaDB/MySQL was
designed to do so few malloc/realloc as possible.  The system uses was
to first calculate how big buffer you would need and then allocate a
buffer big enough for most usage. Then there was some safety calls to
extend the buffer if needed.

What had happened in the MySQL code over time was that new coders was
using a buffer that was not preallocated and it was filled with a lot
of append calls(), each which caused a new realloc().

I fixed a couple of the worst cases to prealloc the buffer properly,
and for the other cases I made a general fix by making the
reallocation addaptive:

The essence was to replace the realloc() call with the following function:

In MySQL 5.3, this gave a speedup for some test cases (in debug mode)
with 25 %.  For an optimized version of MySQL we got a general
performance boost of 3 %.  The difference comes from the fact that
we do a lot of extra checks for the malloc() call in a debug build and
fewer malloc() calls gives a notable speed improvement.

The full patch can be seen by doing this in the MariaDB 5.3 tree:
bzr log  -p -r monty@askmonty.org-20101108114354-jl61qx8e36gvbzm7

Here are the detailed results of the sql-bench runs. I ran sql-bench with MariaDB 5.2.2 with default settings and used MyISAM as storage engine.

Column 1 is in seconds. All other columns are presented relative
to this. 1.00 is the same, bigger numbers indicates slower.

1) MariaDB 5.2.2.
2) MariaDB 5.2.2 patched with Monty’s string append optimization.
3) MariaDB 5.2.2 patched with Monty’s string append optimization.
4) MariaDB 5.2.2 patched with Monty’s string append optimization.

The number in () after each tests shows how many SQL commands the particular
test did.  As one test may have many different parameters this gives only
a rough picture of what was done.

I ran the test on a quite fast machine and therefore you can ignore the results with run times around 0 – 3 seconds. I am adjusting the tests to run at least for 5 seconds to have more reliable results.

As machine I used our new benchmark system “pitbull”, which I also used for benchmarking the MyISAM segmented key cache performance.