MySQL Performance Blog

I never liked how build in Wordpress search works. it shows full documents rather than snippets it does not search comments and it does not have any query language so I always used Google search if I wanted to find something on MySQL Performance Blog.

Today we have published new search functionality for our site which is based on Sphinx. We have developed it as WordPress plugin which will be available as open source software in a few weeks - just want to test it a bit more and write proper documentation before announcing. If you would like to test it however let us know.

As you can see search functionality allows you to specify what would you like to search (Posts, Pages or Comments) as well as if you would like to sort results be relevance of freshness. You can also use query standard Sphinx query language to search phrases or Search MyISAM in post title.

Besides basic search functionality we’ve implemented list of last searches and Top searches which if both nice for fun and for SEO.

Hope this addition will make MySQL Performance Blog more useful for you.

As Peter mentioned in one of previous posts, we’ve done huge work developing robust strategies of InnoDB data recovery to provide our customers effective data recovery services and one of major parts of these strategies is our toolkit for InnoDB data recovery. Today I’m proud to announce its first public release which was used to help some of our customers to recover 95-100% of their deleted data.

This release already has a pretty decent set of features:

• Supports both REDUNDANT (pre mysql 5.0) and COMPACT (mysql 5.0+) versions of tablespaces
• Works with single tablespaces and file-per-table tablespaces
• Able to recover data even when processed InnoDB page has been reassigned to another table and/or was partially destroyed
• Supports all MySQL data types except BLOBs, SETs and BITs (will be implemented in next releases)
• Has really great set of data filters to define data ranges (for numbers), field lengths (for variable length fields), character sets (for strings), date periods (for dates), etc.
• Shipped with easy to use tool which could be used to create innodb table definitions based on CREATE TABLE clauses, so you don’t need to write table definitions yourself - you just need to add data filters and get your data back (well, in most of the cases)
• Results are presented in CSV file format which could be used with MySQL’s LOAD DATA function

So, if you intrigued enough and would like to check it out, welcome to Google Code page of the project where you can find latest version of the toolset code and more links to information resources related to InnoDB data structures and recovery procedures.

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 going to test PBXT in write workload, though currently comparison with InnoDB will not be fair, as PBXT do not perform fsync() after commit and fully rely to OS. The developers promise to fix it soon.
Falcon The Storage Engine which is developed in MySQL. The performance we see in our previous benchmarks was not good enough, but there was some fixes in 5.2 tree. Also this engine will not support index coverage optimization, that makes Falcon less attractive from performance reasons.
Solid The engine with both pessimistic and optimistic locking on the table level. The interesting note Solid proposes High Availability solution for Solid Engine, which can be replacement of MySQL replication (the solution was only announced and will be available later)

And three engines which were only presented and details only available from marketing materials:
NitroEDB The engine promises to handle in very fast way aggregative functions on massive data sets. As I understood they use special index to store pre-calculated values for limited number of functions, and then engine can retrieve values from index. The interesting question if engine is able to calculate combination of functions or user-defined function on TB data. I would like to test it, but most likely it is not possible in near future, as license price is 20.000$ per server ( the price I heard somewhere, not sure if it’s 100% correct)
Infobright Infobright is non-transactional and read-only engine designed for DataWarehouse applications with compression rate from 10:1 to 30:1
ScaleDB The engine based on special index, so named “Trie”, the extension of “Patricia Tree” data structure. The developers promises ultimate speed and scalability for index access operations. It’s interesting to test it in real situations, because theoretical ideas can be very far from life. Also nobody prohibits to add such index to other storage engine if it will show good performance results.

The wide choice is always good, but I’m afraid making right decision about Storage Engine will be much harder.

I’ve been invited to speak at RIT-2007 which stands for “Russian Internet Technologies” conference. It takes place 16-17 April in Moscow, Russia. It looks like this is going to be biggest Russian conference on Internet technologies so far with many large Russian Internet projects sending their delegates. You can check out conference sessions schedule here.

Andrew Aksyonoff, the author of Sphinx Search Engine will also be giving a talk there.

The day following the conference April 18 I’m giving full day “Master Class”/Training on MySQL Performance.

Even though this post is in English the conference and training will be in Russian language

About half a year have passed since me and Vadim have left MySQL to do MySQL Consulting on our own. Bunch of people have been wondering about our experiences so I thought it would be worth to share it here.
[read more...]

Just got email from Jay today about two of my talks being accepted to MySQL Users Conference 2007 “MySQL Server Settings Tuning” and “MySQL Performance Cookbook”. This is great as MySQL Conferences are always fun and great way to get to know people and learn things about MySQL and connected areas.

This will be my 5th time, but only first time, not being MySQL Employee.

No word so far about other talk I proposed - “Innodb Performance Optimization” which I think is one of my best talks. Might be the name sounds old, even though I add new information to it each time to keep it fresh.

We’re happy to announce Andrew Aksyonoff, developer of popular OpenSource full text search software Sphinx joins our team.

Andrew will now have more time to dedicate to Sphinx development so cool new features will come sooner.

Having Andrew on board we’ll be able to solve all kinds of problems related to full text search, tagging and general information retrieval.

It does not mean we’ll push sphinx ether it suits your needs well or it does not. Sphinx is great for high performance high quality or complex full text search applications - but If you’re looking at smaller search applications we can help you getting most of MySQL build in full text search. If there are some features which are missing in Sphinx or it is not best solution for your application for other reasons we can help you with using Lucene or other external search system.

Finally having Andrew onboard we’ll be able to offer official Sphinx support, consulting and custom feature development via our company.