I just unpacked and started setting up the Kysoh Tux toy that I got at OScon. The lucky folks with a press badge were given a free one. I had to pay for mine.

The good:

Physically, it feels to be well and solidly made. It didn't feel like they were being cheap with the fabrication. It weighs just over half a kilogram.

Physical setup is straightforward. Unstrap the parts from the box. Click in the US power module into the internationalized power cord. Plug in the penguin unit, to charge it up. It can operate both plugged in, and on internal batteries, which it charges itself. Plug the fish unit into your computer with the included USB cord. It uses a mini-usb plug.

The firmware running in the "Penguin" main unit, and the firmware running in the "Fish" USB/wireless transmitter unit, and the interface library and control







  [Read more...]

If you are running a Linux laptop that uses NetworkManager, you can get a list of every wireless network that NetworkManager knows about by doing

gconftool-2 --all-dirs /system/networking/wireless/networks

If you want to delete one, do

gconftool-2 --recursive-unset /system/networking/wireless/networks/BADNETWORKNAME

I find this useful when I'm in a venue, such as a tech conference, where there is both a free conference wireless network, and a paid wireless network run by the convention center. If you ever accidentally associate with the paid network, then whenever the signal strength of the paid network is higher, NetworkManager will prefer it to the free one. The fix is to delete the paid network data from the gconf database.

Currently there isn’t a good method to control sending individual queries to the slow query log. Typically this isn’t an issue. It becomes an issue when using very short query times and importing sql files. The slow query log doesn’t have a limit on the length of queries it will write to a log. If you’re importing a sql file with inserts that get sent to the log mysql will dump the entire insert query. This can cause the slow query log to grow to gigs in size in a very short time.

I’ve patched mysql 5.0.41 to add a session variable called sql_log_slow (think sql_log_bin) that when set to 0 will prevent queries from that session from being sent to the slow query log. The patch also updates mysqlbinlog to set that variable in it’s output. One issue with this patch is that the executable comment version in mysqldump is

  [Read more...]

A few months ago, I had a meeting with a small local startup. Their tech and their pitch is pretty neat. They had an angel kick them a megabuck of seed to get started. It's the classic geek startup: two main guys, one wearing the CEO/sales/biz/money hat, and the other wearing the CTO/it/tech/arch/geek hat. And they've hired a couple of coders.

But they've also rented some office space: two cubes, a meeting room, a front desk, and a lockable office for the locking file cabinet. Now, it was cheap office space, but still, why?

It's a waste of the angel's money, is increasing their burn. For what it's costing them, they could hire another coder. Dev speed is their current bottleneck, and going from 2 to 3 causes only minimal invocation of Brook's Law, especially if it's early. If they start to grow, they'll have to hire #3 and #4 soon enough anyway, at which point they've



  [Read more...]

In a decade, on-demand virtualized utility computing will be an invisible utility, part of the vital infrastructure of the technological economy.

People will mostly have forgotten what an enormous pain in the ass provisioning computation was today. Today, we don't truly feel that pain, because it seems "normal", everyone has to suffer it together.

The situation right now is, if you have a delivery van, you have to make your own gasoline. And you have to hire and pay for your own mechanics. Seems stupid, doesn't it? It's amazing that there are any delivery vans at all …

Think of the internet itself, what it did to telecoms.

Twenty-five years ago, if you wanted a high speed data connection to a computer in San Francisco, it was a pain. You'd have to come up with a pile of money, and wait a couple of months, at best. Hardware would be dedicated and provisioned, and

  [Read more...]

I’ve talked to several people that have questions about how alter table works under the hood. They want to know how it handles locking tables why they can sometimes use a table during alter table and other times they can’t. Also why it’s so slow

First let’s look at the basic process alter table typically goes through.

If a transaction is open on this thread, commit it.

Acquire a read lock for the table.

Make a temporary table with new structure

Copy the old table to the temporary table row by row changing the structure of the rows on the fly.

Rename the original table out of the way

Rename the temporary table to the original table name.

Drop the original table.

  [Read more...]

While I'm being insane...

CREATE TABLE housing ...
 ENGINE=FEED
 CONNECTION="http://seattle.craigslist.org/hhh/index.rss";

SELECT title FROM housing WHERE ...;


I think I can get it working with a week or less work.

