Eric Bergen

During my life I have seen the level of fear in our society rise almost out of control. We have really lost the ability to assess risk. I’m going to attempt to realign the fears in our society back on a logical course. One of the many things that I think we should be afraid of that we currently aren’t is cellphones.

Sure people are worried about cellphones causing cancer. For the most part this doesn’t cause much stir because it’s out of sight out of mind (except the cancer in your brain). I’m not worried about my cellphone or the cancer it may be causing. I’m not worried about my cellphone at all. I’m worried about other people’s cellphones and when they use them.

I’ve been commuting in the bay area for a little over 2 years now. Part of that time my commute was about 5 miles of highway and the rest was about 20 miles of highway. It wasn’t a long commute but it was long enough for me to gather enough data to prove a point.

Most people cannot drive and talk on the phone at the same time. Every time I say this someone pipes up and says “I drive just fine while on my phone!”. Fine then, you aren’t in the ‘most’. Even though I bet at least half of the people that say they drive just fine really don’t they just think they do because they’re paying too much attention to their phone to realize they’re running people off the road.

These days parents are paranoid about their kids safety when riding bikes. Kids ride around these days wearing more padding and armor than our soldiers in WWII. Yet parents are perfectly ok with inattentive driving caused by talking on a cellphone. They happily yak away while driving putting their kids lives at risk. What’s worse parents, your kid hitting a fire hydrant at 4mph with no pads or you slamming into a semi at 80 because you couldn’t hang up the phone?

Nine out of ten times when I see someone swerving or looking like they are having difficulty driving and I pass them it’s because they’re on the phone. We have been paranoid about drunk driving and click-it or ticket for years. Let’s get some legislation out there for cell phones. It should work like this. The first time you get pulled over for inattentive driving from being on a cellphone the cop takes away your phone. You get it mailed back to you a few days later. The second time you get caught you get your phone taken away until you get a special device installed in your car. That device would require that you plug your phone into it in order to operate your car and your phone won’t be operable while in the device.

We managed to function as a society for years without the need for phones while driving. Most people that have phones can wait 20 minutes to return a call instead of answering it while driving. If you can’t then you should really take a look at your life. Your phone owns you.

[Update: 10/15/07 I forgot to add that when commuting to work a few months ago a guy in front of me ran into a construction road cone which flipped it up into the air causing me and the car behind me to swerve. The guy was swerving really bad so I passed him as soon as I could. What was he doing? Looking down at a cellphone texting people. Yes, texting in a construction zone. That guy should have his phone taken away]

MySQL Bug Tracker

I realize that MySQL is in a transitional phase making it’s development more transparent to the community so this entry isn’t really a rant but something that will hopefully ease the transition. The public MySQL bug tracker doesn’t seem to have any way to view the history of status changes to a bug. Things like changing a bug from open to inactive. I assume there will always be things that should be hidden from bugs like links to sites internal to MySQL AB but things like simple status changes should be visible to the public.

For example on this Bug #20358 Heikki posted a comment that he was moving the bug from open to unable to repeat. Did he actually make the change? I have no idea because I can’t access the status log. Was it ever changed from unable to repeat back to open or verified? Again I have no idea…

Another “feature” of the bug system that I find questionable is the bug bot. The script trolls the bug database looking for bugs that need an automatic state change. In my opinion it should never find anything. I’ve noticed that it changes state on bugs that haven’t been commented on for a month to an inactive status. I have one example of a Bug #26489 that I think is fairly critical being marked in an inactive state not because the bug wasn’t still occurring, but because we had no new useful information to post at the time. Just because no new information has been found doesn’t necessarily mean that a bug should be taken off of people’s radar. If humans want to mark them as inactive it’s fine but a script making that decision worries me.

I do appreciate the things MySQL has done recently to make the development process more accessible for those of us without MySQL AB on our name tag. #mysql-dev on freenode is well populated with very intelligent minds. It’s nice to have them readily accessible to bounce ideas off of.

Keep the momentum on this transition going and please don’t mark my blog post as inactive

Are you afraid of nagios because of the config files? Don’t worry you’re not alone. GroundWork to the resuce! GroundWork is a set of packages that takes the pain out of configuration for nagios. It’s a web based GUI that supports importing existing nagios configs, audo discovery of servers and much more. I hate for this to sound like an advertisement for GroundWork but it does solve most of my major complaints about nagios. The most important of which is that it’s a pain to setup and configure. GroundWork with basic checks can be setup on a friday afternoon.

I do have a few complaints. First the configuration EZ and configuration sub menus aren’t logical. Configuration doesn’t have commit. In order for any changes to take effect you have to go back to the Configuration EZ menu. This should really be changed to have configuration basic and advanced, both with commit capability. Easy is essentially worthless.

Another issue is the use of RRD for performance historical data. This paragraph deserves it’s own post and I’ll write it some day. For now I’ll just cover the main issue I have not with RRD itself but the way RRD is used most of the time. RRD is purely for round robin data. Historical performance data is not round robin. Hell it has “history” right in the name. This data should be kept as long as possible. It’s useful for planning capacity for future events based on previous events. Aggregating away history data into month or year graphs means it’s impossible to go back and see what traffic looked like on dec 25th 2005 or any other random date(s) because RRD has either purged it or aggregated it away into a yearly view. I know it’s possible to disable the aggregation on rrd but the preallocation of data files makes this impractical for long term data storage and constantly changing servers and services. Once again RRD is a fine tool for what’s it meant to be used for it’s just not meant for historical data.

