I updated my benchmark scripts this week. These scripts can now
run:

- Sysbench benchmarks for MySQL and Drizzle
- DBT2 benchmarks for MySQL and MySQL Cluster
- TPC-W benchmark for MySQL Cluster

There is also a number of scripts to start and stop
MySQL, Drizzle and MySQL Cluster nodes.

In this version I added Drizzle support for sysbench and also
added a README-AUTOMATED file that describes the steps needed
to set-up a completely automated sysbench run for MySQL
and Drizzle.

To run a MySQL sysbench benchmark one needs the DBT2 tarball,
the sysbench tarball and a MySQL tarball (gzipped tarballs).

The tarball is found on www.iclaustron.com in the downloads
section and this version is named dbt2-0.37.47.tar.gz.

I'll be doing a Webinar tomorrow (Nov 18) with some of the MySQL folks on how to build fast read-intensive and analytic databases, so join us if you can.  We'll go over some key decision points that can make big differences in the performance of query-based applications and also talk a little about InfiniDB and our approach.

Register at: http://www.mysql.com/news-and-events/web-seminars/display-459.html . Hope to see you there...

I'll be doing a Webinar tomorrow (Nov 18) with some of the MySQL folks on how to build fast read-intensive and analytic databases, so join us if you can.  We'll go over some key decision points that can make big differences in the performance of query-based applications and also talk a little about InfiniDB and our approach.

Register at: http://www.mysql.com/news-and-events/web-seminars/display-459.html . Hope to see you there...

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 …