InnoDB uses its own mutexes and read-write locks instead of POSIX-mutexes pthread_mutex*, the main reason for that is performance, but InnoDB's implementation isn't ideal and on modern SMP boxes can cause serious performance problems.
Let's look on InnoDB mutex (schematic for simplification):

PLAIN TEXT CODE:

1. spin_loop:
2. for (i=0; i<innodb_spin_locks;i++)
3.   if (mutex_try_lock()) return; // success
4.  
5. // we are here if spin loop was not successful
6. reserve_cell in array of cell;
7. wait on condition_variable in reserved cell;

innodb_spin_locks is configurable via system variable innodb_sync_spin_loops (default value is 20)

There we have:
1. Spin-loop - InnoDB uses spin-loop in hopes thread locked mutex is very fast and will release mutex while current thread runs in …

(See bigger version with better quality at https://chorizo-scanner.com/flash_morcilla )

This video shows you how Morcilla, our brand new PHP extension, lets Chorizo! have a look inside your application on the server.

We are able to hook into every PHP function and trace the payloads of Chorizo!. By default, Morcilla hooks into the whole MySQL function family, fopen, mail, include/require/include_once/require_once, preg_* and others. With a ZendEngine patch, we are able to trace unset variables and a lot more.

See the video how it works (Google Video, YouTube). Check out the …

The (probably and hopefully) last beta versions of XAMPP for Windows and Linux are available for public download in our XAMPP BETA area. New in this version are: Apache 2.2.3, MySQL 5.0.24a, PHP 4.4.4 and 5.1.6, and phpMyAdmin 2.8.2.4.

Beta releases are only for testing purposes. We would very much appreciate it if you could test this release and let us know if you run into any problems.

If no problems are found the final version will follow this weekend.

Follow me to the XAMPP BETA area

I’ve just improved the MySQL duplicate index checker I whipped together a few days ago. As I guessed, my hasty coding left some things to be desired. I’ve fixed some bugs, added support for finding duplicate foreign keys, and switched to a command-line parsing library that comes standard with Perl, so it’s more convenient to run without needing to fetch modules from CPAN. You can download it from the original article.

This is another bugfix release with only a couple of module-related new features.

The news for this release are:

* “fixed a bug with using the alternate search backend. database search was still done while it shouldn’t.”

* MFH: addon.php allows for addon scripts that are implemented using a module

* Added extra hook ‘posting_custom_action’ to the posting editor code

* MFH: Added signing of editor form data to prevent data tampering

* Added a simple general purpose private key based signature system

* redirect back to vroot if mark-read is used (fixing #403)

For users running 5.1.15 we recommend to replace at least search.php to lessen the load on searching. A couple of smaller fixes were also implemented therefore we recommend every user running phorum 5.1.x to upgrade to this version.

If you want to see the full changelog you can find it here …

This is probably well known issue for everyone having some MySQL experience or experience with any other SQL database. Still I see this problem in many production applications so it is worth to mention it, especially as it is connected to MySQL Performance. No it might not affect MySQL Performance per say but it limits our ability tune MySQL Performance as queries become fragile - changing execution plan leads to different query results.

So what I'm speaking about ?

Lets say you have query something like SELECT A,B,MAX(C) FROM TBL GROUP BY A - what would you expect from such query ? Column A is part of group by so its value is same for whole group. MAX(C) is also particular value for each group, while B is not part of GROUP BY and may well correspond to different values. Which one are you looking to get ? In fact this is where results becomes non-deterministic and fragile - any B from the group could be returned, …

My conversion tool is getting better, soon time to release.

What makes it possible is really that T-SQL is such a poor language, not much to take care of. But that is also the problem, since som features are far from perfect, T-SQL code contains a lot of complicated constructs, like error handling with goto's, and checking @@error efter every SQL statement. Converting that to MySQL would make horrible code, in a system where much better error handling is available. But there is currently no GOTO in MySQL...

So that will be unsolved, GOTO's and labels etc. Also references to SQL Server specific system tables, views, procedures and varibles will be largely unconverted.

Given the following scenario:

In a database, that could be represented as such:

CREATE TABLE GroupNum (
…

consulting, database, free consulting, free help, help, internet, mysql, open source, performance, SQL, technology …

Quite a while ago, I wrote a few articles on using MySQL cursors. This time, I will try and shed some light on why cursors are often unnecessary. To illustrate that, I will refactor a typical case of cursor usage.

The first part of this article explains why cursors are usually unnecessary. A few common problems with cursors are briefly discussed. Also, typical stored procedure pattern is described that uses a cursor, and a demonstration is given that shows how it can be refactored to an equivalent procedure that uses a single SQL statement instead.

In the second part of this article, the negative performance implications of using cursors are illustrated with a few benchmarks, and the cases where a cursor might be useful after all are briefly discussed.

Cursor trouble
Some time ago, I used to be quite active in some of the MySQL Forums, …