We recently examined a customer’s system to try to speed up an ETL (Extraction, Transformation and Loading) process for a big data set into a sort of datamart or DW.  What we typically do is ask customers to run the process in question, and then examine what’s happening.  In this case, the (very large, powerful) database server was almost completely idle, with virtually no I/O activity or CPU usage.  So we looked at the server where the ETL process was running.  It was running at 25% CPU usage and was writing some files to disk, but not waiting on I/O.

What’s going on here?  Where’s the bottleneck?  The process is slow, and neither machine is really doing much work.  Why?

Maybe you guessed the network.  Nope, not the network either.  There was plenty of spare network capacity.

If I told you the ETL machine was using exactly 25% of its CPU capacity, would you guess that …

I am very glad to hear that Brian's vision for Drizzle seems to be quite pluggable. This hits home perfectly with my hopes .. well for Drizzle to be pluggable! While I have not had the time to install and play with Drizzle I did stumble over the list of what has been dropped from MySQL so far. I am not sure how complete this list is (for example VIEW's are not listed as having been removed, but it was said that they would be removed), but I assume that nothing on the list is on there falsely. So some brief observations:

1) No windows support, not really surprised about that one. First I assume that none of the developer are interested at this point to worry about Windows, nor do I think that there is much of a Drizzle target audience running Windows servers. That being said, plenty of developer are …

High Performance MySQL, Second Edition
Optimization, Backups, Replication, and More

By Baron Schwartz , Peter Zaitsev , Vadim Tkachenko , Jeremy Zawodny , Arjen Lentz , Derek J. Balling
Second Edition June 2008
Pages: 708
ISBN 10: 0-596-10171-6 | ISBN 13: 9780596101718

When I first read about this book, I figured many sections would be over my head. I was pleasantly surprised when I …

Recently, we added information schema support to Google’s userstats patch.

There are three information schema tables added: user_statistics, table_statistics, index_statistics.

One can now use select * from information_schema.user_statistics along with show user_statistics.

Links:
Patch for 5.0.62
Patch for 5.1.26

Entry posted by Evgeniy | 16 comments

Add to: …

The Drizzle Report is a weekly synopsis on drizzle development. This first report will contain more than just a weeks worth of development in order to catch up.

What has been removed from the initial MySQL source tree can be found at the MySQL Differences wiki page. Some of the highlights were the removal of the mysql database, TINY/MED/LONG BLOB, TINY/MED/LONG TEXT thespatial data types, and FULLTEXT indexes. Certain keywords were removed too like ENGINES, CLIENT and CONTRIBUTORS. Even drizzleadmin has been stripped down to just ping and shutdown.The long-standing MySQL ACL has been ripped out, and initial PAM authentication has been started.

Monty Taylor reports RIP: errmsg.sys, in its place is gettext which is a standard for outputting strings.

Comparing drizzle …

The Drizzle Report is a weekly synopsis on drizzle development. This first report will contain more than just a weeks worth of development in order to catch up.

What has been removed from the initial MySQL source tree can be found at the MySQL Differences wiki page. Some of the highlights were the removal of the mysql database, TINY/MED/LONG BLOB, TINY/MED/LONG TEXT thespatial data types, and FULLTEXT indexes. Certain keywords were removed too like ENGINES, CLIENT and CONTRIBUTORS. Even drizzleadmin has been stripped down to just ping and shutdown.The long-standing MySQL ACL has been ripped out, and initial PAM authentication has been started.

Monty Taylor reports RIP: errmsg.sys, in its place is gettext which is a standard for outputting strings.

Comparing drizzle …

Brian is merging in a patch I’ve been working on that kills errmsg.txt, errmsg.sys and comp_err. In its place is (currently) a header file with a static list of error messages and codes. To handle the i18n of the messages, we’re starting to use gettext. Not only is this a standard thing, but it makes it much easier for us to translate all of the rest of the strings we output inside of Drizzle. (turns out, there are quite a few of them)

Soon I hope to be uploading a .pot file to launchpad so that the i18n work can get underway.

Next on the list as far as this goes, re-working how all of the error message lists are managed internally, and doing some thinking on why we have our own charset stuff that isn’t the system iconv() based stuff.

…

Yesterday I spent some time trying to figure out how I can better support people who are running OSX. I came to the conclusion that it’s not possible for me to do this, and in fact, Apple doesn’t want me to. As part of that process, it made me rethink OpenSolaris a little bit. Here’s my list of things I like about OpenSolaris:

• It’s not OSX
• I can install it in a VM
• It’s trying to get packaging right
• It’s Free Software

Caveats to the above list:

• My reservations about a brand new ZFS-based packaging system
• It may be Free Software, but it’s CDDL

Thank you Sun, for not being the cess-pool of arrogant wanks that Apple is!

…

The purpose of this post is to outline a general flow-chart for improving the performance of queryies in MySQL. Much has been written on using EXPLAIN to optimize queries, but there is a whole process that should be followed in order to maximize the effectiveness of query performance tuning. Following is a visual [...]