Last week I finally decided to try out micro-blogging using Twitter. I am not going to post much personal stuff there, but mostly technical (especially Debian and MySQL related) stuff, here are my updates.

Update: As suggested in the comments, I finally switched to Identi.ca, and also deleted my account on Twitter.

The hackmysql.com website is undergoing changes. Stuff may disappear and/or be relocated at random.

Hi there,

This is my first post on the Mysql Insights Blog :) !

The idea of this blog is to share things I learn about Mysql that might be interesting to others. Currently, I am working at a small company where we are developing a Mysql storage engine so I have been learning lots of cool things about Mysql.

Ivan

Recent scalability fixes in InnoDB and also Google's and your SMP fixes almost made InnoDB results acceptable in primary key lookups queries, but secondary indexes were forgotten for some time. Now having Dell PowerEdge R900 on board (16CPU cores, 16GB RAM) I have some time for experiments, and I played with queries

PLAIN TEXT CODE:

1. SELECT name  FROM sbtest WHERE country_id = ? LIMIT 5

against table

PLAIN TEXT CODE:

1. CREATE TABLE IF NOT EXISTS sbtest (
2.                   id int(10) unsigned NOT NULL auto_increment,
3.                   name varchar(64) …

After several important fixes to our patches we made binaries for build12.

Fixes include:

Control of InnoDB insert buffer to address problems Peter mentioned http://www.mysqlperformanceblog.com/2009/01/13/some-little-known-facts-about-innodb-insert-buffer/, also check Bug 41811 to see symptoms of problem with Insert buffer.

http://www.percona.com/docs/wiki/patches:innodb_io_patches

* innodb_flush_neighbor_pages (default 1) - When the dirty page are flushed (written to datafile), this parameter determines whether the neighbor pages in the datafile are also flushed at the same time or not. If you use the storage …

If you want to learn about Tungsten Replicator first hand, I'll be doing a presentation at the February 2nd MySQL Meet-up in San Francisco. With luck I'll be able to demo not only the replicator but also some cool Amazon/RightScale integration we will be introducing in February. Teaser: It's possible to set up database clusters faster in Amazon than on a laptop.

Advance thanks to Mike Tougeron and Rich Nigra for the invite. For people suffering from obsessive curiosity about MySQL replication or database clustering in general, this talk is for you. Hope to see lots of you at the meet-up.

How smart is the MySQL optimizer? If it sees an expression repeated many times, does it realize they're all the same and not calculate the result for each of them?

I had a specific case where I needed to find out for sure, so I made a little benchmark. The query looks something like this:

PLAIN TEXT SQL:

1. SELECT sql_no_cache
2.    pow(sum(rental_id), 1),
3.    pow(sum(rental_id), 2),
4.    pow(sum(rental_id), 3),
5.    pow(sum(rental_id), 4),
6.    pow(sum(rental_id), 5),
7.    pow(sum(rental_id), 6),
8.    pow(sum(rental_id), 7),
9.    pow(sum(rental_id), 8),
10.    pow(sum(rental_id), 10),
11.   …

A few weeks ago I reported InnoDB performance on the iiBench 1-billion row insert test. Today I'm reporting on Tokutek's Fractal TreeTM storage engine performance.

We ran iiBench on the same hardware (Sun x4150, 8 cores @ 3.16GHz, 16GB memory, 6 SAS disk HW RAID 0) using Tokutek's storage engine for MySQL. The performance looks like

Our engine consumed 20.2 hours elapsed time, and inserted the last 10M rows at a rate of 11,220 rows/second. In contrast, InnoDB finished the test in 207.5 hours, inserting the last 10M rows at a rate of 876 rows/second. For our engine, the my.cnf file has no special parameters defined - the test was run with default parameters. By default, Tokutek uses 50% of the physical memory for its buffer pool. That works well for a broad range of physical memory …

Well, I announced it a few months ago, and now Sheeri made it happen. The MySQL Users Conference and Expo 2009 will have a MySQL Camp embedded.

What is it? Asked most of the people I know, including many colleagues, some of them worried that I was giving away the company jewels.

For starters, it is not a competitor of the Users Conference. It won't duplicate its contents, nor is a way of sneaking into the main conference without a pass. It is a camp, and if you have attended either one of the previous MySQL Camps or the OpenSQLCamp, you know what I mean.

MySQL Camp is a gathering of MySQL geeks, developers, enthusiasts, …

Please don't. Please, please don't. I don't know the number of times I have seen this, heck I know that certain software installations recommend you allowing a "grant all" to the user which will connect to the db from that software (see WordPress installation guide).

So... please don't.

One of the quotes I live by is one I remember from my university days and uttered by the German playwright Bertolt Brecht (I've never found the actual quote, if someone knows let me know) where he basically says, if you put a telephone on stage, use it.

MySQL grants for a user should only include what that user needs, never more, never less. If a piece of software, or if …