Sheeri Dot Org

In http://www.mysqlperformanceblog.com/2008/07/01/should-we-proclaim-mysql-community-edition-dead/, Peter Zaitsev wonders if MySQL’s community edition is dead.

The title of Peter’s inquiry is somewhat misleading, as the database itself works fine. He clarifies a bit with, “there suppose to be 2 yearly binary releases (which are overdue) and 4 predictable yearly source releases, which we have not seen either.” I thought it was clear that “2 per year” doesn’t mean “one every six months”. It’s been eight months, sure. And I don’t actually believe that MySQL is going to have one source release per month until November, to make up for the lack of source releases. However, it’s certainly possible, if not probable.

The fact remains, however, that if you’re just looking for stable, recent, binary MySQL Community release, you might not find it. MySQL offers two out of three — stable and binary Community releases. Not recent, but I think it’s okay to charge for the most up-to-date version. In my experience only about half of the production environments out there have switched to 5.0, and many are running 4.1 and 4.0 still.

At the low end, a license costs just under USD$600. The requirement to buy a license to get the most recent version is a mere inconvenience, not a business-stopper. It’s not like MySQL is forcing everyone to run on version 3.23 unless they pay $10,000 per license. Charging a modest amount for the most up-to-date version is not a bad thing.

It would be nice to have been aware of that ahead of time, but MySQL as a company has not been so great at organizing and having all its ducks in a row. In fact this is where I hope Sun can really help MySQL out, as it has a reputation (a deserved one, in my experience) of being more highly organized.

Have you heard of Hanlon’s razor? “Never attribute to malice that which can be adequately explained by stupidity.”

(more…)

I was contacted by the folks at MONyog and asked if I would review MONyog. Since using MONyog is something I have been wanting to do for a while, I jumped at the chance. Of course, “jumped” is relative; Rohit asked me at the MySQL User Conference back in April, and here it is two months later, in June. My apologies to folks for being slow.

This review is an overall review of MONyog as well as specifically reviewing the newest features released in the recent beta (Version 2.5 Beta 2). Feature requests are easily delineated with (feature request). This review is quite long, feel free to bookmark it and read it at your leisure. If you have comments please add them, even if it takes a while for you to read this entire article.

While the webyog website gives some information about what MONyog can do, it is a bit vague about what MONyog is, although there is a link to a PDF whitepaper on What is MONyog? which does answer much of these questions.

The screenshots available from the website are accurate, so I will not reproduce them here. I will note that I have not shared this feedback with the webyog team yet, so I may be upset that a feature is lacking, and the feature may be implemented but I missed it. I will post a follow-up in that case, even though they will likely comment here too.

My reference points — I have used other monitoring and graphing tools such as Nagios, Cacti, and Intermapper as well as MySQL’s Enterprise Monitor.

Overall

As an overall review — MONyog is the best out-of-the-box GUI monitoring tool for MySQL that I have seen. It “just works.” As promised, getting up and running quickly is easy, and having a centralized location for monitoring is very useful. The graphs are beautiful and the statistics that are graphed are useful time-savers.
(more…)

Usenix 2008 – Automated System Management, by Æleen Frisch of Exponential Consulting (and numerous books)

What is automation?

generic [perl|shell] scripts with cron,at

Problem: overlap of effort

So folks developed automation systems. General automation tools are around:

cfengine, puppet, cfg2

These are general — files, directories, etc. Don’t need to use chmod and chown and underlying commands.

However, they don’t really survive reboots well. For that, we tend to use tools more towards jumpstart, kickstart.

Monitoring with Nagios, related tools are rrd-tools such as cacti, cricket, munin, “or any of 8,000 others.” Automating ideas like iostat.

Nessus is a security testing tool.

homegrown, general, performance related, also automated backups — bakula, amanda, legato.

What do you want automated?

“Coffee machines”.

A lot of unsolved problems are human interaction.

Other problems solved — using remote power management.

Inventory management is another issue. HP OpenView is one, but Frisch says folks are not happy with it. You can pay for high-end monitoring systems.

A question came up about an inventory of users on systems. LDAP or NIS or Active Directory is the traditional solution where there are no local accounts. There’s authentication and then authorization, and the automated tools usually have authentication information but not authorization information. (You can handle it, but making groups on these tools is usually painful.) Authorization is usually handled either locally or as “if you’re authenticated you’re authorized”.

We talked about how to power down 500 machines when the air conditioning goes out, or when the power is going down. Combinations of temperature probes, “wake-on-lan”, remote power on and off were discussed.

What do people use to automate installs and configuration on Windows? For installation, the Windows native tools are great. It was noted that efs works better on Windows.

Anyone using Splunk with Windows? One answer — it works OK, there are some daemon tools to convert Windows Event Log to syslog.

