I had trouble getting Perl DBI and DBD-mysql on Windows in the past. In addition, on Windows 64-bit, you sometimes see recommendations of using 32-bit Perl.

Today I got to test the latest 64-bit ActiveState Perl distro for Windows, version 5.12.3.1204. I tested it on Windows 2008 R2 64-bit. I am happy to report that it works. I am not categorically recommend FOR the installation of 64-bit Perl on Windows, though.

Here are the steps:
1. Get the ActiveState Perl 64-bit package for Windows and install it, following all the default options;
2. On command prompt, do:
cd c:\perl64\bin
ppm install DBI
ppm install DBD-mysql

I then tested against both Oracle’s MySQL 5.5 Community Server and MariaDb’s 5.2.7 on

Mythbusters: How to configure InnoDB buffer poll on large MySQL servers

Yesterday I wrote about the dangers in using top on systems with 100+ GB of RAM, not to mention future systems with 1+ TB. A related topic is, how should I configure MySQL on such a large system?

There is a classic rule of thumb that on a dedicated MySQL server one should allocate 80% of memory to the InnoDB buffer pool. On a 128GB system that is 102.4 GB. This means that I would leave 25.6 GB of RAM "unused". So surely on these large systems, this old piece of advice cannot hold anymore. If the database was previously running on a server that in total had less than that altogether, it seems wrong to leave so much memory just unused. Let's label the old rule of thumb

We all know that a megabyte in binary system is not the same as one million bytes (in decimal system). But have you actually cared much about it? I have to admit I haven't. I know there is a small rounding error, but by and large I always treated 2^10 = 1 kB = 1024 bytes and 10^3 = 1 kB = 1000 as the same thing. (Update: Opening sentence was edited to remove units MB and MiB since it seems even I managed to use them backwards! The math in this article is correct. The rest of the article uses MB, GB and TB mostly to refer to binary magnitudes, which is apparently incorrect. See comments for wikipedia links and discussion.)

More importantly, when you move into larger numbers, rounding errors usually become even less important. Unfortunately, in this case they become bigger:

read more

Notes:
1. The script prints out elapsed time since transaction started, MySQL thread id, and the kill statement for transactions running longer than a defined threshold value, in seconds. Just copy, paster, and then execute the kill statement if you want to terminate the long transaction(s);
2. Adjust shellCmd variable;
3. Adjust longRunningThreshold value as needed. It is measured in seconds;
4. No special libraries/modules needed, as long as there is a working mysql client;
5. re module is used for regex processing. Good place to find examples of regular expression search and grouping. A status variable is used to assist locating MySQL thread id once a transaction running longer than the defined threshold is found.

