Have you ever seen connection in the SHOW PROCESSLIST output which is in "Sleep" state for a long time and you have no idea why this would happen ?

I see if frequently with web applications and it is often indication of trouble. Not only it means you may run out of MySQL connections quicker than you expected but it also frequently indicates serious problems in the application. If you do not use persistent connections and you have connection in Sleep stage for 600 seconds what could it be ? It may mean some of your pages take that long to generate (or might be the code simply gets into the tight loop and page never gets generated) it also could mean some of external Web Services are slow or not available and you're not dealing with timeouts properly. Or may be you have several connections to MySQL server and right now running query which takes that long ? In any case it is something frequently worth looking at.

…

In discussion the other day a co-worker was looking at a benchmark
that I had created for a problem I was studying on threads with write
performance.

He asked "What is open tables set at?"

I responded that I had it set to a thousand, which is normal the
value I keep on the machine I do development on. I normally look at
300 connections as an average load, but every so often I crank it up
to around a 1000 depending on the test (which is a pretty high
simulation, and not common among the average large websites).

It crossed my mind at this point that I had no idea how the database
behaved with default value in MySQL, which is 64, when you were
running with this many threads. Anyone who has a large number of
threads touching tables knows to crank up the table cache.

As the graph points out, depending on which engine you are …

Today I had a sort of short epiphany regarding getting progress of running statements in MySQL. MySQL already keeps a running count of rows touched in most multi-row statements (called thd->row_count1), so I figured there must be a way to make use of it. It was trivial to expose row_count through SHOW PROCESSLIST. After that, it was fairly obvious that another variable could be added: row_count_expected. For certain statements (currently only ALTER TABLE) it is easy to estimate how many rows will be touched, so that number can be used to calculate a Progress_percent column.

The Progress_rows number indicates progress within a given step of executing the query. For instance, if you run a SELECT with a GROUP BY that can’t be done using an index, you will see two cycles of Progress_rows: once with a State of “Copying to tmp table” and once with “Sending data”.

I implemented this all in …

For far too long now RSS has been used in ways that don't really tap its true potential. Being able to syndicate my favorite headlines or blog posts is great. In fact, it helped to kick off a revolution in personal on-line publishing that is still growing and evolving. But I want so much more.

It's not for lack of vision. Back in 2005, Adam Bosworth painted a vision that eventually manifested itself as GData. I wrote about that a bit in Google's GData, MySQL, and the Future of on-line Databases.

It's not for lack of data either. You can get RSS output from lots of non-news and non-blog stuff. Everything from classifieds on eBay and …

-- Sieve of Eratosthenes

-- The sieve of Eratosthenes is a simple algorithm to create a list of prime -- numbers up to some specified limit.

-- First, a list of all interger numbers from 2 up to the specified limit is -- created. Then, the algorithm repeats the following steps until the list -- is empty.

-- The smallest number of the remaining list is picked, it's a prime number. -- All multiples of this numbers are deleted from the list.

-- All the picked numbers are the primes numbers of the original range. DROP PROCEDURE IF EXISTS sieve; DELIMITER $$ CREATE PROCEDURE sieve (amount INT) BEGIN DECLARE cnt INT DEFAULT 2; DECLARE curprime INT; DECLARE lastprime INT DEFAULT 1; -- Temporary table speeds up operations DROP TABLE IF EXISTS mysieve; CREATE TABLE mysieve (nr MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY) ENGINE=MEMORY; DROP TABLE IF EXISTS primes; CREATE TABLE primes (nr MEDIUMINT UNSIGNED NOT NULL) …

Following a discussion on mythtv on #xfs (as you do), and a wondering of “hrrm… i wonder what unit that timeout is” with some NDB code I wish to make the following announcement:

All timeout values in NDB related APIs will now be given in centijiffies of the server system. For APIs that can talk to multiple hosts, it will be furlongs per fortnight.

I feel that having a consistent interface such as this will lead to much less confusion and better apps.

My mom's telephone comes with a speaker that allows you to make calls without using the receiver. Of course, there are two buttons to control the speaker. One to decrease and one to increase the volume. So far so good.

After a power outage, the phone decided to reset the volume for its ringtone. It was barely audible, so my mom went to fix that and pressed the volume-up button she found on the keypad. This was, however, the aforementioned control for the speaker — not the ringer.

To make adjusting the speaker volume even more comfortable, pressing the controls turns on the speaker giving direct audible feedback. Nice. It just happens to also pick up the line to provide any audible signal.

So instead of adjusting the ringtone's volume, my mom picked up the phone line with the speaker which she usually never, if ever, does. I even suspect her not knowing about the speaker function and all the things that come with it. …

By Allison Randal

A thoughtful post by chromatic on the problems and potential solutions around proprietary hardware and drivers with open source software:

However, it’s clear that the normal approach-that is, complaining in small groups and rewarding vendors with your business anyway-is not working. Nor does reverse engineering drivers address the root of the problem. It’s valuable in that it mitigates the damage, but it does little to prevent further problems.

Acknowledging vendors with the courage to deal with their customers respectfully and honestly may help. Free and open source software advocates can do a much better job of praising honest efforts to work with communities-and to encourage other companies to do so in a mutually …

We've been working on the design of a protocol which would enable promotion of a slave to a master in a MySQL replication cluster.

Right now, if a MySQL master fails, most people just deal with a temporary outage. They bring the box back up, run REPAIR TABLEs if necessary, and generally take a few hours of downtime.

Google, Flickr, and Friendster have protocols in place for handling master failure but for the most part these are undocumented.

One solution would be to use a system like DRDB to get a synchronous copy of the data into a backup DB. This would work of course but would require more hardware and a custom kernel.

You could also use a second master in multi-master replication but this would require more hardware as well and complicates matters now that you're using multi-master replication which has a few technical issues.

A simpler approach is to just take a slave and promote it to the master. …