Improved handling of large Falcon transactions
In previous MySQL 6.0 alpha’s, the new Falcon engine didn’t handle ‘large’ transactions (meaning lots of rows inserted at one time) very well. You typically had to fall back to looping through the data with various commit points to get all the data inserted in a timely fashion.
The Falcon team should get some good kudos for putting out the latest alpha release that has much improved handling of large transactions. Below are just a few examples of large inserts on a Fedora Core box with a single CPU. Falcon was given a 200MB record cache size and InnoDB got a comparable 200MB buffer pool size.
mysql> show create table t_mG
*************************** 1. row ***************************
Table: t_m
Create Table: CREATE TABLE `t_m` (
`client_transaction_id` int(11) NOT NULL DEFAULT '0',
`client_id` int(11) NOT NULL DEFAULT '0',
`investment_id` int(11) NOT NULL DEFAULT '0',
`action` varchar(10) NOT NULL,
`price` decimal(12,2) NOT NULL DEFAULT '0.00',
`number_of_units` int(11) NOT NULL DEFAULT '0',
`transaction_status` varchar(10) NOT NULL,
`transaction_sub_timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`transaction_comp_timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`description` varchar(200) DEFAULT NULL,
`broker_id` bigint(10) DEFAULT NULL,
`broker_commission` decimal(10,2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.03 sec)
mysql> select count(*) from t_m;
+----------+
| count(*) |
+----------+
| 2000000 |
+----------+
mysql> create table t_f engine=falcon select * from t_m where 1=2;
Query OK, 0 rows affected (0.05 sec)
mysql> create table t_i engine=innodb select * from t_m where 1=2;
Query OK, 0 rows affected (0.05 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.06 sec)
mysql> insert into t_f select * from t_m;
Query OK, 2000000 rows affected (42.06 sec)
Records: 2000000 Duplicates: 0 Warnings: 0
mysql> rollback;
Query OK, 0 rows affected (5.09 sec)
mysql> insert into t_i select * from t_m;
Query OK, 2000000 rows affected (45.73 sec)
Records: 2000000 Duplicates: 0 Warnings: 0
mysql> rollback;
Query OK, 0 rows affected (41.81 sec)
Much better than previous alpha releases… Still, there’s more work to be done as Falcon can still experience a memory exhaustion error (remember it performs transaction management in memory) when transactions get really large as in this 5.6 million row test:
mysql> insert into t_f select * from t_m_big; ERROR 1296 (HY000): Got error 305 'record memory is exhausted' from Falcon
The Falcon team will be addressing this issue shortly so look for even better things to come in upcoming Beta releases.