I didn’t know you could actually do this before addressing this problem in a benchmark using 5.0.36.

MySQL allows you to update two tables with a single UPDATE statement. I knew you could reference two tables in an UPDATE statement but not update both. However when working with a client after benchmarking I observed a large number of Created_tmp_disk_tables via SHOW GLOBAL STATUS and found that this query was the offending query only for certain circumstances. Understanding took a little longer.

schema.sql

DROP TABLE IF EXISTS a;
CREATE TABLE a(
id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
i   INT UNSIGNED NOT NULL,
c   CHAR(1) NOT NULL)
ENGINE=INNODB;
DROP TABLE IF EXISTS b;
CREATE TABLE b(
id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
i   INT UNSIGNED NOT NULL,
c   CHAR(1) NOT NULL,
t   TEXT NOT NULL)
ENGINE=INNODB;

data.sql

INSERT INTO a VALUES(1,1,'a');
INSERT INTO a …

First it was the SlashDot effect, then it was the Digg effect, now it’s the Facebook effect. I have a friend at Facebook and he was talking about the effect of the Facebook Platform API that was released a few weeks ago. Sites were now struggling to cope with the effect of massive amounts of new traffic, ensuring that experienced MySQL Consultants will have plenty of scale out opportunities.

Here is an abstract from an article I read recently. Analyzing the Facebook Platform, three weeks in

Translation: unless you already have, or are prepared to quickly procure, a 100-500+ server infrastructure and everything associated with …

Note: My views are just that: mine.

1. Real time Query Monitoring

MySQL 5.0 GA provides only 3 ways to look at queries that are executed on a server in some way or another. Slow Query Log, General Query Log and Binary Log. All require a server reboot to activate and de-activate. In a production system, it’s sometimes critical to be able to know “what is going on”, and you simply can’t reboot the server twice (once to turn on, once to turn off). 5.1 goes some way with Log Tables to being able to turn on General and Slow Logs into tables. Question is, as Kevin Burton listed in his points, when is 5.1 going to be out.

Real time query monitoring also needs to have a granularity of operation better then “server”. There needs to be a capacity to assign this on per connection basis. A server is being hammered, …

Came across ask a SQL guru today. Service is run by the Microsoft DB MVP’s. You call them (via Skype), and they answer your query, via a video cast. Its quite new, hasn’t proven itself (I mean, why not just ask a forum?), and also offers itself as a video podcast. I’m thinking it can be useful for newer folk, especially if you like step-by-step instructions.

Going by how useful the Guru Bar at the MySQL Expo 2007 was, and how easy it is to make screencasts these days, I wonder if this would be something some guilds members would consider in addition to the wonderful podcast by Sheeri?

Technorati Tags: …

I was reading only last week the notes from Wikipedia: Site Internals, Configuration and Code Examples, and Management Issues Tutorial by Domas Mituzas at the recent 2007 MySQL Conference. I didn’t attend this session, like a lot of sessions too much good stuff at the same time.

It’s obviously taken a while to catch up on my reading, but with the present MySQL 12 days of Scale-Out I thought I’ll complete my notes for all to see.

If you have never used Wikipedia well, why are you reading this, you should spend an hour there now. Alexa places Wikipedia in one of the top 10 visited sites on the Internet.

…

I was asked recently to confirm a consistent state of data in a non-transactional MySQL table after a failing statement updating multiple rows did not complete successfully.

Hmmm, this is what I did.

• Created a MEMORY table
• Populated with some data, and a Primary Key
• Updated the Primary Key so that it failed with a Duplicate Key Error after updating only half the rows
• Confirmed that the rows that were updated, were, and the rows that were not updated, were not

DROP TABLE IF EXISTS mem1;
CREATE TABLE mem1(
i1  INT UNSIGNED NOT NULL PRIMARY KEY,
c1 CHAR(10) NOT NULL,
dt TIMESTAMP)
ENGINE=MEMORY;

INSERT INTO mem1(i1,c1) VALUES (1,'a'), (2,'b'), (3,'c'), (4,'d'), (5,'e');
SELECT * FROM mem1;
+----+----+---------------------+
| i1 | c1 | dt                  |
+----+----+---------------------+
|  1 | a  | 2007-06-14 17:26:29 |
|  2 | b  | 2007-06-14 17:26:29 |
|  3 | c  | 2007-06-14 …

I recently saw the Planet MySQL post Five months with MySQL Cluster by Brian Moon.

Thought I’d add my 5 cents worth (Australian’s don’t have 1 cent coins any more to make 2 cents worth)

Firstly, it’s great you wrote about your experiences in moving to MySQL Cluster. I think more people should.

Joins

“We used a lot of joins. We learned (later) that joins in the cluster are not a good idea.”

MySQL Cluster’s number one strength is Primary Key Lookups, it’s very good you learned that joins (especially say 5-6-7 table joins) are not a good idea, it is not a strength, and certainly not scalable to thousands of queries per second.

Rewrite

“We rewrote our application (basically, our public web site) to use …

Announced at the 2007 Conference as MySQL Applications of the Year - #1 in 3G Mobile Entertainment, Amp’d Mobile is no longer the poster boy.

Amp?d Mobile Implodes: Burns $360 million, Declares Bankruptcy. Wow, that’s news on a Sunday.

Ok, so I knew about innodb_table_monitor and innodb_tablespace_monitor. I’ve tried them before, looked at the output and given up, partly because it didn’t serve the purpose I wanted it to at the time, and also because it’s format was a little cryptic.

What I didn’t know was there are actually 4 monitors via this “create table functionality”. You can also do innodb_monitor which is the same as SHOW INNODB STATUS, and you can also do innodb_lock_monitor .

Another thing I didn’t know is that these commands don’t send the output just once, it’s on a timer. I’ve found the timers to be different. For innodb_monitor you get every 15 sec, as well as a nice line given time of averages which seems to always say 16 seconds.

=====================================
070601 15:11:25 INNODB MONITOR OUTPUT
=====================================
Per …

Log Buffer #47: a Carnival of the Vanities for DBAs
June 1st, 2007 - by Ronald Bradford

Welcome to the 47th edition of Log Buffer, the weekly review of database blogs. No time to wait, lets read more about this week’s database blogging activities.

The PostgreSQL Conference for Users and Developers wrapped up this week and Peter Eisentraut gives us a review including the lightning talks and wrap-up session with a charity auction in PGCon Day 4. Meanwhile Alex Gorbachev is at Miracle Scotland Database Forum - Day One, sounds like from his post there is a lot …