Feed Aggregator

It is about performance

Bradley C. Kuszmaul - Fri, 2011-01-07 13:13

You are correct that indexes cost something in InnoDB. However, if you measure InnoDB you will likely find it is much faster than MyISAM for insertions when maintaining an index on a large table. My measurements (and others, such as Mark Callaghan at Facebook) say that InnoDB is faster (maybe 4x) for this kind of load.

Also you are correct that some people will not consider using a non-GPL storage engine, and that’s fine. That’s part of why I am pointing out that InnoDB is far better suited than MyISAM for the load you measured.

Some people are willing to use a non-GPL storage engine if it offers some other advantage, however. In the case of TokuDB, one advantage is more than a 10x performance improvement for this kind of load. (There are other advantages such as compression.)

I read your home page, which says you offer “vendor neutral” consulting, and that you are in the business of recommending the best solution to meet your clients needs. I hope that there are some situations …


Taxonomy upgrade extras: 
Categories: 

It is no post MyISAM vs. InnoDB

erkules - Fri, 2011-01-07 10:39

Moin Bradley, the post is about the increasing cost of maintaining (more) indexes.

Secondary indexes with InnoDB are even more expensive. Think about a UUID-PK and every secondary saved separately to the PK. So I doubt there is no increasing cost using InnoDB. (And only this would counter the essence of the blog.)

I don’t know TokuDB, but as TokuDB is not GPL who cares?


Taxonomy upgrade extras: 
Categories: 

Use a better indexer

Bradley C. Kuszmaul - Thu, 2011-01-06 02:30

Using MyISAM is just asking for poor performance. Try using a storage engine that is actually good at indexing.

For example, InnoDB offers a substantial improvement (perhaps a factor of 4) over MyISAM for maintaining indexes. InnoDB uses an change buffer to achieve that performance.

For really high DML operations, try TokuDB (tokutek.com), which is about ten times faster than InnoDB. TokuDB uses fractal tree indexes to achieve its performance.

Both InnoDB and TokuDB are transactional and can recover from crashes. MyISAM will corrupt your data on a crash.


Taxonomy upgrade extras: 
Categories: 

FromDual News

oli - Mon, 2011-01-03 22:02

Follow us on Twitter or subscribe to the FromDual Newsletter.

The content of this page has been moved to our company blog.


Taxonomy upgrade extras: 
Categories: 

How can I find what InnoDB version I am using?

Shinguz - Sat, 2011-01-01 12:05

In the old days everything was simpler. We had one maker of our favourite database management system and possibly the choice between different Storage Engines. Mostly the decision has to be taken between MyISAM and InnoDB. When you care about your data integrity you have chosen InnoDB.

Nowadays it is more complex. We have several different makers of our favourite database management system: Oracle/MySQL, Monty Program AB and Percona with their products: MySQL, MariaDB and Percona Server.

We have different performant and reliable transactional Storage Engines: InnoDB, XtraDB and PBXT and to make it more difficult we can have InnoDB even as built-in and as plugin in the same release (5.1 only).

