Now that you can load an engine, we are going to look at reading data. For this we will need to implement three methods. We will also need the following schema:

CREATE TABLE `services` (
`a` varchar(125) NOT NULL DEFAULT '',
`b` text
) ENGINE=SKELETON DEFAULT CHARSET=latin1



Storage engines provide "handler" objects that are used to read/write/update tables. They inherit from the handler class defined in sql/handler.h.
The file ha_skeleton.cc holds the implementation of the handler for the Skeleton engine. Once a handler object is created it is cached and can be used for different tables that a particular storage engine controls. MySQL uses the open() and close() methods of handler object to tell the handler what table it should currently work with.

open() and close() are not called for each usage of a table, or each usage of a transaction.

For …

We just finished up our 2007 Users Conference and I'd really like to thank David Sceppa and Jeffrey McManus for presenting at the conference.  David is a program manager on the ADO.Net team at Microsoft and I had more than one person come up to me and tell me that having a Microsoft rep at the sessions really indicated that we were serious about working with Microsoft tools and that Microsoft seemed very willing to help with that.  True indeed.

Also, I really appreciated Jeffrey being willing to jump in last minute and do a presentation for us.  He does alot of traveling and presenting so I know his time is precious.  Jeffrey also recently made available a tool he wrote for generating a set of classes for a given database schema.  He calls it Cgen and you can read his announcement about it here.  Keep up the good …

Today everybody writes about MySQL Conference & Expo and I am not an exclusion. I am under impression of count of storage engines were presented. In good old time when Oracle bought InnoDB, MySQL did one step - announced MySQL supports Plugginable Storage Architecture. In that time nobody was able to predict what is the outcoming of such feature, but currently we all see it.
Let me list Transactional Storage Engines I heard of on Conference and main impression about.
InnoDB On the conference InnoDB presented two new interesting features - transparent ZIP of pages stored on disk. Potentially it can imporove performance of tasks with intensive I/O load a lot. Second feature - fast index build. Both should be available in 5.2 MySQL.
PBXT Interesting engine, mainly because of way to store new records "never updates" and just write to the end of file. Surely it has as benefits as and drawbacks. We are …

Last week during the MySQL Conference I took part in the MySQL Quiz Show. Five groups of MySQL staff (or related) trying to guess the answers of questions proposed by three judges in a funny and easy going environment. Frankly, I thought it was going to be all database techie stuff, but actually most of the questions were quite affordable for the average geek mind. Besides, not always the right answer would give you the most points. Sometimes answering something witty would give a good reward, and would make the quiz more creative and enjoyable. And bribing the judges with beer could lead to the definite path to victory I really enjoyed it!

I managed to take some pictures and videos, though the quality is pretty low …

Didn't make MiX '07?  Neither did I.  That's ok, though, cause the sessions are all online here.  Alot of great content here.  I'm listening to a session talking about PHP on Windows.  Gotta get back....

I'm supposed to be working on subquery optimization but I can't get BUG#26630 out of my head.

The bug test case is small and easy to understand: looking at the EXPLAIN:

CREATE TABLE tbl_test( ... ) partition BY range(num) (...);
 
EXPLAIN partitions
SELECT * FROM tbl_co c STRAIGHT_JOIN tbl_test t WHERE t.num=c.num AND reg=8;

..+-------+-------------+------+---------------+------+---------+------+------+-------------+
  | table | partitions  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
..+-------+-------------+------+---------------+------+---------+------+------+-------------+
  | c     | NULL        | ALL  | NULL          | NULL | NULL    | NULL |   17 | Using where |
  | t     | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |   17 | Using where |
..+-------+-------------+------+---------------+------+---------+------+------+-------------+ …

Several people from Solid gave talks at the Conference. They were centered around solidDB--a storage engine for MySQL. The slides from the most important ones are collected here, and also at the Solid's research site http://research.solidtech.com/myUC07/

"solidDB Storage Engines"
Ari Valtanen, Solid Co-Founder, Senior Vice President, CTO
[ Abstract | PPT]

"Strengthening High Availability in MySQL"
Vilho Raatikka, Solid Software Engineer
[ Abstract | PPT | …

We've been doing a lot of performance analysis of MySQL and SATA disks over the last few days and I wanted to get some thoughts out in the open to see what you guys thought.

Now that Debian Etch is out we're seriously looking at making the jump to a full 64bit OS with 8G of memory on each of our boxes.

This also involves benchmarking SATA and potentially migrating away from MyISAM which, while very trusty, is seriously showing its age for us.

First off is raw IO tuning.

XFS benchmarks show about a 20% performance boost from bonnie++ over ext3. This is pretty obvious. Tuning readahead with 'blockdev' didn't seem to help performance much. SATA seems pretty tuned when compared to hdparm and IDE drives.

After fully tuning it seems we can get about 90MBps out or our disks. Not to shabby. The Linux md driver on RAID 0 didn't seem to boost IO performance much (and I'm pretty disappointed). Even with a 1M chunk …

I can't write I'm just back from MySQL Users Conference both because I'm still in USA visiting customers and because it has ended almost a week ago but I was too busy to write anything about it or post my presentations.

This was my 5th MySQL Users Conference so I met a lot of good friends and spend more time speaking to people when expected (even though still did not speak to everyone I wanted to) and less time attending the sessions.

The Conference was solid and interesting this year while it was not so strictly organized as before. For example even though there suppose to be "Session Buddies" they did not show up on my sessions and on one of them I had to myself remind to Jim Starkey he was 10 minutes into my talk already. The fact all rooms were about the same size was also a problem - many interested sessions were jam packed while other rooms were empty. It is surely exciting to speak with standing audience but I'd prefer …

I was asked the question today, “How do I show the details of a Stored Procedure in MySQL. The SHOW PROCEDURE ‘name’ didn’t work.”.

The obvious answer was SELECT ROUTINE_NAME,ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES, given I like to use the INFORMATION_SCHEMA whenever possible. This lead me to think was is the corresponding SHOW command. A quick manual search got me to SHOW CREATE PROCEDURE.

What was interesting was not this, but the list of other SHOW commands I didn’t know. I did not know about SHOW MUTEX STATUS, SHOW OPEN TABLES and SHOW …