Feed Aggregator

Hi Baomin Wang,

1. I come back with an answer later…

2. MySQL Cluster is mainly an In-Memory-Database. In a typical set-up we do not have such things like tablespaces or data files as in other MySQL Storage engines. Just the LCP- and GCP-files (and some others files related to the MySQL Cluster). MySQL Cluster also knows disk based data (which is a less common set-up). There you have indeed such tablespace and UNDO log files. But I did not consider it in this picture because it is a less often used set-up.

3. As written above, an LCP is a dirty image of the data. So it is not a backup at all. But it writes continuously the whole image of your data to disk.

I hope, this helps?

Regards, Oli

1. Why shall 2 LCPs be kept? It seems that 1 LCP is enough for recovering.
2. From the above picture, When I can’t find NDB write data to data file?
3. Does LCP do an accumulated backup or a whole backup?

This week I showed one of my customers our MyEnv. He was very interested in it and suggested to make it known in public. In fact MyEnv is available for download already several years…

But I did not have the heart yet to announce it more publicly because it was not end user ready at all. So I used the weekend to make it nicer, consolidated some of the code, dropped old stuff etc. Now I think it is acceptable to use for public but not perfect.

So what is MyEnv?

MyEnv is a set of scripts to run comfortably multiple MySQL/MariaDB databases on the same server. You can even run multiple databases with different binary versions.

It is more comfortable than mysqld_multi and provides more functionality.

When to use MyEnv?

MyEnv is useful when you have to install several MySQL databases on one machine. This has to be done typically when:

• you have to consolidate many databases from dedicated servers into one machine or
• you have to separate the data of different customers into different databases for example when …

When you are working with MyISAM [ 1 ] tables MySQL provides a feature called the Midpoint Insertion Strategy [ 2 ]. You can enable it with the parameter key_cache_division_limit [ 3 ].

By default, the key cache management system uses a simple LRU [ 4 ] strategy for choosing key cache blocks to be purged:

When using the Midpoint Insertion Strategy feature, the LRU chain is divided into two parts:

• the hot sub list

and

• the warm sub list.

The division point between those two parts is not fixed, but the key cache management system takes care that the warm part always contains at least key_cache_division_limit percent of the key cache blocks.

When an index block is read from the disk into the key cache, it is placed at the end of the warm sub list. After a certain number of hits, it is promoted to the hot sub list. At present, the number of hits required to promote a block (3) is the same for all index blocks.

A block promoted into the hot sub list is placed at the end of the list. The block then circulates …

Bug #57532: Warm key blocks in MyISAM key_cache structure could be reported

And for MariaDB: https://lists.launchpad.net/maria-developers/msg03663.html

We recently had the case with one of our customers where we got externally generated random hash values (up to 70 bytes) and they were used as Primary Keys in InnoDB.

As we know, this is not a very good idea because the size of all secondary indexes becomes large and because a random hash value as a Primary Key gives us a bad locality of our rows in the table ¹.

If we do not specify a Primary Key and have no Unique Key InnoDB generates a hidden Clustered Index based on the Row ID which is a 6 byte field.

This Row ID is increasing monotonically ¹. That would fit our needs.

So we were thinking about getting completely rid of any Primary Key of that InnoDB table.

This has the following advantages:

• It is sorted by time (more or less).
• The Primary Key is shorter (6 bytes instead of 30-70 bytes + something).
• We do not run into the AUTO_INCREMENT bottleneck of Bug
  #16979 ² in MySQL 5.0 ², ³. Yes we are still running on 5.0.
• We exclude possible other AUTO_INCREMENT contention situations.

To get more opinions about …

With one of its partners FromDual plans to offer an Advanced MySQL DBA Workshop. The first workshop should run in November 2010.

To offer the best possible contents to the participants we want your feedback about the proposed topics, about the missing topics and what you think in general about such a workshop.

Please let us know your opinion. Either as comment on our web-site or for our eyes only at Feedback.

Thank you for your participation.

With a partner we are planning and Advanced MySQL DBA workshop. As the name says it should be a workshop. Its planned duration is 2 days. So within 2 days we can cover 4 to 8 topics more deeply.

Requirements: VirtualBox, VMware, own Laptop?

Possible exercises during the workshop

• Set-up a Master-Master replication with 2 Slaves
• Load balance on master with MySQL Proxy and on Slaves with LVS.
• Design a little schema and load with data from foodmart
• Do a backup with XtraDB and LVM
• Do a PITR and and InnoDB crash recovery (from some samples)
• Find some discrepancies between master/slave
• Run some synthetic benchmark and monitor

Planned contents:

Contents

Introduction
  Admin
  Who we are?
  Tasks of a DBA
  Discussion for focus

Planning and evaluating
  Collecting information
    Business plan, amount of users, expected traffic,
    peak and average traffic, requirements, amount of data,
    in what time range
    Information Life Cycle Management
  Plan architecture
    DB vs. NO-SQL vs. no database
    Storage …

FromDual launches its database Performance Monitoring solution for MySQL, Percona Server and MariaDB. This solution provides monitoring capabilities for most MySQL Storage Engines (Aria, InnoDB, MyISAM, NDB, PBXT and XtraDB). Further you can monitor MySQL Master - Slave replication set-ups, UNIX processes (mysqld, ndbd) and MySQL specific information.

More details you can find on the page Performance Monitor for MySQL.

Coming soon…

Have a look at the Performance Monitor Documentation.

