Author Archives: self

Mysterious completion_type, also known as “do something on transaction commit”

Август 12th, 2012 | Posted by self in Pensieve - (Комментарии отключены)

While with most MySQL variables, if you have an idea what it does or what it’s for, but don’t remember the exact name, you can make an educated guess, this one always gets me in a trouble. I do know what it does, and yet, when I need the name, there is no way I can ever find it on the list of MySQL options, and it’s not even good for googling, as the description one can give is too generic (try to google for “transaction commit rollback” and see what happens).

So, completion_type.
And although I remember it now, I can forget in the future, so here are the values:

NO_CHAIN (or 0)

COMMIT and ROLLBACK are unaffected. This is the default value.

CHAIN (or 1)

COMMIT and ROLLBACK are equivalent to COMMIT AND CHAIN and ROLLBACK AND CHAIN, respectively. (A new transaction starts immediately with the same isolation level as the just-terminated transaction.)

RELEASE (or 2)

COMMIT and ROLLBACK are equivalent to COMMIT RELEASE and ROLLBACK RELEASE, respectively. (The server disconnects after terminating the transaction.)

And this is important, too, as I tend to forget and waste a lot of time trying to figure out why “nothing works”:

completion_type affects transactions that begin with START TRANSACTION or BEGIN and end with COMMIT or ROLLBACK. It does not apply to implicit commits [...] It also does not apply for XA COMMIT, XA ROLLBACK, or when autocommit=1.

Btw, the latter is not exactly true, or I don’t know what they meant by that. Surely it works when autocommit=1, otherwise why would we even need BEGIN or START TRANSACTION?..

aio-max-nr in general and “InnoDB: Error: io_setup() failed with EAGAIN” in particular

Август 3rd, 2012 | Posted by self in MariaDB - (Комментарии отключены)

The problem many MySQL/MariaDB 5.5+ users are painfully aware of:

InnoDB: Using Linux native AIO
InnoDB: Warning: io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
InnoDB: Warning: io_setup() attempt 1 failed.
InnoDB: Warning: io_setup() attempt 2 failed.
InnoDB: Warning: io_setup() attempt 3 failed.
InnoDB: Warning: io_setup() attempt 4 failed.
InnoDB: Warning: io_setup() attempt 5 failed.
InnoDB: Error: io_setup() failed with EAGAIN after 5 attempts.
InnoDB: You can disable Linux Native AIO by setting innodb_native_aio = off in my.cnf
InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: Completed initialization of buffer pool
mysqld got signal 11 ;

There is no news that disabling InnoDB native AIO is not exactly the best possible option. It’s also not a secret that the alternative is increasing aio-max-nr if possible. What is always news for me is how it’s done…

to see the current value:
cat /proc/sys/fs/aio-max-nr

to set the new one:
sudo sysctl fs.aio-max-nr=262144

And yeah, it has already been fixed in MariaDB: replication tests crash… But still.

Btw, while we are here — what is aio-max-nr, anyway?

aio-nr is the running total of the number of events specified on the io_setup system call for all currently active aio contexts. If aio-nr reaches aio-max-nr then io_setup will fail with EAGAIN. Note that raising aio-max-nr does not result in the pre-allocation or re-sizing of any kernel data structures.


Where do X-Chat 2 logs live?

Июль 27th, 2012 | Posted by self in Pensieve - (Комментарии отключены)

It’s always like a quest, seriously. This is the third time I’m looking for them, and at the end I have to do search on ‘X-Chat’… Good thing I don’t have that much stuff on the disk.

So, in Windows 7 they are in…
C:\Users\<myname>\AppData\Roaming\X-Chat 2\xchatlogs

Why do they have to be there — I have no idea. Roaming?! Oh well.

grub2: what to edit and what not

Июль 22nd, 2012 | Posted by self in Pensieve - (Комментарии отключены)

With all these changes after the 1st grub, and considering how seldom I have to do it, every time it’s pain to remember. So:

/boot/grub/grub.cfg — watch, don’t touch.
/etc/default/grub — primary defaults file
GRUB_DEFAULT=0 — default menu entry by the position in the list
GRUB_DEFAULT="foo" — default menu entry by the exact name (at least they say so, didn’t try myself)
GRUB_TIMEOUT=10 — timeout

sudo update-grub2 — to rebuild the config file from various defaults files.

Add/remove a passphrase for an SSH key

Июль 19th, 2012 | Posted by self in Pensieve - (Комментарии отключены)

Got tired of searching for it every time, on some reason can never remember that it’s done by keygen…
ssh-keygen -p [-P old_passphrase] [-N new_passphrase] [-f keyfile]

Turning ubuntu firewall off/on

Июль 19th, 2012 | Posted by self in Pensieve - (Комментарии отключены)

A quick note not to search next time:
sudo ufw disable / sudo ufw enable
(reboot if necessary)

selectcol_arrayref: secret weapon

Июнь 27th, 2012 | Posted by self in Pensieve - (Комментарии отключены)

Despite what the name suggests and what quick references say, selectcol_arrayref can be used with more than one column, and not only the first one, which makes it a pretty handy tool, as in some cases it’s much less cumbersome than usual selectall_arrayref and alike.

Normal use (to get values from the first column):

my $tables = $dbh->selectcol_arrayref("SHOW TABLES IN test");
foreach my $table (@$tables) { ... }

