For the my previous post, there was comment to suggest to test db_STRESS benchmark on XtraDB by Dimitri. And I tested and tuned for the benchmark. I will show you the tunings. It should be also tuning procedure for general heavy writing workloads.

At first, <tuning peak performance>. The next, <tuning purge operation> to stabilize performance  and to avoid decreasing performance.

<test condition>

Server:
PowerEdge R900, Four Quad Core E7320 Xeon, 2.13GHz, 32GB Memory, 16X2GB, 667MHz

db_STRESS:
32 sessions, RW=1, dbsize = 1000000, no thinktime

XtraDB: (mysql-5.1.39 + XtraDB-1.0.4-current)
innodb_io_capacity = 4000
innodb_support_xa = false
innodb_file_per_table = …

I'm happy to extend a warm welcome to two new members of the Percona team.

First is Yves Trudeau, about whom I can say many things:

• One of the top MySQL Cluster (NDB Cluster) experts in the world.
• An expert on all things High Availability, including DRBD and Heartbeat.
• Many years of experience with Huge Data.
• Half of the Waffle Grid team.
• A really nice person!

Yves joins us after a tenure of several years as a senior consultant at Sun/MySQL. Together with Matt Yonkovit, he plans to work on WaffleGrid (but as a new project under a new name, to be determined), and integration with XtraDB. Yves lives in Quebec with his family.

Next is Fernando Ipar. Fernando is our first dedicated Shift Support Captain[1]. Fernando specializes in MySQL, GNU/Linux, systems administration, and high availability. Fernando has been involved in computer programming since …

We’ve been able to do MySQL 5.1 binary tarballs for a bit now (great working together with Kristian Nielsen of Monty Program), but packages are bit more tricky. Peter has been working on Debian/Ubuntu while I’ve focused on RH/CentOS. The following is from an OurDelta (trial build run) RPM install on CentOS 5 x64:

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.38-maria-beta1-ourdelta (OurDelta - http://ourdelta.org/)

mysql> CREATE TABLE test.t1 (i int) ENGINE=PBXT;
Query OK, 0 rows affected (0.10 sec)

mysql> SHOW CREATE TABLE test.t1\G
*************************** 1. row ***************************
Table: test.t1
Create Table: CREATE TABLE `test.t1` (
`i` int(11) DEFAULT NULL
) ENGINE=PBXT DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO test.t1 values (1);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM test.t1;
+------+
| i    |
+------+
| …

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 the many blocks are clustered. And to reduce the maximum of the modified ages InnoDB needs to flush many of the blocks in a short time, if these are not flushed at all. Then the flushing storm affect the performance seriously.

We suggested the "adaptive_checkpoint" option of constant flushing to avoid such a flushing storm. And finally, the newest InnoDB Plugin 1.0.4 has the new similar option "adaptive_flushing" as native.

Let's check the adaptive flushing options at this post.

HOW THEY WORK

< adaptive_checkpoint=reflex (older method)>

Today's the day I fly to Los Angeles to teach a private training class, en route to Santa Clara/San Francisco for our public training workshops next week.

Our Montreal practice teach at Station-C went great - it was an opportunity to do a road test and iron out any kinks in the delivery.

What did I learn in the process?

• Solid examples provide context.  I didn't have the best SHOW GLOBAL STATUS data for the practice teach, but I've edited my slides and our official classes will have much better information.
• Operational issues are one of the most important things people want to hear more information on.  A fair number of students know that dropping an index is not as painless as it should be, but not everyone knows about tools like Flipper and MMM.  It's not the main focus of our InnoDB/XtraDB workshop, but we will cover how to solve these …

Note: This post is part 2 of 4 on building our training workshop.

Last week I talked about why you don't want to shard. This week I'm following up with the top 10 enhancements that XtraDB has over the built-in InnoDB included in MySQL 5.0 and 5.1.  Building this list was not really a scientific process - It's always difficult to say which feature is better than another, because a lot of it depends on the individual workload.  My ranking method was to pick the features that have the highest impact and are most applicable to all workloads first:

1. CPU scalability fixes - XtraDB improves performance on systems with multi-cpus (see docs …

OIN offers cash for patents. CentOS crisis averted. Microsoft denies GPL violation. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

# Open Invention Network offered individual inventors cash for patents, and acquired patents from V_Graph.

# The H Open reported that the management problems at CentOS are now resolved.

# Sam Ramji told Network World in detail why Microsoft believes its Linux IC code did not violate the GPL (from 15m 30s).

# Canonical delivered an on-premise version of …

Today, we are announcing that we're ready to offer training for InnoDB and XtraDB in Santa Clara and San Francisco.  The course was developed by Morgan Tocker with input from all our team - and covers a lot of the performance problems we run through in our consulting practice.

The Details:

14th Sept - Santa Clara
1 day intensive course
Cost: $300*

16th Sept - San Francisco
1 day intensive course
Cost: $300*

(* includes a copy of High Performance MySQL if you register before 31st Aug).

The delivery format:

Being only one day, we elected to deliver the course in a predominately lecture-format - but there will be a few opportunities to try examples.  For more information see the …

If you do not follow MariaDB development, I want to head up XtraDB has been commited to MariaDB server and will be included in binary releases of MariaDB (scheduled on end of August - September) as replacement of InnoDB storage engine. MariaDB will also include PBXT storage engine, Sphinx storage engine and few our non-InnoDB related patches (extended stats into slow-log)

Entry posted by Vadim | One comment

Add to: | …

The feature I announced some time ago http://www.mysqlperformanceblog.com/2009/06/08/impossible-possible-moving-innodb-tables-between-servers/ is now available in our latest releases of XtraBackup 0.8.1 and XtraDB-6.

Now I am going to show how to use it (the video will be also available on percona.tv).
Let's take tpcc schema and running standard MySQL ® 5.0.83, and assume we want to copy order_line table to different server. Note I am going to do it online, no needs to lock or shutdown server.

To export table you need XtraBackup, and you can …