After playing yesterday a bit with INSERT ... SELECT I decided to check is Innodb locks are relly as efficient in terms of low resource usage as they are advertised.

Lets start with a bit of background - in Innodb row level locks are implemented by having special lock table, located in the buffer pool where small record allocated for each hash and for each row locked on that page bit can be set. This in theory can give overhead as low as few bits per row, so lets see how it looks in practice:

I used the same sample table as yesterday:

PLAIN TEXT SQL:

1. CREATE TABLE `sample` (
2.   `i` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3.   `j` varchar(255) DEFAULT NULL,
4.   PRIMARY KEY  (`i`),
5.   KEY `j` (`j`)
6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
7.  
8. mysql> SHOW TABLE STATUS LIKE "sample" …

- PRESS RELEASE -

DAY ONE OF NEW EU PATENT WAR:
EU COMMISSION PUSHES FOR LITIGATION AGREEMENT

EU internal market commissioner McCreevy said at yesterday’s hearing
on the future of European patent policy in Brussels that he wants to
“move forward” with the European Patent Litigation Agreement (EPLA) -

Anti-software patent campaigners vehemently oppose the EPLA,
claiming it is “from a software patents point of view […] far worse”
than the directive they defeated in the European Parliament last year

Brussels (July 13, 2006) - At yesterday’s European Commission hearing in Brussels on the future of European patent policy (http://ec.europa.eu/internal_market/indprop/patent/hearing_en.htm), the EU’s internal market commissioner Charlie …

Pia Waugh recently wrote an excellent article on economic and social reasons for using FOSS. It's for Australia, but of course it applies almost everywhere. You may find it useful!

As Pia says, "getting the right information to the right people in the right order to get a right outcome." or as I generally note, "don't try geek arguments on non-geeks." ;-)

Yes, apparently it's quite unusual to use a train in the US. But consider this... for a trip of upto a few hours, it might even be faster if you take into account having to be at the airport early, etc. Much less hassle, more seat space, and with a business class seat you even get an AC socket so you can plug in your laptop! And it's definitely cheaper.

I'm travelling to Portland next week, for OSCON. My trip from home in Brisbane, Australia will take me via Auckland (New Zealand), and then into San Francisco. When going to the US I always start a few days early, so when an event starts I'll be past my jetlag... I'll first stay in Cupertino (Silicon Valley) for a day, visiting our US offices there. Then fly up to Portland. I already had that booked. So why the train?

Well, Brian just invited me to visit Seattle for the few days before OSCON. It appears that various MySQL people will be there, actually, and that's a great …

MySQL 5.0.23 will not be officially released due to the fix for bug #10952 (an ALTER TABLE to MERGE or BLACKHOLE should have been forbidden to prevent the loss of data in the table). The fix introduced some unwanted side effects and needs to be reverted in the upcoming release 5.0.24. But this was not the only bugfix with negative side effects in 5.0.23: I tried the build today and just discovered three other bugfixes introducing new bugs while actually trying to fix old ones.

Just a few days ago Baron Schwartz wrote an article on how to replace large IN clauses with a UNION SELECT …

I just noticed that Boing Boing brought up the old six degree patent. I commented on this patent in 2003 when I wrote the Zoo system for Slashdot. The patent is narrow in its implementation so sites like Slashdot or even LJ have little to be worried about.

Someone should spend the time to write up the prior art in this area, just so that if anyone gets taken to court over these social network patents the information will be readily available.

Have I mentioned lately that I hate patents? Someday I should post some of the world's "Most Obvious Database Patents". I don't know which are worse, the web ones, or the database ones.

Since the last few versions and especially since 5.0.23, the mysqldump command includes new and very important bug fixes.

I'd like to mention three of them that I was affected by:

Bug 16878 (fixed in 5.0.19, 5.1.8): Dump of triggers
Bug 17201 (fixed in 5.0.23, 5.1.12): mysqldump sometimes creates database twice
Bug 18462 (fixed in 5.0.23): mysqldump does not dump view structures correctly

After an interesting question I'm posting here a simple description of how to place your MySQL data on another drive, if you got a server with multiple disks this can really benefit performance.
The picture is this, you have installed MySQL server in the usual C:\Program Files\MySQL\MySQL Server 5.0\ folder, but you have another drive D: and you want to place your data there, you'll create three folders there:

D:\MySQL DataDir\ which will contain MyISAM tables (.frm, .MYD, .MYI files) and also InnoDB tables definitions (.frm files)

D:\MySQL InnoDBDatafiles\ which will contain InnoDB data (ibdata1 etc. files)

D:\MySQL InnoDBLogfiles\ which will contain InnoDB logs (ib_logfile0, ib_logfile1 etc.)

this will be accomplished by:

1. creating those folders …

Just stumbled over this article, which might come in handy for our Microsoft Office users out there: "Using Excel to analyze MySQL data" by Jason Gilmore explains how to set up the MySQL Connector/ODBC and connecting to a MySQL database using Microsoft Excel.

If you're a Linux user, this article on NewsForge covers a similar topic, using OpenOffice.org

to connect to a MySQL Server.

Update: Thanks to Guillermo Roditi for pointing out that both links pointed to the same article - now fixed. Sorry about that.

Everyone using Innodb tables probably got use to the fact Innodb tables perform non locking reads, meaning unless you use some modifiers such as LOCK IN SHARE MODE or FOR UPDATE, SELECT statements will not lock any rows while running.

This is generally correct, however there a notable exception - INSERT INTO table1 SELECT * FROM table2. This statement will perform locking read (shared locks) for table2 table. It also applies to similar tables with where clause and joins. It is important for tables which is being read to be Innodb - even if writes are done in MyISAM table.

So why was this done, being pretty bad for MySQL Performance and concurrency ? The reason is - replication. In MySQL before 5.1 replication is statement based which means statements replied on the master should cause the same effect as on the slave. If Innodb would not locking rows in source table other transaction …