XtraDB is a fork of InnoDB and claims to be a drop-in replacement for InnoDB. They look quite similar but have partly different features. When you come to a new customer it makes sense to find out which version of InnoDB/XtraDB they are running. When you ask them sometimes they do not know because they did not care (then …


Taxonomy upgrade extras:  english, storage engine, innodb, plugin, pbxt, version, xtradb, builtin,
Categories: 

Impact of indices on MySQL

Shinguz - Fri, 2010-12-31 17:05

It is generally well known that indexes help a lot to speed up database queries (especially SELECT but also UPDATE and DELETE). It is less known, that indexes also have some disadvantages.

One of these disadvantages is, that indexes require space in memory and on disk. An other disadvantage of indexes is, that they slow down DML statements like INSERT and DELETE.

We often see at our customers that they do not realized this behavior. Now I found the time to show the impact of (too) many indexes graphically.

In the following test scenario we created a simple table:

CREATE TABLE `test` (
  `id` INT(10) UNSIGNED NOT NULL,
  `f1` INT(10) UNSIGNED NOT NULL,
  `f2` INT(10) UNSIGNED NOT NULL,
  `f3` INT(10) UNSIGNED NOT NULL,
  `f4` INT(10) UNSIGNED NOT NULL,
  `f5` INT(10) UNSIGNED NOT NULL,
  `f6` INT(10) UNSIGNED NOT NULL,
  `f7` INT(10) UNSIGNED NOT NULL,
  `f8` INT(10) UNSIGNED NOT NULL
) ENGINE = MyISAM;

and added some indexes:

ALTER TABLE test ADD PRIMARY KEY (id);
ALTER TABLE test ADD INDEX (f1);
...

Then …


Taxonomy upgrade extras:  english, performance, tuning, index,
Categories: 

Follow this thread on MariaDB

oli - Fri, 2010-12-31 13:43

https://lists.launchpad.net/maria-developers/msg03814.html


Taxonomy upgrade extras: 
Categories: 

Does the statement run in its own transaction?

oli - Fri, 2010-12-31 13:40

Hello Justin,

Does the statement run in its own transaction after the main connection terminates?

It looks like not! :( I tried 4 different cases:

  • KILL QUERY
  • KILL CONNECTION
  • kill <pid>
  • kill -9 <pid>
An in all 4 cases the changed values were still there after the termination (working with InnoDB).

Does the SQL properly increment global status variables, etc?

Yes. It looks like.

And referring to your last comment: You are absolutely right! This was just a pilot or prototype. So far away from being optimal.


Taxonomy upgrade extras: 
Categories: 

MyISAM warm key blocks made it into MariaDB 5.2.4

oli - Fri, 2010-12-31 11:55

I just found that the MyISAM warm key blocks feature made it into MariaDB 5.2.4… :)


Taxonomy upgrade extras: 
Categories: 

FromDual partner

oli - Tue, 2010-12-21 17:44

FromDual cooperates with the following companies:


TopicCompanyDescription
MariaDB/MySQL TrainingLinuxhotel GmbHThe Linuxhotel is one of the most important Open Source Software training and meeting places in German-speaking countries.Linuxhotel
MariaDB SupportMariaDB ABFromDual helps you to get into MariaDB Enterprise Subscriptions and Services.MariaDB
MariaDB/MySQL TrainingGFU Cyrus AGGFU is one of the most important Enterprise training facilities in Germany.GFU
MariaDB/MySQL TrainingHeinlein AcademyThe Heinlein Academy is THE Linux- and OpenSource-Training facility in Berlin (Germany).Heinlein Academy
MySQL SupportMySQLFromDual helps you to get into MySQL Enterprise Edition and its MySQL Technical Support.MySQL
MySQL NDB Cluster SupportiClaustron ABiClaustron has a deep insight into MySQL NDB Cluster and …

Taxonomy upgrade extras:  partner, oracle, mysql, mariadb, gfu, linuxhotel, heinlein, rondb,
Categories: 

Getting it into MariaDB

admin - Sat, 2010-12-11 08:57

Hi Sergei,

OK I will do so… The problem with maria-developers@ is just that there is so much traffic I can oversee something which concerns me…

Oli


Taxonomy upgrade extras: 
Categories: 

MySQL logon and logoff trigger for auditing

Shinguz - Fri, 2010-12-10 23:23

A while ago I did some research about MySQL audit functionality and logon a and logoff triggers. MySQL and MariaDB provide a logon trigger in the form of the init_connect variable but no logoff trigger where most of the work for auditing would be done. When we would have a logoff trigger we could track the login and possibility some activity of a user and implement auditing functionality.

Yesterday when I was looking into the code for an answer to the question of one of my customers this research came into my mind again. Today I was a bit more curious and I tried to find a way to patch the MySQL code to get a logoff trigger. Luckily I was successful right away and I created the exit_connect variable which acts as the logoff trigger.

The patches for the logoff trigger you can find here.

What you can do with these patches you will see in the following example. First we create an audit schema with an audit table:

CREATE SCHEMA audit;
USE audit;

-- thread_id is no good PK, because of restart!
CREATE TABLE …

Taxonomy upgrade extras:  english, logon trigger, trigger, login trigger, audit, sql/psm,
Categories: 

Answering your question on

Sergei - Sat, 2010-12-11 01:01

Answering your question on IRC - yes, we can get it in MariaDB, although I’d prefer a slightly different interface. But anyway - we cannot discuss that in the blog, it’s the wrong medium, right? But if you write to maria-developers@ we can continue there.


Taxonomy upgrade extras: 
Categories: 

transactions?

Justin Swanhart - Sat, 2010-12-11 00:28

Does the statement run in its own transaction after the main connection terminates?

For example: conn_begin BEGIN insert into some_table (…) /* connection is lost*/ (implicit rollback)

audit_trigger_begin: BEGIN – hope this doesn’t commit the old work! INSERT INTO some_audit_table (…) COMMIT

Does the SQL properly increment global status variables, etc?

Also your triggers access the information schema a lot. Consider reading from STATUS_VARIABLES/GLOBAL_VARIABLES and pulling multiple values at once. The entire structure is materialized when you access the table so you might as well access as much data as you can in one pass.


Taxonomy upgrade extras: 
Categories: 

MySQL Client Error Codes and Messages 2050 - 2099

oli - Mon, 2010-12-06 12:04

2000 - 2049

  • Error: 2050 (CR_FETCH_CANCELED)

    Message: Row retrieval was canceled by mysql_stmt_close() call

  • Error: 2051 (CR_NO_DATA)

    Message: Attempt to read column without prior row fetch

  • Error: 2052 (CR_NO_STMT_METADATA)

    Message: Prepared statement contains no metadata

  • Error: 2053 (CR_NO_RESULT_SET)

    Message: Attempt to read a row while there is no result set associated with the statement

  • Error: 2054 (CR_NOT_IMPLEMENTED)

    Message: This feature is not implemented yet

  • Error: 2055 (CR_SERVER_LOST_EXTENDED)

    Message: Lost connection to MySQL server at ‘%s’, system error: %d

  • Error: 2056 (CR_STMT_CLOSED)

    Message: Statement closed indirectly because of a preceeding %s() call

  • Error: 2057 (CR_NEW_STMT_METADATA)

    Message: The number of columns in the result set differs from the number of bound buffers. You must reset the statement, rebind the result set columns, and execute the statement again

  • Error: 2058 (CR_ALREADY_CONNECTED)

    Message: This handle is already connected. Use a …


Taxonomy upgrade extras:  mysql, support, error, help,
Categories: 

MySQL Client Error Codes and Messages 2000 - 2049

oli - Mon, 2010-12-06 12:02

1700 - 1749 2050 - 2099

  • Error: 2000 (CR_UNKNOWN_ERROR)

    Message: Unknown MySQL error

  • Error: 2001 (CR_SOCKET_CREATE_ERROR)

    Message: Can’t create UNIX socket (%d)

  • Error: 2002 (CR_CONNECTION_ERROR) Message: Can’t connect to local MySQL server through socket ‘%s’

    How does the MySQL error message look like?

    shell> mysql --user=root --socket=/tmp/mysql.sock
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

    What does the MySQL error message mean?

    This MySQL error message means, that your MySQL Client utily cannot connect to the Server using the specified socket file.

    More information you can find with the command:

    shell> perror 2
OS error code   2:  No such file or directory

    When does this MySQL error happen?

    This MySQL error happens when your MySQL Server is not running (locally) or when the socket file is not at the location you have specified or where the default points to.

    How to fix this MySQL error?

    Try to find out if your MySQL …


Taxonomy upgrade extras:  mysql, support, error, help, aborted_clients, max_allowed_packet,
Categories: 

MySQL Server Error Codes and Messages 1700 - 1749

oli - Mon, 2010-12-06 12:00

1650 - 1699 2000 - 2049

  • Error: 1700 SQLSTATE: HY000 (ER_GRANT_PLUGIN_USER_EXISTS)

    Message: GRANT with IDENTIFIED WITH is illegal because the user %-.
    *s already exists

  • Error: 1701 SQLSTATE: 42000 (ER_TRUNCATE_ILLEGAL_FK)

    Message: Cannot truncate a table referenced in a foreign key constraint (%s)

  • Error: 1702 SQLSTATE: HY000 (ER_PLUGIN_IS_PERMANENT)

    Message: Plugin ‘%s’ is force_plus_permanent and can not be unloaded

  • Error: 1703 SQLSTATE: HY000 (ER_SLAVE_HEARTBEAT_VALUE_OUT_OF_RANGE_MIN)

    Message: The requested value for the heartbeat period is less than 1 millisecond. The value is reset to 0, meaning that heartbeating will effectively be disabled.

  • Error: 1704 SQLSTATE: HY000 (ER_SLAVE_HEARTBEAT_VALUE_OUT_OF_RANGE_MAX)

    Message: The requested value for the heartbeat period exceeds the value of
    `slave_net_timeout’ seconds. A sensible value for the period should be less than the timeout.

1650 - 1699 2000 - 2049


Taxonomy upgrade extras:  mysql, support, error, help,
Categories: 

MySQL Server Error Codes and Messages 1650 - 1699

oli - Mon, 2010-12-06 11:59

1600 - 1649 1700 - 1749


Taxonomy upgrade extras:  mysql, support, error, help, general query log,
Categories: 

MySQL Server Error Codes and Messages 1600 - 1649

oli - Mon, 2010-12-06 11:54

1550 - 1599 1650 - 1699


Taxonomy upgrade extras:  mysql, support, error, help,
Categories: 

MySQL Server Error Codes and Messages 1550 - 1599

oli - Mon, 2010-12-06 11:53

1500 - 1549 1600 - 1649


Taxonomy upgrade extras:  mysql, support, error, help,
Categories: 

Pages

Subscribe to FromDual aggregator