Ran into an interesting issue with a client today who was using innodb_file_per_table. This client had a lot of tables ( 300K+), they were using innodb_file_per_table. Today they had a problem that caused their db to crash. After fixing their issue, the database restart seemed to hang. The only thing in the error log was the mysql was started, their was no crash recovery messages. An strace revealed that the mysqld process was stating all the datafiles. It seems that during a recovery the first step is to just check to see if all the individual files are really their. The entire process to simply stat each datafile took over 45 minutes to complete after which the actual crash recovery started. Just a quick note to remind myself in the future if I see this again.

Here is a sample table you can create to test following problem/solution on: CREATE TABLE `t1` ( `a` char(1) default NULL, `b` smallint(6) default NULL ); insert into t1 values ('y','1'); I have a table t1 which has column a and b, I want column a to be updated to ‘n’ when column b = 0. Here is the first version [...]

I’m at the Sun Tech Days Australia 2008, these few days, in Sydney, Australia. Its March 4th - 6th, and if you’d like to catch up, it looks like there are free evenings, from the agenda. At the event, I’ll be the guy donning MySQL attire, of course ;)

Expect blog posts, photos (I’ve got my pocket camera), and more soon. Don’t know if my expensive room at the Sheraton Four Points has free Internet (how much you want to bet, it doesn’t).

If you want to meet me, or catch up, don’t hesitate calling me at +61433 580 941.

Technorati Tags: sun tech days, sydney, …

Recently we noticed a strange locking problem within our application. While the previous release worked flawlessly the current version produced a number of Lock Wait Timeouts. The code itself did not change in the area we experienced the problems, however our persistence framework had been updated to a new release in conjunction with our app. One notable difference was the schema generation

I'm happy to announce the release of Memcached UDFs version 0.3 (downloadable at http://download.tangent.org/memcached_functions_mysql-0.3.tar.gz). In this version:

• 
  
• memc_stats() added
  
• memc_stat_get_keys() added
  
• buffering behavior option added for better performance
  

This will allow you to query the state of your servers. This makes a great way to possibly add tools to monitor the database and memcached from one source.


mysql> select memc_stats('localhost')\G
*************************** 1. row ***************************
memc_stats('localhost'): Listing 2 Server

Server: 127.0.0.1 …

working with the iodbc driver manager on mac os x has been a frustration on two fronts.

first, the installer api functions provided by iodbc constantly set the configuration mode to ODBC_BOTH_DSN, which means you have to keep resetting it to the correct value after nearly every installer api call. this problem is platform-agnostic — the iodbc code is just plain wrong.

second, when called from the odbc administrator application on mac os x, any failures that the driver reports or passes through from the installer api in registering the driver are ignored, and the application instead uses a generic prompt for dsn configuration.

so even with the first problem fixed, the second problem has led to a lot of tail-chasing until i discovered that the odbc administrator application only obtains enough privileges to write to /Library/ODBC as …

I have finalized the design for Federating Connections and part of the design has been implemented so far with amazing results. Overnight the dependency on replication has been reduced. Feed updates no longer are lagging and query load doubled without the need for new hardware.

Social connections (not mySQL connections) at Flickr in particular are directly responsible for permission levels. This allows members to see into a another members photostream. This global requirement means that every logged in page viewed on Flickr requires a database read if the page is not the members own page. So if the cluster is down, all access to photostreams default to the most restrictive state, i.e. public photos only.

As a result, the service needs to be extremely responsive on reads, since possibly every page view on Flickr could produce a realtime query on the contact cluster.

Next, the data has to be redundant and …

End Software Patents earlier claimed that the US economy suffers an $11 billion hit each year due to needless software patents. It turns out that End Software Patents was wrong.

The number is actually $30.4 billion.

What's $20 billion between friends? The group revised upward its earlier, more conservative estimates based on the following [...

Courtesy of our friends at Oracle cost containment company Miro Consulting, I am giving a webinar tomorrow at 1pm EST (click this link for the time in other timezones please. The subject I’ve chosen is how to apply the best practices around advanced supply management that are extremely successful and mature in the product supply [...]

Hey Texans!

This Sunday (3/9) Sun will be hosting an event at La Zona Rosa in Austin and has invited MySQL'ers and community members. Music will be from the Smithereens and Seven Mary Three. And Zack Urlocker will be in attendance.

We up in Dallas are trying to get a group to head South and hope to see you there.

If you are in the Austin area or can get there, this is a great opportunity to socialize.