Splunk came up as a topic of discussion, how it’s a great log management software and solves a problem we’ve had for decades — how to deal with logs. Frisch says, “Splunk is the most promising thing out there.”

Record keeping of time was brought up, as well as time management. Basically what we do at Pythian, so I explained how we do things. Other folks brought up ticketing systems as well. Jira and RT (Request Tracker) and OTRS (Open Ticket Request System) were brought up as well.

Also for change management, some folks use ClearCase (not open source), and others use rancid, others use Trac or bugzilla + change management system like subversion. Jira was recommended as a product that does both (with an add-on).

Use DHCP to help automate IP assigning. rsync is your friend too.

(it occurs to me that a dishwasher is an interesting problem; why do we have a dishwasher instead of just having a sink/dishwasher hybrid? Similarly, a hamper that does laundry for you when it’s full.)

From 7:30 – 8:30 pm tonight, Wed. June 25th, in the Berkeley room of the Sheraton Boston, I will be hosting a Birds of a Feather conversation entitled “Pros and Cons of Managed Services”. This will go beyond MySQL and even beyond remote database management, and just deal with the overall pros and cons.

Come, share your good and bad experiences, and discuss why managed services may or may not be appropriate for your situation. I will try to take notes at the BoF.

(Note: I have no idea if they check badges for Birds of a Feather sessions or not)

I have *never* had this happen to me.

Maybe it’s because it’s MySQL 6.0.4, maybe it’s because it’s on Windows, or perhaps I am just up working too late.

I have seen mojibake before, but usually it is unintelligible. But this? After I post this I am backing away slowly from my computer.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 6.0.4-alpha-community MySQL Community Server (GPL)

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

mysql> use test;
Database changed
mysql> create table bits (val bit);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into bits (val) VALUES (1),(0),(1),(1),(0);
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from bits;
+------+
| val  |
+------+
| ☺    |
|      |
| ☺    |
| ☺    |
|      |
+------+
5 rows in set (0.00 sec)

mysql>

Has your data ever smiled at you?

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 6.0.4-alpha-community MySQL Community Server (GPL)

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

mysql> use test;
Database changed
mysql> create table bits (val bit);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into bits (val) VALUES (1),(0),(1),(1),(0);
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from bits;
+------+
| val  |
+------+
| ☺    |
|      |
| ☺    |
| ☺    |
|      |
+------+
5 rows in set (0.00 sec)

mysql>

I was asked today about the ANSI_QUOTES SQL mode.

