MySQL Performance Blog

One of our customers gave me a chance to run some benchmarks on 24-core (intel cpu based) server, and I could not miss it and ran few CPU-bound tasks there.

The goal of benchmarks was investigation of InnoDB-plugin and XtraDB scalability in CPU-bound load.
[read more...]

As you may know Sun / MySQL made release of community 5.0.75 only as source code release. We made binaries with our patchset.
Patches are mostly equal to build 10
http://www.mysqlperformanceblog.com/2008/12/11/mysql-binaries-percona-build10/, only change:
it includes bugfix for https://bugs.launchpad.net/percona-patches/+bug/308849

You can download binaries (RPMS x86_64) and sources with patches here
http://www.percona.com/mysql/5.0.75-b11/

We have a lot of customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work.  The first thing these have in common is that they’re generally some kind of loggable event.

The next characteristic of a lot of these systems (real or planned) is the desire for “real-time” analysis.  Our customers often want their systems to provide the freshest data to their own clients, with no delays.

Finally, the analysis is usually multi-dimensional.  The typical user wants to be able to generate summaries and reports in many different ways on demand, often to support the functionality of the application as well as to provide reports to their clients.  Clicks by day, by customer, top ads by clicks, top ads by click-through ratio, and so on for dozens of different types of slicing and dicing.

And as a result, one of the most common questions we hear is how to build high-performance systems to do this work. Let’s see some ways you can build the functionality you need and get the performance you need. Because I’ve built two such systems to manage online ads through Google Adwords, Yahoo, MSN and others, it’s easy and familiar for me to use the example of search engine marketing. I’ll do that throughout this article.

[read more...]

When your goal is to optimize application performance it is very important to understand what goal do you really have. If you do not have a good understanding of the goal your performance optimization effort may well still bring its results but you may waste a lot of time before you reach same results as you would reach much sooner with focused approach.

The time is critical for many performance optimization tasks not only because of labor associated expenses but also because of the suffering - slow web site means your marketing budget is wasted, customer not completing purchases, users are leaving to competitors, all of this making the time truly critical matter.
[read more...]

Peter said me that previous results http://www.mysqlperformanceblog.com/2008/12/18/xtradb-benchmarks-15x-gain/ are too marketing, and we should show other results also.

Here is the run for CPU Bound,or it would be more correctly to say in-cache benchmark, because there is a lot of CPU remains idle. This run is exactly the same as Disk Bound but with innodb_buffer_pool_size=8G which is more than enough to fit all data in memory.

The XtraDB is showing very modest gain of about 2.5% in this case which can be attributed to measurement error too.

[read more...]

I guess it is first reaction on new storage engine - show me benefits. So there is benchmark I made on one our servers. It is Dell 2950 with 8CPU cores and RAID10 on 6 disks with BBU, and 32GB RAM on board with CentOS 5.2 as OS. This is quite typical server we recommend to run MySQL on. What is important I used Noop IO scheduler, instead of default CFQ. Disclaimer: Please note you may not get similar benefits on less powerful servers, as most important fixes in XtraDB are related to multi-core and multi-disks utilization. Also results may be different if load is CPU bound.

[read more...]

So lets say you have .frm file for the table and you need to recover CREATE TABLE statement for this table. In particular when we do Innodb Recovery we often get .frm files and some mess in the Innodb tablespace from which we have to get data from. Of course we could relay on old backups (and we do ask for them for a different reason anyway) but there is never guaranty there were no schema changes in between.

So how to recover CREATE TABLE from .frm file ?

[read more...]

How much space would empty MyISAM table take ? Probably 8K for .frm file, 1KB for .MYI file and 0 for MYD file. .MYI file can be larger if you have many indexes.

How much space will Innodb take:
[read more...]

Today we officially announce our new storage engine, “Percona XtraDB“, which is based on the InnoDB storage engine. It’s 100% backwards-compatible with standard InnoDB, so you can use it as a drop-in replacement in your current environment. It is designed to scale better on modern hardware, and includes a variety of other features useful in high performance environments.

Percona XtraDB includes all of InnoDB’s ACID-compliant design and advanced MVCC architecture, and adds features, more tunability, more metrics, more scalability on many cores, and better memory usage. We choose features and fixes based on customer requests and on our best judgment of real-world needs. We have not included all the InnoDB patches available. For example Google’s well-known InnoDB patch set is omitted (at least for now).

The first version of our new storage engine is 1.0.2-1, which is forked from InnoDB-plugin-1.0.2. Percona XtraDB is released under GPL v2, as is InnoDB-plugin base source code. Percona XtraDB is released only under the GPL v2 with no dual-licensing, and commercial support is available from Percona.

So what’s new in the engine? Here is a list of new features and enhancements:

• INFORMATION_SCHEMA.XTRADB_ENHANCEMENTS. This table contains information about the differences between the Percona XtraDB and the same version number of standard InnoDB, so you can always learn what your engine can do. documentation
• Improvements to SHOW INNODB STATUS. We’ve added more memory information and lock information, and fixed problems with lock information. documentation
• Improvements to InnoDB IO. Improvements of InnoDB IO subsystem, such as multiple read and write threads, read-ahead control, control io capacity and adaptive checkpointing. documentation
• InnoDB RW-lock fixes Improvements to scalability for systems with 8+ cores. documentation
• Buffer pool fixes Improvements of buffer_pool scalability. documentation
• innodb_buffer_pool_pages Information about content of buffer_pool pages. documentation

Documentation is on http://www.percona.com/docs/wiki/percona-xtradb:start.

Percona XtraDB available :
* in source code from Launchpad https://launchpad.net/percona-xtradb, the version 1.0.2-1 you can get as bzr branch lp:percona-xtradb -r tag:percona-xtradb-1.0.2-1 percona-xtradb-1.0.2-1
* as source code in tar.gz http://www.percona.com/mysql/5.1.30/source/percona-xtradb-1.0.2-1.tar.gz
* as binaries, percona xtradb is compiled in into MySQL-5.1.30 RPMS
* as separated shared library, to use as drop-in plugin for existing MySQL-5.1.30 installation http://www.percona.com/mysql/5.1.30/binary/percona-xtradb-1.0.2-1-5.1.30.x86_64.tar.gz
* OurDelta will also be using Percona XtraDB for its upcoming 5.1 builds.

XtraDB is fully compatible with existing InnoDB tables 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 https://bugs.launchpad.net/percona-xtradb. You can also report bugs there. Also have setup two maillists http://groups.google.com/group/percona-discussion for General discussions and http://groups.google.com/group/percona-dev for development related questions.

We are looking for 6-month release cycle of XtraDB. First several releases may come faster, as many features are planned.

Why do we say it’s a new storage engine, and not just a patchset? Because we are taking this as a serious project to evolve a new storage engine that will eventually become much more than just small variations from standard InnoDB. Percona will improve this storage engine using our own ideas, as well as incorporate improvements specifically sponsored by customers. Please contact us if you would like to sponsor any specific features. Contact form: http://www.percona.com/contacts.html