I did some drawing exercise and put XtraDB / InnoDB internals in Visio diagram:

The XtraDB differences and main parameters are marked out.

PDF version is there http://www.percona.com/docs/wiki/percona-xtradb:internals:start.

Entry posted by Vadim | 4 comments

Add to: | …

The video of my 5-minute Ignite talk on XtraDB is up on YouTube. Ignite talks have exactly 20 slides on auto-advance every 15 seconds. “Enlighten us, and make it fast!” It’s better than a lightning talk. I explained the difference between Percona XtraDB and other technologies such as standard MySQL, OurDelta, and MariaDB.

Related posts:

1. Xtrabackup is for InnoDB tables too, not just XtraDB Just thoug
2. Recap of CPOSC 2009, plus slides Yesterday
3. Learn about mk-query-digest …

MySQL-5.5.4 ® is the great release with performance improvements, let's see how it performs in
tpcc-like workload.

The full details are on Wiki page
http://www.percona.com/docs/wiki/benchmark:mysql:554-tpcc:start

I took MySQL-5.5.4 with InnoDB-1.1, tpcc-mysql benchmark with 200W ( about 18GB worth of data),
InnoDB log files are 3.8GB size, and run with different buffer pools from 20GB to 6GB. The storage is FusionIO 320GB MLC card with XFS-nobarrier. .

While the raw results are available on Wiki, there are graphical results.

I intentionally put all line on the same graph to show trends.

…

I haven’t been blogging much about the changes in MySQL for a while. But the MySQL and InnoDB engineers have been doing a ton of work over the last couple years, and now it’s seeing the light of day, so it’s time to offer words of congratulations and appreciation about that.

I was holding my breath for a big-splash 5.5 GA announcement at last week’s conference, but I was wrong. Still, there’s a lot to talk about in 5.5, with a dozen or more substantial blog posts from the InnoDB and MySQL teams alone over the last week or so! Here are a few choice tidbits of the good, the bad, and the ugly.

InnoDB is the default storage engine

“No big deal,” I thought. “Serious users do this anyway.” But then Morgan Tocker pointed out that it really is a big deal. This is going to cause a sea change in the way MySQL is …

600 new customers for SugarCRM. James Gosling leaves Oracle. 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.”

# SugarCRM added nearly 600 customers in the first quarter of 2010.

# James Gosling resigned from Oracle.

# VMware’s SpringSource acquired Rabbit Technologies and its RabbitMQ messaging software.

# EnterpriseDB hired Sun’s former MySQL VP Karen Tegan Padir as vice president of products and marketing.

# …

While a scale-out solution has traditionally been popular for MySQL, it's interesting to see what room we now have to scale up - cheap memory, fast storage, better power efficiency.  There certainly are a lot of options now - I've been meeting about a customer/week using Fusion-IO cards.  One interesting choice I've seen people make however, is buying an SSD when they still have a lot of pages read/second - I would have preferred to buy memory instead, and use the storage device for writes.

Here's the benchmark I came up with to confirm if this is the case:

• Percona-XtraDB-9.1 release
• Sysbench OLTP workload with 80 million rows (about 18GB worth of data+indexes)
• XFS Filesystem mounted with nobarrier option.
• Tests run with:
  • RAID10 with BBU over 8 disks
  • Intel SSD X25-E 32GB
  • FusionIO 320GB MLC
…

In our March DBJ article we talked about some of the storage engines to choose from with MySQL.  With it’s plugin storage engine architecture, you have a range of options.  In our April article we continue to discuss a further selection of storage engines, and what features they offer to the DBA and database architect.

Database Journal – Exotic Storage Engines

In XtraDB we have the table INNODB_BUFFER_POOL_PAGES_INDEX which shows which pages belong to which indexes in which tables. Using thing information and standard TABLES table we can see how well different tables fit in buffer pool.

PLAIN TEXT SQL:

1. mysql> SELECT d.*,round(100*cnt*16384/(data_length+index_length),2) fit FROM (SELECT schema_name,table_name,count(*) cnt,sum(dirty),sum(hashed)  FROM INNODB_BUFFER_POOL_PAGES_INDEX GROUP BY schema_name,table_name ORDER BY cnt DESC LIMIT 20) d JOIN TABLES ON (TABLES.table_schema=d.schema_name AND TABLES.table_name=d.table_name);
2. +-------------+---------------------+---------+------------+-------------+--------+
3. | schema_name | table_name          | cnt     | sum(dirty) | sum(hashed) | fit    |
4. …

In Released and new coming features I did not mentioned two additional INFORMATION_SCHEMA tables available in XtraDB:
It is

• INNODB_TABLE_STATS
• INNODB_INDEX_STATS

These table show statistics about InnoDB tables ( taken from InnoDB data dictionary).

INNODB_TABLE_STATS is

• | table_name | table name in InnoDB internal style ('database/table') |
• | rows | estimated number of all rows |
• | clust_size | cluster index (table/primary key) size in number of pages|
• | other_size | other index (non primary key) size in number of pages|
• | modified | internal counter to judge whether statistics recalculation should be done |

INNODB_INDEX_STATS is

• | table_name | table name in …

Recently Alexandr announced new Percona-XtraDB-9.1 release, and now it is good time to summarize features we have and what is expected soon.

This release contains long waited features from 5.0:

• extended slow.log
• USER/TABLE/INDEX/CLIENT_STATISTICS + THREAD_STATISTICS ( coming in release-10)

Extended slow.log is now even more extended, there is additional information for each query:

PLAIN TEXT CODE:

1. # Bytes_sent: 4973  Tmp_tables: 1  Tmp_disk_tables: 1  Tmp_table_sizes: 7808

That is you can see how many bytes was returned by query, was temporary table used,
was it disk table or in-memory, and how big was temporary table.

Also you can profile each individual …