Ronald Bradford
MySQL Expert

MySQL Expert Ronald Bradford shares valuable input in MySQL Performance Tuning, MySQL Scalability and general MySQL Help from his two decades of working with MySQL, Oracle, Ingres and development technologies.

Archive for the ‘Drizzle’ Category

« Older Entries
Newer Entries »

An important Drizzle/MySQL difference

Saturday, July 4th, 2009

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.

mysql> create table t1(c1 int unsigned not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset latin1;
Query OK, 0 rows affected (0.05 sec)
mysql> create table t2(c1 int unsigned not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> create table t3(c1 int unsigned not null auto_increment primary key, c2 varchar(256) not null, unique key (c2)) engine=innodb default charset utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset latin1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'charset latin1' at line 1
drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Only a maximum of 191 is now possible.

drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(191) not null, unique key (c2)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(192) not null, unique key (c2)) engine=innodb;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Posted in Databases, Drizzle, General, MySQL, Open Source, Professional | 1 Comment »

Benchmarking Drizzle with MyBench(DBD::drizzle)

Wednesday, July 1st, 2009

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 first test with single and multi thread tests shows an improvement in all figures in Drizzle via MySQL, however I will need to run this on various different versions of MySQL including the latest 5.0 to confirm.

Posted in Databases, Drizzle, General, MySQL, Open Source, Professional | 2 Comments »

Problems compiling MySQL 5.4

Thursday, June 11th, 2009

Seem’s the year Sun had for improving MySQL, and with an entire new 5.4 branch the development team could not fix the autoconf and compile dependencies that has been in MySQL for all the years I’ve been compiling MySQL. Drizzle has got it right, thanks to the great work of Monty Taylor.

I’m working on the Wafflegrid AWS EC2 AMI’s for Matt Yonkovit and while compiling 5.1 was straight forward under Ubuntu 8.10 Intrepid, compiling 5.4 was more complicated.

For MySQL 5.1 I needed only to do the following:

apt-get install -y build-essential
apt-get install libncurses5-dev
./configure
make
make install

For MySQL 5.4, I elected to use the BUILD scripts (based on Wafflegrid recommendations). That didn’t go far before I needed.

apt-get install -y automake libtool

You then have to go compiling MySQL 5.4 for 10+ minutes to get an abstract error, then you need to consider what dependencies may be missing.
I don’t like to do a blanket apt-get of a long list of proposed packages unless I know they are actually needed.

The error was:

make[1]: Entering directory `/src/mysql-5.4.0-beta/sql'
make[1]: warning: -jN forced in submake: disabling jobserver mode.
/bin/bash ../ylwrap sql_yacc.yy y.tab.c sql_yacc.cc y.tab.h sql_yacc.h y.output sql_yacc.output -- -d --verbose
make -j 6 gen_lex_hash
make[2]: Entering directory `/src/mysql-5.4.0-beta/sql'
rm -f mini_client_errors.c
/bin/ln -s ../libmysql/errmsg.c mini_client_errors.c
make[2]: warning: -jN forced in submake: disabling jobserver mode.
rm -f pack.c
../ylwrap: line 111: -d: command not found
/bin/ln -s ../sql-common/pack.c pack.c
....
make[1]: Leaving directory `/src/mysql-5.4.0-beta/sql'
make: *** [all-recursive] Error 1

What a lovely error ../ylwrap: line 111: -d: command not found

ylwrap is part of yacc, and by default in this instance it’s not even an installed package. I’ve compiled MySQL long enough that it requires yacc, and actually bison but to you think it would hurt if the configure told the user this.

It’s also been some time since I’ve compiled MySQL source, rather focusing on Drizzle. I had forgotten just how many compile warnings MySQL throws. Granted a warning is not an error, but you should not just ignore them in building a quality product.

Posted in Amazon, Amazon Web Services, Databases, Drizzle, EC2, General, Linux, MySQL, Open Source, Professional | 1 Comment »

OSCON 2009 at a discounted rate

Friday, June 5th, 2009

OSCON moves this year from Portland to San Jose.

As one the community panel for Drizzle: Status, Principles, and Ecosystem I also have a speaker discount which you can combine with O’Reilly having also extended early bird registration until June 23.

Be sure to add the os09fos code for an additional 20% off, and be sure to shout me a drink there.

Posted in Databases, Drizzle, General, MySQL, OSCON 2009, Open Source, Professional | 1 Comment »

Drizzle now available on Mosso

Monday, April 27th, 2009

Mosso the Rackspace Cloud now has a Drizzle developer image much like the first Drizzle AMI on EC2.

The Mosso interface is definitely different, it’s a GUI, and I definitely prefer CLI, but it’s a simpler navigation for a new user. I suspect an API may be available.

I had an issue with the backup process, more the lack of feedback. The Knowledge Base didn’t help, so both calling and Live Chat directed me ultimately to the same person. I also found a bug in the backup process, that is being able to select an incomplete backup to try and launch a new server. I talked to Support about and apparently already known.

And in true open source form, the Drizzle version is actually one point higher then yesterday’s AWS image.

I don’t know how to *publish* this backup so others can try it. Something on the list of things to do, however I was able to verify my backup with a new instance.

$ drizzle
Welcome to the Drizzle client..  Commands end with ; or \g.
Your Drizzle connection id is 2
Server version: 2009.04.998 Source distribution

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

drizzle>
drizzle> select version();
+-------------+
| version()   |
+-------------+
| 2009.04.998 |
+-------------+
1 row in set (0 sec)

drizzle> select count(*) from sakila.film;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.18 sec)

Posted in Cloud Computing, Databases, Drizzle, General, Mosso, MySQL, Open Source, Professional | 1 Comment »

Announcing Drizzle on EC2

Sunday, April 26th, 2009

I have published the very first sharable Drizzle Amazon Machine Image (AMI) for AWS EC2, based on the good feedback from my discussion at the Drizzle Developer Day on what options we should try.

This first version is a 32bit Developer instance, showcasing Drizzle and all necessary developer tools to build Drizzle from source.

What you will find on drizzle-ami/intrepid-dev32 – ami-b858bfd1

Ubuntu 8.10 Intrepid 32 bit base server installation:

  • build tools
  • drizzle dependencies
  • bzr 1.31.1

From the respective source trees the following software is available:

  • drizzle 2009.04.997
  • libdrizzle 0.0.2
  • gearman 0.0.4
  • memcached 1.2.8
  • libmemcached 0.28

Drizzle has been configured with necessary dependencies for PAM authentication, http_auth, libgearman and MD5 but these don’t seem to be available in the binary distribution.

I will be creating additional AMI’s including 64bit and LAMP ready binary only images.

The following example shows using drizzle on this AMI. Some further work is necessary for full automation, parameters and logging. I’ve raised a number of issues the Drizzle Developers are now hard at work on.

1. Starting Drizzle

ssh ubuntu@ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com
sudo /etc/init.d/drizzle-server.init start &

2. Testing Drizzle (the sakila database has been installed)

$ drizzle
Welcome to the Drizzle client..  Commands end with ; or \g.
Your Drizzle connection id is 4
Server version: 2009.04.997 Source distribution

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

drizzle> select version();
+-------------+
| version()   |
+-------------+
| 2009.04.997 |
+-------------+
1 row in set (0 sec)

drizzle> select count(*) from sakila.film;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0 sec)

3. Compiling Drizzle

sudo su - drizzle
ls
deploy  drizzle  libdrizzle  sakila-drizzle
cd drizzle
./configure --help
Description of plugins:

   === HTTP Authentication Plugin ===
  Plugin Name:      auth_http
  Description:      HTTP based authentications
  Supports build:   static and dynamic

   === PAM Authenication Plugin ===
  Plugin Name:      auth_pam
  Description:      PAM based authenication.
  Supports build:   dynamic

   === compression UDFs ===
  Plugin Name:      compression
  Description:      UDF Plugin for compression
  Supports build:   static and dynamic
  Status:           mandatory

   === crc32 UDF ===
  Plugin Name:      crc32
  Description:      UDF Plugin for crc32
  Supports build:   static and dynamic
  Status:           mandatory

   === Error Message Plugin ===
  Plugin Name:      errmsg_stderr
  Description:      Errmsg Plugin that sends messages to stderr.
  Supports build:   dynamic

   === Daemon Example Plugin ===
  Plugin Name:      hello_world
  Description:      UDF Plugin for Hello World.
  Supports build:   dynamic

   === Gearman Logging Plugin ===
  Plugin Name:      logging_gearman
  Description:      Logging Plugin that logs to Gearman.
  Supports build:   dynamic

   === Query Logging Plugin ===
  Plugin Name:      logging_query
  Description:      Logging Plugin that logs all queries.
  Supports build:   static and dynamic
  Status:           mandatory

   === Syslog Logging Plugin ===
  Plugin Name:      logging_syslog
  Description:      Logging Plugin that writes to syslog.
  Supports build:   static and dynamic
  Status:           mandatory

   === MD5 UDF ===
  Plugin Name:      md5
  Description:      UDF Plugin for MD5
  Supports build:   static and dynamic

   === One Thread Per Connection Scheduler ===
  Plugin Name:      multi_thread
  Description:      plugin for multi_thread
  Supports build:   static
  Status:           mandatory

   === Old libdrizzle Protocol ===
  Plugin Name:      oldlibdrizzle
  Description:      plugin for oldlibdrizzle
  Supports build:   static
  Status:           mandatory

   === Pool of Threads Scheduler ===
  Plugin Name:      pool_of_threads
  Description:      plugin for pool_of_threads
  Supports build:   static
  Status:           mandatory

   === Default Signal Handler ===
  Plugin Name:      signal_handler
  Description:      plugin for signal_handler
  Supports build:   static
  Status:           mandatory

   === Single Thread Scheduler ===
  Plugin Name:      single_thread
  Description:      plugin for single_thread
  Supports build:   static
  Status:           mandatory

   === Archive Storage Engine ===
  Plugin Name:      archive
  Description:      Archive Storage Engine
  Supports build:   static
  Status:           mandatory

   === Blackhole Storage Engine ===
  Plugin Name:      blackhole
  Description:      Basic Write-only Read-never tables
  Supports build:   static and dynamic
  Configurations:   max, max-no-ndb

   === CSV Storage Engine ===
  Plugin Name:      csv
  Description:      Stores tables in text CSV format
  Supports build:   static
  Status:           mandatory

   === Memory Storage Engine ===
  Plugin Name:      heap
  Description:      Volatile memory based tables
  Supports build:   static
  Status:           mandatory

   === InnoDB Storage Engine ===
  Plugin Name:      innobase
  Description:      Transactional Tables using InnoDB
  Supports build:   static and dynamic
  Configurations:   max, max-no-ndb
  Status:           mandatory

   === MyISAM Storage Engine ===
  Plugin Name:      myisam
  Description:      Traditional non-transactional MySQL tables
  Supports build:   static
  Status:           mandatory

Report bugs to <http://bugs.launchpad.net/drizzle>.

Posted in Amazon, Databases, Drizzle, EC2, General, Linux, MySQL, Open Source, Professional | No Comments »

Compiling libdrizzle

Sunday, April 26th, 2009

Compiling libdrizzle is a rather trivial task. The following are the steps I undertook on Ubuntu 8.10 Intrepid 32 bit.

There was one pre-requisite from the most basic installed developer tools.

sudo apt-get install -y  automake

bzr clone lp:libdrizzle
cd libdrizzle
./config/autorun.sh
./configure
make
make install

And there they are:

$ ls -l /usr/local/lib/libdrizzle*
-rw-r--r-- 1 root root 1122710 2009-04-26 18:10 /usr/local/lib/libdrizzle.a
-rwxr-xr-x 1 root root     940 2009-04-26 18:10 /usr/local/lib/libdrizzle.la
lrwxrwxrwx 1 root root      19 2009-04-26 18:10 /usr/local/lib/libdrizzle.so -> libdrizzle.so.0.0.2
lrwxrwxrwx 1 root root      19 2009-04-26 18:10 /usr/local/lib/libdrizzle.so.0 -> libdrizzle.so.0.0.2
-rwxr-xr-x 1 root root 1003734 2009-04-26 18:10 /usr/local/lib/libdrizzle.so.0.0.2

I added the following to enable other programs using libdrizzle to find the libraries in the system path.

echo "/usr/local/lib" > /etc/ld.so.conf.d/drizzle.conf
ldconfig

Posted in Compiling, Databases, Drizzle, General, Linux, Open Source | No Comments »

Drizzle/bzr dependency

Sunday, April 26th, 2009

A number of developers had problems on Friday at the Drizzle Developer Day with compiling bzr. The distro in question I was helping with was CentOS 5 32-bit. I had no issues on CentOS 5 64bit.

Today while creating the first deployed Drizzle AWS AMI I discovered the same problem using Ubuntu 8.10 Intrepid 32 bit.

The solution was actually rather trivial. Installing the python-dev package solved the problem.

apt-get install python-dev

Bzr 1.13.1 Compiling error

building 'bzrlib._btree_serializer_c' extension
gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall -Wstrict-prototypes -fPIC -I/usr/include/python2.5 -c bzrlib/_btree_serializer_c.c -o build/temp.linux-i686-2.5/bzrlib/_btree_serializer_c.o
bzrlib/_btree_serializer_c.c:4:20: error: Python.h: No such file or directory
bzrlib/_btree_serializer_c.c:5:26: error: structmember.h: No such file or directory
bzrlib/_btree_serializer_c.c:35: error: expected specifier-qualifier-list before ‘PyObject’
....
bzrlib/_btree_serializer_c.c:1651: error: request for member ‘f_lineno’ in something not a structure or union
bzrlib/_btree_serializer_c.c:1651: warning: statement with no effect
bzrlib/_btree_serializer_c.c:1652: warning: implicit declaration of function ‘PyTraceBack_Here’

  Cannot build extension "bzrlib._btree_serializer_c".
  Use "build_ext --allow-python-fallback" to use slower python implementations instead.

error: command 'gcc' failed with exit status 1

Posted in Compiling, Databases, Drizzle, General, Open Source, Professional | No Comments »

Adding a Drizzle Plugin

Friday, April 24th, 2009

I joined about 50 others including a number of core MySQL developers and MySQL community members today for the 2009 Drizzle developers day at Sun Microsystems Santa Clara campus.

In addition to a number of presentations and various group discussions most of my individual hacking time was under the guidance of Drizzle team developer Stewart Smith were Patrick Galbraith and myself started the porting of Patrick’s memcached UDF functions for MySQL.

Leveraging some existing Drizzle plugin’s such as CRC32() and UNCOMPRESS() we were easily able to navigate the src/plugin/memcached directory plug.in, Makefile.am and drizzle_declare_plugin definition in the new get.cc to get a working stub ‘Hello World Example’;

plug.in

$ more plug.in
DRIZZLE_PLUGIN(memcached,[memcached UDF],
        [UDF Plugin for memcached])
DRIZZLE_PLUGIN_STATIC(memcached,   [libmemcachedudf.a])
DRIZZLE_PLUGIN_MANDATORY(memcached)  dnl Default
DRIZZLE_PLUGIN_DYNAMIC(memcached,   [libmemcachedudf.la])


Makefile.am

$ more Makefile.am
# Copyright (C) 2006 MySQL AB
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA

EXTRA_LTLIBRARIES =	libmemcachedudf.la
pkgplugin_LTLIBRARIES =	@plugin_memcached_shared_target@
libmemcachedudf_la_LDFLAGS =	-module -avoid-version -rpath $(pkgplugindir)
libmemcachedudf_la_LIBADD =		$(LIBZ)
libmemcachedudf_la_CPPFLAGS=	$(AM_CPPFLAGS) -DDRIZZLE_DYNAMIC_PLUGIN
libmemcachedudf_la_SOURCES =	get.cc

EXTRA_LIBRARIES =	libmemcachedudf.a
noinst_LIBRARIES =	@plugin_memcached_static_target@
libmemcachedudf_a_SOURCES=	$(libmemcachedudf_la_SOURCES)

$ more get.cc
/* Copyright (C) 2009 Patrick Galbraith, Ronald Bradford
...
*/
#include <drizzled/server_includes.h>
#include <drizzled/sql_udf.h>
#include <drizzled/item/func.h>
#include <drizzled/function/str/strfunc.h>

#include <stdio.h>
#include <libmemcached/memcached.h>

using namespace std;

/* memc_get */
class Item_funcmemc_get : public Item_str_func
{
public:
  Item_funcmemc_get() : Item_str_func() {}
  const char *func_name() const { return "memc_get"; }
  bool check_argument_count(int n) { return (n==1); }
  String *val_str(String*);
  void fix_length_and_dec() {
    max_length=32;
    args[0]->collation.set(
      get_charset_by_csname(args[0]->collation.collation->csname,
                            MY_CS_BINSORT), DERIVATION_COERCIBLE);
  }

};

String *Item_funcmemc_get::val_str(String *str)
{
  assert(fixed == 1);
  String * sptr= args[0]->val_str(str);
  str->set_charset(&my_charset_bin);
  if (sptr)
  {
    null_value=0;
    str->set("hello memcached test", 20,system_charset_info);
    return str;
  }
  null_value=1;
  return 0;
}

Create_function memc_get_factory(string("memc_get"));

static int memcached_plugin_init(PluginRegistry &registry)
{
  registry.add(&memc_get_factory);
  return 0;
}

drizzle_declare_plugin(memcached)
{
  "memcached",
  "0.1",
  "Patrick Galbraith, Ronald Bradford",
  "memcached plugin",
  PLUGIN_LICENSE_GPL,
  memcached_plugin_init, /* Plugin Init */
  NULL,   /* Plugin Deinit */
  NULL,   /* status variables */
  NULL,   /* system variables */
  NULL    /* config options */
}
drizzle_declare_plugin_end;

Posted in Databases, Drizzle, General, MySQL, Open Source | No Comments »

MySQL Users Conference Opening Lines

Tuesday, April 21st, 2009

Opening introduction from Colin Charles got us started. Karen Tegan Padir VP MySQL & Software Infrastructure was the opening keynote.

She comes from a strong tech background and is passionate about open source, the communities and how to make a successful product.

There isn’t a person that doesn’t go a day without interacting with a website or hardware system that uses a MySQL database.

The big news was the announcement of MySQL 5.4 – Performance & Scalability. Key features include.

  • InnoDb scalability 16way x86 and 64 way CMT servers
  • subquery optimization
  • new query algorithms
  • improved stored procedures, and prepared statements
  • enhanced Information Schema
  • improved DTrace Support

More information at MySQL 5.4 Announcement Details….

Other key points includes:

1. Ken Jacobs announces today an Embedded Innodb with a powerful API (not SQL based). Read more at Innobase Introduces Embedded InnoDB
2. MySQLCluster 7.0 is also released today. Some benchmarks 4.3x improvements. New features also include LDAP support.
3. The next release of MySQL Query Analyzer, 2.1 announced.
4. Sun announces a commitment to accept contributions from the community.
5. Community also gets the Monthly Rapid Updates.
6. MySQL Drizzle Project is discussed as a technology incubator.

Partners of the year: Intel, Infobright and Lifeboard.
Appliation of the year: Zappos, Alcatel-lucent and Symantec.
Community members of the year: Marc Delisle, Ronald Bradford, Shlomi Noach.

Posted in 42SQL, Databases, Drizzle, General, MySQL, MySQL User Conferences, Professional, mysqluc09 | 1 Comment »

« Older Entries
Newer Entries »