MySQL type sizes

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

Another piece of trivia which sometimes is not easy to find fast, and which easily gets demoted into the cold cache (if that) of my brain when isn’t used.

CHAR(M) 255 characters
VARCHAR(M) 65,535 characters
TINYTEXT 255 characters
TEXT 65,535 characters
MEDIUMTEXT 16,777,215 characters
LONGTEXT 4,294,967,295 characters
BINARY 255 bytes
VARBINARY 65,535 bytes
TINYBLOB 255 bytes
BLOB 65,535 bytes
MEDIUMBLOB 16,777,215 bytes
LONGBLOB 4,294,967,295 bytes
TINYINT -128 — 127
TINYINT UNSIGNED 255
SMALLINT -32,768 — 32,767
SMALLINT UNSIGNED 65,535
MEDIUMINT -8,388,608 — 8,388,607
MEDIUMINT UNSIGNED 16,777,215
INT -2,147,683,648 — 2,147,683,647
INT UNSIGNED 4,294,967,295
BIGINT -9,223,372,036,854,775,808 — 9,223,372,036,854,775,807
BIGINT UNSIGNED 18,446,744,073,709,551,615
FLOAT precision 0..23
DOUBLE precision 24..53
DECIMAL 65 digits
ENUM 65,635 members
SET 64 members
BIT 1..64 bits
DATE 1000-01-01 — 9999-12-31
TIME -838:59:59 — 838:59:59
DATETIME 1000-01-01 00:00:00 — 9999-12-31 23:59:59
TIMESTAMP 1970-01-01 00:00:00, 1970-01-01 00:00:01 — 2038-01-19 03:14:07 UTC
YEAR(4) 1901 — 2155
YEAR(2) 1970 — 2069

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?..

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.