Note: This post is part 2 of 4 on building our training workshop.

Last week I talked about why you don't want to shard. This week I'm following up with the top 10 enhancements that XtraDB has over the built-in InnoDB included in MySQL 5.0 and 5.1.  Building this list was not really a scientific process - It's always difficult to say which feature is better than another, because a lot of it depends on the individual workload.  My ranking method was to pick the features that have the highest impact and are most applicable to all workloads first:

1. CPU scalability fixes - XtraDB improves performance on systems with multi-cpus (see docs …

If you  have asynchronous replication where the slave database is using MySQL Cluster then you can use the NDB API events functionality to mask/overwrite data. You might do this for example if the replica is to be used for generating reports where some of the data is sensitive and not relevant to those reports. Unlike stored procedures, NDB API events will be triggered on the slave.

The first step is to set up replication (master->slave rather than multi-master) as described in Setting up MySQL Asynchronous Replication for High Availability).

In this example, the following table definition is used:

mysql> use clusterdb;
mysql> create table ASSETS (CODE int not null primary key, VALUE int) engine=ndb;

The following code should be compiled and then executed on a node …

This post is an update of my previous one about XtraDB-6 performance - as InnoDB plugin-1.0.4 announce came the same day I did not have yet any benchmark results on that time :-)

To be short, the new InnoDB plugin looks very positive and have several very valuable improvement (and of course we expected to see them much more earlier, no? ;-) on the same time analyzing all latest updates - probably it's the first sign that things will go much more faster in the near future? ;-)

Anyway, what I liked with this release:

• group commit is back! (and we should thank a lot Percona team for their efforts to get it fixed! ;-)
• configurable number of I/O threads and I/O capacity (aligned now with XtraDB, Google patched and MySQL 5.4)
• adaptive flushing (idea is similar to Percona's Adaptive Checkpoint, but more …

NetBeans 6.8 M1 introduces support for creating Java EE 6 applications ... cool!

This Tip Of The Day (TOTD) shows how to create a simple web application using JPA 2.0 and Servlet 3.0 and deploy on GlassFish v3 latest promoted build (58 as of this writing). If you can work with the one week older build then NetBeans 6.8 M1 comes pre-bundled with 57. The example below should work fine on that as well.

1. Create the database, table, and populate some data into it as shown below:
   
   

   ~/tools/glassfish/v3/58/glassfishv3/bin >sudo mysql --user root …

Red Hat is celebrating the 10 year anniversary of its initial public offering. An anniversary to be proud of for Red Hat, but one that has given The VAR Guy pause for thought about the relative success of open source in the past 10 years.

“Would anyone have predicted that no additional open source companies would launch IPOs over the next decade? Ten years without an open source IPO … amazing and somewhat depressing for open source business advocates,” writes the VAR Guy.

It is somewhat depressing that there are not more public open source vendors. However, the statement that there have been …

Lately, I've had a few people ask me "How much data is in our database?". Sure, you can look at the file sizes of MyISAM tables and indexes and get a ballpark figure, but what if you need exact results, or are running InnoDB storage engine? That proves to be more challenging! In playing around with the information_schema, I've put together some queries to help:

Calculate index sizes

mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size'
FROM information_schema.TABLES WHERE table_schema LIKE 'database';

+------------------+
| Total Index Size |
+------------------+
| 1.70 GB |
+------------------+
1 row in set (1.60 sec)

To calculate the total size of the data in the database

mysql> SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 2), ' GB') AS 'Total Data Size'
FROM …

Lately, I've had a few people ask me "How much data is in our database?". Sure, you can look at the file sizes of MyISAM tables and indexes and get a ballpark figure, but what if you need exact results, or are running InnoDB storage engine? That proves to be more challenging! In playing around with the information_schema, I've put together some queries to help:



Calculate index sizes



mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size'

FROM information_schema.TABLES WHERE table_schema LIKE 'database';



+------------------+

| Total Index Size |

+------------------+

| 1.70 GB |

+------------------+

1 row in set (1.60 sec)



To calculate the total size of the data in the database
…

NetBeans 6.8 M1 introduces support for creating Java EE 6 applications ... cool!

This Tip Of The Day (TOTD) shows how to create a simple web application using JPA 2.0 and Servlet 3.0 and deploy on GlassFish v3 latest promoted build (58 as of this writing). If you can work with the one week older build then NetBeans 6.8 M1 comes pre-bundled with 57. The example below should work fine on that as well.

1. Create the database, table, and populate some data into it as shown below:
   
   

   ~/tools/glassfish/v3/58/glassfishv3/bin >sudo mysql --user root …

NetBeans 6.8 M1 introduces support for creating Java EE 6 applications ... cool!

This Tip Of The Day (TOTD) shows how to create a simple web application using JPA 2.0 and Servlet 3.0 and deploy on GlassFish v3 latest promoted build (58 as of this writing). If you can work with the one week older build then NetBeans 6.8 M1 comes pre-bundled with 57. The example below should work fine on that as well.

1. Create the database, table, and populate some data into it as shown below:
   
   

   ~/tools/glassfish/v3/58/glassfishv3/bin >sudo mysql --user root …

The InnoDB 1.0.4 plugin for MySQL 5.1 supports group commit. This is a big improvment for high-throughput workloads. I read the post from Yoshinori and then the source to understand the change.

As I don't think I have ever described this correctly, I wanted to write it down for future reference. The protocol used to keep the binlog and InnoDB in sync on commit is implemented by ha_commit_trans and does the following:

1. call binlog_prepare which does nothing
2. call innodb_xa_prepare
   • call trx_prepare_for_mysql which writes the InnoDB in memory transaction log buffer to the current log file. The data written to the log file may include redo for other transactions so the optional fsync call done in this step may be amortized over several transactions. It then forces the …