Continuing from my earlier post Auditing your MySQL Data, Roland has accurately highlighted that my initial post leaves out some important information for auditing. As the original charter was only to keep a history, for the purpose of comparing certain columns, a history was all that was needed.

Providing a history of changes forms the basis of auditing, and in keeping with my post title and intended follow-up, this is the all important second part. However in order to provide true auditing additional information is necessary. This includes:

1. When was an operation performed
2. What operation was performed, i.e. INSERT, UPDATE and DELETE
3. Who performed the operation

Date and operation can …

to build connector/odbc 5.1 on mac os x leopard, the first thing you will need is xcode. then you will want to install a recent version of mysql (5.0 or 5.1, or even 6.0 if you are feeling adventurous).

to be able to build the gui setup library, you will need to install qt, but i have found it easiest to work with qt3, not the latest qt4. you can download the last release of that from trolltech’s ftp server — the file is called qt-mac-free-3.3.8.tar.gz. you will need to apply this patch to allow it to compile on leopard. i configure it with some options to eliminate stuff i don’t care about, and to build statically:

./configure …

KEY ACCOMPLISHMENTS LAST WEEK

• Worked on Skoll's data processing scripts and test result generation infrastructure.
• Modified packaging format of runtime data collection, started automated runtime information collection tasks with the new Skoll client.
• Researched tools and methods to analyze runtime information (data mining and Weka).

KEY TASKS THAT STALLED LAST WEEK

• None

KEY CONCERNS

• None

TASKS IN THE UPCOMING WEEK

• Continue to research tools and methods to analyze runtime information.
• Modify Skoll to use push-build tar balls for compilation and testing.

To understand the runtime behavior of MySQL under different configurations, Skoll needs to collect runtime data while testing MySQL builds. To accomplish this, the Skoll client takes advantage of gcov, a test coverage program that's part of the GNU Compiler Collection (GCC). gcov collects runtime information such as how many times a line of code, a method or a file was executed, what was the coverage and much more.

The source tree of MySQL actually provides a few build scripts that enables gcov under the BUILD directory. However, these scripts build MySQL using the default configuration; not exactly what Skoll needs. Skoll client builds and tests MySQL in different configurations by passing compile-time and run-time flags to the configure script before compilation. For example …

So I was hacking around in the MySQL server code this weekend, and took at look at the implementation of the UUID() function.

Instead of using Theodore Tso uuid library, which now ships by default on all Linux and MacOS machines (I dont know about BSD and Solaris), we implemented our own from scratch. With a global mutex lock on generation, which will utterly blow on a multicore machine.

So I pulled down a branch of the server source code from Launchpad, and then branched it again twice locally. In those two branches, I replaced the implementation with a call to libuuid.so uuid_generate_random() and with a call to libuuid.so uuid_generate_time(). Then I built all three trees, and benchmarked 10 …

I was at a client site with Yves Trudeau ( another MySQL consultant ) and the client had purchased a brand new top of the line 16 core server.  It is well documented in many places that scalability beyond 4-8 cores with innodb is less then optimal.  We were asked for a ballpark on the performance of a 16 cores vs an 8 cores, and specifically if their were any options to reduce the number of cores the mysqld process could use.   We decided to benchmark this using DBT2.   To do the test we ended up setting the CPU affinity of the mysqld process.  You can set this with the following command:  taskset.   Yves ended up trying the same DBT2 tests for 1-16 cores.    I won’t write too much about the scalability here, as I said their are way better resources out their that can explain it better, but what I wanted to do is post the results of …

The Sphinx project just released version 0.9.8, with many enhancements since the previous release. There’s never been a better time to try it out. It’s really cool technology. What is Sphinx? Glad you asked. It’s fast, efficient, scalable, relevant full-text searching and a heck of a lot more. In fact, Sphinx complements MySQL for a lot of non-search queries that MySQL frankly isn’t very good at, including WHERE clauses on low-selectivity columns, ORDER BY with a LIMIT and OFFSET, and GROUP BY.

Apparently High Performance MySQL, 2nd Edition is selling quite well – I’m not sure exactly how well – because we’re preparing for a second printing. This makes me very happy. I don’t think they anticipated going back to the press for quite some time. The book fluctuates between sales rank 1000 and 2000 on Amazon during the day, and has reached as high as 600 or so. This is just phenomenal.