According to http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html, ANSI_QUOTES mode changes the functionality of double quotes (”) to be like the backtick (`). Normally the functionality of double quotes is more like that of single quotes (’).

You might use this when you have a table with spaces or other special characters you would like to escape, without having to use the backtick key. This is also ANSI standard SQL behavior (one of the more annoying things about Oracle is that I keep forgetting I can’t use “, only ‘).

Here is an example in the MySQL default mode — allowing ” to be more like ‘ :

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select @@SQL_MODE;
+------------+
| @@SQL_MODE |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> create table `table with space` (num int);
Query OK, 0 rows affected (0.03 sec)

mysql> select * from `table with space`;
Empty set (0.00 sec)

mysql> select * from "table with space";
ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"table with space"' at line 1
mysql> select * from 'table with space';
ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''table with space'' at line 1

And here we chnage the SQL mode, and show that ” acts like ` in SQL_MODE=ANSI_QUOTES:

mysql> SET @@session.sql_mode=ANSI_QUOTES;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@SQL_MODE;
+-------------+
| @@SQL_MODE  |
+-------------+
| ANSI_QUOTES |
+-------------+
1 row in set (0.00 sec)

mysql> select * from `table with space`;
Empty set (0.00 sec)

mysql> select * from "table with space";
Empty set (0.00 sec)

mysql> select * from 'table with space';
ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''table with space'' at line 1

mysql>

If you’re wondering why all your queries are giving strange results such as what we saw above, or even ERROR 1054: Unknown column 'col_name' in 'field list' if what you are quoting is a string that MySQL is interpreting as a column name, check your SQL mode.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select @@SQL_MODE;
+------------+
| @@SQL_MODE |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> create table `table with space` (num int);
Query OK, 0 rows affected (0.03 sec)

mysql> select * from `table with space`;
Empty set (0.00 sec)

mysql> select * from "table with space";
ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"table with space"' at line 1
mysql> select * from 'table with space';
ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''table with space'' at line 1

mysql> SET @@session.sql_mode=ANSI_QUOTES;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@SQL_MODE;
+-------------+
| @@SQL_MODE  |
+-------------+
| ANSI_QUOTES |
+-------------+
1 row in set (0.00 sec)

mysql> select * from `table with space`;
Empty set (0.00 sec)

mysql> select * from "table with space";
Empty set (0.00 sec)

mysql> select * from 'table with space';
ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''table with space'' at line 1

mysql>

If you are attending Usenix 2008 at the Sheraton Hotel in downtown Boston, you can meet me and ask your burning MySQL questions at my “The Guru is In” session. On Friday, June 27th, 2008 from 2 – 3:30 pm in Constitution B, I will be helping folks out by optimizing queries and schemas, teaching general principles of working with MySQL databases, and answering (to the best of my ability) any other question they may throw at me.

The event details are at:
http://www.usenix.org/events/usenix08/tech/#fri

Hope to see you there!

The Boston MySQL User Group was lucky enough to get Keith Murphy to speak at the June User Group meeting, about backups.

Direct play the video at:
http://technocation.org/node/559/play

Direct download the video (351 MB) at:
http://technocation.org/node/559/download

Links referred to in the presentation:

MyLVMBackup by Lenz Grimmer
http://lenz.homelinux.org/mylvmbackup/

InnoDB Hot Backup:
Prices are at:
http://www.innodb.com/hot-backup/order/
and at the time of this writing are:
1-Year License € 390 USD$ 605 per server
Perpetual License € 990 USD$ 1540 per server

Zmanda
prices are at: http://network.zmanda.com/shop/home.php?cat=1,3
Current prices are between the range of $100-$450, and a license is needed for each server and each client. So if you have 1 server and 3 clients, you need 4 licenses.

Online Backup
This is interesting reading about online backup, an article about how the online backup works at: http://blogs.mysql.com/peterg/2008/05/19/mysql-60-feature-2-online-backup/

This page (at the bottom, under “Replication”) states that the desired behavior is that the binary log does not log the restore or the backup. However, it does state that this is not implemented yet, nor even decided upon.

There’s also a great page on Online Backup at the MySQL Forge: http://forge.mysql.com/wiki/OnlineBackup

And if you’re short on time, the manual has a short page at http://www.nongnu.org/rdiff-backup/
duplicity: http://duplicity.nongnu.org/

It was mentioned that some people use a code repository to backup their database: http://krow.livejournal.com/593424.html

So, we have all heard that Billy Joel played a concert at Oracle’s OpenWorld in 2007.

What follows is an actual IRC conversation among Don Seiler, Dave Edwards, and myself:

(4:02:46 PM) don: ha @ Billy Joel at OOW
(4:03:38 PM) dave: “We didn’t fire the startup…”
(4:07:53 PM) don: “we didn’t start the backup”?
(4:12:53 PM) dave: “Don’t go changin’ . . . your slave and master”
(4:20:19 PM) ***sheeri shoots Dave
(4:20:49 PM) sheeri: “I don’t want clever replication, we never could have come this far”
(4:24:05 PM) sheeri: “And the server sounds like an aero-plane, and replication chugs along as it must…and the inserts go on, replication corrupts, and I say “Man, now I’m workin’ all night!”

(4:24:29 PM) dave: “I said ‘ls -u’ . . . that’s for access”
[”I said I love you . . . that’s forever”]

(4:24:30 PM) don: UP-TIME GIRL
(4:34:09 PM) dave: “Say it’s not wrong, execution plan!”
(4:43:39 PM) sheeri: Where’s my execution plan, oh man?
[Sing us a song of a piano man]
(4:45:52 PM) sheeri: Go ahead with your schema, leave me alone!

Comment here with your own database-themed parody of a Billy Joel song. Perhaps if we get enough MySQL-themed entries, we can get him to come to the MySQL Conference in April.

That and maybe thousands of dollars………..

According to the manual, FLUSH LOGS is supposed to:

Closes and reopens all log files. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file. On Unix, this is the same thing as sending a SIGHUP signal to the mysqld server (except on some Mac OS X 10.3 versions where mysqld ignores SIGHUP and SIGQUIT).

If the server is writing error output to a named file (for example, if it was started with the –log-error option), FLUSH LOGS causes it to rename the current error log file with a suffix of -old and create a new empty log file. No renaming occurs if the server is not writing to a named file (for example, if it is writing errors to the console).

There is a bug, however. In the case when the error log writes to a non-default path, FLUSH LOGS actually does not work as specified for the error log. I have not seen issues with binary logs in non-default paths, but we just ran into this issue on a client site and it threw us for a big loop, and we had to wait for an appropriate downtime window so we could actually restart the server so the logfile was rotated and written to appropriately.

The bug description is here: http://bugs.mysql.com/bug.php?id=19642
and is actually a duplicate of: http://bugs.mysql.com/bug.php?id=6061

This is a pretty serious bug in my opinion, and it’s only fixed in MySQL 5.1 — there are no patches or fixes for 4.1 or 5.0.