Sam Varghese of iTWire invited me for a broad interview when I was teaching in Melbourne last week, it's now published (MySQL: the Australian connection).

I just got around to reading Stephen O’Grady’s post on the relative openness of open source vendors and realized I had failed to be as clear as I could have been in my original post on the subject.

Responding to my note about Milking the GNU’s suggestion that a new independent organization could be formed to judge vendors on their level of openness, Stephen wrote:

“Those in the industry that might care have, I would argue, already formed their opinions on whether or not a project such as MySQL?s is or is not open source. And those outside the industry, well, I don?t expect they?d care. At all. Most of the enterprises I speak with are …

Marten Mickos will visit Rome on May 30th. He will meet the press and customers in the morning, and will be the main guest at a conference hosted at La Sapienza, Rome well known University.

Innotop is a very useful tool to monitor innodb information in real time. This tool is written by Baron Schwartz who is also an author of “High Performance MySQL, Second edition” book. [Side note: I highly recommend getting this book when it comes out (in June, 08?). Other authors include: Peter Zaitsev, Jeremy Zawodny, Arjen Lentz, Vadim Tkachenko and Derek J. Balling.] Quick summary of what innotop can monitor (from: http://innotop.sourceforge.net/): InnoDB transactions and internals, queries and processes, deadlocks, foreign key errors, replication status, system variables and status and much more.

Following are the instructions on how to install innotop on CentOS x64/Fedora/RHEL (Redhat enterprise). Most probably same instructions can be used on all flavors of Linux. If not, leave me a comment and I will research a solution for you. Let us start with downloading innotop. I used version 1.6.0 which is the latest at the time of …

I always use to blog my temporary ideas on one of my Japanese blog (id:kazuhooku's memos). When I wrote my thoughts on how to further optimize Q4M, Nishida-san asked me "how fast is the raw performance without client overhead?" Although it seems a bit diffcult to answer directly, it is easy to measure the performance of MySQL core and the storage engine interface, and by deducting the overhead, the raw performance of I/O operations in Q4M can be estimated. All the benchmarks were taken on linux 2.6.18 running on two Opteron 2218s.

So at first, I measured the raw performance of MySQL core on my testbed using mysqlslap, which was 115k queries per second.

$ perl -e 'print "select 1;\n" for 1..10000' > …

Over the past few years of dealing with mysql in larger environments, one thing I’ve always felt that ALTER TABLE statements are flaky with replication, but could never really prove it. I never had a chance to dig into some of strange things I saw, and would tend to overlook and fix them

While working for a client, I encountered an issue that I could no longer ignore. In order to reload a small table from a master database to a number of slaves, I simply piped the output of mysqldump into the master and expected everything to flow into the slaves.

The bug is very specific, and probably not very common. If you send a statement like below, as mysqldump automatically adds, to your master:

/*!40000 ALTER TABLE table_name DISABLE KEYS */;

and have configured something like

replicate-rewrite-db=mydb->mydb_slave

on …

Sometimes due to programming error or due to very complex query you can get your PHP script running too long, well after user stopped waiting for the page to render and went browsing other sites.
Looking at Server-Status I've seen scripts executing for hours sometimes which is obviously the problem - they take Apache Slot, MySQL Connection and other resources.

I had discussion today who was thinking Apache would kill the script after "Timeout" specified in Apache configuration is reached - this was not my experience so I decided to run couple of tests to check it.

I wrote couple of very simple scripts which demonstrate different behavior for scripts running very long time. For simplicity I did not use MySQL, but other system call - sleep() which has similar behavior for sake of experiment:

Script which simply runs in tight loop "forever"

PLAIN TEXT PHP:

…

Remembering that I did RENAME DATABASE in MySQL 5.1.21 and found it useful I tried it with 5.1.24 (I was playing with 20GB InnoDB database, so dumping is not fastest way) and all my tries finished with "Syntax error".
So RTMF and documentation says
"This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23."

For me term 'dangerous' is interesting there , as I'd expect really dangerous is DROP DATABASE (which I hope will not be removed in next release) , and RENAME DATABASE is supposed to be kind of safe - just replaces old name to new one.

I guess there are some related bugs - and there are:
Bug#28360: RENAME DATABASE destroys routines
Bug#17565: …

So imagine you have a table and for every row it contains an INT that represents a user, a medium int that represents an activity number, and a ENUM that represents state.

CREATE TABLE UserStack (
id int unsigned not null default 0,
activity medium int unsigned not null default 0,
state ENUM('ready','processing','processed', 'error'),
PRIMARY KEY(id),
INDEX (state, activity)
) ENGINE = INNODB;

Now you have an application that is spread across many servers which pops a set of users off the stack and sets them to a processing state.

START TRANSACTION

SELECT * FROM UserStack WHERE state='ready' ORDER BY activity DESC LIMIT 10 FOR UPDATE

foreach id 
   
   Mark them Processing

If multiple threads do this at the same time; the 1st thread will …

Did you notice that I moved this blog from pair Networks to pairLite hosting? Probably not, unless you check the DNS of xaprb.com regularly! Don’t you hate it when people say “I’m moving my blog, I hope there won’t be more than a few days of downtime, blah blah…” Why is this ever necessary, I wonder? I wonder the same thing about a lot of hosting providers – recently I had a client in my consulting practice whose (very large, well-known) hosting provider tried to help them with some very simple MySQL work and ended up causing them an obscene amount of downtime, like many many days, and there was no end in sight.