2

The table have two fields: id, content, and only one Primary key(id).

The field id type is bigint. The field content type is TEXT, for this field is var-lenth, maybe some record will be 20k, and average length of record is 3k.

Table schema:

CREATE TABLE `events` (
  `eventId` bigint(20) NOT NULL DEFAULT '0',
  `content` text,
  PRIMARY KEY (`eventId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It's just used as a Key-Value storage.

My Test result is:

InnoDB: 2200 records/second
TokuDB: 1300 records/second
BDB-JE: 12000 records/second
LevelDB-JNI: 22000 records/second(not stable, need test again)

the result is very very bad.

Is 3K too big for tokuDB?

In My Application, there many insert(>2000 records/second， about 100M records/day), and rare update/delete. 

TokuDB version: mysql-5.1.52-tokudb-5.0.6-36394-linux-x86_64-glibc23.tar.gz 
InnoDB version: mysql 5.1.34
OS: CentOS 5.4 x86_64

One reason that we choose InnoDB/TokuDB is we need partition support and maintenance friendly. Maybe I will try LevelDB or other Key-Value storage? any sugguest will welcome.

===========

Thanks everybody, finally test performance of TokuDB and InnoDB both not good enough for our use case.

Now we have using solution like bitcask as our storage. Bitcask append-only style write performance is much better than what we expect. We just need to handle the memory problem about the hash index.

Ben
  • 391
  • 6
  • 13
  • We expect the performance to be >5000 records/second, and we data are append-only style. – Ben Feb 24 '12 at 02:43
  • And what version of MySQL are you using? InnoDB has been greatly improved from 5.5 and on. It can easily handle that kind of load when correctly configured. Row-level locks will help with clogging as well. – Chris Bornhoft Feb 24 '12 at 02:50
  • [For the last 10M rows inserted, InnoDB averaged 1,555 rows/sec while TokuDB averaged 16,437 rows/sec – about 10.6x faster.](http://www.mysqlperformanceblog.com/2009/04/28/detailed-review-of-tokutek-storage-engine/) from MySQL Performance Blog – Ben Feb 24 '12 at 02:51
  • Thanks, we will test InnoDB 5.5 and redo the compare. – Ben Feb 24 '12 at 02:54
  • Yes but that's insert. Are you mainly inserting or updating. When you say "append-only" that could mean either. Toku is very weak with updates. – Chris Bornhoft Feb 24 '12 at 02:55
  • Yes, in our case, we just insert, no update, no delete. – Ben Feb 24 '12 at 03:09
  • You have tagged it as leveldb, can you use leveldb? If you can, I'm almost positive it will be faster! – Kiril Feb 24 '12 at 05:08
  • Hi Ben, I work on TokuDB. Maybe I can help. Can you describe your workload a bit more? Would you feel comfortable sharing your exact table schema and most common SQL queries? In my experience, an average row size of 3k should not cause issues like this for TokuDB. – leif Feb 24 '12 at 05:21
  • Ben, I'm also a Tokutek employee. I'd also suggest that you run on the current version of TokuDB which is v5.2.7. Contact us directly if you want any assistance. – tmcallaghan Feb 24 '12 at 13:12
  • @Lirik， we also trying to test LevelDB, but have a little problem with the JNI Java edition. I want tag this question as "tokudb", but don't have enough Reputation to create this new tag. – Ben Feb 24 '12 at 14:14
  • @leif, i have update the question to include table schema. – Ben Feb 24 '12 at 14:31
  • @tmcallaghan, thanks for your advise, we'll test again using v5.2.7. – Ben Feb 24 '12 at 14:32
  • @ben it looks like you may be getting bitten by "hidden searches" that are part of maintaining uniqueness of a primary key. If you can avoid uniqueness checking, you should see an improvement. I think you should contact us by email so we can learn more about your application and help you figure this out. – leif Feb 24 '12 at 14:37

2 Answers2

0

If you are inserting your rows one at a time I'd recommend changing to a multi-row insert statement if possible as in "insert into events (eventId,content) values (1,'value 1'), (2,'value 2'), ..." as there can be quite a bit of overhead transactions. Also, TokuDB has improved performance with each release, I'd recommend running on a current release.

tmcallaghan
  • 1,292
  • 2
  • 10
  • 20
-1

the main feature of TokuDB is:

  • Hot schema changes:
    • Hotindex creation: TokuDB tables support insertions, deletions and queries with no down time while indexes are being added to that table. It uses Fractal Tree for indexing while innodb uses B-Tree
    • Hot column addition and deletion: TokuDB tables support insertions, deletions and queries with minimal down time when an alter table adds or deletes columns.

More details at what is TokuDB? How to install TokuDB on mysql 5.1?

Alaa
  • 4,471
  • 11
  • 50
  • 67