As part of my A call to arms! post about a month ago, I’ve had a number of unofficial comments of support. In addition, I’ve also been approached to assist in the completion of a MySQL Transactional support engine. More information on the PBXT engine will be forthcoming soon by it’s creator.

Anyway, I’ve taken on the responsiblity of assisting in testing this new storage engine. This will also give me the excuse of being able to pursue some other ideas about the performance of differing storage engines for differing tables in business circumstances, such as MyIsam verses InnoDB in a highly OLTP environment. Part of testing will be ensure ACID conformance in varying situations and multi-concurrency use. Of course the ability to also do performance and load testing would be a obvious extension.

Considering how I’m going to benchmark is an interesting …

I just read about OLAP4ALL's Materialized Views for MySQL software that "offers the functionality of Materialized Views in MySQL that are not natively supported in the MySQL database.".

What nags me about this is the fact that it is "implemented as a separate Java program running on the server where MySQL is installed".

The server-side of this should be doable using Stored Procedures and/or Triggers. For the client-side a bit of convenience functionality for query rewriting would be …

I just read about OLAP4ALL's Materialized Views for MySQL software that "offers the functionality of Materialized Views in MySQL that are not natively supported in the MySQL database.".

What nags me about this is the fact that it is "implemented as a separate Java program running on the server where MySQL is installed".

The server-side of this should be doable using Stored Procedures and/or Triggers. For the client-side a bit of convenience functionality for query rewriting would be needed.

Sounds like an interesting project that I would like to pursue when I find the time.

…

Apache JMeter is a 100% pure Java desktop application designed to load test functional behavior and measure performance. It was originally designed for testing Web Applications but has since expanded to other test functions. Specifically it provides complete support for database testing via JDBC.

Some References: Homepage http://jakarta.apache.org/jmeter/  ·  Wiki Page  ·  User Manual

Initial Installation Steps

$ su -
$ cd /opt
$ wget http://apache.planetmirror.com.au/dist/jakarta/jmeter/binaries/jakarta-jmeter-2.1.1.tgz
$ wget …

This was an honor, but I must decline. We already have such an "ambassador." Thank you, Miguel.

14:15 <@miguel2> What is MaxDB?
14:15 <@tjfontaine> http://en.wikipedia.org/wiki/MaxDB
14:16 < cj> miguel2: it was written in pascal in the mid 70s.  it's a
            pre-relational database with all sorts of features [added] on
14:16 <@miguel2> Wait, is that a different DB?
14:16 < cj> miguel2: yep
14:16 <@miguel2> So its not MySQL?
14:16 < cj> miguel2: nope
14:16 <@miguel2> That is crazy talk
14:16 <@miguel2> crazy
14:17 <@miguel2> I dontbelieve you
14:17 < cj> miguel2: heh, okay.  MySQL doesn't have *any* pascal code
in the
            kernel, afaik
14:19 <@miguel2> Who uses that?
14:19 <@miguel2> So why dont they pay you to hack on Mono?
14:19 < cj> miguel2: that's something I'd like to know :)
14:19 <@miguel2> We need a firm MySQL commitment to Mono
14:19 …

I got a "Sun T2000":http://www.sun.com/servers/coolthreads/t2000/ server from their "test drive":http://www.sun.com/emrkt/trycoolthreads/index.html program a few days ago. I didn't have the right serial adapter to get to the console port at home and my 30 minutes of trying to stitch together the right cable didn't work out, so I am taking it down to the perl.org rack instead of playing with it at home.

I took a few pictures and uploaded them to flickr.

I don't know if we get to keep it, but if we do then it's probably going to be in that rack anyway, so... (crossed fingers - if you work for Sun please put in a good word for me with your marketing department :-) )

When we get it up we'll try setting it up as a perl5 and parrot smoke test server. With the Solaris zones we might be able to have it safely run tests of everything …

I got a "Sun T2000":http://www.sun.com/servers/coolthreads/t2000/ server from their "test drive":http://www.sun.com/emrkt/trycoolthreads/index.html program a few days ago. I didn't have the right serial adapter to get to the console port at home and my 30 minutes of trying to stitch together the right cable didn't work out, so I am taking it down to the perl.org rack instead of playing with it at home.

I took a few pictures and uploaded them to flickr.

I don't know if we get to keep it, but if we do then it's probably going to be in that rack anyway, so... (crossed fingers - if you work for Sun please put in a good word for me with your marketing department :-) )

When we get it up we'll try setting it up as a perl5 and parrot smoke test server. With the Solaris zones we might be able to have it safely run tests of everything …

One of the biggest annoyances in the current MySQL versions is that you still have no access to the original view definition (at least by SQL, there is a way through the file system, which I described in a former article).

So again, if you create a view like

CREATE VIEW v_checktest AS
SELECT id, val FROM checktest\n  
  WHERE val >= 1 AND val <= 5\n  
  WITH CHECK OPTION

SHOW CREATE VIEW v_checktest returns

mysql> SHOW CREATE VIEW v_checktest\G

*************************** 1. row ***************************
       View: v_checktest
Create View: CREATE ALGORITHM=UNDEFINED 
  DEFINER=`mpopp`@`localhost` 
  SQL SECURITY DEFINER VIEW `v_checktest` AS 
select `checktest`.`id` AS `id`,
  `checktest`.`val` AS `val` 
 …

You may have seen several great queries how to get useful information out of information_schema - so here's one more:

SELECT TABLE_SCHEMA,
  sum((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024)) as size_mb
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
ORDER BY size_mb DESC

This query lists all of your databases with their sizes in megabytes in descending order.

If you only want to see those databases exceeding 1 MB, do the following:

SELECT TABLE_SCHEMA,
  sum((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024)) as size_mb
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
HAVING size_mb > 1
ORDER BY size_mb DESC

Quite simple, but very effective!