Yesterday I had a look at the Open Source Census (OSC), a project aiming at counting the installations of open source packages.

It is a collaborative effort. The OSC offers the infrastructure, and it's open to cooperation. It works with a disc scanner, and relies on the principle that the contributions are voluntary and anonymous.
If you want to contribute, you need to register, and you are encouraged to do so anonymously. The OSC is not interested in who you are, but the registration gives you a chance of tracking your results with the rest of the community.
The registration is also necessary to avoid duplicates, and to track your installations over time, should you decide to do that on a regular basis.
If you want to give it a try, the procedure goes like this:

1. Register (with any fancy name you want);
2. download the scanner package (it's open source)
3. scan your computer (or some selected directories), using your unique identifier
4. look at the results;
5. send the results to the census (using the same tool).

Bear in mind that there is no way for the OSC to track down who you are, because you are not asked in the first place. So the results are truly anonymous.

Some concerns:

• The scan package is open source. However, even the simplest package available the pure Ruby) is more than 6000 lines of code. If you are security conscious, examining the code is not a quick task. You will have to rely on peer review from the community, or do a test scan in a virtual machine to examine what the software does (that's what I did);
• If you have a large machine, the scan may take hours. You will have to plan for night scans if you want to contribute seriously.

On the plus side, the process of scanning is open. You can contribute the signatures of your favorite open source tools, to be included in the next scans.
Looking at the results may bring some surprises. I did not know that I had two different versions of MySQL-connector/J in my laptop, or two versions of PostgreSQL connector either! And I wonder how I managed to get 5 (FIVE) different versions of docbook-xml in my laptop, given that I haven't ever asked to install it.
However, the scanner gives you more than the public list of packages found in your box. For your own consumption (it is not sent to the census) it produces a detailed list of where each package was found. So you cam analyze that list and eventually clean up the system.
Other surprising results. I checked how often MySQL was installed, and it turns out to be present on 37% of the scanned boxes. The surprising results, however, is the the distribution of old versions. About 20% of MySQL servers are still using version 4.x. Fascinating!
The project is young, but very promising, in my opinion. There may be problems for adoption from large corporations (security policies will be hard to deal with), but if the community picks up, it may produce good results. Give it a try.

There were a number of problems in the installer script and in support for COMPLETE refresh materialized views. These problems have been rectified and an updated version is available on sourceforge. Also, you can pull the latest source from the svn repository:
https://flexviews.svn.sourceforge.net/svnroot/flexviews/src

Here is a little example for complete refresh:

CALL flexviews.create('mv2', 'test', 'COMPLETE', 1);
SET @MVID = LAST_INSERT_ID();
CALL flexviews.set_definition(@MVID, 'SELECT 1');
CALL flexviews.enable(@MVID);
CALL flexviews.refresh(@MVID, 'COMPLETE');

Since few weeks I was working on patching the MySQL 5.1 with libevent support to have thread pooling in server end as it is needed by few properties where they do not need a persistent connection and needed to scale server with thousands of connections as each call just does a simple query execution by connecting and disconnecting and pooling seemed to be a right choice.

When I brought this idea to Monty; he pointed me to 6.0 where it is in preliminary stage. Now I took the same design and implemented in 5.1 to be compatible with how its working in 6.0; and things so far seems to be running fine, except few misc glitches that am trying to solve now.

It uses the same thread_handling=one-thread-per-connection (default) and new thread_handling=pool_of_threads and thread_pool_size controls how many will be initialized and kept at the startup.

| thread_handling | pool-of-threads | | thread_pool_size | 5010 |

Few things that are missing from the 6.0 is the status of thread pools (number of threads currently in use, max used so far from pool etc) are all added now.

I am currently benchmarking the performance between the regular persistent to pool-of-threads model to see how it works out. I will post more details once I have the graphs and numbers along with working model.

Did you notice that I moved this blog from pair Networks to pairLite hosting?

Probably not, unless you check the DNS of xaprb.com regularly!

Don’t you hate it when people say “I’m moving my blog, I hope there won’t be more than a few days of downtime, blah blah…” Why is this ever necessary, I wonder? I wonder the same thing about a lot of hosting providers — recently I had a client in my consulting practice whose (very large, well-known) hosting provider tried to help them with some very simple MySQL work and ended up causing them an obscene amount of downtime, like many many days, and there was no end in sight. As I spoke on the phone with him and asked him about his business, he said “we have X thousand users in our beta.” long pause. “Well, we did anyway.” The poor man hadn’t slept in I don’t know how long. I could only empathize with what it must have felt like to say those words in that mental and physical state. And as I spoke with him I had to tell him, cringing as I said it, that his downtime was completely unnecessary. His host was utterly ignorant of what they were doing.

Does this ever happen to someone you know? It’s such a shame. I wouldn’t be surprised, really, if this client has a hard time recovering fully from this blow.

This is not to demonize hosting providers. They are often great at hosting. But they are not MySQL experts. (Some of them hire Percona to do their MySQL support, and that is good.) If you need expert MySQL help, hire an expert. We can also tell you what to watch out for on your shared hosting — the hosting providers often don’t understand the hardware requirements for a database server, and we constantly see simple and really bad avoidable mistakes such as a 32-bit OS on 64-bit hardware or a misconfigured RAID controller. Don’t rely on your hosting provider for anything database-related, especially backups.

Similarly, if you need expert hosting, call an expert hosting provider, not someone who’s just reselling. I’ve had such good luck with pair Networks (and now pairLite, their budget service) that I write love letters like this blog post constantly. And I recently switched away from Embarq for my DSL provider, to BRIWorks, a local shop with really friendly, smart people who charge me more money than Embarq, and yet I love them for it. (By the way, they’re not just local; they can help you if you don’t live in Charlottesville. If I wasn’t already a pairLite customer, I’d use them.)

My point? With good hosting, and my skills with MySQL and PHP and Apache, I moved with no downtime. OK, it’s not hard — but neither is a non-corrupt MySQL backup that doesn’t kill your entire business. If you know what you’re doing.

If you don’t know what you’re doing, hire someone who does!

backups, BRIWorks, Embarq, hosting, mysql, pair Networks, pairLite, WordPress

A while ago I blogged about using Mercurial as a backup repository for my databases. I was asked about this again
last night over dinner.

This is an example of what I see via the web:




I can see both changes to schema, and do deltas between days (aka slice out a days worth of data or grab a snapshot of anyone one particular days data). The nice thing with hgweb is that I can make this happen via my browser :)

