Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com May 24 '11 at 20:30

This question came from our site for professional and enthusiast programmers.

    
Thread means "thread or process" for the purpose of this question as far as I can tell - eg postgres is not multi-threaded but the question is not trying to compare (H2, postgres) against (Oracle, SQL Server etc) –  Jack Douglas Jun 1 '11 at 8:52

6 Answers 6

up vote 14 down vote accepted
+25

Here is my opinion:

Usually the bottleneck (or slowest part) of a DB system is the disk. The CPU only spikes during arithmetic operations, processing, or any other task that the CPU does. With proper architecture, multithreading can help to offset the load of a query onto the CPU instead of doing the slow disk reads/writes. There are cases where it is faster to calculate a value using the CPU cycles rather than to create a computed column (that was previously saved to disk) and read this column from disk.

In some RDBMS there is a temporary DB (tempdb) that is used by all the DBs on that instance for sorting, hashing, temporary variables, etc... Multithreading and splitting up this tempdb files can be used to improve the throughput of the tempdb, thereby improving overall server performance.

Using multithreading (parallelism), the result set of a query can be split up to be processed on the different cores of the server, rather than using one core alone. This feature does not always improve the performance, but there are cases where it does, and hence the feature is available.

The threads available to the DB are used for many purposes: reading/writing to disk, user connections, background jobs, locking/latching, network IO, etc... Depending on the OS architecture the threads are preemptively fed to the CPU and are managed using waits and queues. If the CPU can crunch these threads pretty quickly then the wait times will be low. A multi-threaded DB will be faster than a single-threaded DB, as in a single-threaded DB there will be the overhead of recycling only one thread rather than having other treads readily available.

Scalability also becomes an issue, as more threads will be required to manage and execute the scaled DB system.

share|improve this answer
    
Thanks for the insight. I hear people praising solid state drives. I guess investing in those is probably the best thing to do after making sure queries are well written and the application is reasonably parallelized. –  JVerstry May 26 '11 at 16:06
    
@Stan - I think multithreaded in this context means something different, ie that all transactions are serialized as Luke mentions in his answer. –  Jack Douglas May 26 '11 at 19:09
    
@JVerstry ~ No, not really. Go read Jeff Atwood's thoughts on SSDs ... they have a high failure rate. The best thing to do is to properly index the data and to have well written queries. –  jcolebrand May 26 '11 at 19:16
    
@jcolebrand Ok, he seems to advocate them for speed only with a strong backup system for when they fail –  JVerstry May 26 '11 at 19:24
2  
@Jverstry ~ Yes, and if you understand that concept, and are ok with it, and don't mind rebuilding your entire production environment (or waiting for an automated failover to kick in and then rebuilding at some point in that near future) then go for it, they will make things faster still, yes. –  jcolebrand May 26 '11 at 19:29

A multithreaded database will only benefit you when you have more than 1 parallel query going to the database. It depends on number of users you have. If you have more than ten users working on the application at the same time, most likely they are going to produce more than one query on the database at the same time.

Moreover, a multithreaded database can only benefit when there's multi core on the CPU. If there's single core, multi threaded database has to queue the job and execute them sequentially on the single core. When there's multi-core, each core can run one thread in parallel. Thus better performance.

Does this answer your query?

share|improve this answer
6  
Multithreaded databases are beneficial even on single core systems. It prevents a single long-running query from blocking all other database access, plus you could have several threads waiting on disk or network I/O, while another thread is actively parsing queries, processing prefetched data, etc. –  Anonymous May 24 '11 at 17:02

As soon as you have multiple concurrent users or processes, or even a single process with multi-threaded database access, having a database that supports threading will become potentially interesting.

H2 is thread-safe, but serializes all requests to the database, which may become a potential performance issue in a heavy load scenario. Whether this is actually the case for a particular project depends on a combination of your performance requirements, the number of threads/users/processes accessing the database, the frequency of queries executed by these threads, and the average and worst-case performance of your queries.

