About single threaded versus multithreaded databases performance H2 is a single threaded database with a good reputation regarding performance. Other databases are multi-threaded.

My question is: when does a multi-thread database become more interesting than an single thread database? How many users? How many processes? What is the trigger? Anyone has experience to share?

Summary


The usual bottleneck is disk access
SSD's are fast, but fragile (failure procedure is a must)
One long query on a single thread system will block all others
Configuring multi-threading system can be tricky
Multithreaded databases are beneficial even on single core systems
If there is one thing I can say about MySQL is that InnoDB, its transactional (ACID-compliant) storage engine, is indeed multithreaded. However, it is as multithreaded as YOU CONFIGURE IT !!! Even right "out of the box," InnoDB performs great in a single CPU environment given its default settings. To take advantage of InnoDB multithreading capabilities, you must remember to activate a lot of options.

innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks. UPDATE : As I learned firsthand from the Percona NYC Conference, you should set this to 0 in order to alert InnoDB Storage Engine to find the best number of threads for the environment it is running in.

innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.

innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.

innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).

innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64.

innodb_replication_delay imposes thread delay on a slave is innodb_thread_concurrency is reached.

innodb_read_ahead_threshold allows linear readings of the set number of extents (64 pages [page = 16K]) before switching to asynchronous reading.

Time would escape me if I named more options. You can read about them in MySQL's Documentation.

Most people are unaware of these features and a quite satisfied with InnoDB just doing ACID-compliant transactions. If you tweak any of these options, you do so at your own peril.

I have played with MySQL 5.5 Multiple Buffer Pool Instances (162GB in 9 buffer pools instances) and have attempted to have data auto-partitioned in memory this way. Some experts say that this should give you 50% performance improvement. What I got was a ton of thread locking that actually made InnoDB crawl. I switched to 1 buffer (162GB) and all was well again in the world. I guess you need Percona experts at your disposal to set this. I'll be at the Percona MySQL Conference in New York tomorrow and will ask about this if opportunity affords itself.

In conclusion, InnoDB behaves well now in a multi CPU server given its default settings for multithreaded operations. Tweaking them takes great care, great patience, great documentation, and great coffee (or Red Bull, Jolt, etc.).

Good morning, good evening, and good night !!!

UPDATE 2011-05-27 20:11

Came back from Percona MySQL Conference in New York on Thursday. What a conference. Learned a great deal, but I got an answer I will look into concerning InnoDB. I was informed by Ronald Bradford that setting innodb_thread_concurrency to 0 will let InnoDB decide the best course of action internally with thread concurrency. I will experiment with this further in MySQL 5.5.

UPDATE 2011-06-01 11:20

As far as one long query goes, InnoDB is ACID-compliant and operates very well using MultiVersion Concurrency Control. Transactions should be able carry isolation levels (repeatable reads by default) that prevents blocking others from accessing data.

As for multi core systems, InnoDB has come a long way. In the past, InnoDB could not perform well in a multicore environment. I remember having to run multiple mysql instances on a single server to get the multiple cores to distribute the multiple mysqld processes across the CPUs. This is no longer necessary, thanks to Percona, and later MySQL (eh, Oracle, saying that still makes me gag), as they have developed InnoDB into a more mature storage engine that can access the cores with simplicity without much tuning. The current instance of InnoDB today can operate well in a single core server.