PlanetMysql.ru — информация о СУБД MySQL

At MySQL, we know our users want Performance, Scalability, Reliability, and Availability, regardless of the platform the choose to deploy. We have always had excellent benchmarks on Linux, and with MySQL 5.5, we are also working hard on improving performance on Windows.

The original patch of improving Windows performance was developed by MySQL senior developer Vladislav Vaintroub; benchmarks by QA engineer Jonathan Miller. We integrated the patch into MySQL 5.5 release.

The following two charts show the comparison of MySQL 5.5 vs. MySQL 5.1 (plugin) vs. MySQL 5.1 (builtin) using sysbench:

MySQL 5.5 includes a Windows performance patch, plus other performance improvements. This Windows performance patch has two part: part one is to fix the inefficiency of InnoDB slow mutex implementation on Windows – implement slow mutex as CriticalSection; and part two is to take advantage of condition variables on Windows Vista or newer Windows operating systems. Condition variables are not supported on Windows Server 2003 and Windows XP/2000.

What if I have an old Windows on which condition variables are not supported? Well, you can still benefit from the new slow mutex implementation; but will not be able to take the advantage of condition variables. The same binary runs on old Windows too. The InnoDB does a dynamic checking during start-up, to see whether condition variables are supported by the operating system it runs on.

Another benefit from this patch is the reduced consumption of Windows kernel objects (or handles), which deserves another blog on its own.

Download MySQL 5.5 RC release to give it a try for yourself! Thanks to Vladislav and Jonathan for their hard work!

Hardware configuration and operating system

Intel x86_64, 4 CPU x 2 Cores , 3.166 GHz, 8GB RAM, and Windows Server 2008.

MySQL / InnoDB configuration

Relevant my.cnf parameters:

max_connections=500
max_connect_errors=50
table_cache=2048
query-cache-size=0
query-cache-type=0
disable-log-bin
transaction_isolation=REPEATABLE-READ
innodb-file-per-table=1
innodb_data_file_path=ibdata1:200M:autoextend
innodb_buffer_pool_size=4G
innodb_additional_mem_pool_size=20M
innodb_log_file_size=650M
innodb_log_files_in_group=2
innodb_log_buffer_size=16M
innodb_support_xa=0
innodb_doublewrite=1
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=1


At MySQL, we know our users want Performance, Scalability, Reliability, and Availability, regardless of the platform the choose to deploy. We have always had excellent benchmarks on Linux, and with MySQL 5.5, we are also working hard on improving performance on Windows.

The original patch of improving Windows performance was developed by MySQL senior developer Vladislav Vaintroub; benchmarks by QA engineer Jonathan Miller. We integrated the patch into MySQL 5.5 release.

The following two charts show the comparison of MySQL 5.5 vs. MySQL 5.1 (plugin) vs. MySQL 5.1 (builtin) using sysbench:

MySQL 5.5 includes a Windows performance patch, plus other performance improvements. This Windows performance patch has two part: part one is to fix the inefficiency of InnoDB slow mutex implementation on Windows – implement slow mutex as CriticalSection; and part two is to take advantage of condition variables on Windows Vista or newer Windows operating systems. Condition variables are not supported on Windows Server 2003 and Windows XP/2000.

What if I have an old Windows on which condition variables are not supported? Well, you can still benefit from the new slow mutex implementation; but will not be able to take the advantage of condition variables. The same binary runs on old Windows too. The InnoDB does a dynamic checking during start-up, to see whether condition variables are supported by the operating system it runs on.

Another benefit from this patch is the reduced consumption of Windows kernel objects (or handles), which deserves another blog on its own.

Download MySQL 5.5 RC release to give it a try for yourself! Thanks to Vladislav and Jonathan for their hard work!

Hardware configuration and operating system

Intel x86_64, 4 CPU x 2 Cores , 3.166 GHz, 8GB RAM, and Windows Server 2008.

MySQL / InnoDB configuration

Relevant my.cnf parameters:

max_connections=500
max_connect_errors=50
table_cache=2048
query-cache-size=0
query-cache-type=0
disable-log-bin
transaction_isolation=REPEATABLE-READ
innodb-file-per-table=1
innodb_data_file_path=ibdata1:200M:autoextend
innodb_buffer_pool_size=4G
innodb_additional_mem_pool_size=20M
innodb_log_file_size=650M
innodb_log_files_in_group=2
innodb_log_buffer_size=16M
innodb_support_xa=0
innodb_doublewrite=1
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=1


