That’s right, I said InnoDB+, with a “plus” at the end. I didn’t know it existed until, while following some links from Monty’s appeal to save MySQL, I decided to read a Groklaw post that links to Eben Moglen’s letter to the EU Commission, which includes this text:
Innobase could therefore have provided an enhanced version of InnoDB, like Oracle’s current InnoDB+, under non-GPL license, …
I don’t know anything more. Do you?
Related posts:
[Read more]I can think of at least two major reasons why systems delay flushing changes to durable storage:
1. So they can do the work when it's more convenient.
2. So they can do less work in total.
Let's look at how the second property can be true.
A commenter on Deva's recent post on InnoDB adaptive flushing asked,
That’s really interesting stuff; am I reading it correctly though that adaptive flush actually increased the IOOPS? Looking at the IO graphs, it looks like both the peak IO rate and average IO rate were higher with adaptive flush nabled (assuming I’m reading properly).
Yes. Adaptive flushing actually increased the overall number of I/O operations performed. Smoothing out the workload can cause more work to be done. To see why, remember that InnoDB works in 16kb pages at a time. Suppose …
[Read more]There’s one stage in InnoDB crash recovery where it reads log file, you know, this one:
InnoDB: Doing recovery: scanned up to log sequence number 354164119040 InnoDB: Doing recovery: scanned up to log sequence number 354169361920
On a machine with bigger logs it will start spending nearly 100% CPU somewhere in recv_scan_log_recs. Guess what it does…. -fno-inline builds to the rescue:
#0 mem_block_get_len at ./include/mem0mem.ic:86 #1 mem_heap_get_size at ./include/mem0mem.ic:591 #2 recv_scan_log_recs at log/log0recv.c:2727
And:
samples % symbol name 8467 72.9222 mem_heap_get_size 291 2.5062 recv_add_to_hash_table 95 0.8182 mem_block_get_len
To speak in layman’s terms, InnoDB does SUM(LENGTH(allocation)) on its relatively wide memory (tens, hundreds of thousands of entries) arena, FOR EVERY LOG SEGMENT, to make sure it didn’t run out of available …
[Read more]I had an interesting case recently. The customer dealing with large MySQL data warehouse had the table which was had data merged into it with INSERT ON DUPLICATE KEY UPDATE statements. The performance was extremely slow. I turned out it is caused by hundreds of daily partitions created for this table. What is the most interesting (and surprising) not every statement is affected equally as you can see from the benchmarks above:
I got the following test table created:
PLAIN TEXT SQL:
I recently had the chance to witness the effects of innodb_adaptive_flushing on the performance of InnoDB Plugin 1.0.5 in the wild, which Yasufumi wrote about previously here and here.
The server in question was Solaris 10 with 8 disk RAID10 and 2 32GB SSDs used for ZIL and L2ARC, 72G RAM and 40G buffer pool. We started it up with innodb_adaptive_flushing=OFF and innodb_doublewrite=OFF, then ramped up traffic and everything looked stable ... but I noticed one troubling thing: ~2GB of uncheckpointed data.
mysql> SHOW INNODB STATUS\G .... Database pages 2318457 Old database pages 855816 Modified db pages 457902 Log flushed up to 10026890404067 Last checkpoint at 10024612103454 ....
We enabled …
[Read more]My previous post Redis, Memcache, Tokyp Tyrant, MySQL comparison had a flaw as pointed out by this comment. The MySQL was taking a huge time for doing a reverse DNS lookup.
I turned on the skip-name-resolve parameter in the my.cnf and the Throughput of MySQL grew considerably, almost more than double.
I am not sure if this is a bug or how MySQL works on validating constraints in association with ON DUPLICATE KEY (late or early checking). For example, consider the following use case (this is irrepective of storage engine and MySQL version):
mysql> create table t1(id int not null primary key, val int not null) Engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t1 values(10,20);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values(20,10);
Query OK, 1 row affected (0.00 sec)
mysql> create table t2(id1 int not null primary key, val1 int) Engine=MyISAM;
Query OK, 0 rows affected (0.14 sec)
mysql> insert into t2 values(10,NULL);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(id, val) select id1, val1 from t2 ON DUPLICATE KEY UPDATE val=IF (VALUES(val) IS NULL, val, VALUES(val));
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 1 Duplicates: 1 Warnings: … |
Recently I looked at table_cache sizing which showed larger table cache does not always provides the best performance. So I decided to look at yet another similar variable - innodb_open_files which defines how many files Innodb will keep open while working in innodb_file_per_table mode.
Unlike MyISAM Innodb does not have to keep open file descriptor when table is open - open table is purely logical state and appropriate .ibd file may be open or closed. Furthermore besides MySQL table_cache Innodb maintains its own (called data dictionary) which keeps all tables ever accessed since table start - there is no variable to control its size and it can take significant amount of memory in some edge cases. Percona patches though provide innodb_dict_size_limit to restrict …
[Read more]You can now yum (RPM) or apt-get (DEB) MariaDB 5.1.39, courtesy of OurDelta and in close cooperation with Monty Program Ab. Simply follow the info on the CentOS, Debian or Ubuntu pages.
(note: give the mirrors some hours to sync up)
Quick overview
For the past two months, I have been running tests on TokuDB in my free time. TokuDB is a storage engine put out by Tokutek. TokuDB uses fractal tree indexes instead of B-tree indexes to improve performance, which is dramatically noticeable when dealing with large tables (over 100 million rows).
For those that like the information “above the fold”, here is a table with results from a test comparing InnoDB and TokuDB. All the steps are explained in the post below, if you want more details, but here’s the table:
| Action | InnoDB | TokuDB |
|---|---|---|
| Importing ~40 million rows | 119 min 20.596 sec | 69 min 1.982 sec |
| INSERTing again, ~80 million rows total | 5 hours 13 min 52.58 sec … |