I know, I know, loads of people have been waiting for these…

So here we go, I’ve finally sorted a downloaded version of the Dojo examples from the presentation I provided at the MySQL Users Conference 2009.

There are three examples:

• The auto-paging table example, which uses the functionality of the Dojo Toolkit and the QueryReadStore to automatically load content from a table.
  
• The basic graphing example, which loads data dynamically and plots a graph.
  
• And the zooming version of the same basic graph interface

There’s a README in the download that contains instructions on getting everything up to speed, although it should be fairly obvious. It’s attached to the bottom of this post too.

Any questions for getting this to work, please go ahead and ask!

Download the package …

As I had written earlier in Understanding InnoDB MVCC, I am trying to understand why InnoDB is taking a lock on data during an UPDATE when I do not expect this to happen.

Not wanting to go looking at the InnoDB source code to understand the problem, I’m endeavouring to possibly use existing MySQL monitoring to try and understand the problem better. In this case, I’m going to investigate SHOW ENGINE INNODB STATUS first. An old but still relevant article for reference is SHOW INNODB STATUS walk through. The MySQL High Performance book is also a good starting reference.

I’ve just installed 5.1.36 on a new Linux 64 bit laptop for this test.

For now all I’ve done is ensure the innodb_buffer_pool_size is sufficient …

Received a "certificate of attendance as speaker" for recently concluded FISL 10.



This is sweet, thanks FISL organizers! It certainly adds a personal touch to the whole experience.

I don't remember receiving a personal certificate like this :)

Technorati: conf fisl brazil glassfish netbeans mysql eclipse

Received a "certificate of attendance as speaker" for recently concluded FISL 10.



This is sweet, thanks FISL organizers! It certainly adds a personal touch to the whole experience.

I don't remember receiving a personal certificate like this :)

Technorati: conf fisl brazil glassfish netbeans mysql eclipse

Received a "certificate of attendance as speaker" for recently concluded FISL 10.



This is sweet, thanks FISL organizers! It certainly adds a personal touch to the whole experience.

I don't remember receiving a personal certificate like this :)

Technorati: conf fisl brazil glassfish netbeans mysql eclipse

There are many features that are similar in MySQL and Drizzle. There are also many that are not.

I’ve previously discussed topics like Datatypes and tables, SQL_MODE and SHOW.

A key difference in Drizzle is the definition of utf8 as 4 bytes, not 3 bytes as in MySQL. This combined with no other character sets leads to an impact on the length in keys supported in Innodb.

During a recent test with a client, I was unable to successfully migrated the schema and provide the same schema due to unique indexes defined for utf8 VARHAR(255) fields.

Here is the problem. …

I was trying to demonstrate to a client how to monitor queries that generate internal temporary tables. With an EXPLAIN plan you see ‘Creating temporary’. Within MySQL you can use the SHOW STATUS to look at queries that create temporary tables.

There is the issue that the act of monitoring impacts the results, SHOW STATUS actually creates a temporary table. You can see in this example.

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.1.31-1ubuntu2 |
+-----------------+
1 row in set (0.00 sec)

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 48    |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 155   |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| …

With thanks to Patrick Galbraith and his DBD::drizzle 0.200 I am now able to test client benchmarks side by side with MySQL and Drizzle.

For simple benchmarking with clients, generally when I have little time, I use a simple Perl framework mybench. I was able to change just the connection string and run tests.

The diff of my two scripts where:

---
> my $user      = $opt{u} || "appuser";
> my $pass      = $opt{p} || "password";
> my $port      = $opt{P} || 3306;
> my $dsn       = "DBI:mysql:$db:$host;port=$port";
---
< my $user      = $opt{u} || "root";
< my $pass      = $opt{p} || "";
< my $port      = $opt{P} || 4427;
< my $dsn       = "DBI:drizzle:$db:$host;port=$port";
---

It’s too early to tell what improvement Drizzle will make. Just running my …

Quick one here; If you’re thinking to yourself “why can’t I make the MySQL data partition JFS on my RHEL5 server…” here’s a good how to: http://phaq.phunsites.net/2008/02/04/enabling-reiserfs-xfs-jfs-on-redhat-enterprise-linux/