import re, shlex, subprocess
def runCmd(cmd):
    proc = subprocess.Popen(shlex.split(cmd), stdout=subprocess.PIPE,

When I think of FUD (Fear, Uncertainty, Doubt), I think of the intangible (and irrational) sense that a given technology is inherently bad, will cause your site to go down, and your girlfriend to leave you.  

Over time SQL FUD has cropped up from all the NoSQL evangelists hammering on about how NoSQL solves all the traditional problems RDBMSs have.  Do they have valid points?  In a lot of cases, sure.  But that doesn’t mean that NoSQL is without its own problems.

Part of making peace between the SQL and NoSQL camps should include being honest about the strengths and weaknesses of each technology, so engineering decisions get made, not sales decisions.  Every technology has its strengths and weaknesses, just like physical materials in, say, structural engineering.

I attended Vadim Tkachenko’s talk on XtraBackup during MySQL conference in Santa Clara last week. Backups are obviously very important, but the use case I had in mind is this:
Replicating a database that has Innodb tables in it, while keeping both master and slave on line if possible.

Tangent: by the way, I love the native backup utility that was once promised in MySQL 6.0, similar to SQL Server’s way of backup. It was like running “BACKUP myDb to DISK = ‘/backupDirectory/myDb.bak’” under mysql client, but I digress…

I have used mysqldump to accomplish this in the past, but I wondered how XtraBackup would fare in this task, especially after hearing Vadim’s talk and reading news on Percona’s development effort. To cut to the

Using MySQL replication slave(s) for reporting (with potentially different storage engines) is a very popular way of scaling database read activities. As usual, you want to be on top of things when replication breaks so end users can be notified and issues addressed. When Nagios, Zabbix, or whatever monitoring tools are not available or otherwise not accessible, there got to be another way. I wrote the following Python script for that purpose. The script can then be scheduled via crontab to check replication status in an interval you define. When things break, you get a notification email.

Notes:
1. I toyed with MySQLdb Python module for this task, but I don’t like the fact that I cannot easily retrieve values via column names in a MySQLdb cursor. If there is an easier way that I am not aware of due to my ignorance, I’d appreciate it if you could let

Update:

Commenter MarkR made a great point: if possible, use some packaging tools, to try to maintain proper dependencies, to the extent that is possible. Install from the source should be Plan B. So, try yum install MySQL-python first.

This is mostly for my own future reference. It’ll be icing on the cake if it helps you!

This is geared for CentOS or Red Hat. Use apt-get or other packaging tools for different flavours of Linux.

1. Get Python module setuptools called easy_install. I love easy_install, by the way, sort of like CPAN for Perl modules;
2. To install MySQLdb package, you would think easy_install MySQLdb would do. But that is not the case. I hope the developer would fix that. Instead, you need:

easy_install MySQL-python

3. If you have build errors, you may need:

Jonathan Levin has an excellent blog post titled How to make MySQL cool again. It is almost word for word something I've wanted to write for a long time. Now I don't need to, thanks Jonathan.

Once again Blogger failed to post my comments to his site, so I will make some comments as a new post of my own instead. Jonathan actually lists things that exist already but isn't getting used enough. My list contains also a few things that I don't know if they exist or not.

Hi Jonathan

read more

I am reading and following examples in MySQL 5.1 Plugin Development. After compiling and moving a .so file (think DLL or Assembly file in Windows) into MySQL plugin directory, I got this message when I tried to create a UDF (User Defined Function):

mysql> create function udf_staticexample returns integer soname 'udf_staticexample.so';
ERROR 1126 (HY000): Can't open shared library 'udf_staticexample.so' (errno: 0 /usr/lib/mysql/plugin/udf_staticexample.so: failed to map segment from shared object: Permission denied)

This is caused by the fact that the .so shared object file is not in the right

2011-06-26 update:

I am not sure if there are any changes in the latest make and gcc packages. Anyway, I noticed when run make, I encountered the message below:

make: g: Command not found
make: [qgen] Error 127 (ignored)

To fix this, find where gcc is at, then created a symbolic link g that points to gcc. All is well afterwards:
[root@ip-10-245-209-196 dbgen]# which gcc
/usr/bin/gcc
[root@ip-10-245-209-196 dbgen]# cd /usr/bin/
[root@ip-10-245-209-196 bin]# ln -s gcc g

End update
Recently I found myself doing some data loading benchmark testing with table partition. Data loading and storing for BI/DW/DSS stuff almost always involves data partitioning. SQL Server partition has a nice feature called partition switch, where you can swap data in and out of a partitioned table.

The sample my-small.cnf, my-medium.cnf, my-large.cnf, my-huge.cnf, and my-innodb-heavy-4G.cnf files from MariaDB and Percona (I didn’t bother checking Oracle’s distro) were from the dinosaur age, with comment like this for my-huge.cnf:

This is for a large system with memory of 1G-2G where the system runs mainly MySQL.

So I suggested some updating is needed on IRC and mailing lists. After getting a kick in the pants from the always affable Kurt von Finck (I thought “kick in the pants” is a pretty good rendition of “遣将不如激将”, for those who followed our exchanges in mailing lists), I decided to take this upon myself.

It’s actually not hard to update this. I believe just 6 files in total, at most, need to be touched under the support-files directory inside the trunk: my-small.cnf.sh, my-medium.cnf.sh,

IBM's Watson natural language Question & Answer system made headlines recently with its primetime debut on Jeopardy.  Despite a few embarassing answers, Watson trounced top Jeopardy players Brad Rutter and Ken Jennings.  Watson is built from 90 IBM Power 750 IBM Linux servers with 16 terabytes of memory providing 80 Teraflops of processing power.  Watson is perhaps the most famous "Big Data" systems out there.  Watson's knowledge base

Vadim Tkachenko published interesting benchmark results with PCI-E based SSDs here. I recently got a chance to benchmark FusionIO’s 320 GB PCI-E drive. It was really impressive. My results, done on Windows with sqlio, are consistent (not identical, of course, but in the same ballpark) with what Vadim reported in that blog post, done with sysbench on Linux.

sqlio is a popular IO throughput testing tool from Microsoft. I didn’t get to test the throughput when the SSD is close to full. The key takeaways that I learned from my testing are:

1. I can confirm that there is no difference between random and sequential IO, contrary to the traditional spindle based hard disks;

2. Read is significantly faster than write. Reads and writes

Last fall, before I joined Zendesk, I took a role as an Executive-in-Residence at Scale Venture Partners. A lot of people asked me about this, so I've written an article at GigaOm that describes my thought process and what I ended up working on.

While there are as many variations on the EIR position as there are venture firms, there are two flavors, generally speaking: Entrepreneur-in-Residence and Executive-in-Residence. Most firms have some experience with Entrepreneur-in-Residence programs.

I’ve done this in the past, but thought this time I’ve got to take notes. It can be used as a crude check list in the future. Don’t underestimate the power of a practical, down-to-earth check list! Perhaps documents like this should be kept in a wiki page, for easy updating to avoid being stale, a proeblem with blog entries, it seems.

P in LAMP here stands for php, not Python or Perl. L is CentOS (I used CentOS 5.5) or Red Hat Linux. I am not covering moving all databases in a MySQL instance, just a select few or just one.

I’d appreciate your comments or suggestions.

Software install and configuration
MySQL:
MariaDB or Percona.

MariaDB can be had here.

For Percona server and client tools, it’s best to have direct access to Percona’s repository:

yum

From a stock/standard/typical/desktop install of Linux, it seems these are required in order to build MySQL/MariaDb/Percona forks:

gcc
gcc-c++
automake
libtool
bison
ncurses (Thanks Justin!)

Do apt-get, yum, rpm, emerge, or whatever to get them before doing configure, make and such. I am missing one, and I think it has “curse” or something like that in its name. Will update this post when I find that out.

I only recently found out about GigaOm's upcoming Net:Work conference.  It's held December 9 at UCSF Mission Bay conference center.  While the name of the conference is a bit ambiguous, the actual area of focus is very clear: how will we collaborate in the 21st century?  

The impact of smartphones, tablet computing, social networks, Software-as-a-Service and Cloud computing is just starting.  As a result, I think there are tremendous opportunities for startup companies to disrupt existing markets with more modern, lightweight applications

Rapid Application Development (RAD) is a way of developing computer software applications with less effort than the traditional means.

RAD tools focus on providing code generation and automated testing capabilities with the use of convention over configuration to provide a streamlined workflow to create applications.

Even with the most advanced and easiest to use RAD tools, there are times which the traditional enterprise and the business software development vendors which are having their own implementations and in-house built frameworks are continuously refusing to adopt them.

Most of the misconceptions on the RAD are based on FUD (Fear, Uncertainty

I wrote a guest column for GigaOm on how open source software, cloud and software as a service are helping to bring about the consumerization of IT: namely bringing simplicity where complexity reigned.  I cited some examples including New Relic, Box.net and Apple.

Open source has gone a long way toward putting power back in the hands of developers, who can download, install and deploy software without having to go through any kind of convoluted sales or budget approval process.  You want MySQL?  You can download and install in 

It seems obvious that given the decreasing cost of storage and computation, there's going to be a significant increase in the volume of data that organizations accumulate over the next 10 years.  But the type of data being accumulated may be different from the areas where traditional DBMSs dominated.  It's not just about transactions; it's search patterns, on-line behavior, click-thru data, events fired off by smartphones, messages over Twitter & Facebook, log data of various kinds.

If an organization can figure out a better way identify prospects, or deliver more targeted ads, or optimize pricing decisions by analyzing terrabytes of data, they'd be crazy not to. Over the long term, companies

Mixing up some MySQL and SQL Server hacking activities can be fun. I’ve also noticed that when you work with different database platforms, it can bring additional clarity to certain concepts and techniques. One reason for that, I think, is that different terminologies and ways of explanations are used for people practicing on different platforms, and that tends to re-enforce or otherwise bring clarity to things that you may not have a good grasp to begin with.

Anyway, I am following Baron Schwartz et al’s High Performance MySQL book and thought the example of building your own hash index for URL lookup looks really useful and clever. On the topic of hash collison, the book discussed using words in

We’re gearing up to release v0.5 of GenieDB right now, and the biggest new feature in v0.5 is the generic indexing framework.

Originally, GenieDB provided indices on fields of records. As we do not mandate a per-table schema, each record in a table could have totally different fields; so when an index is created on the field “foo” of the table “bar”, we index any record in “bar” that has a “foo” field under that value in the index. If the record did not have a “foo” field, then it doesn’t appear in the index at all (whereas records with a NULL value for “foo” are indexed under NULL, of course).

However, expecting future interesting developments, we made sure this part of the code – figuring out how a given record should appear in a given index – would be easy to

To benchmark IO on Linux and MySQL transaction processing, SysBench is a popular choice that can do both. After poking around at the source code, it seems PostgreSQL and Oracle are also included for transaction processing testing if you have the proper header files, but I didn’t test those.

To benchmark IO on Windows and SQL Server transaction processing, Microsoft provides two tools, SQLIO and SQLIOSim. SQLIO is a misnomer in that it really doesn’t have much to do with SQL Server. It is a general purpose disk IO benchmark tool.

So today I was playing with SysBench and noticed that I can compile and build it on Windows as well. I decided I should run IO benchmark on a single machine with both tools (SQLIO and SysBench), and see if I could reconcile the results.

To make things simple, I thought I would just benchmark random read of 3G

Lately I worked quite a bit with Python and Linux, writing monitoring and automation utilities. I am in a transition period, so I thought I ought to write some Python stuff interfacing with MySQL for fun, and start positioning myself for expanded career horizon, I hope.

To get started, I thought it would be fun to rewrite a Perl utility I wrote before with Python. That script converts MySQL character sets to utf8, a very common task for wikis and blogs during an upgrade. This time, I did everything from scratch: firing up an Amazon EC2 Linux instance, hand install and configuring MySQL 5.1.50 (creating mysql user, group, wget tarball, setting directory ownership and permissions, creating symbolic to MySQL binaries, editing my.cnf,

I'm the boards of two companies (Pentaho, Revolution Analytics) that are starting to see a lot of customer traction around Big Data. More and more companies in media, pharma, retail and finance are doing advanced analysis, reporting, graphing, etc with massive data sets. It made me wonder what other areas of the technology stack might evolve with the trend towards Big Data.  Obviously, there's new middleware layers like Hadoop and Map Reduce, and we're also seeing the emergence of