Feed Aggregator

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 …

When I dug through RethinkDB white papers I have seen that they make use of round-robin mechanisms.

We have to investigate a bit further to see if this can be used as a RRD SE and keep you informed…

Hello Shinguz,

NTFS is sparse file-capable too. However there is no tools to deal with it in convenient way. I have written one. Check the “SparseChecker” (http://www.opalapps.com/sparse_checker/sparse_checker.html). Current version is free.

I guess my post is relevant here because MySQL is available for Windows too. And for huge preallocated database files sparse regions (regions full of continuous zeroes) is a known problem, as well as disk and memory images for virtual machines and preallocated download manager’s files (pending downloads).

Best regards, Oleh

P.S. It would be interesting and valuable to hear your proffesional opinion about the SparseChecker as well as if it makes sense to port it to Linux.

Our presentation MySQL, where are you going? of March 25 at the OpenExpo in Bern is now available in German and English.

When you have missed it, you can download it now from here…

The video recording should be available as well soon.

On this page you can find the different blogs of FromDual employees and some useful blog aggregates.

FromDual blogs

Oli’s Blog

Shinguz’s Blog

Abdel-Mawla’s Blog

Jörg’s Blog

Cédric’s Blog

FromDual sales Blog de

FromDual sales Blog en

FromDual aggregates

FromDual Feed collection in German

FromDual Feed collection in English

FromDual TechFeed

FromDual TechFeed

FromDual InfoFeed

FromDual InfoFeed

MySQL Tech-Feed

The MySQL and MariaDB default configuration is not very great for production use. Some of the default values in my.cnf should be changed when you need it for business critical applications.

The following MySQL configuration file is in our opinion a good average configuration file for MySQL, Galera Cluster, MariaDB and Percona Server. For MySQL servers more performance tuning is not need in most cases.

Download the sample MySQL and MariaDB configuration file with wget directly from here.

The following solutions are used with MySQL:

• Redhat Cluster Suite (RHCS)
• SteelEye LiefKeeper
• Heartbeat (from the Linux HA project), Using Linux HA Heartbeat
• Crossroads
• Failover
• DRBD, Using MySQL with DRBD
• MySQL Cluster

A little summary about the MySQL CSV table engine:

• The SQL-Demo script (930 byte) for the following article.
• The CSV converter to convert normal CSV files into a CSV format which is for MySQL acceptable.

Caution: Use on your own risk!

MySQL CSV tables (internally also called TINA tables) are driven by the MySQL CSV storage engine. This feature was added in MySQL release 4.1.4. CSV tables store data in text files using the Character-Separated-Value format.

mysql> SELECT version();
+------------+
| version()  |
+------------+
| 5.0.16-max |
+------------+

CSV tables are an equivalent to Oracle external tables. They can be use to import data from your favourite spread sheet software (e.g. OpenOffice Calc), exchange data from or with other data sources or just migrating data from an other database system to MySQL.

To enable CSV tables, use the --with-csv-storage-engine option during configure when you build MySQL. If you got a already compiled MySQL binary you can find out if CSV tables are supported like …

This is an automated analysis of the DBI:mysql:database=foodmart;host=localhost database for migration into MySQL Cluster. No warranty is made to the accuracy of the information.

This information should be valid for MySQL 4.1 and 5.0. Since 5.1 is not a final release yet, the numbers should be used as a guide only.

5.1-dd is for tables stored on disk. The ndb_size.pl estimates are experimental and should not be trusted. Notably we don’t take into account indexed columns being in DataMemory versus non-indexed on disk.

Parameter Settings

NOTE the configuration parameters below do not take into account system tables and other requirements.

Memory usage because of parameters

Usage is in kilobytes. Actual usage will vary as you should set the parameters …

Connected to: DBI:mysql:database=foodmart;host=localhost

Including information for versions: 4.1, 5.0, 5.1

Table List

• account
• account_account_id$unique
• category
• category_category_id$unique
• currency
• currency_currency_id_date$unique
• customer
• customer_customer_id$unique
• days
• department
• department_department_id$unique
• employee
• employee_employee_id$unique
• expense_fact
• inventory_fact
• position
• position_position_id$unique
• product
• product_class
• product_product_id$unique
• promotion
• promotion_promotion_id$unique
• region
• reserve_employee
• reserve_employee_employee_id$unique
• salary
• sales_fact
• store
• store_store_id$unique
• time_by_day
• warehouse
• warehouse_class

account

DataMemory for Columns

* means varsized DataMemory

Database Architecture & Design

Application logic in the middle tier?

Reduce money spent on software licenses by moving logic to the middle tier. This means moving stored procedures into the application server or web server layer where it is much cheaper to scale out. Reducing or eliminating business logic running in the DB may reduce Database CPU utilisation and hence save support costs for the DB Portability is key! [1]

The complete Open Source MySQL High Availability and Scale-Out Architecture Stack

Click on the map to get to you favourite mater…

Legend

• DRBD: Distributed Replicated Block Device
• LVM:Logical Volume Manager
• Lighttp: light footprint + httpd = LightTPD (pronounced lighty)
• Cacti: Official Cacti website
• Nagios: Nagios is an open source host, service and network monitoring program.
• mon: mon is a scheduler and alert management tool used for monitoring service availability and triggering alerts upon failure detection
• MySQL Cluster MySQL Cluster documentations
• LVS: Linux Virtual Server. A …

Following question came up recently: What happens if you drop or add a partition of an existing already partitioned table with ALTER TABLE? Will it be copied or will just the single partition be dropped and added? How are the index(es) rebuild after such a drop/add?

In the MySQL documentation were not to many details mentioned:

ALTER TABLE … ADD PARTITION creates no temporary table except for MySQL Cluster.
…
ADD or DROP operations for RANGE or LIST partitions are immediate operations or nearly so. ADD or COALESCE operations for HASH or KEY partitions copy data between changed partitions
…
If other cases, MySQL creates a temporary table, even if the data wouldn’t strictly need to be copied …

So it would be interesting to find out what happens. Because I am not a developer I am not able to read code. So I have to find it out somehow different:

1. We need the following Tool: MySQL Super Smack

2. With gen-data from MySQL Super Smack we generate some data:

   ./gen-data -n 1000000 -f …