We’ve recently done benchmarks comparing different MySQL versions in terms of their CPU efficiently in TPC-C like Workload. We did it couple of weeks ago so MySQL 5.0.67, MySQL 5.1.29 and Innodb Plugin 1.0.1 were used which are not very recent, though we do not think results will differ a lot with today versions. Results are as follows: The system was 2* Quad Core Xeon E5310, CentOS 5, Data stored on ramfs. We controlled number ...

Innodb 作为 MySQL 中使用最为广泛的 事务型存储引擎，不仅在事务实现数据版本控制方面和其他存储引擎有一定的区别，其数据结构也是以非常有特点的方式存储的。 每个Innodb表的数据其实可以说就是以一个树型（B-Tree）结构存储的，表的数据和主键（Primary Key）共同组成了一个索引结构，也就是我们常说的Innodb的Clustered Primary Key。在这个Clustered Primary Key中，Leaf Nodes其实就是实际的表记录，我们常规理解上的索引信息全部在Branch Nodes上面。 除了Clustered Primary Key之外的其他所有索引在Innodb中被称为Secondary Index。Secondary Index就和普通的B-Tree索引差不多了，只不过在Secondary Index的所有Leaf Nodes上面同时包含了所指向数据记录的主键信息，而不是直接指向数据记录的位置信息。 所以，在 Innodb 中，如果主键值占用存储空间较大的话，会直接影响整个存储 Innodb 表所需要的物理空间，同时也会直接影响到 Innodb 的查询性能。 下面是画的一张 Innodb 索引基本结构图，包括 Primary Key 和 Secondary Index 两种索引的比较。 原文出自： Innodb 索引结构了解 - Innodb Index Structure

Today I attended kick-ass ZFS talk (3 hours of incredibly detailed material presented by someone who knows the stuff and knows how to talk) at CEC (Sun internal training event/conference), so now I know way more about ZFS than I used to. Probably I know way more about ZFS than Average Joe DBA \o/ And now I think ZFS has lots of brilliant design and implementation bits, except it doesn’t match database access pattern needs. ...

It wouldn't be the first time bank regulators broke from accounting rulemakers to find a solution to dwindling capital reserves.

Admit it, you've done it many times before: you create a database schema, arbitrarily guessing at upper limits to the lengths of various columns, only to later have to perform annoying schema upgrades as you change those columns to fit real-world data. If you're using PostgreSQL, what you're doing is pointless. There is quite literally no performance benefit to be had, and possibly a performance penalty as the database needs to check the length constraint. ...

(thank you greg)

Introduction to the Innodb IO subsystem Basics When a client connects to MySQL, it creates a thread to handle it. This thread executes SQL queries and interacts with the storage engine (for simplicity, lets call them user threads). Innodb uses a four (4) additional threads to implement asynchronous io1. Although Innodb has an option innodb_file_io_threads to control the number of IO handler threads, it has no effect on how many IO handler threads are actually ...

Stories: 1) Who's Carl This Time? 2) Opening Panel Round 3) Bluff The Listener 4) Not My Job: John Hodgman 5) Panel Round Two 6) Who's Carl Two 7) Lightning Fill In The Blank 8) Prediction

Photo: W. Woessner That’s a wire sculpture of an escalator by Fritz Panzer, as seen at the Krobath Wimmer gallery: Link

There is patch from Google to improve SMP performance , but for some workloads it showed for us reverse scalability. E.g. update_key benchmark from sysbench. There are also results with Yasufumi's rw_locks (http://bugs.mysql...) Threads Standard InnoDB Google smpfix Yasufumi rw_lock 1 9700.28 10601.96 9432.44 2 14355.66 16673.31 12783.58 4 16104.20 2669.39 6308.92 8 18507.55 780.22 1854.43 16 16178.09 842.73 1601.05 32 12337.50 2028.89 3274.13 64 7801.18 1911.94 4043.85 results are in tps, more are better ...

The unsung heroes of InnoDB are the logfiles. They are what makes InnoDB automatic crash recovery possible. Database administrators of other DBMS may be familiar with the concept of a “redo” log. When data is changed, affected data pages are changed in the innodb_buffer_pool. Then, the change is written to the redo log, which in MySQL is the InnoDB logfile (ib_logfile0 and ib_logfile1). The pages are marked as “dirty”, and eventually get flushed and written ...