Part of what really makes this work is the --tab option to mysqldump (I also use Innodb so my backups are all done online). I can compress it, but frankly disk is cheap and storing deltas is cheap :)

I am proud to announce the availability of Flexviews for MySQL 1.0.0-alpha under LGPLv3!
http://flexviews.sourceforge.net

The documentation needs quite a bit of work. When you run into problems (and I'm sure you will) you can ask for help on the sourceforge support tracker. Please download the example database for some examples of incremental refreshable views.

I haven't tested COMPLETE refresh views in quite a while, but I think they break when you try to enable them. I'll fix this today or tomorrow and upload a new version. INCREMENTAL refresh is what is interesting anyway :)

Please report bugs (of which I'm sure there are quite a few), as well as feature requests, etc, on the sourceforge bug tracker.

http://barcamp.org/BarCampBrisbane gives the details... I'm going in the afternoon, and might do a talk on RepRap. There'll be enough software talks, so why not something different!
(not that I *always* talk about MySQL - I have done other talks at various confs)

I keep on with my productivity little tricks. This time I’m sharing some of my firefox shortcuts. I’m not in the mood for explaining how to actually install these, so check out the excellent article Firefox and the art of keyword bookmarking, if you need help.

# Dictionary search
dict http://dictionary.reference.com/search?q=%s

# Yahoo finance stock
fi http://finance.yahoo.com/q?s=%s

# Wikipedia page
wp http://en.wikipedia.org/wiki/Special:Search?search=%s
slang http://www.urbandictionary.com/define.php?term=%s

# Search in MySQL website/manual
my http://mysql.com/%s

# BitTorrent search
bt http://www.yotoshi.com/?keyword=%s
code http://www.google.com/codesearch?q=%s

# Ruby documentation
ri http://www.google.es/search?hl=es&client=firefox-a&rls=org.mozilla%3Aes-ES%3Aofficial&hs=3zt&q=%s+site%3Aruby-doc.org&btnI=B%C3%BAsqueda&meta=

# Google search (experimental version with keyboard shortcuts)
g http://www.google.com/search?q=%s&esrch=BetaShortcuts

# Google search (I'm feeling lucky)
gg http://www.google.com/search?q=%s&esrch=BetaShortcuts&btnI=Lucky

# eBay search
ebay http://search.ebay.es/search/search.dll?satitle=%s

# Post current page in delicious
pkd javascript:location.href='http://del.icio.us/koke?v=3&url='+encodeURIComponent(location.href)+'&title='+encodeURIComponent(document.title)

# My delicious (with tag support)
kd http://del.icio.us/koke/%s
as https://www.google.com/adsense/report/overview
gr http://www.google.com/reader/view/
fb http://www.feedburner.com/fb/a/myfeeds
ga https://www.google.com/analytics/home/

By the way, I haven’t typed all these urls. In your firefox user dir (~/.firefox or ~/.mozilla), there should be a places.sqlite file.

Some sqlite love actually helps

sqlite> SELECT k.keyword, p.url FROM moz_bookmarks b JOIN moz_places p ON p.id = b.fk JOIN moz_keywords k ON k.id = b.keyword_id WHERE b.keyword_id IS NOT NULL;

At the MySQL Conference and Expo, right after my participation in scaling up or scaling out keynote panel, I talked to Sun's Multimedia team about Sun and MySQL in our environment.

Recently, I found the interview on Sun's Multimedia page. The video of my discussion is embedded below:

I just returned from a meeting with the great Sun people in Prague. The main topic of our discussions was the database support in NetBeans and their MySQL support in general. To have the best possible database integration in all of Sun’s products is one thing that is high on my personal agenda.

As the current resources working on the NetBeans database integration are limited we looked into utilizing what we have done in MySQL Workbench to get a more powerful database interface into the hands of NetBeans users. But there are a few things that make this approach quite difficult. First, the two tools are using different development languages. Just to name a few. NetBeans is written in Java while MySQL Workbench is written in C/C+. MySQL Workbench does not include any query functionality yet and we are still working on the multi-platform support.

Still, we had good discussions and are currently working out a plan to overcome all these obstacles and help NetBeans users have an improved database experience.

While in Prague, Kaj Arnö and I gave talks at the Prague University. While Kaj was covering all MySQL related topics I was focusing on MySQL Workbench of course. People there asked a lot of questions and the feedback we got was pretty good.

We are currently working to get the next bug-fix release of WB 5.0 built. The release should happen soon.

In parallel we are making progress with WB 5.1, namely the Linux port. Maksym has GRT module loading working and is continuing to write makefiles and adjustments to the code to making everything compile. Stay tuned.

Found an article comparing MySQL and SQL Server...

http://articles.techrepublic.com.com/5100-10878_11-1054385.html

Bug fixes:
* In some situations where SSH connections failed (including if MONyog was not able to connect to MySQL, wrong SSH authentication details for tunnelling etc.), sockets were not being closed and it could result in that many sockets were kept in CLOSE_WAIT state. (note: connections to MySQL not using SSH were not affected!)

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Todd Hoff, who apparently learned a hell of a lot during a short stint at Yahoo followed by some startups has an extremely well-written and edutaining article about how scaling to a million or more users requires jettisoning more or less everything we know and love about relational modeling.

Even though he uses bigtable (Google’s distributed hash storage system) as his example, in reality this approach works well with relational datastores like MySQL and Oracle too, you just have to think about your data differently and use the databases differently. So I’m including this article in the MySQL and Oracle categories because I think it would be of interest.

Here’s a taste of how it reads:

How do you structure your database using a distributed hash table like BigTable? The answer isn’t what you might expect. If you were thinking of translating relational models directly to BigTable then think again. The best way to implement joins with BigTable is: don’t. You–pause for dramatic effect–duplicate data instead of normalize it. *shudder*

Flickr anticipated this design in their architecture when they chose to duplicate comments in both the commentor and the commentee user shards rather than create a separate comment relation. I don’t know how that decision was made, but it must have gone against every fiber in their relational bones…

But Flickr?s reasoning was genius. To scale you need to partition. User data must spread across the shards. So where do comments belong in a scalable architecture?

The answer is, in case you aren’t following yet, you store it everywhere you might need it and worry about keeping your multiple copies in sync later, if at all.

BigTable data ethics are more Mardi Gras than dinner with the in-laws. Data just wants to have fun. BigTable won?t stop you from hurting yourself. And to get the best results you may have to engage in some conventionally risky behaviors. But if those are the glass bead necklaces you have to give for a peak at scalability, why not take a walk on the wild side?

So anyway, this is awesome stuff and thanks Todd. For your reading and learning enjoyment: Todd Hoff’s “How I learned to stop worrying and use lots of disk space to scale”.

Referred to by Launchpad Bug #119271 and MySQL Bug#28850, MySQL installations get bitten after an upgrade, if they were acting as a replication slave. However, the actually root cause is not an upgrade.

If you simply set up say Ubuntu Feisty, you'll encounter the same problem. If you set up as a slave, the server uses a relay log. In the affected versions, its put under /var/run. That's a serious snafu, because /var/run is generally on tmpfs and a) very small, and b) gets wiped on a restart. Only runtime foo like .pid files should be under /var/run (as per LSB, Linux Standards Base).
Anyway, the "gets wiped on restart" is where new installations get bitten, although the error is of course the same as on an upgrade where the path changes from /var/lib/mysql: the server simply can't find the relay logs it thought it had.
If you have a perfectly running replication slave, shut it down and restart the machine, you'll have broken replication anyway. One of my students encountered this in the MySQL Replication Workshop last week, just after another student had brought up an error they'd spotted in the logs of their production system.

