After several important fixes to our patches we made binaries for build12.
Fixes include:
Control of InnoDB insert buffer to address problems Peter mentioned https://www.percona.com/blog/2009/01/13/some-little-known-facts-about-innodb-insert-buffer/, also check Bug 41811 to see symptoms of problem with Insert buffer.
https://www.percona.com/docs/wiki/patches:innodb_io_patches
* innodb_flush_neighbor_pages (default 1) – When the dirty page are flushed (written to datafile), this parameter determines whether the neighbor pages in the datafile are also flushed at the same time or not. If you use the storage which don’t have “head seek delay†(e.g. SSD or enough Write-Buffered), 0 may show better performance. 0:disable, 1:enable
* innodb_ibuf_max_size (default [the half of innodb_buffer_pool_size](bytes)) – This parameter is startup parameter. If the lower value is set than the half of innodb_buffer_pool_size, it is used as maximum size of insert buffer. To restrict to the too small value (e.g. 0) is not recommended for performance. If you don’t like the insert buffer growing bigger, you should use the following parameters instead. (* If you use very fast storage, small value (like several MB) may show better performance.)
* innodb_ibuf_accel_rate (default 100(%)) – This parameter is additional tuning the amount of insert buffer processing by background thread. Sometimes, only innodb_io_capacity is insufficient to tune the insert buffer.
* innodb_ibuf_active_contract (default 0) – By default (same to normal InnoDB), the each user threads do nothing about contracting the insert buffer until the insert buffer reaches its maximum size. 1 makes the each user threads positive to contract the insert buffer as possible in asynchronous.
Second important fix introduces variable use_global_long_query_time, which allows all current threads see change of long_query_time. By default value set in SET GLOBAL long_query_time=N command is visible only on new established connection, which is problem if you have pre-established connection pool, say in Java or Ruby on Rails application. With use_global_long_query_time=true even all current threads will respect SET GLOBAL long_query_time=N. The feature made for EngineYard, hosting provider for Ruby on Rails application.
You can download binaries (RPMS x86_64) and sources with patches here
https://www.percona.com/mysql/5.0.75-b12/
Vadim,
Will making innodb_ibuf_max_size=0 stop using insert buffer at all ? Is it possible to disable this functionality all together ?
Also can you explain what exactly innodb_ibuf_accel_rate is because “this is additional tuning variable” is not very clear. Same about innodb_ibuf_active_contract – I do not understand what exactly this option does.
Peter,
innodb_ibuf_max_size=0 does not disable insert buffer activity, there still will be operation to perform merge operations, and innodb_ibuf_max_size=0 can make it bad. Probably better to use innodb_ibuf_max_size=1M.
Other options will comment Yasufumi.
I downloaded https://www.percona.com/mysql/5.0.75-b12/source/mysql-5.0.75-percona-b12-src.tar.gz, but each time I try to decompress it, I get an error:
mysql-5.0.75/Docs/INSTALL-BINARY
mysql-5.0.75/Docs/manual.chm
gzip: stdin: invalid compressed data–format violated
tar: Unexpected EOF in archive
tar: Unexpected EOF in archive
tar: Error is not recoverable: exiting now
Could it be that the archive is corrupted on the server, or is it a problem on my side?
Thanks a lot.
(Just forgot to tell that I tried 3 times to download it, just to be sure it wasn’t just a bad transfert…)
kyle,
Repacked, and copied again – please try now
Vadim,
Can we make an option to disable insert buffer completely in case it is set to 0 – to basically have non unique indexes using the same code as unique indexes ? I think this will be valuable (or at least nice to research) for cases when random IO is cheap.
Vadim,
Work like a charm now ! Thanks a lot.
Peter,
innodb_ibuf_max_size=0 never inserts to insert buffer. So, it is equal to disable insert buffer. But it make all of insert to secondary index synchronous (read IO) and all of the such inserts always must be slower. I never recommend innodb_ibuf_max_size=0. If you don’t like insert buffer growing, I recommend innodb_ibuf_max_size=[several MB] and innodb_ibuf_active_contract=1 instead.
And, Currently, innodb_io_capacity affects to all of background IO activity (flush, insert buffer, etc…). I think it is insufficient of degree of freedom. Especially, insert buffer activities are not only affected by IO capacity. For now, innodb_ibuf_accel_rate is added to increase the insert buffer activity.
The amount of insert buffer merge per 1 calling ibuf_contract_for_n_pages() from background:
[default activity] * innodb_io_capacity(=%) * innodb_ibuf_accel_rate(%)
I think we will need reordering these parameters someday.
Thanks Yasufumi,
It would be good to check different values in benchmarks. I understand disabling insert buffer will make insert slower but it also mean no merge will be needed for future select.
Unless there is significant IO saving by merges it is just pushes the job to other threads (or background thread)
Hi,
you don’t offer your patched mysql version for Win32, do you?
Igor,
Right. We do not have builds on Windows because we do not have customer demand for that. We would appreciate feedback though from someone building it on windows.
Hi Peter,
I’ll give it a try.
Can you give me your configuration otions (e.g. “configure WITH_INNOBASE_STORAGE_ENGINE…”), so I can try to build a comparable version?
Thanks..
Igor,
configure line is
./configure ‘–disable-shared’ ‘–with-server-suffix=-percona-highperf-b12’ ‘–without-embedded-server’ ‘–with-innodb’ ‘–with-csv-storage-engine’ ‘–with-archive-storage-engine’ ‘–with-blackhole-storage-engine’ ‘–with-federated-storage-engine’ ‘–without-bench’ ‘–with-zlib-dir=bundled’ ‘–with-big-tables’ ‘–enable-assembler’ ‘–enable-local-infile’ ‘–with-mysqld-user=mysql’ ‘–with-unix-socket-path=/var/lib/mysql/mysql.sock’ ‘–with-pic’ ‘–prefix=/’ ‘–with-extra-charsets=complex’ ‘–exec-prefix=/usr’ ‘–libexecdir=/usr/sbin’ ‘–libdir=/usr/lib64’ ‘–sysconfdir=/etc’ ‘–datadir=/usr/share’ ‘–localstatedir=/var/lib/mysql’ ‘–infodir=/usr/share/info’ ‘–includedir=/usr/include’ ‘–mandir=/usr/share/man’ ‘–enable-thread-safe-client’ ‘–with-comment=MySQL Percona High Performance Edition (GPL)’ ‘–with-readline’ ‘CC=gcc’ ‘CFLAGS=-O2 -g’ ‘CXXFLAGS=-O2 -g’ ‘CXX=gcc’ ‘LDFLAGS=’
you may also get it running mysqlbug from distribution.
Do you guys have customers using SSDs?
Kevin,
We have bunch of customers who are evaluating SSD to use in production, but I can’t recall one running in real production yet.
Vadim,
We actually have couple. But surely much more are evaluating them now.
We are using SSDs in production, we already are collecting I/O and MySQL data from those servers with approx 8000 queries/sec. Could that kind of data be of interest for you?
is innodb_ibuf_max_size allocated inside innodb_buffer_pool_size or not? I believe it’s inside otherwise we need to leave 50% of buffer pool size memory just for the insert buffer.