I thought of doing this at the Yahoo Pipes talk at the Vancouver PHP conference a few months ago, but dimissed it as a joke. I thought of it again at Pasha Sadri of Yahoo's final keynote yesterday. This time, I know that I can do it, but I don't know if I can dismiss it as a joke.

Is this a great idea, or a sick one?

The MySQL Con has done something amazingly intellegent. Taped down to the floors in all the session rooms and meetings rooms, there are power strips. No matter where you sit, there is a powerstrip within a couple of meters. So instead of spending time looking for power outlets and having clumps of people gathered around the few outlets around the walls, you can sit where you want, and take notes and do research while listening and speaking.

This is something that all other tech conferences would do well to emulate.

I’ve been thinking about the query cache since last years user conference. One of the features of the query cache is that it’s completely transparent to client. It achieves this by clearing cache entries for tables as soon as the tables are updated. This makes the cache inefficient for tables that are constantly updating.

While thinking about this also thought about slaves and non critical reads. In a replication setup reads that are sent to slaves expect to get data back that might not be the most current. In this situation it doesn’t make sense to expire the cache for every single update because queries running against the slave already know that they are going to get back slightly old data. I thought, “Why not add a time to live to cache entries instead of clearing them for every update?” So, I did.

I added an option to select called

  [Read more...]

Do you want to say hi? Pick up a free bottle opener (I haven’t seen them yet but Jeremy says they are high quality)? Sway my opinion on DRBD? Most of my time at the user conference this year will be spent at the Proven Scaling corner of the Solid booth. I also have the privilege of participating in Meet the Experts “Replication in the Real World” event at the Solid booth during lunch on Wednesday.

I’ve noticed a few blog posts recently about people saying how great DRBD is as a fail over mechanism for MySQL. My experience with DRBD has been the complete opposite. It offers almost no benefit over binary log replication for typical MySQL setups and prevents a few things that are possible with binary log replication.

Kaj Arnö has written an excellent blog post on the basics of DRBD. DRBD has one great feature that binary log replication doesn’t have. It can ensure that a write is synced to disk on two different hosts before allowing the application to continue. This is great for data redundancy but it introduces potential for instability in the setup. In a good fail over scenario a problem on the backup master should never cause an issue on the primary master. With DRBD the second master lagging behind because

  [Read more...]

It?s simply too inaccurate to be useful. I?ve switched to using google with firefox bookmark keywords instead. Here is how to set it up. In firefox click on Organize Bookmarks under the Bookmarks menu. Click the New Bookmark button and fill in the following values.

• Name: MySQL Manual
• Location: http://www.google.com/search?hl=en&q=site:dev.mysql.com%20%s
• Keyword: mysql
• Description: Keyword bookmark for using Google to search the MySQL manual.

Click ok and close the bookmark menu. Now instead of typing mysql.com/foo or using the search box on mysql.com you can simply type mysql foo into the address bar. Firefox will use your keyword to load the location in the bookmark replacing %s with foo resulting in results from the mysql manual. This can be done with any search engine. Simply do a test search and replace the search term with %s for the bookmark url.

One small fact about backup table and restore table that isn’t listed in the manual is that these commands lose the auto increment value if rows at the head of the table are deleted. For example if you have auto increment values of 1,2,3,4 in a table the auto increment value is 5. If you delete row 4 the next auto increment will still be 5. If you backup/restore the table the auto increment will be reset to 4. The auto increment value in myisam is stored in the MYI file. Since this file isn’t backed up myisam restores the auto increment value from the highest existing value in the table. This value may or may not be the actual value of auto increment. As the manual says these commands should not be used. Instead use mysqlhotcopy.

Innochecksum is a small undocumented (at least in the manual) utility that verifies the checksum of ibdata file pages. I stumbled across it the other day while wandering through the source tree. It has a small bug that prevents it from being able to check files larger than 4G. This patch fixes it in linux. Someone that knows more about large file support in different OSs please comment on the patch.

It lives in the extras folder in the MySQL source. Here is the credits comment:

/*
InnoDB offline file checksum utility. 85% of the code in this file
was taken wholesale fron the InnoDB codebase.

The final 15% was originally written by Mark Smith of Danga
Interactive, Inc.

Published with a permission.
*/

I think as a community we need to make sure that tools like this are kept in the forge and actively developed. More on this when I have had some sleep.