For instance if your performance requirements are to have a response within a second, you have no more than 10 concurrent users executing a single query that takes 0.05 seconds to execute, a single-threaded database would still allow you to hit those goals (though multithreaded would likely already give a noticeable performance boost). Given the same scenario with a single potential query with a worst-case performance of half a second though, serializing your database access won't allow you to meet your performance goals anymore.

If you're currently using H2 on your project, I would advise you to run a profiler against your codebase under a load scenario (just kick off an x number of threads hitting your code concurrently using some typical usecases). This will give you actual metrics regarding the performance and bottlenecks in your codebase, instead of just theorizing. If this shows your requests spending a large percentage of their time just waiting to access the database, it's time to move to a threaded database.

share|improve this answer
    
Does H2 serialize all requests - or just DML? –  Jack Douglas May 27 '11 at 4:05

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-complaint transactions. If you tweek 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. Tweeking 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 sinlge core server.

share|improve this answer
    
Sorry I missed you at the conference. It was very enlightening –  Derek Downey May 29 '11 at 20:59
    
"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" - According to the documentation, a setting of 0 means "InnoDB [creates] as many threads as it needs." This does not mean it creates the best number of threads, it means it doesn't reuse threads in a threadpool. This will degrade performance with a large number of threads, as these performance benchmarks show. –  BlueRaja - Danny Pflughoeft May 21 '13 at 0:20
    
@BlueRaja-DannyPflughoeft During the conference, Ronald Bradford (the most prolific MySQL blogger on Earth) told me directly in front of about 50 people (during his seminar on InnoDB) to set to 0. I was not about to argue with him. In addittion, MySQL 5.5, which was just being released, has the default value for innodb_thread_concurrency at 0. MySQL 5.6 is the same. I believe InnoDB in MySQL 5.6 can handle 48 cores before degrading. Nevertheless, thank you for expressing faith in setting the value because many set it to weird values without knowing the effects. –  RolandoMySQLDBA May 21 '13 at 0:35

From what I can tell, "single-threaded" is a bit of a misnomer for H2. The point is that it serializes all transactions (ie does them one at a time).

The crucial question regarding whether that is "ok" or not for your application is not "How many users?" or even "How many processes?", but "How long are my transactions going to take?"

If all your transactions are sub-second that may be fine, if some take several hours to complete, that may not be fine as all other pending transactions will be waiting for them to finish. The decision about whether that is "fine" or not will depend on your own performance requirements - ie how long is an acceptable wait for my users hitting the database with transactions.

--EDIT

It seems that H2 doesn't really serialize transactions - just DML. In other words lots of short updates within a single long transaction will not block other updates. However unless you are using the experimental MVCC feature, table locking means this has a similar effect in practice. There is also an experimental "multi_threaded" feature but it cannot be used at the same time as MVCC

share|improve this answer

Quoting bits and pieces from the PostgreSQL site... Please note that I've absolutely no idea of the merits of these arguments -- they merely didn't fit in a comment.

From the Developer FAQ ("Why threads aren't used..."):

http://wiki.postgresql.org/wiki/Developer_FAQ#Why_don.27t_you_use_threads.2C_raw_devices.2C_async-I.2FO.2C_.3Cinsert_your_favorite_wizz-bang_feature_here.3E.3F

Threads are not currently used instead of multiple processes for backends because: (...)

  • An error in one backend can corrupt other backends if they're threads within a single process
  • Speed improvements using threads are small compared to the remaining backend startup time.
  • Sharing of read-only executable mappings and the use of shared_buffers means processes, like threads, are very memory efficient
  • Regular creation and destruction of processes helps protect against memory fragmentation, which can be hard to manage in long-running processes

From the Todo list ("Features we do not want"):

http://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want

All backends running as threads in a single process (not wanted)

This eliminates the process protection we get from the current setup. Thread creation is usually the same overhead as process creation on modern systems, so it seems unwise to use a pure threaded model, and MySQL and DB2 have demonstrated that threads introduce as many issues as they solve. (...)

So, again... I've absolutely no idea of the merits of the above. It was merely too long to fit in a comment.

share|improve this answer

protected by Paul White May 24 at 3:31

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.