I’ve posted a new table statistics patch which is the next version of the session table/index statistics patch This version of the patch adds slow query log output. If a query is logged to the slow query log it will have row count statistics added to it.

I’m not sure about the format of the log which is why I’m posting this so early. The first format I tried was:

# Time: 100119 19:24:37
# User@Host: [ebergen] @ localhost []
# Query_time: 10 Lock_time: 0 Rows_sent: 7 Rows_examined: 3
# Rows_read: sbtest.foo:3, sbtest.bar:3,
select * from foo a, bar b where sleep(1) = 0;

Discount educational software

Where there …

I had some free time over Thanksgiving so I decided to work on something I have been thinking about for quite some time. I hacked up Google’s show table_statistics patch to also track stats per connection. I say this is a first draft hack because I based it off of the v2 patch which uses a straight up hash table instead of the intermediate object cache.

I’ve added the global/session key word to the existing show table_statistics command in the same way that show status works. This means that the default behavior of show table_statistics is to show session data instead of global data. This is different from the Google patch which only works globally. This has been running in production environments for a bit and seems stable. Note that these environments don’t run at the concurrency that motivated Google to update the patch to be less likely to lock a global mutex. You have been warned!

I’m planning on updating the patch with …

Previously I wrote about how late the mysql pid file is created in the startup process. At first glance it seemed like a relatively easy thing to fix. In main() there is a call to start_signal_handler(). The first instance of static void start_signal_handler() does only one thing. It checks !opt_bootstrap to make sure mysqld isn’t being called by mysql_install_db. I’m not sure why mysql_install_db can’t have a pid file created but that’s getting outside the scope of my investigation. It seems simple enough to move the call to start_signal_handler() above the call to init_server_components() in main() and have the pidfile created earlier. It turns out pidfile creation happens differently on different arches.

For windows and netware start_signal_handler simply creates the pid file. For __EMX__ (I’m not sure what …

mysql_safe is responsible for restarting mysqld if it crashes and will exit cleanly if mysqld was shutdown. The way it determines if mysqld shutdown correctly is if the pid file is cleaned up correctly. MySQL does quite a few things before creating the pid file like initializing storage engines. It can take quite a while to initialize innodb if it has to roll forward the transaction log. During this time if mysqld crashes mysqld_safe won’t restart it. Normally this is ok because the server would just crash again but it can mess with your head a bit if you’re testing changes to mysqld_safe. Especially if those changes involve what mysqld_safe does if mysqld crashes. I think it makes sense to create the pidfile earlier and there is a bug for it. Chime in on the bug if this has burned you.

InnoDB Deadlock Count Patch

Deadlocks are a common occurrence in relational databases. They usually aren’t a problem until they start happening too frequently. Innodb can provide you with information about the latest deadlock in SHOW ENGINE INNODB STATUS. This can be useful for debugging but it’s almost impossible to get the rate at which deadlocks are occurring. This patch applies against MySQL 5.0.72sp1 and probably quite a few other versions. It adds a counter to show table status that tracks the number of deadlocks. In this example mysql-1> is connection 1 and mysql-2> is connection 2.

mysql-2> show global status like …

In a Great Magnet moment Trent Lloyd posted an excellent write-up on how to recover from relay log name changes on the same day I was going to write up a procedure to send to a client who had a similar issue. Thanks Trent! The problem goes a bit deeper than server hostname changes because there have been a few changes to how mysql handles default log file names in 5.0

Prior to 5.0.38 the default log file name started with the hostname. The problem is, as Trent points out, that if the hostname of the server changes then mysql doesn’t generate default log file names correctly. The error message though is something like:

090825 18:54:53 [ERROR] Failed to open the relay log ‘/mysql/old_hostname-relay-bin.000015′ (relay_log_pos 107657)

There are a few strange things going on here. First if the relay-log index file default …

Sunday morning, in the wee hours of the morning, while packing, I discovered that I didn't know where either my DL or my Passport is. After spending an hour searching, I gave up, and decided to throw myself on the mercy of the TSA. Which I did at the airport. The TSA supervisor checked my credit cards, FlyClear card, corporate ID card, and CostCo card, and then stamped my boarding pass. I only need to do this 3 more times on this trip, and then do a very deep search of my room when I get back.

After I landed at SJC, I discovered that Tim Lord has shared the flight with me. If I had known that ahead of time, I would have let him share my taxi ride from Capitol Hill neighborhood to the SEA airport. As it was, I let him share my ride to San Jose Convention Center. I was actually too early to check into my hotel room. So instead I checked in my luggage, and then went off to find the Postgres Day.

Meeting up with open source …

fadvise is a system call that can be used to give Linux hints about how it should be caching files. It has a few options for caching, not caching, read ahead, and random access. I was looking into used fadvise because a client ran into an issue where some infrequently used myisam data files were being pushed out of the filesystem cache by binary logs and other activity. The files are used infrequently for queries but when they are used they need to be fast. When the files weren’t cached the particular query ran in about 30 seconds. When they were cached the query ran in .8 seconds — huge difference. The fix seemed pretty trivial, call fadvise on the myd files, they stay in cache, the queries are consistently faster and the problem is solved. It seemed simple but it wasn’t. I’ll cover the myisam issue in more detail in another post, this is all about fadvise and debugging.

I couldn’t find an rpm that contained a simple tool to …

When Oracle agreed to acquire Sun there was some speculation that Oracle might try to kill MySQL. First this wouldn’t be a very prudent effort on Oracle’s part and second it’s not even possible. I think Monty has the best explanation from his comment on his blog:

The simple fact is one can’t own an open source project. One can control it by controlling the people which are leading and developing it. When a company doesn’t take good care of their employees and those employees start to leave the company and work on the project elsewhere, that company has lost control of the project.

The whole comment is worth reading. Monty does a good job of putting the Sun purchase into perspective with regards to MySQL and the developer community.

[Update: I found the magic javascript links that show old releases of sysbench.]

Sysbench is an application that can be used to benchmark different system parameters and also includes support for testing MySQL directly. Google has released a patch for sysbench that adds a lot of new OLTP tests. It’s great for testing MySQL and for drag races against Mark’s tests. Their patch seems to apply against sysbench 0.4.10. I was able to find sysbench 0.4.10 but it wasn’t easy so I’ve ported Google’s patch to sysbench 0.4.12.

Grab the patch here.