FromDual TechFeed (de)

At the DOAG 2010 we found that there are already a lot of Plugins etc. for MySQL available. Therefor it will be a good time to start with a collection of Plugins, UDF’s etc. available.

Because we have done already a collection of MySQL Storage Engines earlier please look there form more information.

Hi Baomin Wang,

After some thinking an talking to some colleagues I found the answer:

When MySQL Cluster writes LCP’s it first writes LCP 1, then LCP 2. After LCP 2 it OVERwrites the LCP 1 again and then overwrites LCP 2 again and so on…

This assures that we have always ONE complete and good LCP from where we can recover in case of a complete Cluster crash.

When we would have only 1 LCP file this cannot be assured.

Regards, Oli

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 …

PBXT Maatkit tools Sandbox InnoDB plugin vs. built-in NF 5.5

Official course announcement

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 …

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…

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.

                    

                    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 …