Anyway if you like nagios but are afraid of the configs give GroundWork a try. Beware! If you install it on a server with and existing apache package GroundWork will overwrite /etc/init.d/httpd

When I first moved to down town Mountain View the person at the leasing office spent about 15 minutes trying to convince me that DSL/Cable availability wasn’t an issue because Google was going to provide free internet to all of Mountain View. I tried to explain that WiFi wasn’t good enough because I routinely ssh into servers across the country thus ping time is critical. My initial assumptions about Google WiFi have been proven right over the past two years. Unless you’re aligned with Google just right it doesn’t work. I’ve tried from in buildings, in parkings lots all around town my conclusion is that it’s a compete and total failure.

I’m willing to give them a few more months to get it working in downtown mountain view. What I can’t forgive is the fact that they can’t get it working from their own campus. I’m at Google HQ building 43 right now for the MySQL Meetup and I can see an SSID for Google WiFi from my laptop but I can’t connect to it long enough to even authenticate through wifi.google.com. To me a good way to promote your free wifi service is to have it readily available on your campus.

I’ll continue the hunt for a google wifi “hotspot” in down town mountain view but don’t hold your breath.

Recently I’ve been making more modifications to the MySQL source. Part of making modifications is testing them. For years I’ve been curious why there are both tests and mysql-test sub directories in the source tarballs. Arjen Lentz tells me that tests is old and that mysql-test is the current testing framework. This makes sense since the manual testing pages are all about mysql-test.

Are there plans to clean up the remnants of the old testing framework? What else is there in the tarball that’s outdated and needs to be removed?

“The following serial number must be entered to activate your software. We recommend writing the number inside your manual for future reference.”

Why didn’t you just put the sticker with the serial number on the manual?

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 set to 5.0.41 but it will error when imported on any 5.0.41 server that doesn’t have this patch. If it’s merged the version in the comment will need to be bumped.

sql_log_slow is also handy for controlling noise in the slow query log. It can be used to prune down queries that are known to be slow so only true problem queries are logged.

Here is the patch. I’ll post this on internals soon.

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.

1. If a transaction is open on this thread, commit it.
2. Acquire a read lock for the table.
3. Make a temporary table with new structure
4. Copy the old table to the temporary table row by row changing the structure of the rows on the fly.
5. Rename the original table out of the way
6. Rename the temporary table to the original table name.
7. Drop the original table.
8. Release the read lock.

The slowest part of the process is copying rows from the original table to the temporary table. For large tables this can take minutes to hours. There are a few optimizations built into this process. If the alter table query only renames the table then mysql doesn’t bother copying all the rows to a temporary table and just renames it. For most other things such as renaming columns, adding/dropping indexes, making columns nullable, changing the column default all require copying the entire table.

During the first 4 steps MySQL allows other clients to read from the table being altered. When alter table is done copying rows to the temporary table and is ready to rename it it changes the table lock. MySQL instructs all other clients currently reading from the table to close the table when they are done. While alter table is waiting for existing clients to finish reading from the table it prevents other clients from starting to read from the table. During this time selects will be blocked on “Waiting for tables”. When the last client is done reading from the table alter table continues renaming the table.

This has some interesting implications for transactions and repeatable reads. Internally innodb keeps track of when rows are created. When a transaction is started it can only see rows that were created before the transaction was started (using repeatable read). Any rows created after are not returned. Since alter table copied rows from the old table to the new table rows get a new version number as they are inserted into the temporary table. An alter table can cause a dirty read in transactions that span an alter table. Transactions started before alter table will get no rows back from the table after alter table is finished. If your application is sensitive to dirty reads or getting no rows back from a table (really dirty read ) then don’t run alter table on a server when clients are running.

Here is an example.

mysql a> alter ignore table t add unique index (t);

mysql b> begin;
Query OK, 0 rows affected (0.00 sec)

#This select is from the original table while alter table is copying rows
mysql> select * from t limit 10;
+——+
| t |
+——+
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
+——+
10 rows in set (0.00 sec)
#alter table finishes
#Rows created in the temporary table before we issued begin
mysql> select * from t limit 10;
+———-+
| t |
+———-+
| 10 |
| 6920631 |
| 27998430 |
| 41865298 |
| 49403894 |
+———-+
5 rows in set (0.00 sec)

#Get a new view of the table
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

#This select returns all the rows.
mysql> select * from t limit 10;
+———–+
| t |
+———–+
| 10 |
| 6920631 |
| 27998430 |
| 41865298 |
| 49403894 |
| 50522347 |
| 84441015 |
| 109401269 |
| 110202688 |
| 123590778 |
+———–+
10 rows in set (0.00 sec)

If we start the transaction before alter table the select after alter table has finished will return no rows even though there are rows in the table. I’m not sure why innodb allows rename of a table when transactions still have a few of that table open. It seems like a bug to me.

[Updated 2007-05-14 Bug #28432

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 sql_cache_ttl. This option instructs the query cache hold the entry in the cache for query_cache_ttl seconds. query_cache_ttl can be set globally or per thread. Initial testing shows that it does help eliminate the cache thrashing that occurs during updates on tables that have many large cache entries.

I have a few more optimizations that I want to add to the patch before sending it into MySQL. The patch below is a preview that works with 5.0.37.

The patch.

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.