It’s been a while since I’ve posted anything new. In the meantime I’ve been pretty busy. Working on production systems often means that you are not running bleading edge. That’s fine but sometimes you need to look at doing the upgrade and to do that you have to do quite a few checks to see how well newer versions of the software you use will work.

I’ve been in that situation with MySQL. I have quite a few boxes most of which are 5.0 and have been working fine. MySQL-5.1 has been GA now for some time, and I have started to look at it as support for 5.0 is about to end.  I’ve already found quite a few 5.1 bugs, good enough to crash the server, but again that’s to be expected. Heavy loads and odd usage of the server means that MySQL can never be able to test all things.  Replication problems caused a few issues with the first versions of 5.1 but they all seem to have been ironed out now.  The other …

1. ChipHacker -- collaborative FAQ site for electronics hacking. Based on the same StackExchange software as RedMonk's FOSS FAQ for open source software.
2. Democracy Live -- BBC launch searchable coverage of parliamentary discussion, using speech-to-text. One aspect we're particularly proud of is that we've managed to deliver good results for speech-to-text in Welsh, which, we're told, is unique. I think of this as the start of a They Work For You for video coverage. I'd love to be able to scale this to local government coverage, which is disappearing as local newspapers turn into …

Today I’m proud to announce the first public release of our ActiveRecord database connection magic plugin: DbCharmer.

DB Charmer – ActiveRecord Connection Magic Plugin

DbCharmer is a simple yet powerful plugin for ActiveRecord that does a few things:

1. Allows you to easily manage AR models’ connections (switch_connection_to method)
2. Allows you to switch AR models’ default connections to a separate servers/databases
3. Allows you to easily choose where your query should go (on_* methods family)
4. Allows you to automatically send read queries to your slaves while masters would handle all the updates.
5. Adds multiple databases migrations to ActiveRecord

Installation

There are two options when …

I have for the first time seen a client implement MySQL Resource Limits. I got the following error tying to connect to the database.

$ mysql -udba -p
ERROR 1226 (42000): User 'dba' has exceeded the 'max_user_connections' resource (current value: 10)

I see from the documentation the ability to see the limits in the mysql.user table. I see this is included in the SHOW GRANTS output.

SHOW GRANTS for 'dba'@'%';
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for dba@%                                                                                                                         |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL …

Maatkit is a pretty useful set of utilities for MySQL. From their site:

You can use Maatkit to prove replication is working correctly, fix corrupted data, automate repetitive tasks, speed up your servers, and much, much more.

One of the first things you can do after installing the toolkit (which may already be installed if you are running CentOS or Debian) is to run the mk-audit utility. It will give you a nice summary of your server, as well as point out potential problems in your configuration.

Here's a list of all the utilities included in Maatkit:

• mk-archiver Archive rows from a MySQL table into another table or a file.
• mk-audit Analyze, summarize and report on MySQL config, schema and operation
• mk-checksum-filter Filter checksums from …

I’ve had plenty of comments on specific products to Monitoring MySQL Options before providing the completed list. Here are the results from my survey to give everybody a more complete list.

With IntelliJ now being available under an Open Source license, developers have another option to choose from when it comes to Java-based IDEs/Frameworks (Eclipse and NetBeans being the other two prominent ones). Choice is always good, and being an Open Source enthusiast, I of course welcome JetBrain's move!

However, as I'm not really a heavy GUI-based IDE user myself, I can't really comment on which one is the best. These kind of discussions tend to turn into a Holy War anyway... In the end it's likely that each of them gets the job done and you have to come to your own conclusions, based on your personal preference and requirements.

I personally would be interested in …

My recent poll What alert monitoring do you use? showed 25% of the 58 respondents to bravely state they had no MySQL monitoring. I see 1 in 3, ~33% in my consulting so this is consistent.

There is no excuse to not have some MySQL Monitoring on your production system. At the worse case, you should be logging important MySQL information for later analysis. I use my own Logging and Analyzing scripts on every client for an immediate assessment regardless of what’s available. I combine that with my modified statpack to give me immediate text based analysis, broken down by hour chunks for quick reference. These help me in troubleshooting, but they are …

I had trouble today on a client site using my MySQL power tools Maatkit and Statpack.

$ ~/scripts/statpack.py --files=mysql.status.091015.080001.txt,mysql.status.091015.090001.txt
Traceback (most recent call last):
  File "/home/rbradfor/scripts/statpack.py", line 563, in ?
    main()
  File "/home/rbradfor/scripts/statpack.py", line 527, in main
    locale.setlocale(locale.LC_NUMERIC, '')
  File "/usr/lib64/python2.4/locale.py", line 381, in setlocale
    return _setlocale(category, locale)
locale.Error: unsupported locale setting

$ cat /var/log/slow-query.log | ./mk-query-digest
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "e_US"
    are supported and installed on your system.
perl: warning: Falling back to the standard …

Q: What SQL is running on your MySQL database server now?
A: The bane of pain for MySQL DBA’s when there is no official MySQL instrumentation that is dynamic and fine grained sufficiently to solve this problem at the SQL interface.

While hybrid solutions exist, the lack of dynamic and real-time are the issues. There is however great work being done by Baron and others on Maatkit mk-query-digest and packet sniffing the MySQL TCP packets.

$ sudo tcpdump -i eth0 port 3306 -s 65535  -x -n -q -tttt | ./mk-query-digest --type tcpdump
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
# Caught SIGINT.
5444 packets captured
8254 packets received by filter
2809 packets dropped by kernel
# 2.1s user time, 40ms system time, 22.23M rss, 57.60M vsz
# …