Antony's MySQL bits

Traversing larger graphs with OQGRAPH 3. Time for Bacon?

2011-04-19T11:12:00.000-07:00

Using the IMDB data, I have loaded the actor and actress database into a few simple InnoDB tables and have performed the following test:

MariaDB [imdb]> show status like "Innodb_rows_read";
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| Innodb_rows_read | 1237459 |
+------------------+---------+
1 row in set (0.00 sec)

MariaDB [imdb]> select x.* from entity as orig, entity as dest, imdb_graph, entity as x where dest.type='ACTOR' and dest.name='Kevin Bacon' and imdb_graph.latch=2 and imdb_graph.origid=orig.id and imdb_graph.destid=dest.id and x.id=imdb_graph.linkid and orig.type='ACTOR' and orig.name='James Earl Jones';
+---------+---------+------------------------------------------------------------------+
| id      | type    | name                                                             |
+---------+---------+------------------------------------------------------------------+
|  780890 | ACTOR   | James Earl Jones                                                 |
| 1922997 | ARCHIVE | "20 to 1" (2005) {Our All Time Favourite Films (#9.11)}  (voice) |
|  131965 | ACTOR   | Antonio Banderas                                                 |
| 1945521 | ARCHIVE | Oscar, que empiece el espect?culo (2008)                         |
| 1003551 | ACTOR   | Kevin Bacon                                                      |
+---------+---------+------------------------------------------------------------------+
5 rows in set (2.80 sec)

MariaDB [imdb]> show status like "Innodb_rows_read";
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| Innodb_rows_read | 2102482 |
+------------------+---------+
1 row in set (0.00 sec)

MariaDB [imdb]> select (2102482 - 1237459)/2.8;
+-------------------------+
| (2102482 - 1237459)/2.8 |
+-------------------------+
|             308936.7857 |
+-------------------------+
1 row in set (0.09 sec)

Over 300,000 edges examined per second... Nifty.

The entity table has approximately 3 million entries and the link table has approximately 30 million edges.

Note - this performance is with a warmed up buffer pool. With a cold buffer pool, the query takes nearly 30 seconds.

OQGRAPH at MySQL UC 2011

2011-04-14T12:27:00.000-07:00

I had a good and lively audience at my presentation today. I was happy to give them a sneek preview of the Mk.III implementation of OQGRAPH.

Oh yeah! I guess I'm announcing the availability of the source code of the MkIII implementation.

Links are in my slides... http://goo.gl/UrybZ

Btw, building the storage engine requires Boost libraries and libJudy installed. It's currently in a MariaDB repo ... versions for MySQL and Drizzle are to follow.

Tip that will save hours of sleep...

2010-08-02T06:48:00.000-07:00

This tip is several months late but here goes anyway.

In my opinion, when you have an environment where you're using multiple MySQL storage engines (using the right tool for the job and all that jazz), there is nothing more annoying than to have tables created or altered into a storage engine that you did not request. The default behaviour of MySQL is to use the default engine if the requested storage engine is not available.

Why is this annoying? Because when a specific storage engine is requested, it needs to be using that specific engine and anything else is an error.

Fortunately, there is an SQL-Mode in MySQL which will enable the most desirable behaviour ... that is, if an engine, specifically requested, is not available, there will be an error instead of substituting the default storage engine: NO_ENGINE_SUBSTITUTION

Everyone should put this into their site's my.cnf files. I am glad to see more and more people using it.

(My only apology is that I am sorry I failed to argue strongly enough to make this behaviour default when I implemented it, years ago)

Oracle's MySQL Report Card,

2010-06-06T16:46:00.000-07:00

Yeah, I know it is early days right now but I do think that Oracle's acquisition of Sun Microsystems has not harmed MySQL.

Oracle has not done anything to get in the way of MySQL and as far as I can tell, it's basically business as normal: People are still using MySQL.

Admittedly, I have been busy at my job at Blizzard Entertainment but I really haven't seen anything which alarms me.

Besides, the forks are a good thing: It is a sign of a healthy community that there are forks which explore different development ideas. I just hope that the forks have sufficient respect for each other that they will allow code to be merged in every direction. For that reason, the few code contributions I have made for MariaDB (for example) have been all made using the BSD license, which should not prevent them from being merged into MySQL.

Personally, I would like to encourage more people to contribute code using the BSD license. I do believe that the use of the BSD license is perhaps the best decision made by Brain Aker for his Drizzle project.

"Save MySQL"; my 2 cents.

2010-01-04T09:08:00.000-08:00

There is no denying that MySQL has a huge amount of momentum behind it. 10+ years as the leading free SQL database kinda does that. It has successfully changed the perception of databases - where people used to visualise big-iron mainframes in specially cooled rooms to where anyone can install it for free within 15 minutes.

That is not what this blog post is about. The topic of today is the "Save MySQL" campaign and how I believe it is unnecessary.

In fact, I believe that it could be harmful.

Like Anders, I have mostly been refraining from participating in this campaign but now I think it would be helpful for people to know my reasoning why.

Let us consider some scenarios:

Oracle acquires Sun, ceases MySQL development.

This would be a Pyrrhic victory for Oracle as the community would immediately coalesce around MariaDB / popular forks. Everything continues... except the MySQL trademark.

Oracle is not that stupid.
Oracle acquires Sun, allows MySQL development to continue as before.

In my opinion, this would be the worst possible scenario for MySQL/MariaDB/forks as it would divide and fragment the community as the codebases diverge.

Are the "Save MySQL" campaigners really trying to achieve this goal?
Oracle does not acquire Sun, Sun continues MySQL development as before.

See previous point. Equally disastrous.
Sun sells MySQL intellectial property to third party. Oracle may or may not acquire Sun.

Who would the third party be? Sun shareholders would be very annoyed if the sale was for less than $1bn... and rightfully so! Who has the cash to pay $1bn for a open-source database? I doubt that IBM would come to the rescue here as I doubt that there is enough IP in MySQL alone to interest them and the SAP bridge has already been burned.

So, what do I want to happen? That is a good question. There are a lot of tough decisions which has to be made to complete MySQL/MariaDB/etc. I like that MariaDB is being developed by Monty and many other long-time MySQL developers: A part of me wishes to be working with them to help shape it's future.

Perhaps "MySQL" should become like "Linux". A brand owned by a benevolent organization and permits many distributions to use it to name their database product. The "MySQL" brand would require that so named software be GPL, share a common protocol and SQL dialect. There would be an open MySQL Compatibility Test kit which would test distros for compatibility to some "gold standard".

Perhaps that is what I would like.

Version Number 5 point WTF?

2009-12-15T06:28:00.000-08:00

What is going on with the version numbers coming out of Sun/MySQL?

Once upon a time, we had 5.2.

Then, to incorporate Falcon, it became 6.0.

Somewhere along the line, there was a 5.3.

Then a 5.4 appeared which seemed to be 5.1 with performance fixes for InnoDB.

Now there is a 5.5, which claims to be a 5.4 with a few stuff added.

Stop confusing your community.

And whatever happened to community participation?

People would like to know.

2010 will be a good year,

2009-12-02T08:40:00.001-08:00

I suppose I should make it official: 2010 should see me being much more active in the Community.

Why? Simply because I am now working for Blizzard Entertainment where I hope to help them make better, more efficient, use of MySQL/MariaDB. I expect to be working on some of MySQL's internals and storage engines in order to achieve what is wanted and helping them enhance their existing use of MySQL.

Exactly what my role will be and the other little details, is still taking form but hopefully, we would be able to contribute some stuff back into the community.

Crouching dolphin, hidden bugs.

2009-11-20T09:51:00.000-08:00

I hate it when the changelog of any MySQL release references bugs which when clicked, simply says "You do not have access to bug".

A year in review; new direction.

2009-10-03T07:45:00.000-07:00

It has been more than a year since my self-imposed hiatus from serious MySQL development started and I think it is about time that I get back into the saddle. I have a handful of working prototypes but I should get the code out there, back into the community.

I learned a bunch of stuff during the past year at Google but in the end, working on JavaScript, HTML/CSS and Google proprietary languages didn't really suit me during my role as SRE. (Also the 2-3 hours spent every day commuting to and from the office took it's toll)

My immediate plan is to look for another job which would allow me to finish off the patches I have been toying with:

constifying the server code so that GCC can make smarter decisions and generate more optimal object code.
Splitting the parser. Removing the stored procedure implementation from the main parser yet preserving functionality and allowing alternate implementations to be installed. Use some modern bison features.
Remove the direct use of Protocol class from most of the server core.
External Language Stored Procedure rewrite. It will be simpler than current fork and to take advantage of all the previous items.
Various bug fixes.

I also want to get more engaged again with the MySQL community and hopefully will have new and exciting stuff to present at the UC next year.

Eraser ...

2009-09-17T23:03:00.000-07:00

I let a couple of these pass by but just thought I'd mention it (just in case any other ex-MySQLer is experiencing anything similar...

Why does the MySQL bugs system remove former employees from the historical record?

Date: Thu, 17 Sep 2009 20:26:35 +0200
From: Bug Database 
Subject: #29838 [Csd]: myisam corruption using concurrent select ... and update
In-reply-to: 
X-Originating-IP: [140.98.193.23]
To: antony.curtis@ieee.org
Message-id: <200909171826.n8HIQZU6002514@bugs.mysql.com>
MIME-version: 1.0
Content-type: text/plain; charset=utf-8
Content-transfer-encoding: 8BIT
X-Bug: 29838
X-Bug-URL: http://bugs.mysql.com/29838
X-Bug-Category: Server: MyISAM
References: 
X-Authentication-warning: bugs.mysql.com: apache set sender to
 bounce+antony.curtis=ieee.org@bugs.mysql.com using -f

Dear developer, the bug assigned to you for review has been re-assigned to .

 Updated by:       James Day
 Reported by:      Shane Bester
 Category:         Server: MyISAM
 Severity:         S2 (Serious)
 Status:           Closed
 Changeset:        http://lists.mysql.com/commits/31944
 Version:          4.0.30, 4.1.23, 5.0.46BK, 5.1BK, 6.0.1
 OS:               Any
 Tags:             concurrent_insert, error 127, corruption
 Assigned To:      Ingo StrÃ¼wing
 Priority:         P1 (Critical)
-Affected issues:  17915,16700,15911,15439,17046,15681,15442,14965,16745,18487,15427,18623,19293,21309,23978,23763
+Affected issues:  17915,16700, 15911,15439, 17046,15681, 15442,14965,
                   16745,18487, 15427,18623, 19293,21309, 23978,23763
-Reviewer:         Antony Curtis [done]
+Reviewer:         Bugs System [done]
 Reviewer:         Sergey Vojtovich [done]
 Internal Tags:    sr5_1
 Affects customer: Yes
 Triage:           

[No new comments]

Celebration of forks?

2009-07-25T18:35:00.000-07:00

This blog post was inspired by Masood Mortazavi's comment: "OSCon to be somewhat disappointing this year -- many low quality sessions and a celebration of forks"

I did not attend OSCON, even though I live in California. I did not even bother to submit a presentation proposal, even though people who attended my talks at the MySQL Conference and Expo earlier this year and the year before, all greatly enjoyed it. I have even received emails from people suggesting that I present the talks at OSCON, FOSDEM etc. Just to clarify: I have not submitted any proposals, anywhere (except a tentative volunteer at a MySQL Los Angeles Meetup, mostly because it's on the route home that I drive. Note to self: I need to confirm a date).

I must admit that my enthusiasm for MySQL has been on the wane since there has been so little interest in proactively incorporating the work that I have done. I haven't done any hacking on MySQL (or any of its forks) in several months.

Sun/Oracle must fix the mistakes of the past and cannot continue to develop MySQL in the same way that MySQL has been developed for the past 4 years. The development needs to be open and embrace the work that is happening outside of Sun. MySQL first started alienating open-source developers by persevering with using BitKeeper for several years past the point where it should have been abandoned and Sun has continued the alienation by having a closed development process - which left seasoned developers, such as myself, surprised with the MySQL 5.4 announcement.

Open source does not like surprises.

Today, it still seems that "official" development is still very closed. We only see intermittent pushes to the "public" LaunchPad pushes: The 5.4 repository is 3 weeks old; the 5.1 and 5.0 repositories are about 2 weeks old. In the bad-old BitKeeper days, the community can see the repository via bkbits but now, nada.

Sun! Please! Open up the development and interact with the community before the goodwill of the MySQL brand disappears. It is not good enough just to continue as how MySQL AB was handling the development during the last few years before it was acquired: That was broken already. Have all your developers push to public repositories. Make the pushbuild results public. Proactively work to incorporate contributions with the knowledge that the contributors are often not paid to work on MySQL: They are a volunteers with only a small amount of time available.

My regular employment at Google does not involve any MySQL development work. Google does not (and probably never will) use my external stored procedures patch for MySQL, for example. Some of you are probably thinking "Googlers have 20% time": So what? Perhaps I want to spend my "20% time" doing something more fun and productive than continuously merging a patch which will never be pushed? And just whose fork of MySQL am I supposed to develop and contribute to?

Just my 2¢.

Binary Literals

2009-07-09T09:33:00.000-07:00

I was randomly browsing for some stuff and I stumbled across this post on binary literals. It proposed the following as an option for C++ which the article admits as being inefficient:

unsigned long const mask
    = std::bitset<6>(std::string("111100")).to_ulong();

I was thinking that C++ templates must be able to offer something more efficient...

template <int N>
struct Binary
{
  enum { value = Binary<N % 10> | 2 * Binary<N / 10>::value }
};

template<>
struct Binary<0>
{
  enum { value = 0; }
};

template<>
struct Binary<1>
{
  enum { value = 1; }
}

int foo = Binary<111100>::value;

Usual caveats apply. Code is untested and may eat kittens.

Perl and Java Stored Procedures for MariaDB 5.1

2009-05-15T11:03:00.001-07:00

I just applied the external stored procedure patch to a branch of MariaDB and uploaded it to LaunchPad.
You can see the branch at https://code.launchpad.net/~atcurtis/maria/5.1-wl820
Note that this is not in any reasonable condition to merge into MariaDB. Hopefully we can engage in dialog as to how we can bring this feature properly to MariaDB, MySQL and Drizzle, hopefully making the plugins reasonably easy to port between them, but to be "native and maintainable" without getting in the way of future development. For example, some of the infrastructure I have used to implement table functions was removed from the MySQL 6.0 branch. It would be nice if contributers can see and participate in roadmap discussions; historically MySQL may be Open Source but it would be good to have Open Development too.

Verizon FiOS is FAIL

2009-05-08T06:54:00.000-07:00

When is "Static IP" service not a "Static IP" service?
It is not a "Static IP" service when it is provided by Verizon FiOS Internet service.
There is no way to assign the static IP address to your own equipment.

Verizon FiOS Static IP is FAIL.

Conference Aftermath

2009-04-27T09:12:00.000-07:00

It is always after the conference when you get home that you notice missing items.

I think I left a zip-up storage bag in Conference room A after my presentation of Perl Stored Procedures which may contain a few cables and a laptop security lock.

Annoying when stuff like that happens.

Thoughts on Drizzle Developer Day

2009-04-26T20:07:00.000-07:00

I wasn't able to spend the whole day at the Drizzle Developer Day, which was held at the Sun campus on the day after the MySQL Conference, but it was interesting none the less.
I enjoyed discussing stuff with Monty Taylor and Stewart Smith and was able to show them some ideas which I hope will inspire more innovation in Drizzle. I would have liked to have had some time to chat with Brian Aker some time during the last week but time always seems to run out.

It would be fun/nice if the MySQL conference would move to somewhere else in the world - it would be great to meet and chat with other former colleagues and friends who cannot (or will not) travel all the way to the Pacific coast of America.

Downloads for External/Perl Stored Procedures (aka LaunchPad is painfully slow)

2009-04-23T15:12:00.000-07:00

It appears that LaunchPad.net is pretty slow.

So that people can have something to play with, I have transferred files to my own web site.

Presentation files:

UC2009_presentation.zip

Source downloads:

Please don't melt my router.... kthanx.

Minor rant...

2009-04-23T09:07:00.000-07:00

There apparently was T-shirts for speakers at the MySQL Conference but a few of us speakers have not received them - it was not clear where to get them from in the conference hall and there was confusion when I asked other speakers when I asked them as they did not know. Well - it seems that there was a conference T-Shirt but it was from the conference hall (which is now all packed up and gone) ... the blue-ticket thing was the thing to use to get it.

*sighs*

Was there a conference T-Shirt last year for speakers?
Perhaps I am just unfortunately busy and I haven't acquired the necessary swag-grabbing techniques.

(My) Community Contributions to MySQL BOF

2009-04-22T23:16:00.000-07:00

I arrived late for this talk yesterday. Don't know what was said before I arrived.
Was working on my presentation, putting on a few finishing touches, during the BoF session.
Asked about WorkLog visibility.
Asked about reviewer availability.

Take away quotations:
"Contributions will be reviewed in reverse order of size (complexity)."
"Contributions will be tracked in bugdb."
"WorkLog will be replaced, maybe."

I created Bug#44398.

A FederatedX related contribution - Bug#29523 was last updated 221 days ago.

My LOCK_open contribution for Storage Engine ::open and ::create, Bug#30051 was last updated 631 days ago.

*sighs*

Brian thinks I should blog this.
I'm tired.

Perl Stored Procedures for MySQL

2009-04-22T20:07:00.000-07:00

Files used in conference presentation here: UC2009 presentation

I think todays presentation for Perl Stored Procedures for MySQL was quite successful. The audience was quite engaged and asked questions throughout (yes, I invited questions through the talk). I made sure to mention Eric Herman's Java plugin because even though he didn't get time to submit a talk, the work is noteworthy. Sometimes I worry that my accent may make it harder for people to understand what I was talking about so I tried to talk as clearly as I could. Note to self: I should have a bottle of water on the table for myself the next time I do a talk.

The audience seemed to enjoy the demonstration part of the talk. I think it drew the appropriate amount of chuckles. One attendee said that the demo alone made his day at the conference worthwhile. Compared to last year, I think it was the right decision to split the presentation into one targeting developers wanting to get into the internals and a presentation simply to target end users.

I just hope that the communication with the MySQL/Sun/Oracle community contribution team continues and we can get this work into the main tree where more people can make use of it.

Launchpad Project Repository: https://launchpad.net/sakila-server/wl820
Source and downloadable resources: https://launchpad.net/mysql-wl820/trunk/5.1.33-wl820

External Stored Procedures for MySQL

2009-04-21T17:04:00.000-07:00

Finished my presentation earlier this afternoon. I had a better audience than last year and there was interest in the download URL for the source tarballs so I hope to see people hacking on it soon.

The link to the presentation is here.

There has been lots of good communication with staff from MySQL^WSun Microsystems^W^WOracle so maybe we shall see this code to begin to be integrated soon. In other news, there was the MySQL 5.4 announcement. I'll pull the code and have a play...

Opinion and Speculation: Log Structured vs Traditional Block

2009-03-28T00:28:00.000-07:00

At least since early 2006, at the MySQL Athens meeting where I first met and listened to Jim Starkey, I have been of the firm conviction that log structured databases will be the future for disc-based storage devices. Their largely sequential write pattern ideally suits modern drives which are optimized to write whole tracks of data at a time. I even proposed such a project to Monty and Brian at that meeting. Of course, they said that I should go ahead and write one but circumstances contrived against me and I never really progressed much beyond the experimental proof of concept stage, my time having been occupied with the aborted Amira project and providing some assistance to the early Falcon project.

For that reason, when I first heard of PBXT, I was very excited. I have told many people to keep an eye on that project because although it was slower, it will catch up and then surpass traditional block storage databases such as InnoDB.
It's taken a while but ... a big THANK YOU to Paul and his team at Primebase for ensuring that I do not have to eat my words for all the talking up I have done about PBXT for the past couple of years

The game-changer in the near future is Flash storage and other solid state media. Such technologies mean that there is no seek/head settle time. Optimization strategies like clustered indexes become obsolete. However, Flash does have some overhead in writing and it is preferred to write whole flash blocks at a time. Right now, I believe that most Flash media use 64 KB blocks but as Flash media increases in size and performance, by increasing the bit-width, the effective block size of the media will increase. Log-structured storage can optimize for this because all writes are consolidated into a block and there will be little penalty for the index to be scattered across many segments because of there being no seek penalty.

After Flash... Let us imagine a future where we have some form of ultra-fast memristor based storage which supplant DRAM, Flash and disc media, then all this talk about database storage engines becomes practically moot ... just wire up the memristor memory to your 64bit CPU with it's 64bit address bus. Provide record version control, perhaps by some form of in-memory index, perhaps vaguely log-structured but no need for contiguous segments in order to scale on NUMA architectures (which now Intel is transitioning to with their new HyperTransport inspired workalike).

MySQL 5.1.32 + External Stored Procedures

2009-03-20T18:27:00.000-07:00

I have synced the codebase with the 5.1.32 release of MySQL and it appears to work just fine. Sometimes frustrating that Bazaar takes a bizarre amount of time to do a merge.

Download link for the source tarball are available from LaunchPad Download.

As an experiment, I have built a Mac OS 10.5 installer package (x86 32bit) which I have also placed there. Took a bit of fiddling about to discover how to use PackageMaker and how to automate it. It's built to work against Apple's Perl and Java, which have 32bit runtimes. Time permitting, I will later look at how to do a fenced plugin which would enable using an external language plugin of a different architecture. No guarantees - YMMV. If it works, enabling the Perl and Java stored procedure plugins should be as simple as :

~$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.32-wl820 External Language Stored Procedures - https://launchpad.net/mysql-wl820

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> INSTALL PLUGIN Perl SONAME 'psm_perl.so';
Query OK, 0 rows affected (0.01 sec)

mysql> INSTALL PLUGIN Java SONAME 'psm_java.so';
Query OK, 0 rows affected (0.10 sec)

mysql> SHOW PLUGINS;
+------------+--------+----------------+-------------+---------+
| Name       | Status | Type           | Library     | License |
+------------+--------+----------------+-------------+---------+
| binlog     | ACTIVE | STORAGE ENGINE | NULL        | GPL     | 
| CSV        | ACTIVE | STORAGE ENGINE | NULL        | GPL     | 
| MEMORY     | ACTIVE | STORAGE ENGINE | NULL        | GPL     | 
| InnoDB     | ACTIVE | STORAGE ENGINE | NULL        | GPL     | 
| MyISAM     | ACTIVE | STORAGE ENGINE | NULL        | GPL     | 
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL        | GPL     | 
| Perl       | ACTIVE | PSM LANGUAGE   | psm_perl.so | GPL     | 
| Java       | ACTIVE | PSM LANGUAGE   | psm_java.so | GPL     | 
+------------+--------+----------------+-------------+---------+
8 rows in set (0.01 sec)

Making SVG graphs with MySQL Perl Stored Procedures

2009-02-23T12:44:00.000-08:00

It is possible to generate SVG graphs directly without requiring access to the Google Chart Servers. Perl provides a wealth of libraries which can create SVG graphs (but most not quite as pretty as the charts which the Google service creates).

This could be invaluable for people who want to serve all the information from their own website - for example, SSL encrypted websites - where some 'secured' web browsers may not display embedded content which is not sourced from the same domain.

Here is an example of something which would provide much the same data as the example posted by Ruturaj Vartakand Walter Heck.

For my demonstration, I have used the SVG::TT::Graph modules, which the Pie chart is currently limited to a maximum of 12 segments but there are other Perl modules available for generating SVG graphs.

package DemoGraphs;

use 5.008008;
use strict;
use warnings;
use Symbol qw(delete_package);
use DBI;
use SVG::TT::Graph::Pie
require Exporter;
our @ISA = qw(Exporter);
our @EXPORT_OK = qw(  );
our @EXPORT = qw( benchmark );
our $VERSION = '0.01';

# Preloaded methods go here.


# -- returns a single row resultset
# CREATE PROCEDURE test.db_size_graph(width int, height int)
#        READS SQL DATA
#        DYNAMIC RESULT SETS 1
#        LANGUAGE Perl
#        EXTERNAL NAME "DemoGraphs::db_size_graph";
#

sub db_size_graph($$)
{
  my ($width, $height) = @_;
  my $dbh = DBI->connect('DBI:mysql:test', undef, undef);

  my $data = $dbh->selectall_arrayref(q(
 SELECT
  t.table_schema,
  ROUND(SUM(t.data_length + t.index_length) / 1024)
   AS data_length_schema
 FROM
  information_schema.tables t
 GROUP BY
  t.table_schema
 ORDER BY
  t.table_schema
  )) or die $dbh->errstr;

  my @fields = ();
  my @data = (); 
 
  my $row;

  foreach $row (@$data) {
    push @fields, $row->[0];
    push @data, $row->[1];
  }

  my $graph = SVG::TT::Graph::Pie->new({
    # Required
    'fields'  => \@fields,
    # Optional
    'height'  => $height,
    'width'  => $width,
    'show_graph_title' => 1,
    'graph_title' => 'Database Sizes (KB)',
    'key'  => 1,
   });

  $graph->add_data({
    'data'  => \@data,
    'title'  => 'Database Sizes (KB)',
  });

  return {
    'svg' => $graph->burn(),
  };
}

1;
__END__

Yet more on Stored Procedure performance

2009-02-17T12:19:00.000-08:00

In Karlsson's blog post, More on Stored Procedure performance, he has written a simple C program to send queries repeatedly to the MySQL server in order to do performance measurement. Unfortunately, his sample client will end up factoring a lot of the synchronous communications overhead in to his performance comparison.

I think that he should enable the MySQL multi-statement feature and then perform his comparison by concatenating the test statement repeatedly before sending it to the server:

#include <my_global.h>
#include <mysql.h>
#include <my_sys.h>
#define MY_SOCKET "/tmp/mysql.sock"

int main(int argc, char *argv[])
{
  MYSQL *pMySQL;
  int i, nLoop;
  char *pStmt;

  if(argc < 2)
  {
    fprintf(stderr, "Usage: %s \n", argv[0]);
    return 0;
  }
  nLoop = atoi(argv[1]);
  pStmt = argv[2];

  DYNAMIC_STRING query;
  init_dynamic_string(&query, "", 0, 4096);

  for(i = 0; i < nLoop; ++i)
  {
    dynstr_append(&query, pStmt);
    if (query.str[query.length-1] != ';')
      dynstr_append(&query, ";");
  }

  pMySQL = mysql_init(NULL);
  if(mysql_real_connect(pMySQL, NULL, "perf", "perf", "test",
  0, MY_SOCKET, CLIENT_COMPRESS | CLIENT_MULTI_STATEMENTS) == NULL)
  {
    fprintf(stderr, "Error %s connecting to MySQL.\n",
    mysql_error(pMySQL));
    mysql_close(pMySQL);
    return 1;
  }

  if(mysql_query(pMySQL, query.str) != 0)
  {
    fprintf(stderr, "Error %s in MySQL query.\n", mysql_error(pMySQL));
    mysql_close(pMySQL);
    return 1;
  }

  mysql_close(pMySQL);
  return 0;
}

So, what do my results look like...

$ time ./spperf 100000 'INSERT INTO foo VALUES(57, "Some data")'

real 0m5.198s
user 0m3.101s
sys 0m1.887s
$ time ./spperf 1 'CALL PERF(100000)'

real 0m16.944s
user 0m0.005s
sys 0m0.003s

Oops! very different results right at the outset!

Calling the PERF procedure 100000 in a multi-statement query seems to crash the server... Looks like the free_root() memory reclamation which occurs at the end of the dispatch_command() function in sql_parse.cc probably needs to be called more often when working with large multi-statement queries.
No, I haven't bothered to check if this is a known bug in 5.1.31.

Be careful with pseudo-benchmarks!