Tag - InnoDB

InnoDB thread concurrency

InnoDB has a mechanism to regulate count of threads working inside InnoDB.
innodb_thread_concurrency is variable which set this count, and there are two friendly variables
innodb_thread_sleep_delay and innodb_concurrency_tickets. I’ll try to explain how it works.
MySQL has pluginable architecture which divides work between mysql common code
(parser, optimizer) and storage engine. From storage engine’s point of view it […]

Read more

InnoDB memory usage

There are many questions how InnoDB allocates memory. I’ll try to give some explanation about the memory allocation at startup.
Some important constants:
NBLOCKS=count of block in innodb_buffer_pool = innodb_buffer_pool_size / 16384
OS_THREADS= if (innodb_buffer_pool_size >= 1000Mb) = 50000
else if (innodb_buffer_pool_size >= 8Mb) = 10000
else = 1000 (it’s true for *nixes, for Windows there is a bit […]

Read more

Jeremy Cole on MySQL Replication

Jeremy Cole recently posted very nice post about MySQL Replication Performance. There are however few points I should comment on.

Jeremy Speaks about MyISAM and Innodb in the same terms, in fact they are quite different for Replication. As you probably know MyISAM uses buffered IO so if master crashes you’re out of sync […]

Read more

Group commit and XA

Returning to post Group commit and real fsync I made several experiments:
I ran sysbench update_key benchmarks without —log-bin, with —log-bin, and with —log-bin and —innodb-support-xa=0 (default value is 1). Results (in transactions / sec)

threads
without —log-bin
—log-bin
—log-bin and
—innodb_support-xa=0

1
1218.68
614.94
1010.44

4
2686.36
667.77
1162.60

16
3993.59
666.14
1161.56

64
3630.55
665.18
1151.36

So we can see group commit is not only broken when XA is enabled but also if XA […]

Read more

Mess with innodb_thread_concurrency

In MySQL 5.0.19 the meaning of innodb_thread_concurrency variable was changed (yeah, again).
Now innodb_thread_concurrency=0 means unlimitied count of concurrent threads inside InnoDB. It’s logical, but there was long way. In MySQL versions below 5.0.8 for unlimited threads you had to set innodb_thread_concurrency over 500 (and default value for innodb_thread_concurrency was 8 ).
Starting with MySQL 5.0.8, […]

Read more

Innodb Fuzzy checkpointing woes

As you might know Innodb storage engines uses Fuzzy Checkpointing technique as part of it recovery strategy.  It is very nice approach which means database never needs to “stall” to perform total modified pages flush but instead flushing of dirty pages happens gradually in small chunks so database load is very even.
This works great […]

Read more