I have some warm up questions on how MySQL handles LOBs internally and then some questions on high availability. Especially with HA I feel like I have heard all/most the solutions, but they all seem to lack in one area that really makes you hurt, but I keep stumbling over questions from people asking me which way to go. Finally I have a little backup question for extra credit at the end.

So for the LOB questions, I am wondering if MySQL (or any of the storage engines) store the actual LOB data on separate data pages or not. I am also wondering if the MySQL query cache has any special handling for LOBs, like ignoring all queries that fetch LOB data. I guess there is a setting to set the maximum size for a result set to go into the query cache that could be employed to prevent a few large LOBs to fill up the query cache, but then again you …

The part of software building I called essence is the mental crafting of the conceptual construct; the part I called accident is its implementation process --Frederick Brooks1

Data warehousing involves considerable amounts of accidental and essential complexity. While being a satisfying pursuit when successful, data warehousing is also decidedly prone to failure. You need to learn to manage the essential complexity and eliminate the accidental.

Data warehousing deals with accidental complexity resulting from availability of …

We've been running replication poll and we've got some answers, so I thought I would comment a little on the results of the poll and what our future plans with respect to replication is as a result of the feedback. As I commented in the previous post, there are some items that require a significant development effort, but the feedback we got helps us to prioritize.

The top five items from the poll above stands out, so I thought that I would comment on each of them in turn. The results of the poll were (when this post were written):

Disclaimer - views expressed in this blog (and this entry) are my own and do not necessarily reflect the views of MySQL AB

Ever since I wrote my blog entry about Google Gears and the query tool for the browser embedded offline Google Gears database service, I have been wondering how MySQL might fit in here.

I have heard an idea to write a MySQL storage engine for SQLite and although I do not think this is necessarily a bad idea, I don't think it will be immediately useful for typical …

jag.singh@dwoptimize.com

Optimizing performance and usability of large data warehouses using parallelism, partitions, and aggregate summaries, in conjunction with dimensional modeling, across the complete application stack comprising of ETL, databases, and reporting.

This is a personal blog with personal opinions.

Banner picture adapted from wikimedia.

Since starting the innotop and mysqltoolkit projects on Sourceforge, I have learned a lot about how to use source control more effectively – especially how branching and tagging can be used. Still, I have limited experience. I want to package all the tools in MySQL Toolkit together and release them in one archive, but I don’t know the best way to do it; every idea seems to have drawbacks. Read on for the details, and if you have suggestions, would you please leave comments for me?

MySQL Table Checksum 1.1.6 enhances chunking, adds features and fixes bugs. The chunking functionality is where I continue to put most of my effort. This release’s behavior is incompatible with the last release, and it will probably change again in the future. Thanks to everyone who has been helping me chase down bugs, including one user who sent me a major patch! It’s a great feeling to get a patch.

The other day I blogged about the fact that I thought partitioning was broken in 5.1.18.

There does in fact appear a bug that was introduced somewhere between 5.1.17 and 5.1.18 which is causing MySQL to dump core.

I tried recompiling 5.1.18 from source and the problem remained. Installing a 5.1.17 source build fixed the problem and we’ve been online for almost 72 hours without a core dump. By way of comparison 5.1.18 wouldn’t stan up for more than a few hours.

I’m not sure I have a ton of time to debug the issue but I wanted to get the information out into the public.

Assumptions:

RHEL
x86_64
EXT3
RAID

What Raid to use?
RAID-10

Why?
It's faster. RAID-5 offers more disk space but the parity bit messes things up, unless you have some uber hardware-raid card that caches that operation. Personally I am not a fan.


Stripe Size:
128K - this is really good for INNODB, you'll see a huge boost in responsiveness by making your Stripe Size 128K. I had a 64K stripe size, and I was blown away by the improvement of 128K


Mount options:

mkfs.ext3 -T largefile | mkfs.ext3 -T largefile4

Unless your going to have millions of files, this is a good option.


Make sure /etc/fstab mounts the mysql partition or the data that mysql resides on with noatime.

atime is accesstime: this is a huge boost in performance, tracking each time …

Ok, so I knew about innodb_table_monitor and innodb_tablespace_monitor. I’ve tried them before, looked at the output and given up, partly because it didn’t serve the purpose I wanted it to at the time, and also because it’s format was a little cryptic.

What I didn’t know was there are actually 4 monitors via this “create table functionality”. You can also do innodb_monitor which is the same as SHOW INNODB STATUS, and you can also do innodb_lock_monitor .

Another thing I didn’t know is that these commands don’t send the output just once, it’s on a timer. I’ve found the timers to be different. For innodb_monitor you get every 15 sec, as well as a nice line given time of averages which seems to always say 16 seconds.

=====================================
070601 15:11:25 INNODB MONITOR OUTPUT
=====================================
Per …