Feed Aggregator

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 various blogs of FromDual employees and some useful blog aggregates.

FromDual employee blogs

Oli’s Blog in German

Shinguz’s Blog in English

Abdel-Mawla’s Blog in English

Jörg’s Blog in English

Cédric’s Blog in English

FromDual Sales Blog in German

FromDual Sales Blog in English

FromDual blog aggregates

FromDual AllFeed in German

FromDual AllFeed in English

FromDual TechFeed in German

FromDual TechFeed in English

FromDual InfoFeed in German

FromDual InfoFeed in English

MySQL Tech-Feed in German

MySQL Tech-Feed in English

PostgreSQL Tech-Feed in German

PostgreSQL Tech-Feed in English

This page was translated using deepl.com.

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 …

shell> kill -l

Linux

Solaris 10 (x86, Sparc)

MySQL Statement Syntax

Data Definition Language (DDL) Statements

Data Manipulation Language (DML) Statements

MySQL Utility Statements

MySQL Transactional and Locking Statements

Account Management 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...

# ----------------------------------------------------------------------

[TCP DEFAULT]

# Default is too small!
SendBufferMemory              = 2M
ReceiveBufferMemory           = 2M


# When this is configured together with section above ndb_mgmd will
# return with erro -1 (255). This is a bug and should be fixed earlier
# or later!
# When you move this section to the bottom it should work.
# You need one TCP section for EACH cluster node pair!
# (for example: 4 nodes = 6 sections)

# [TCP]
#
# NodeId1: 10
# …

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 little further down…

Calculating or estimating

For calculating or estimating how much Memory you need, you have several different possibilities:

• You can do it by hand.
• This OO calc spread sheet helps you.
• You can have it much easier by using ndb_size.pl (or the newer not yet official released version → link).
• Or you can extrapolate from a consisting data set.

Memory usage

First we want to see where memory is used in Cluster. When we do a ps we know how much memory our cluster process allocates:

# …

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 AUTO_INCREMENT PRIMARY KEY
  , data VARCHAR(32)
) ENGINE=MEMORY;

INSERT INTO mem
SELECT NULL, data
  FROM test
 LIMIT 5;

SELECT *
  FROM mem;

+-----+------+
| seq | data |
+-----+------+
|   1 | abc  |
|   2 | def  |
|   3 | ghi  |
|   4 | abc  |
|   5 | def  |
+-----+------+

b) with a user defined variable

SET @seq=0;
SELECT @seq:=@seq+1, data FROM test WHERE id < 100 LIMIT 5;
+--------------+------+
| @seq:=@seq+1 | data |
+--------------+------+
|            1 | abc  |
|            2 | def  |
| …

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

The operating system hard limit of open files was exceeded.

Analysis

Finding the soft and hard limits of open files for your account you can find like this:

# ulimit -Sa | grep "open files"
open files                    (-n) 1200

# ulimit -Ha | grep "open files"
open files                    (-n) 8192

This corresponds to:

mysql> show variables like ...
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| open_files_limit  | 1200  |
| table_cache …

Table of Contents

• Search with special characters
• Why is InnoDB disabled?
• How to find MySQL system information?
• What is the difference between MySQL certified server and community server?
• MySQL monitoring
• MySQL backup
• Corrupt MyISAM table
• How to compile MySQL
• Test restore procedure
• Reset a MySQL user password
• Reset the MySQL root user password
• How to enable the InnoDB plugin
• Storage Engines shipped with MariaDB / MySQL
• Compiling MySQL Cluster ndb-test fails
• NDB information schema does not show up
• Hyper Threading (HT) enabled?
• How to make a patch for MariaDB?
• Where does the InnoDB AUTO-INC waiting come from?
• My character encoding seems to be wrong. How can I fix it?
• I think my Slave is not consistent to its Master any more. How can I check this?
• My MySQL Server is swapping from time to time. This gives hick-ups in MySQL. How can I avoid this?
• How can I find which I/O scheduler my device is using?
• How can I find why my mail is not sent?

Search with special characters

This Question has been moved to the Forum. …

Often asked but informations are spread around: The limitations of MySQL.

If you know any other MySQL limitations, please let us know.

Table of Contents

• General limitations of MySQL
• Limitations of MySQL 4.1
• Limitations of Joins
• Limitations of the MyISAM storage engine
• Limitations of MySQL 5.0
• Limitations of Joins
• Limitations of the MyISAM storage engine
• Limitations of InnoDB
• Limitations of MySQL 5.1
• Limitations of Joins
• Limitations of Partitions
• Limitations of MySQL Cluster
• Limitations in Galera Cluster for MySQL

General limitations of MySQL

[Lit.]

32-bit binaries cannot address more than 4 Gbyte of memory. This is not a MySQL limitation, this is a technical limitation.

BLOB’s are limited to 1 Gbyte in size even thought you use LONGBLOB because of a limitation in the MySQL protocol: The protocol limit for max_allowed_packet is 1GB.

Limitations of MySQL 4.1

Limitations of Joins

In MySQL 4.1, the maximum number of tables that can be referenced in a single join is 61. This also applies to the number …