The issue has only recently been resolved in Ubuntu (5.0.51a), upstream (MySQL has it pushed for 5.0.54), and the problem appears to be fairly prolific among the various distros. CentOS also has something on this. From the comments in all of these, it appears that the basic problem is, although simple, not actually that well understood. The MySQL relay logs should NOT be deleted (or vanish through other means) on a restart. That's all. Simple.

So how did it happen? I first thought the prob was restricted to Debian (from which Ubuntu is derived), but the Debian/Ubuntu my.cnf files have not changed and actually don't contain a relay-log entry. The problem originated upstream at MySQL where the default path for the PID file was changed from the datadir to /var/run/mysqld. That in itself was a correct move (again, for LSB compliance), but elsewhere in the code the base path for the relay log got derived from the path of the PID file, and that's where the trouble originates.
The change of the PID default path was not really noticed anywhere, as most distros already put it in /var/run/mysqld through their default my.cnf file. But since none of them explicitly specifies a relay-log path, it gets put where the compiled-in defaults tell it to go. Kaboom.

So, coding error. The path should not have been derived from the PID base, but programmers are human ;-)
I do wonder why MySQL's QA didn't spot this, they're a fairly thorough bunch.

Another thing to note is that if you have an affected version (any distro, or direct from MySQL), the quick fix is to just put some extra lines in your my.cnf: # We're fixing up the paths for the relay log infrastructure (repl.slaves) # 2008-05-21 by a r j e n (at) o p e n q u e r y (dot) c o m (dot) a u # NOTE 1: adapt the filenames to whatever they currently are on disk! # The filenames may depend on your hostname or distro specifics. # NOTE 2: If you built your MySQL server from source, # or if you installed from the binary tarball, # your data path will be different from /var/lib/mysql, # such as /usr/local/mysql/data. Check and adapt. relay-log = /var/lib/mysql/relay-bin # let's do these too, just in case relay-log-index = /var/lib/mysql/relay-bin.index relay-log-info-file = /var/lib/mysql/relay-bin.infoThen your replication slave universe should return to operate within normal parameters. Essentially, it un-breaks replication ;-)