At MySQL, we know our users want Performance, Scalability, Reliability, and Availability, regardless of the platform the choose to deploy. We have always had excellent benchmarks on Linux, and with MySQL 5.5, we are also working hard on improving performance on Windows.

The original patch of improving Windows performance was developed by MySQL senior developer Vladislav Vaintroub; benchmarks by QA engineer Jonathan Miller. We integrated the patch into MySQL 5.5 release.

The following two charts show the comparison of MySQL 5.5 vs. MySQL 5.1 (plugin) vs. MySQL 5.1 (builtin) using sysbench:

MySQL 5.5 includes a Windows performance patch, plus other performance improvements. This Windows performance patch has two part: part one is to fix the inefficiency of InnoDB slow mutex implementation on Windows – implement slow mutex as CriticalSection; and part two is to take advantage of condition variables on Windows Vista or newer Windows operating systems. Condition variables are not supported on Windows Server 2003 and Windows XP/2000.

What if I have an old Windows on which condition variables are not supported? Well, you can still benefit from the new slow mutex implementation; but will not be able to take the advantage of condition variables. The same binary runs on old Windows too. The InnoDB does a dynamic checking during start-up, to see whether condition variables are supported by the operating system it runs on.

Another benefit from this patch is the reduced consumption of Windows kernel objects (or handles), which deserves another blog on its own.

Download MySQL 5.5 RC release to give it a try for yourself! Thanks to Vladislav and Jonathan for their hard work!

Hardware configuration and operating system

Intel x86_64, 4 CPU x 2 Cores , 3.166 GHz, 8GB RAM, and Windows Server 2008.

MySQL / InnoDB configuration

Relevant my.cnf parameters:

max_connections=500
max_connect_errors=50
table_cache=2048
query-cache-size=0
query-cache-type=0
disable-log-bin
transaction_isolation=REPEATABLE-READ
innodb-file-per-table=1
innodb_data_file_path=ibdata1:200M:autoextend
innodb_buffer_pool_size=4G
innodb_additional_mem_pool_size=20M
innodb_log_file_size=650M
innodb_log_files_in_group=2
innodb_log_buffer_size=16M
innodb_support_xa=0
innodb_doublewrite=1
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=1


At MySQL, we know our users want Performance, Scalability, Reliability, and Availability, regardless of the platform the choose to deploy. We have always had excellent benchmarks on Linux, and with MySQL 5.5, we are also working hard on improving performance on Windows.

The original patch of improving Windows performance was developed by MySQL senior developer Vladislav Vaintroub; benchmarks by QA engineer Jonathan Miller. We integrated the patch into MySQL 5.5 release.

The following two charts show the comparison of MySQL 5.5 vs. MySQL 5.1 (plugin) vs. MySQL 5.1 (builtin) using sysbench:

MySQL 5.5 includes a Windows performance patch, plus other performance improvements. This Windows performance patch has two part: part one is to fix the inefficiency of InnoDB slow mutex implementation on Windows – implement slow mutex as CriticalSection; and part two is to take advantage of condition variables on Windows Vista or newer Windows operating systems. Condition variables are not supported on Windows Server 2003 and Windows XP/2000.

What if I have an old Windows on which condition variables are not supported? Well, you can still benefit from the new slow mutex implementation; but will not be able to take the advantage of condition variables. The same binary runs on old Windows too. The InnoDB does a dynamic checking during start-up, to see whether condition variables are supported by the operating system it runs on.

Another benefit from this patch is the reduced consumption of Windows kernel objects (or handles), which deserves another blog on its own.

Download MySQL 5.5 RC release to give it a try for yourself! Thanks to Vladislav and Jonathan for their hard work!

Hardware configuration and operating system

Intel x86_64, 4 CPU x 2 Cores , 3.166 GHz, 8GB RAM, and Windows Server 2008.

MySQL / InnoDB configuration

Relevant my.cnf parameters:

max_connections=500
max_connect_errors=50
table_cache=2048
query-cache-size=0
query-cache-type=0
disable-log-bin
transaction_isolation=REPEATABLE-READ
innodb-file-per-table=1
innodb_data_file_path=ibdata1:200M:autoextend
innodb_buffer_pool_size=4G
innodb_additional_mem_pool_size=20M
innodb_log_file_size=650M
innodb_log_files_in_group=2
innodb_log_buffer_size=16M
innodb_support_xa=0
innodb_doublewrite=1
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=1


