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 …

This time, I’m talking about indexes for string typed columns. In particular, I’ll show a procedure I find useful while looking for good index length values for these columns. I’ll use a sample table called people. Here’s what it looks like: mysql> desc people; +————+——————+——+—–+———+—————-+ | Field | Type | Null | Key | Default … Continue reading Indexing text columns in MySQL →

Related posts:

1. Using MySQL Proxy to benchmark query performance By transparently sitting between client and server on each request,...
2. Making use of …

I would like to proudly announce that we have migrated our Connector/Net source code repositories to LaunchPad (www.launchpad.net) and are using BZR for our source control.  We have taken our old public SVN repository (http://svn.mysql.com/svnpublic/connector-net/) offline. 

We will be using LaunchPad for source control and feature management.  This means we'll use blue prints and the series system.  We will continue to use our existing bugs database found at bugs.mysql.com and our existing forum found at forums.mysql.com.

You can find the new source repositories for Connector/Net at https://launchpad.net/connectornet.  If you have any questions about how this affects you, please don't hesitate to send me an email.

Every once in a while a programmer finds himself in need of a tool that allows him to edit very large text files. By large, I mean several gigabytes. For DBAs it is common, especially if you’re using MySQL dumps a lot. What do you do if you’re doing this on Windows?
If you’re using Notepad++ or any other Scintilla derivatives, you’re out of luck - those editors are not cut out for this kind of work. Using Visual Studio also won’t work. There are some partial solutions just for viewing, like LTFViewer – but it cannot handle large files without line breaks, something common in MySQL dumps. So what do you do?
The answer is simple and somewhat unexpected – use Vim for Windows. …

IMPORTANT:
This article is out of date and the replication API has been updated. Please see the follow-up article for the most up to date information! I wanted to start writing about how Drizzle's new replication system works, how its internals are structured, how logs are formatted, what are its (current) limitations, what are planned features, how you can get involved in development, and a lot more. Before jumping in, you may want to read a quick overview about the concepts of Drizzle replication here.

Fortunately, some advice from my friend Edwin DeSouza got me back to reality: "Jay, do a series of small, targeted, easily digestible blog posts". And, so, this …