In my previous Post, we took a look at the file structure of the InnoDB primary key index. With the same table structure, but a slightly different dataset:

mysql> select * from test_innodb_growth limit 10;
+----+------------+--------+
| id | data       | spacer |
+----+------------+--------+
|  1 | a          | |      |
|  2 | aa         | |      |
|  3 | aaa        | |      |
|  4 | aaaa       | |      |
|  5 | aaaaa      | |      |
|  6 | aaaaaa     | |      |
|  7 | aaaaaaa    | |      |
|  8 | aaaaaaaa   | |      |
|  9 | aaaaaaaaa  | |      |
| 10 | aaaaaaaaaa | |      |
+----+------------+--------+
10 rows in set (0.00 sec)

All the rows after id = 10 have data = ‘aaaaa’. Let’s take a look at the secondary index ‘idxdata’. I recently had an argument with a client that claimed that a varchar is fully expanded in a secondary index. The following will prove it is not …

While going through the DATA TYPES section of the Certification Study Guide, I was refreshed of the ENUM datatype, which I rarely use.

I usually create individual tables for enumerations, so that new values can be added with just an insert, or deprecated values can be marked as such.

However, today I got to think about the performance issues involved in all that joining, and how could an ENUM column improve a select.

Here’s what I came up with:

mysql> create table project_types (
    -> id int unsigned not null auto_increment,
    -> name char(30) not null,
    -> primary key (id),
    -> index(name)
    -> ) Engine InnoDB;
Query OK, 0 rows affected (0.00 sec)

create the projects table

mysql> create table projects (
    -> id int unsigned not null auto_increment,
    -> name char(30) not null,
    -> project_type int unsigned not null,
    -> primary key (id), …

This is part 3 in my RAMSan Series.

While I am confident the read-only test was a reasonably good test ( I just needed to push more ), my mixed load test was marred by issues.  It was really a quick attempt to get a heavy read/write workload.  I ran into issues with how I wrote this so I will spare you the details.  Some flash devices are notoriously poor performing in writes, so its important to at least briefly look at this.  What I will share are the IOPS & latency numbers from this test.  The mixed workload does updates & selects at this point, these are a mix of PK updates, secondary index updates, etc.  These typically are built to run faster and smaller the the read-only IO bound workload.

By the 11th interval the Ramsan was pretty much complete.  The peaks are whats interesting…  lets look at this in a slightly different way.

So in the admittedly flawed mixed workload I am …

Since a long time people (including me) complained that storeconfigs was a real resource hog. Unfortunately for us, this option is so cool and useful.

What’s storeconfigs

Storeconfigs is a puppetmasterd option that stores the nodes actual configuration to a database. It does this by comparing the result of the last compilation against what is actually in the database, resource per resource, then parameter per parameter, and so on.

The actual implementation is based on Rails’ Active Record, which is a great way to abstract the gory details of the database, and prototype code easily …

This is part 2 of My RAMSan Series.

In my normal suite of benchmarks I typically run dbt2 & sysbench oltp benchmarks next…  and I did run then, but to be honest they just weren’t that interesting.  They showed an improvement over my intel ssd results I ran on frankenmatt,  but it was difficult to provide an apples to apples comparison.   The server hardware was way different ( cpu, memory, controller, etc ).  Plus I typically run a test -vs- non-flash then a test with flash, and ran tests with varying degrees of memory… the test box had 2GB of memory and sparse internal disk, so my normal test cycles were already in jeopardy.  For what I ran   I was pushing CPU limits long before I was hitting the IOPS I saw above.  In fact in a 100W test I ended up peaking @ 1200 iops, while the CPU was @ 100%.

The challenge is building an effective solution that will easily maximize MySQL …

I am going to get asked later today on this, so I figured I would head it off at the pass. So I am working on a new benchmark, and I am using an early test release of it to do some of my SSD testing. The quick 10K foot overview of what It does.

I build three large tables and several small tables. The large tables contain the exact same structure as each. Why 3? well its a cheap easy way to help ensure that the BP is turned over, and I can use them later to join to each other. One of the things I wanted to accomplish was to test different types of queries… I.e. full scan, index scan with low card, med card, high data skew, etc. So I added columns to help with each. Here is the structure of the large tables:

Well its about time I posted this:)  This is part 1 of 3 in my Ramsan series.

For those who have paid attention to my blog, know I love talking  IO!  I also love performance.  Absolutely love it.  Love disk, disk capactiy, io performance, solid state..  So as I march towards my UC session on MySQL Performance on Solid State Disk my goal is to try and test as many high end solid state disk systems as possible.  All the vendors have been great, giving me access to some really expensive and impressive toys.  I finished up testing Texas Memory System’s flash appliance the RamSAN 500 this week and wanted to post some numbers and some thoughts. TMS makes RamSAN appliances that merge disk and RAM into a really fast SANS.     First I go a ways back with TMS, I deployed and Oracle Rac installation on one of their …

DBT2, with over 40% conflicts, is an very challenging benchmark, especially for an MVCC based engine. And, as a result, it is not a test that an engine is automatically good at. InnoDB has been extensively optimized for DBT2, and it shows.

For the last few weeks I have had the opportunity to focus on PBXT DBT2 performance for the first time. I started with a memory bound DBT2 test and the current state of this work is illustrated below.

These results were achieved using MySQL 5.1.30 on an 8 core, 64-bit, Linux machine with an SSD drive and a 5 warehouse DBT2 database.

The dip off at 32 threads is left as an exercise for the reader :) Patches will be excepted!

So what were the major changes that lead to this improvement?

Don't Wait Too Long!

When I began the optimizations, PBXT …

Here are some common performance tuning concepts that I frequently run into. Please note that this really is only a basic introduction to performance tuning. For more in-depth tuning, it strongly depends on your systems, data and usage.

Server Variables

For tuning InnoDB performance, your primary variable is innodb_buffer_pool_size. This is the chunk of memory that InnoDB uses for caching data, indexes and various pieces of information about your database. The bigger, the better. If you can cache all of your data in memory, you’ll see significant performance improvements.

For MyISAM, there is a similar buffer defined by key_buffer_size, though this is only used for indexes, not data. Again, the bigger, the better.

Other variables that are worth investigating for performance tuning are:

query_cache_size - This can be very useful if you have a small number of read queries that are repeated frequently, …

It is well known that MySQL can use a lot of temporary files and that, using a ramdisk (tmpfs on Linux) can improve performance drastically as Matt has shown here. The problem with tmpfs is its limited size. You need just one query creating a huge temporary file that happens once per day to screw up the tmpfs scenario. I have done some fuse programming in my previous job and I think it is possible to use fuse to mix a tmpfs filesystem with a regular filesystem, the regular filesystem being used only when the tmpfs is full. Fuse has a lower performance level than a regular filesystem but I think the possibility to use tmpfs the vast majority of the queries needing a temporary file will overcome the performance limitation of fuse.

That is the explanation for the MiniWheatFS name, the frosted side is the tmpfs and the healthy side is the regular filesystem. There are many fuse …