Actually I think that relay-log-info-file probably used the datadir or the binary log base as its default path already, and that would be why the server would think that the relay logs are somewhere when they're not: the relay-log.info file would contain the current active log filename and position. With the log files disappearing.... you understand. But just in case, make it all explicit and thus prevent problems of this nature.

I have been using Maatkit in a different way since I joined Percona as a consultant. When I’m working on a system now, it’s a new, unfamiliar system — not one where I have already installed my favorite programs. And that means I want to grab my favorite productivity tools fast.

I intentionally wrote the Maatkit tools so they don’t need to be “installed.” You just run them, that’s all. But I never made them easy to download.

I fixed that. Now, at the command line, you can just run this:

wget http://www.maatkit.org/get/mk-table-sync

Now it’s ready to run. Behind the scenes are some Apache mod_rewrite rules, a Perl script or two, and Subversion. When you do this, you’re getting the latest code from Subversion’s trunk.[1][2] (I like to run on the bleeding edge. Releases are for people who want to install stuff.)

Because there’s some Perl magic behind it, I made it even easier — it does pattern-matching on partial names and Does The Right Thing:

baron@kanga:~$ wget http://www.maatkit.org/get/sync --21:38:50-- http://www.maatkit.org/get/sync => `sync' Resolving www.maatkit.org... 64.130.10.15 Connecting to www.maatkit.org|64.130.10.15|:80... connected. HTTP request sent, awaiting response... 302 Moved Location: http://www.maatkit.org/get/mk-table-sync [following] --21:38:50-- http://www.maatkit.org/get/mk-table-sync => `mk-table-sync' Connecting to www.maatkit.org|64.130.10.15|:80... connected. HTTP request sent, awaiting response... 200 OK Length: unspecified [application/x-perl] [ <=> ] 163,259 136.51K/s 21:38:51 (136.13 KB/s) - `mk-table-sync' saved [163259]

