While looking at the performance of multiple datafiles I noticed something kind of interesting.  A query searching for a integer based PK value that had the value escaped with single quotes was faster then another lookup on a PK without the single quotes.  That seemed a little counterintuitive… if anything I would expect escaping an integer to take a little longer.  But because they were searching for different keys, its not really all that meaningful, but i thought I would try and see just how much of an impact quoting your integer will cause.  So I used the same query tool I put together in perl to do the multi-datafile tests to run 2 queries searching for an integer PK.

See here:

mysql> select qryid, note, avg(averagequeryruntime),  count(*), querytext from Logger_single group by qryid, note; …

Over the past couple of months I have seen a few different clients with innodb setup to have multiple datafiles opposed to using file per table or a single datafile. While I generally do not think twice about a handful of datafiles, I started seeing clients with 50-100 or more 2-4GB datafiles on a single RAID set ( or in some cases a single disk ), and this started to raise the little alarms in my head. Asking these various clients the key question, why? The answers were surprisingly similar. Things like: “we always did it this way” , “their used to be a 2GB file limit”, “we did this in oracle”, etc. Their was not a ton of compelling answers ( like manageability and performance ).

Looking at these systems it seemed like they are really causing “self-induced” fragmentation. They all have large tables, and are doing scans over large amounts of the data… for instance a 40GB table in a database with 2GB datafiles. In the best …

I’m looking for some more unstoppable hackers to work with at Canonical, and doing the usual thing of going through CV/resume submissions. What’s different from when I was doing this last year? YouTube! In the past I’ve been impressed when someone not only had a resume but links to a personal site or profile that highlighted their professional activities in much more detail than a plain resume would - perhaps links to open source contributions that they are proud of, or designs that they’ve done, etc. One resume I just got links to a YouTube video showing a demo of a robot that the applicant built and programmed in C and Python. This definitely got my attention.

I can’t say I’ve used the ARCHIVE storage engine before, but at the NY MySQL Meetup last night there was discussion of the improvements to ARCHIVE in 5.1 and the fact that you could not DELETE from archive. A simple test confirmed this indeed throws an error.

DROP TABLE IF EXISTS url_log;
CREATE TABLE url_log(
log_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
log_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
user_id INT UNSIGNED NULL,
url VARCHAR(100) NOT NULL,
PRIMARY KEY (log_id))
ENGINE=ARCHIVE;

DELETE FROM url_log;
ERROR 1031 (HY000): Table storage engine for 'url_log' doesn't have this option

However, part of MySQL 5.1 which is RC status, there is partitioning. Thinking that one …

VMWare announced that their 2008 revenue growth outlook would be "modestly below" its earlier stated target of 50% and that CEO and founder Diane Greene would be resigning immediately. That double whammy sent VMWare stock tumbling by more than 25%. VMWare appears to have increased competition, not only from Microsoft's recently released near final Hyper-V technology, but also from open source variants from Red Hat, Citrix, Sun and others based on the Xen hypervisor virtual machine monitor. All of these choices are pretty much free, which will be a tough price for VMWare to match. In an odd twist of... READ MORE

My second webinar (hey it's an officially recognized word now!) on memcached, Memcached for MySQL: Advanced Use Cases, is now available on-demand from MySQL website.

Also see:

• Slides for Memcached for MySQL: Advanced Use Cases
  

After stepping off of the GROUP_CONCAT() solution for calculating quantiles I figured it would be nice to find a better way to calculate the median too.
Solution
I previously wrote on how to calculate the median using GROUP_CONCAT(), but I think that this is a better way:

SELECT     AVG(length) AS median                 -- take the average of left and right median
,          MIN(length) AS left_median            -- 
,          MAX(length) AS right_median           --
,          @l          AS left_median_position   --
,          @r          AS right_median_position  --
FROM (
     SELECT     @n, length                       -- @n is just here to …

In http://www.mysqlperformanceblog.com/2008/07/01/should-we-proclaim-mysql-community-edition-dead/, Peter Zaitsev wonders if MySQL’s community edition is dead.

The title of Peter’s inquiry is somewhat misleading, as the database itself works fine. He clarifies a bit with, “there suppose to be 2 yearly binary releases (which are overdue) and 4 predictable yearly source releases, which we have not seen either.” I thought it was clear that “2 per year” doesn’t mean “one every six months”. It’s been eight months, sure. And I don’t actually believe that MySQL is going to have one source release per month until November, to make up for the lack of source releases. However, it’s certainly possible, if not probable.

The fact remains, however, that if you’re just looking for stable, recent, …

We did it. Designing Scalable Architectures with MySQL Proxy was delivered successfully, with over 150 attendees.
There is a large number of questions that were asked during the session, and you can find them in MySQL Proxy FAQ.
The slides, with the highly entertaining images used by John Loehrer to illustrate his point are also online
Finally, John posted his Connection pooler Lua script in the Forge.
Thanks to John Loehrer for his lively presentation, to Jimmy Guerrero and Rich Taylor for organizing the event, to Jan Kneschke, for answering questions online …

I keep talking about Flexviews, but I figured I'd step back and explain exactly what it does and how it can help you maintain aggregate tables cheaply. Read that again. It really is important. If you have a BI or DW running MySQL then you probably spend a lot of your time creating aggregate tables with cron jobs or some other ETL tool.

These aggregate generating queries might take many hours to run, and running multiple aggregations concurrently likely severely impacts performance. The more data you have the longer the aggregations take to run. If you are attempting to answer real-time business questions this presents a big challenge. Flexviews to the rescue. It gets rid of those cron jobs and custom aggregation scripts and replaces them with an API for creating materialized views (a fancy name for those aggregate tables). It supports two kinds of views: COMPLETE and INCREMENTAL refresh.

A COMPLETE materialized …