I pushed more patches to the Facebook patch for MySQL. Expect more patches as several people are on the team (Domas committed a change today). We will soon be publishing patches for MySQL 5.1. The patches include:

• Protect InnoDB LRU during full table scan. This is a backport from the InnoDB plugin version 1.0.5 (pending release in MySQL 5.1.41) and it fixes bug 45015. This adds the my.cnf variables innodb_old_blocks_time and innodb_old_blocks_pct.
• Add my.cnf variable innodb_mmap_buffer_pool to allocate memory for InnoDB bufer pool using mmap. Read the code. This is a work in progress.
• Provide a fast and non-blocking alternative to FLUSH TABLES WITH …

The old "Export as SQL" dialog is gone. Functionality was moved to the table tools dialog which has one tab per tool (maintenance, find in db, export SQL). Most features are still alive, only the target version drop down was found to be more confusing then helpful to me so I left it out for now.

Apart from the GUI changes, I rewrote the whole bunch of code which loops through your table rows and creates INSERTs. Probably you have noticed the old dialog was very slow when it came to export large tables. Not so the new dialog - it is nearly as fast as mysqldump does an export!

I have a 23M table with 1,1 Mio rows, used for testing purposes:
* Old HeidiSQL export took ~1 minute
* New dialog: 10 seconds
* mysqldump: 4 seconds
Another 5M table with 40,000 rows:
* Old dialog: 22 seconds
* New: 1 second
* mysqldump: 1 second

The problem with the old dialog was …

A final update on training for this year - registration for InnoDB/XtraDB training in Chicago (8th December) and Atlanta (10th December) are now open.

While in Atlanta I'll be giving a talk at the Atlanta PHP User Group on Optimizing MySQL Performance (details to be posted to their website shortly).  If you're in Chicago and would like me to speak at your group on 7th-8th December, let me know!

Entry posted by Morgan Tocker | No comment

Add to: …

I forwarded on a list of questions about PBXT to Paul McCullagh today.  While Paul's busy answering them, I'd like to announce that Robert Dempsey (InfiniDB storage engine) and Bradley C. Kuszmaul (TokuDB storage engine) have also accepted an interview. If you have any questions about either storage engine, please post them here by Friday 20th November.

Entry posted by Morgan Tocker | No comment

Add to: …

In this post we will look into the MySQL memory utilization estimation or calculation based on the global variables. Using a simple stored procedure call you can get the memory…

The post Calculate MySQL Memory Usage – Quick Stored Procedure first appeared on Change Is Inevitable.

The 10 largest tables in one production database use 17.8G with InnoDB, 11.1G with MyISAM and 8.6G with packed MyISAM. From tests done by Ryan show that InnoDB compression reduces our database to 59% of the original size. Assuming this holds for the top 10 tables (and I have not tested that), then the result would be 10.5G.

This can save me a lot of work. I expected to add support to MySQL for using different storage engines on the same partitioned table. With that feature, I could use compressed MyISAM for the inactive data and InnoDB for the active data. Now I want to use compressed InnoDB for everything.

I wrote a post a while back that said why you don't want to shard.  In that post that I tried to explain that hardware advances such as 128G of RAM being so cheap is changing the point at which you need to shard, and that the (often omitted) operational issues created by sharding can be painful.

What I didn't mention was that if you've established that you will need to eventually shard, is it better to just get it out of the way early?  My answer is almost always no. That is to say I disagree with a statement I've been hearing recently; "shard early, shard often".  Here's why:

• There's an order of magnitude better performance that can be gained by focusing on query/index/schema optimization.  The gains from sharding are usually much lower.
• If you shard …

KAYAK, the world’s leading travel search engine, is using TokuDB for MySQL to provide a more personalized user experience. Read all about it in today’s press release.

MySQL Connector/Net 6.1.3, a new version of the all-managed .NET driver for MySQL has been released. This is our latest GA release and is suitable for use in all scenarios against servers ranging from version 4.1 to 5.4!

It is now available in source and binary form from [http://dev.mysql.com/downloads/connector/net/6.1.html] and mirror sites (note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site.)

This release builds on the cool new features introduced with 6.1.2 and adds the following changes/bugfixes.

• fixed session state provider table definition to allow more than 64K per-session data (bug#47339)
• fixed compilation problem in NativeDriver inside ExecuteDirect  (bug #47354)
• fixed …

MySQL Connector/Net 6.2.1, a new version of the all-managed .NET driver
for MySQL has been released. This is a beta release and is intended to
introduce you to the new features and enhancements we are planning. This
release should not be used in a production environment.

It is now available in source and binary form from
[http://dev.mysql.com/downloads/connector/net/6.2.html] and mirror sites
(note that not all mirror sites may be up to date at this point of time
- if you can't find this version on some mirror, please try again later
or choose another download site.)

The new features or changes in this release are:

• fixed SessionProvider to be compatible with 4.x MySQL, replaced TIMESTAMPDIFF with TIME_TO_SEC (bug#47219)
• implemented support for client SSL certificates …