The redirection is there because otherwise wget will save the file under the name ’sync’ instead of ‘mk-table-sync’.

And if you’ve forgotten which tools exist, you can just click on over to http://www.maatkit.org/get/ and see.

A quick poll: instead of getting the latest trunk, should this give you the code from the last release? I can do that, if you want.

[1] OK, it’s only refreshed every hour. So you’re getting code that’s up to an hour old.

[2] update: now /get/foo gets the latest release, and /trunk/foo gets the latest trunk code.

Apache, mod rewrite, Subversion

A while ago I wrote about native MySQL connectivy for OpenOffice.org which Georg Richter had spent a lot of time on, but which then got stuck in some licensing foo. That blog entry generated a number of responses from former MySQL colleagues, noting that something was in the works. A bit later Georg himself commented:Arjen,

sorry, but I didn't find the time to blog about - I had to write some code :-)

The license of the code will be LGPL 3.0 (like OpenOffice) - it still uses libmysql. The code will be available via cvs.openoffice.org pretty soon.

If you're interested in testing some binaries, check out the new preview version at http://forge.mysql.com/wiki/Connector_OpenOffice

/GeorgSo that's cool, and everybody please do check it out!

It's still not clear to me how this actually works in terms of licensing, if it still uses libmysql. Ohwell, I guess it doesn't really matter to users of OOo (LGPL+GPL is fine for us) and the most important thing is that it'll be in the main OOo codebase soon. How Sun makes it jive for StarOffice is entirely their problem ;-)

One of the comments to last week's scribble on joins referred to the POSIX join command. What does this mean? There is a commandline tool called 'join' available on most Unix-ish boxes, including Linux and OS X. From my own Mac ("man 1 join"):NAME
join -- relational database operator

SYNOPSIS
join [-a file_number | -v file_number] [-e string] [-o list] [-t char]
[-1 field] [-2 field] file1 file2The commenter also mentions this article which explores the command some more with examples.

It's not MySQL, but it looks very useful. Not all data should be in a db, sometimes a textfile is perfectly sensible, and sometimes you just need an operator like this. And it already exists. Coolness.

Sam Varghese of iTWire invited me for a broad interview when I was teaching in Melbourne last week, it's now published (MySQL: the Australian connection).

I just got around to reading Stephen O’Grady’s post on the relative openness of open source vendors and realized I had failed to be as clear as I could have been in my original post on the subject.

Responding to my note about Milking the GNU’s suggestion that a new independent organization could be formed to judge vendors on their level of openness, Stephen wrote:

“Those in the industry that might care have, I would argue, already formed their opinions on whether or not a project such as MySQL?s is or is not open source. And those outside the industry, well, I don?t expect they?d care. At all. Most of the enterprises I speak with are still struggling with the basics of what open source is and what it means.”

He is of course right that most enterprise adopters would not care about an openness index, and in fact such a thing could actually cause more harm than good by confusing potential adopters. However enterprises were not the potential audience that I (or I believe MTG) envisioned for what MTG called the Equitable Open Source label.

The potential audience I was referring to in the post was the “community edition” user base, which is a distinct* audience from paying customers and one that needs to be addressed differently by vendors. It is this audience that Stephen was referring to as “those in the industry that might care”.

As to whether they have already made up their minds about the openness of specific vendors, things can and do change and that is why vendors may find that a self-imposed social contract or externally-imposed openness index becomes a ongoing factor in community (as opposed to customer) relations.

*They do, of course potentially overlap - one can move from being a community user to a paying customer and back again, while a paying customer can also simultaneously be a community user, depending on the implementation - however the needs of that customer/user differ depending on the nature of their relationship with the vendor with respect to that implementation.

Innotop is a very useful tool to monitor innodb information in real time. This tool is written by Baron Schwartz who is also an author of “High Performance MySQL, Second edition” book. [Side note: I highly recommend getting this book when it comes out (in June, 08?). Other authors include: Peter Zaitsev, Jeremy [...]