When Jeremy left yahoo a lot of people were left wondering “who would be the next Jeremy?” some people thought it would be me. Since then I have been asked several times why I’m not going to take a turn in the ivory tower. Now that things are in place the secret can be let out. Jeremy’s startup Proven Scaling is not just Jeremy’s startup but our startup. We have decided to take our MySQL skills and apply them to the problems of several companies.

Eric Bergen
MySQL Geek / Owner
Proven Scaling L.L.C.
eric@provenscaling.com

At the first ever MySQL Camp Proven Scaling is holding a session/BOF on replication. We want to hear what you like and don’t like about replication. What better to get conversation going but FREE BEER!

There is one tiny problem. We don’t know how much to buy or what kind. If you’re going to the camp help us out by putting your name and your favorite brew (brand and type) on the MySQL Replibeertion page.

If you can remember back to mid ‘05 when MySQL 5.0 was being released there was something missing. I have been thinking about writing about MySQL 5.0 being released too soon but I don’t think that was the case. Was 5.1 released too late? Maybe. Was something wrong with the 5.0 -> 5.1 schedule and feature set? Oh yeah.

Looking back at the change logs for 4.1, 5.0, and 5.1 I noticed something that I hadn’t though about before. I knew there was a difference in the release dates for 5.0->5.1 from 4.1->5.0 but I didn’t realize how drastic the difference was until I drew it out (on paper, sorry) today. Here is a summary of the major milestones from 4.1 Alpha to today:

