April 19, 2014

kernel_mutex problem cont. Or triple your throughput

This is to follow up my previous post with kernel_mutex problem. First, I may have an explanation why the performance degrades to significantly and why innodb_sync_spin_loops may fix it. Second, if that is correct ( or not, but we can try anyway), than playing with innodb_thread_concurrency also may help. So I ran some benchmarks with […]

kernel_mutex problem. Or double throughput with single variable

Problem with kernel_mutex in MySQL 5.1 and MySQL 5.5 is known: Bug report. In fact in MySQL 5.6 there are some fixes that suppose to provide a solution, but MySQL 5.6 yet has long way ahead before production, and it is also not clear if the problem is really fixed. Meantime the problem with kernel_mutex […]

How Innodb Contention may manifest itself

Even though multiple fixes have been implemented in Percona Server and MySQL 5.5, there are still workloads in which case mutex (or rw-lock) contention is a performance limiting factor, helped by ever growing number of cores available in the systems. It is interesting though the contention may manifest itself in the different form from the […]

Effect from innodb log block size 4096 bytes

In my post MySQL 5.5.8 and Percona Server: being adaptive I mentioned that I used innodb-log-block-size=4096 in Percona Server to get better throughput, but later Dimitri in his article MySQL Performance: Analyzing Percona’s TPCC-like Workload on MySQL 5.5 sounded doubt that it really makes sense. Here us quote from his article: “Question: what is a […]

Tuning InnoDB Concurrency Tickets

InnoDB has an oft-unused parameter innodb_concurrency_tickets that seems widely misunderstood. From the docs: “The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread […]

Which adaptive should we use?

As you may know, InnoDB has 2 limits for unflushed modified blocks in the buffer pool. The one is from physical size of the buffer pool. And the another one is oldness of the block which is from the capacity of transaction log files. In the case of heavy updating workload, the modified ages of […]

5.0.77 / 5.0.82 -build16 Percona binaries

Dear community, We are pleased to announce the build16 of MySQL server® with Percona patches. Since the build13 there was a couple of customer specific releases, which explains cutover in numbering and a pause between the builds. Also we prepared build for both 5.0.77 and 5.0.82 versions. Since that time new patches were added: profiling_slow.patch […]

Linux schedulers in tpcc like benchmark

I mentioned earlier that IO scheduler CFQ coming by default in RedHat / CentOS 5.x may be not so good for MySQL. And yesterday one customer reported that just changing cfq to noop solved their InnoDB IO problems. I ran tpcc scripts against XtraDB on our Dell PowerEdge R900 server (16 cores, 8 disks in […]

Another scalability fix in XtraDB

Recent scalability fixes in InnoDB and also Google’s and your SMP fixes almost made InnoDB results acceptable in primary key lookups queries, but secondary indexes were forgotten for some time. Now having Dell PowerEdge R900 on board (16CPU cores, 16GB RAM) I have some time for experiments, and I played with queries

Fix of InnoDB/XtraDB scalability of rollback segment

Recently I wrote about InnoDB scalability on 24-core box, and we made research of scalability problems in sysbench write workload (benchmark emulates intensive insert/delete queries). By our results the problem is in concurrency on rollback segment, which by default is single and all transactions are serialized accessing to segment. Fortunately InnoDB internally has mechanism to […]