This is self-explanatory, we want to do something with every table in a schema.
Now, I also want to know if a table is a base table or a view, and do something different based on that.

my $tbl_ref = $dbh->selectcol_arrayref("SHOW FULL TABLES IN test", { Columns=>[1,2] } );
my %tables = @$tbl_ref;
foreach my $t (keys %tables) {
if ($tables{$t} eq 'VIEW') { ... }
else { ... }

Why is it easier than selectall_arrayref?
Just because I don’t need to bother about the second-level array reference, everything is already in the plain sight. Simple is good.

What can I say, reading documentation can be useful…

Hardcoded RQG constants

Июнь 16th, 2012 | Posted by self in Pensieve | Testing - (Комментарии отключены)

There are some important RQG constants that sometimes need to be changed to make particular tests more reasonable. Maybe some day I’ll make them configurable, but so far I just modify them in the code. Since it doesn’t happen every day, I tend to forget where they are located, so here they are:

For performance comparison test (new values can be different depending on the nature of the test):


# Configurable constants:
-use constant MIN_DURATION   => 0.02;  # (seconds) Queries with shorter execution times are not processed.
+use constant MIN_DURATION   => 0.2;  # (seconds) Queries with shorter execution times are not processed.
use constant MAX_DURATION   => 300;   # (seconds) Queries with longer execution times are not processed.
-use constant MIN_RATIO      => 1.5;   # Minimum speed-up or slow-down required in order to report a query
+use constant MIN_RATIO      => 5;   # Minimum speed-up or slow-down required in order to report a query
use constant MAX_ROWS       => 200;   # Skip query if initial execution resulted in more than so many rows.
use constant MIN_SAMPLES    => 0;     # Minimum number of execution time samples to do for each query per server.
use constant MAX_SAMPLES    => 0;     # Max number of execution time samples per query per server. Must be no less than MIN_SAMPLES.

For result comparison tests, where queries too often fail due to reaching max rows threshold:

-use constant MAX_ROWS_THRESHOLD => 50000;
+use constant MAX_ROWS_THRESHOLD => 500000;

For running experimental combinations, to avoid aborting a set of tests prematurely due to an error which RQG considers an environment failure, make STATUS_ENVIRONMENT_FAILURE not fatal:

-use constant STATUS_ENVIRONMENT_FAILURE => 110; # A failure in the environment or the grammar file
+use constant STATUS_ENVIRONMENT_FAILURE => 90; # A failure in the environment or the grammar file

For creating less tables than it’s usually done by default (but not bothering to create your own zz file just yet):

=== modified file 'lib/GenTest/App/'
-use constant GDS_DEFAULT_NAMES => ['A', 'B', 'C', 'D', 'E', 'AA', 'BB', 'CC', 'DD'];
+use constant GDS_DEFAULT_NAMES => ['A', 'B', 'C', 'D'];

Using Windows profiler with mysqld

Июнь 10th, 2012 | Posted by self in MariaDB - (Комментарии отключены)

My colleague who is a Windows guru taught me how to do very basic things with the Windows profiler. While this stuff is primitive, the sad truth is that many people don’t know even this little about Windows, me including. I’ll keep the hints here, and hopefully will learn more with time.

  • Use RelWithDebInfo builds, Debug build will screw the picture, it will tell you _db_enter is the most expensive function;
  • Start mysqld;
  • Use x64 VS command prompt:

Start => All Programs => MS Visual Studio 2010 => Visual Studio Tools => Visual Studio x64 Win64 command prompt

  • Set _NT_SYMBOL_PATH to the package bin directory, so mysqld.pdb can be found, and start the IDE:

set _NT_SYMBOL_PATH=... && devenv.exe

  • In the VS command prompt, run

vsperfcmd /start:sample /output:mysample
vsperfcmd /attach:mysqld.exe

  • Run whatever flow you need on mysqld;
  • In the VS command prompt, run

vsperfcmd /shutdown
(It will start waiting till mysqld is stopped)

  • Stop mysqld.

The output of the above is mysample.vsp in the directory where you ran VS command prompt;

  • Open the file in Visual Studio.

Visual Studio provides a number of different views and slices.

  • In Functions, ‘exclusive samples’ is the time spent in this function only, ‘inclusive samples’ is the time spent in this function and its “children”.
  • Callstack is useful, especially with “hot” button in the menu (it can be pushed more than once to see deeper data).
  • You can also create comparison reports.

Get rid of the Windows problem solution dialog

Июнь 4th, 2012 | Posted by self in Pensieve | Testing - (Комментарии отключены)

foo.exe has stopped working Windows can check online for a solution to the problem Check online for a solution and close the program Close the program Debug the program

This screen (a problem notification dialog, debug screen, or whatever it is called), is all cool and nice when you are actually debugging something, but is real pain when you are running a batch job, auto tests, or anything else where you’d be happy with just a windows dump and error logs from your application. But no, the screen pops up, everything stops and waits for your reaction.

Here is the solution if your driver is in Perl, as mine often are:

require Win32::API;
my $errfunc = Win32::API->new('kernel32', 'SetErrorMode', 'I', 'I');
my $initial_mode = $errfunc->Call(2);
$errfunc->Call($initial_mode | 2);

It’s not my invention, I found it long time ago on ActiveState lists. Works like a charm.