Because I have seen this week again a similar situation I thought about what could cause such InnoDB block corruptions. I came to the following evil-doers:

• Editing the InnoDB tablespace file with a normal text editor.
• Virus checker
• fsck / chkdsk
• Active/active DRBD/SAN (2 mysqld processes accessing the same InnoDB tablespace files at the same time)
• Human interaction (on purpose?)
• Broken I/O controller or other issues with the I/O system

If you have some more ideas. I would be happy to learn about!

InnoDB is known to have crash-recovery capabilities and thus is called a crash safe storage engine (in contrary to MyISAM). Nevertheless under certain circumstances it seems like InnoDB pages can get corrupt during a crash and then a manual crash-recovery is needed.

Oracle/MySQL blames in such cases the Operating System, the I/O system or the hardware. What we have seen is that such incidents occur more often on Windows systems and when people are running their databases in a virtualized environment (VMWare). Because of the small number of cases we are aware of, this may not be representative.

One of our customers did systematic crash tests with InnoDB in a virtual machine and got the problem below (MySQL error log):

InnoDB: Log scan progressed past the checkpoint lsn 0 548857890
InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer... …

What customers say about FromDual

Case study / Referenzbericht

KiKxxl Gmbh: Hochverfügbare Kommunikationsservices auf Basis der MySQL Datenbank und Galera Cluster (Highly available communication services based on a MySQL database and Galera Cluster) ( …

MySQL Cluster is mainly an in-memory database. Nevertheless it requires a good I/O system for writing various different information to disk.

The information MySQL Cluster writes to disk are the:

• Global Checkpoints (GCP) which are the transactions.
• Local Checkpoints (LCP) which is a dirty image of the data.
• Backup.

In the following schema (a 2-node Cluster) you can see what is related to each other:

Please find here the meaning of each parameter:

To find out more about the VIEW’s behaviour I was playing around with the ALGORITHM = TEMPTABLE feature [1]. It was also related to a customers question of how VIEW’s effect performance of queries.

The results are ambivalent:

EXPLAIN
SELECT * FROM v LIMIT 10;

CREATE ALGORITHM = MERGE VIEW v
+----+-------------+-------+--------+---------------+---------+---------+-------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows   | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------+--------+-------------+
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL        | 131073 |             |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.b.a_id |      1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-------------+--------+-------------+
2 rows in set (0.00 sec)

-> = default …

Henrik Ingo from the Open Database Alliance (ODBA) did an interview with Oli Sennhauser of FromDual about the European MySQL database landscape and its future. If you want to know more about our opinion in this matter please read here.

For more technical insight see also our former presentation: MySQL, where are you going?.

At a customer we had a nice example of how the MySQL Optimizer is cheating when used in combination with the MySQL Cluster. The customer had queries running not too slow in the development environment but when he tried them on the acceptance test environment (with more data) the query was running much too long which was unacceptable because this query can occur many times per second.

What has happened?

First of all we had a look at the execution plan of the query generated by the MySQL Optimizer:

EXPLAIN
SELECT t0.*, t1.*
  FROM t2
  JOIN t0 ON t2.t0_id = t0.id
  JOIN t1 ON t1.t0_id = t0.id
 WHERE t2.productnumber LIKE '%3301'
   AND t0.organization_id = 157
   AND t0.type = 'User';

1 row in set (8.78 sec)

+-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+
| table | type   | possible_keys       | key           | key_len | ref      | rows | Extra                             | …

Justin Swanhart from Percona just poked me an told me about FlexViews, a package for Materialized Views he built, based on my ideas. Please have also a look there, possibly it can help you any further…

Uster, Switzerland – April 26, 2010 – FromDual has signed the Service Provider Partnership Agreement of the Open Database Alliance (ODBA).

FromDual is the first official ODBA consulting partner in Europe. The growing number of downloads and use of MariaDB, an improved and enhanced derivation of the MySQL database, also requires consultancy services for MariaDB in Europe.

We are excited to be working with ODBA as their first consulting partner in Europe says Oliver Sennhauser, Owner of FromDual. Through our ODBA partnership, we will significantly help strengthen the position of MariaDB and ODBA in the Open Source database market.

About FromDual

FromDual is the leading vendor independent and neutral MySQL consulting company in Europe!

As a ODBA Silver Partner, FromDual provides consultancy services for MySQL and its derivatives like MariaDB, Percona-Server, XtraDB, PBXT and Drizzle for enterprise-level database applications mainly in Europe. For more information, please visit www.fromdual.com.

About …

Uster, Switzerland – April 26, 2010 – FromDual has signed the Service Provider Partnership Agreement of the Open Database Alliance (ODBA).

FromDual is the first official ODBA consulting partner in Europe. The growing number of downloads and use of MariaDB, an improved and enhanced derivation of the MySQL database, also requires consultancy services for MariaDB in Europe.

We are excited to be working with ODBA as their first consulting partner in Europe says Oliver Sennhauser, Owner of FromDual. Through our ODBA partnership, we will significantly help strengthen the position of MariaDB and ODBA in the Open Source database market.

About FromDual

FromDual is the leading vendor independent and neutral MySQL consulting company in Europe!

As a ODBA Silver Partner, FromDual provides consultancy services for MySQL and its derivatives like MariaDB, Percona-Server, XtraDB, PBXT and Drizzle for enterprise-level database applications mainly in Europe. For more information, please visit www.fromdual.com.

About …