FromDual TechFeed (de)

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 …

What customers say about FromDual

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 …

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

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…

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.

                    

                    Taxonomy upgrade extras: 
                    
                      
                        Configuration 
                      
                        Sample 
                      
                        My.cnf 
                      
                        My.ini 
                      
                        Template 
                      
                    
                  

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();
+------------+
| …

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 …

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 …

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 …

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 …

shell> kill -l

Linux

MySQL Statement Syntax

Data Definition Language (DDL) Statements

This is a chaotic collection of my MySQL Cluster experience…

Table of Contents

• config.ini template
• my.cnf template
• General Rules and/or experience
• MySQL Cluster restore
• Skript for converting tables to NDB (alter_engine.pl)
• MySQL Cluster memory sizing

config.ini template

A generic MySQL Cluster configuration file (config.ini) to start with. It is pretty much what the MySQL Cluster experts recommend right now:

#
# config.ini
#

# This configuration file is fore MySQL Clusters 6.2 and above...

# …

MySQL Cluster is pretty fast. The reason for this is, that it is completely memory based. Nowadays memory is still, in contrary to disk, limited to your systems. Thus, before installing a MySQL Cluster you have to calculate the amount of memory you need.

To say it in advance: You should consider to only use 64-bit Linux system with huge amount (4 - 64 GB) of RAM!

In release 5.1 MySQL Cluster became disk based. Now you have the possibility to swap out some data to disk. How much it is we will probably see a …

Table of Contents

• Result set with temporary sequence
• Determination of optimal length of prefixed indexes
• Using MySQL keywords in table or columm names
• Missing Primary Key Index
• Problems while installing a MySQL 5.5 database
• InnoDB AUTO_INCREMENT at 2^nd position

Result set with temporary sequence

Sometimes you would like to have a result set with something like a rownum. You can do this at least in the following two ways:

a) with a TEMPORARY MEMORY table:

CREATE TEMPORARY TABLE mem (
    seq  INT NOT NULL …

Table of Contents

• max_open_files warning during mysqld startup
• Troubles after NON recommended upgrade path
• Com_* counters not updated in SHOW STATUS
• ERROR 1300 (HY000): Invalid utf8 character string
• MySQL crashes during import
• MySQL workbench gives an openGL error

max_open_files warning during mysqld startup

Problem

[Warning] Changed limits: max_open_files: 1024  max_connections: 100  table_cache: 457

[Warning] Could not increase number of max_open_files to more than 1024 (request: 1070)

Explanation …