PHP, the universe and all the rest

mysql> CREATE TABLE t1 (id INT PRIMARY KEY ) 
             engine=myisam PARTITION BY HASH(id) PARTITIONS 1000;
Query OK, 0 rows affected (0.18 sec)

mysql> alter table t1 add column v int;
ERROR 1016 (HY000): Can't open file: './test/t1.frm' (errno: 24)

mysql> quit
Bye

$ perror 24
OS error code  24:  Too many open files

Looks as if MyISAM tries to open all the 1000 partition .MYD/.MYI files at once and runs out of file handles during this operation ...

Creating or dropping a partitioned table on InnoDB can become a quite expensive operation, on my laptop i'm seeing the following times for a simple table with 100 or 1000 partitions (using 5.1.58 right now as i'm testing on stock Ubuntu 11.10):

  mysql> CREATE TABLE t1 (id INT PRIMARY KEY )
               engine=innodb PARTITION BY HASH(id) PARTITIONS 100;
  Query OK, 0 rows affected (5.21 sec)

  mysql> drop table t1;
  Query OK, 0 rows affected (5.11 sec)

  mysql> CREATE TABLE t1 (id INT PRIMARY KEY )
               engine=innodb PARTITION BY HASH(id) PARTITIONS 1000;
  Query OK, 0 rows affected (52.76 sec)

So the time to create a partitioned InnoDB table grows linearly with the number of partitions at a 'speed' of about 20 partitions per second, and during that time the hard disk LED is always on. The rate for creating regular InnoDB tables on this machine is about 10 tables per second by the way.

MyIsam on the other had can create around 17 tables per second, and creating a single partitioned MyISAM table only takes about a 10th of a second.

So what is going on with InnoDB here? First of all from the engines point of view each partition of a partitioned table is an actual table, all the partitioning magic happens on a layer above the storage engine one, and the storage engine only receives handler requests for the actual partition tables involved. On the storage engine layer the actual engines are not aware that these tables are part of a larger partitioned table at all (with the exception of ndbcluster tables, but MySQL Cluster is a different story in this respect anyway).

So when creating an InnoDB table with 1000 partitions the InnoDB storage engine actually receives 1000 individual requests to create a table. Now what seems to happen is that for each
created partition/table InnoDB flushes at least its internal data dictionary to disk. Given the constant disk activity and rather low internal table creation rate it may even do a full checkpoint for each table, i haven't checked the details on this yet. And when dropping a partitioned table the same thing happens in reverse.

So this may be something to keep in mind when planning to use large numbers of partitions on InnoDB ...

It is probably about time to re-read the MySQL manual end-to-end as i more and more find myself discovering features i either completely forgot about or which i never was aware of in the first place ...

Todays guest is slave_compressed_protocol, an option that has probably been there ever since MySQL 3.23 at least (so that i can't claim that i seem to have missed the ChangeLog entry as i usually do

With slave_compressed_protocol enabled the communication between slave and master uses the MYSQL_OPT_COMPRESS option to compress the protocol stream if both sides support it (and it's very unlikely to find an installation that does *not* support it these days), so this can be a big savior if your master and slave are at different sites with only limited bandwidth between the two.

Unfortunately this is a global server option though, IMHO this should be part of the options provided by the CHANGE MASTER command, similar to all the SSL encryption related stuff that is part of CHANGE MASTER.

Looks as if it is about time for YAFR (Yet Another Feature Request) ...