Return to Answer

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.

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.

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.).

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.

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.

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.

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.

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.).

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.

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.

If there is one thing that 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 capbilities, you must remember to activate a lot of options.

Give morning, good evening, and good night !!!

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.

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

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.

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.