At MySQL, we know our users want Performance, Scalability, Reliability, and Availability, regardless of the platform the choose to deploy. We have always had excellent benchmarks on Linux, and with MySQL 5.5, we are also working hard on improving performance on Windows.

The original patch of improving Windows performance was developed by MySQL senior developer Vladislav Vaintroub; benchmarks by QA engineer Jonathan Miller. We integrated the patch into MySQL 5.5 release.

The following two charts show the comparison of MySQL 5.5 vs. MySQL 5.1 (plugin) vs. MySQL 5.1 (builtin) using sysbench:

MySQL 5.5 includes a Windows performance patch, plus other performance improvements. This Windows performance patch has two part: part one is to fix the inefficiency of InnoDB slow mutex implementation on Windows – implement slow mutex as CriticalSection; and part two is to take advantage of condition variables on Windows Vista or newer Windows operating systems. Condition variables are not supported on Windows Server 2003 and Windows XP/2000.

What if I have an old Windows on which condition variables are not supported? Well, you can still benefit from the new slow mutex implementation; but will not be able to take the advantage of condition variables. The same binary runs on old Windows too. The InnoDB does a dynamic checking during start-up, to see whether condition variables are supported by the operating system it runs on.

Another benefit from this patch is the reduced consumption of Windows kernel objects (or handles), which deserves another blog on its own.

Download MySQL 5.5 RC release to give it a try for yourself! Thanks to Vladislav and Jonathan for their hard work!

Hardware configuration and operating system

Intel x86_64, 4 CPU x 2 Cores , 3.166 GHz, 8GB RAM, and Windows Server 2008.

MySQL / InnoDB configuration

Relevant my.cnf parameters:

max_connections=500
max_connect_errors=50
table_cache=2048
query-cache-size=0
query-cache-type=0
disable-log-bin
transaction_isolation=REPEATABLE-READ
innodb-file-per-table=1
innodb_data_file_path=ibdata1:200M:autoextend
innodb_buffer_pool_size=4G
innodb_additional_mem_pool_size=20M
innodb_log_file_size=650M
innodb_log_files_in_group=2
innodb_log_buffer_size=16M
innodb_support_xa=0
innodb_doublewrite=1
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=1


I’ve been noticing an undeniable trend in my consulting engagements in the last year or so, and when I vocalized this today, heads nodded all around me. Everyone sees a growth in the number of cases where otherwise well-optimized systems are artificially limited by InnoDB contention problems.

A year ago, I simply wasn’t seeing the need for analysis of GDB backtraces en masse. These days, I’m writing custom tools to gather and analyze backtraces. A year ago, I simply looked at the SEMAPHORE section of SHOW INNODB STATUS. These days I’m writing custom tools to aggregate and reformat that data so I can interpret it more easily. And I’m actually seeing cases of this type of problem multiple times every week. I remember the first time I ran into a server that was literally optimized to the limit, but struggling under the load. It was something new for me, not that long ago. Oh, I’d seen it before, plenty, but was always able to point out where something could be improved without changing InnoDB itself. Now it’s commonplace: schemas are fine — check. Queries are all well-indexed — check. Everything else — check. InnoDB is bottlenecked and absolutely nothing can be improved — check.

Part of the difference is the rapidly improving hardware. It’s getting hard to buy a server with fewer than 8 or even 16 cores, and 16GB of RAM feels like something I’d install in a wristwatch. But I also suspect that if I’d been characterizing the workload of servers over time in a way that was easy to compare, I’d see a clear trend towards bigger data and more queries per second. We’re just pushing MySQL + InnoDB harder today than we ever have before.

What can be done? Well, InnoDB needs to be improved, that’s all. Oracle, Percona, Google, Facebook and others are working on it, and in many cases these efforts have yielded dramatic results. But there is still much room for improvement.

Related posts:

1. Hindsight on a scalable replacement for InnoDB A while ag
2. What do you know about Oracle’s InnoDB+ storage engine? That
3. Xtrabackup is for InnoDB tables too, not just XtraDB Just thoug

Related posts brought to you by Yet Another Related Posts Plugin.