MySQL Performance Blog

We finally managed to get Intel X25-E SSD drive into our lab. I attached it to our Dell PowerEdge R900. The story making it running is worth separate mentioning – along with Intel X25-E I got HighPoint 2300 controller and CentOS 5.2 just could not start with two RAID controllers (Perc/6i and HighPoint 2300). The problem was solved by installing Ubuntu 8.10 which is currently running all this system. Originally I wanted to publish some nice benchmarks where InnoDB on SSD outperforms RAID 10, but recently I faced issue which can make previous results inconsistent.
[read more...]

Today we announce release 1.0.2-3 of our XtraDB storage engine.

Here is a list of enhancements:

• Move to MySQL 5.1.31
• Scalability fix — ability to use several rollback segments

Increasing the number of rseg may be helpful for CPU scale of write-intentional workloads. See benchmark results.

• Scalability fix — replaced page_hash mutex to page_hash read-write lock. See benchmark results.
• Fix broken group commit in InnoDB

As you know, the group commit of InnoDB doesn’t work after MySQL 5.0 with binlog.
We pretend to fix this and added “innodb_enable_unsafe_group_commit=[0|1]” parameter. Note if you set this parameter to “1″ record order of binlog and InnoDB transaction log could be not same. But because InnoDB manages transactions in correct order most users may not have problem with this.

• New parameters in the XtraDB io patches:
  • innodb_ibuf_max_size — Restrict innodb-insert-buffer size
  • innodb_ibuf_active_contract — Enabling it makes the each user threads positive to contract the insert buffer as possible in asynchronous
  • innodb_ibuf_accel_rate — This parameter is additional tuning the amount of insert buffer processing by background thread.
  • innodb_flush_neighbor_pages — When you use the storage which don’t have “head seek latency” (e.g. SSD or enough Write-Buffered), 0 may show better performance.
• Changed parameter for control read ahead activity. Now it accepts string values:
  • innodb_read_ahead = [none|random|linear|both]
• Deb builds for Ubuntu

Percona XtraDB 1.0.2-3 (Sapporo) available in source and several binary packages.

XtraDB is compatible with existing InnoDB tables (unless you used innodb_extra_undoslots) and we are going to keep compatibility in further releases. We are open for features requests for new engine and ready to accept community patches. You can monitor Percona’s current tasks and further plans on the Percona XtraDB Launchpad project. You can also request features and report bugs there. Also we have setup two maillists for General discussions and for Development related questions.

There are few terms you need to have a good understanding if you’re working with high volume systems. I commonly see these mixed and people not understanding the difference between them.

Performance – The performance comes down to performing up to users expectations (or expectations of your boss) which drills down to two important metrics – response time also sometimes called latency and throughput also called capacity. Response time applies to even single user and mostly critical in applications when a complex task needs to be performed such as report generation or search. It is just unacceptable if search takes a minute even if system has a capacity running for 100 of them at the same time. The response time is the most important aspect of performance because when we define system capacity we also define conditions on response time. For example system can handle 100 transactions a second with 95 percentile response time of 1 second.
[read more...]