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.

Let's assume you have a 512MB table, and you decide to alter the table to add an index to make queries faster.

How long would you expect this alter to take? Hours? Days?

Even with 7200 RPM-slow disks the alter should of finished in less then 1/2 hour.

I ran across an alter that was running for 4 days-on 512MB datasize. The reason why it ran so long is because there was a SELECT that was running preventing mySQL from performing "rename table", the last leg of the ALTER TABLE process.

Killing that SELECT released the shared lock allowing the alter to finish.


Do not KILL THE ALTER when stuck in this SHARED LOCK STATE; Do NOT then remove the temporary tablespace file "#sql-320f_106f99a2.*".

What will happen if you do remove the #sql* file by hand?

Well for one INNODB will crash the mysql instance saying it could not find the temporary table …

My new edition of the MySQL 5.1 Cluster DBA Certification Study Guide arrived today. It features a bigger format, corrections, and much better graphics. And it is $10 less expensive.

The MySQL Cluster is just different enough from the 'old fashioned' server to be confusing to long time MySQL DBAs. This book points out common problems people have getting their clusters configured. So if you are curious about how clusters work and want more than the man pages as a reference, order you copy today!