Martin Scholl (@zeit_geist) has started a new project based on the PBXT storage engine: EPBXT - Embedded PBXT! In his first blog he describes how you can easily build the latest version: Building Embedded PBXT from bzr.

The interesting thing about this project is that it exposes the "raw" power of the engine. Some basic performance tests show this really is the case.

At the lowest level, PBXT does not impose any format on the data stored in tables and indexes. When running as a MySQL storage engine it uses the MySQL native row and index formats. Theoretically it would be possible to expose this in an embedded API. The work Martin …

PMP on demand revealed one of reasons why we’ve been seeing ‘Opening tables’ during proper operations, not just during startup (see my previous post on this topic).

We had a thousand or so threads waiting on LOCK_open, and the only thread holding the mutex was this darling:

Thread 902 (Thread 1637624128 (LWP 22113)):
#3  mutex_spin_wait (mutex=0x2aaaac3232b8,
        file_name=0x8b3bf7 "trx0trx.c", line=220) at sync0sync.c:565
#4  trx_allocate_for_mysql
#5  ha_innobase::allocate_trx
#6  check_trx_exists
#7  ha_innobase::info
#8  ha_innobase::open
#9  handler::ha_open
#10 openfrm
#11 open_unireg_entry
#12 open_table
#13 open_tables
#14 open_and_lock_tables
#15 mysql_insert

So, kernel mutex, which is quite contended, will escalate to LOCK_open on table open, which will block all queries from …

Columnar DBMS tuning may require some new approaches versus traditional row-based tuning; gone are full/partitioned table scans, index lookups, and nested-loop join operations, instead replaced by full/partitioned column scans, access by RowID, and hash join operations.  This series of articles is intended to measure potential benefits of different approaches to jump-start your expertise with this new paradigm.  Today’s topic is dRead More...

We just noticed that the libmysql.dll shipped with the 4.0 and 5.0 beta releases does not work as expected with the above mentioned SSL keys. So, if you run into some access violation in such cases, you will need a fresh one, can by copied from a MySQL/Win32 server installation, located in the "bin" directory.

However, I will release a second Beta as soon as possible so you can forget my above notice soon.

(This post was written at my old location.  You’re now reading it at the new location. )

I’m in the process of moving my blog to hosted WordPress at WordPress.com. Much as I loved the flexibility of hosting my own blog (and the rest of the company), the upkeep required just isn’t worth the extra flexibility. So my customization will go away, which is sad, but I should have to spend less time managing all the blogs at SmugMug. Which might mean I actually blog more often again.

Here’s the new URL in ‘beta’ form: http://don.blogs.smugmug.com/

I should have redirects up shortly, so everything should work fine, but if your feed reader breaks or something, now you know why. Apologies in advance for any hiccups…

And, of course, I …

Running many benchmarks on fast storage (FusionIO, SSDs) and multi-cores CPUs system I constantly face two contention problems.

So I suspect it's going to be next biggest issues to make InnoDB scaling on high-end system.

This is also reason why in benchmarks I posted previously CPU usage is only about 50%, leaving other 50% in idle state.

First problem is index->lock mutex.
InnoDB uses single mutex per index, so when you run mixed read / write queries, InnoDB locks index for write operation and thus keeps all selects waiting when update/insert is done. This is implemented in this way because write operation may cause B-Tree page split, and InnoDB needs to move records between pages to finish operation. It is getting even worse when for write you need to perform some additional IO to bring page into buffer_pool.

What could be done there internally: there is B-Tree lock free or only page-level lock …

If you update to the latest build now, you will find a bunch of new SSL fields, used to select certificates and keys needed to connect to a SSL featured MySQL server. Thanks to SuperNiFF for posting most relevant code.



Second thing is, you can specifiy a startup script now. This is a simple way to execute one or more customized queries automatically when you connect to a server. Just select a SQL file here and HeidiSQL runs that as soon you have a connection established.

If you have been reading the Kontrollbase performance reports and noticed that one alert says your connection usage vs max connections ration is too high but then recommends you to decrease the max_connections variable, then you will find this fix handy. Its two simple queries that execute on the Kontrollbase schema to update the max_connections [...]

From Stack Overflow:

Here is my schema:

I need to find the sids of suppliers who supply every red part or every green part.

This task requires matching the sets.

We need to compare two sets here: the first one is the set of the parts of given color; the second one is the set of parts provided by a given supplier. The former should be the subset of the latter.

Unlike other engines, …

This is a simple matter with a simple solution that might help someone save time and confusion. Emacs wasn’t coloring my comments correctly so I went ahead and had it change them to red-italic. If you are having similar issues you can drop the following into your home directory’s .emacs file. Enjoy. Keep in mind that if you are using emacs in a terminal session as opposed to the X-server gui then you will not see the italics.


(global-font-lock-mode 1)
(custom-set-variables
'(gud-gdb-command-name "gdb --annotate=1")
'(large-file-warning-threshold nil))
(custom-set-faces
'(font-lock-comment-face ((((class color) (background light)) (:foreground "red" :slant italic)))))