• Apr 03 - 4.1 Alpha
• Dec 03 - 5.0 Alpha
• Jul 04 - 4.1 Beta
• Aug 04 - 4.1 Gamma
• Oct 04 - 4.1 Release
• Mar 05 - 5.0 Beta (Where is 5.1

  [Read more...]

I’m not the first person to run into this and I certainly won’t be the last. This is also already covered in: Bug #19093 I just want to say again how annoying it is that the default for show status is session instead of global. It bit me again yesterday. I ran show status and was confused because most of Com_* is 0 yet uptime was a few days. Then it hit like a brick to the face that I was working on 5.0 instead of 4.1 and the default for show status is session. I know the default has changed and it still bites me. This is going to cause people hours of confusion the first time they do what I just did.

If you want the default changed please go to Bug #19093 and add a comment.

Similar to the useless use of cat award. The useless use of if award highlights code examples where people use the if function or ternary operator when the return of the expression does the exact same thing. I first noticed this with returning boolean values from php functions. To protect the innocent the winner of today’s award will remain anonymous.

<anonymous> to my knowledge, youll have to SUM(IF(your_field “”, 1, 0)) as total_non_empty

Ignoring that the whole query should be using where your_field != ” and group by the non if() way to write this is:

SUM(your_field ”)

These examples aren’t an award to a specific person since I’m digging them up from my memory. This pretty much applies to both C and PHP.

function foo()

{

  [Read more...]

Growing up in the world of linux uptime was always considered a good thing. On IRC every once in a while someone would post an uptime. Everyone else in that channel would then check their uptime and if it was greater or close they would post it in the channel. Most of these systems were home linux boxes used for compiling random programs or maybe hosting a webserver for experimenting. It was fun to see how long we could keep them running for. Since those days I have come to realize that high uptimes are a bad thing.
Keeping a server up for months or even years means that you aren’t maintaining it. It hasn’t been kept up to date with new kernels that have fixes for security holes. It doesn’t have new packages or new tools that can help it run more efficiently and have features that can make using it easier. It’s also not up to date with new servers that are

  [Read more...]

Expect a longer post in the near future on upgrade procedures. For now enjoy this quote from a gentoo user illustrating the worst way to upgrade.

linolium: is there a way to wipe every single table and start over from scratch?
linolium: ( the upgrade from mysql 4 to 5 didn’t go as smoothly as planned
me: did you read the upgrade notes?
linolium: no, I just hoped that portage would be kind enough to do those things for me

Every year the user conference gets better and better. I’m not sure if it’s the actual conference or just that I know so many more people than I did the year before so I’m that much more excited to see them all again. I was a speaker this year which is something like being a C celebrity. The attendees at the conference were split into a few very distinct groups. High order geeks, geeks with questions, and business people. The sessions seemed to be setup to appeal to one of these three groups. Of the sessions I attended my favorites were the row based replication session (which inspired Row based replication and application developers) and Timour Katchaounov’s session on new features of the 5.0 optimizer.

It was interesting walking

  [Read more...]

While attending Lars Thalmann’s session on Row Based Replication I began to think about how the new features will allow us to do creative new things with MySQL Replication. For example we can now issue an update query with a join and have one slave update only rows from table a and another slave only update rows from table b. This is very powerful but also very dangerous.

My experience has been that the average php developer can write average sql but doesn’t tend to think about the overall impact on a system when writing code. This is where system architects apply. A system architect can view an entire system and design it’s components to scale well, be fault tolerant and easily maintained. An application level developer will implement the system based on the design.

  [Read more...]

Have you ever tried to take a snapshot from a mysql server only to find an hour later that the box ran out of disk space when trying to create the tar ball? An easy solution is to use ssh to pipe the tar data directly to another server without it even touching the disk. For those unfamiliar with unix shells or pipes a pipe allows you to tie the output of one program to the input of another. This is most commonly used to manipulate that stream of data. For example if you want to find a specific file in a directory. In these examples cartman is server A (where the commands are ran from). Stan is server B.

cartman> ls | grep mysql-5
mysql-5.0

The | (pipe) tells the shell to direct the output of ls to the input of grep which looks for the pattern ‘mysql-5′. This same functionality can be used with the tar command.

  [Read more...]

While reading planetmysql.org I ran across this Tune a MySQL server in 5 minutes.. I think that entry is really missing a lot in terms of actual tuning for the real world. Also I thought max_user_connections is number of connections per hour. Since I only have 3 minutes I can’t look it up! You probably don’t want to set this with apache.

So here in three minutes are the four variables you really need and some generic guidlines for setting them. This assumes that MySQL is running on it’s own server and has one apache server connecting to it.

max_connections - set to the same (or a little bigger than) as MaxChildren from apache if you’re only using one mysql connection per request (this is most common)

key_buffer_size - Set to roughly half your available

  [Read more...]

I have had a blog entry waiting to be posted for a while about how bad the manual search has sucked since the versioning was implemented. I love the versioning but not being able to search it has been maddening. That post will never see light. The manual search has been fixed! We now have the best of both worlds. A versioned manual and search that works.
(type this into your browser address bar)

mysql.com/search term

For those of you that might not know this also works on php.net and freshmeat.net.

Other people have already drilled into the Oracle purchase of Sleepycat enough so I only have one question. Does any MySQL user really care that Oracle bought Sleepycat? Was anyone using BDB in production? Why?

An elevator picks people up and moves them to different floors based on where they have requested. If an elevator picked up exactly one person on the ground floor of a building, dropped them off, and went down to get another person it wouldn’t be very efficient. Modern elevators pick up people and move them between floors in an optimal pattern. Disk access is pretty much the same idea if you think of the disk head as an elevator and the disk as a building. It takes time to move an elevator just as it takes time to move a disk head. Modern operating systems can change the order of disk accesses into a more optimal pattern.

There are many different algorithms for scheduling I/O. I’m going to cover the ones I know from linux and how they can help you make the best use of your servers disks.

CFQ (Completely Fair Queue)

CFQ maintains

  [Read more...]

Previously I wrote about the manual search and how it had been fixed. Now I think it’s time for polish. Most of the basic keyword searches work but not all of them. The syntax keyword searches should always be spot on.

mysql.com/select syntax

Works great but the less common syntax searches fail

mysql.com/grant syntax

There really is no excuse for the syntax searches failing. These are the sections that people need the most on quick reference. I noticed tha recently the keyword searches that do work put me into the correct page and not just the search results with that page at the top. I love this! I just needs to work for every syntax search.

While I’m on the subject of manual search I would like to request a few features. Migration to

  [Read more...]

One of the things I always like about growing up with MySQL is that I had to learn joins. There was no sub select to fall back on if I couldn’t get a join to work correctly. With MySQL 4.1 the norm and 5.0 quickly on it’s heels I see more and more problems that people are solving with sub selects that could as easily be solved with joins if they knew how to write them. In my experience joins are faster and easier to optimize than sub selects but I seem to be in the minority now with that idea.

Don’t worry join syntax. I won’t forget you!