Table

Some applications have the bad behaviour to CREATE or DROP tables while our FromDual Backup Manager (bman) backup is running.

This leads to the following bman error message:

/opt/mysql/product/5.7.26/bin/mysqldump --user=dba --host=migzm96i --port=3306 --all-databases --quick --single-transaction --flush-logs --triggers --routines --hex-blob --events | tee >(md5sum --binary >/tmp/checksum.23357.md5) | gzip -1
to Destination: /var/mysql/dumps/mysql96i/daily/bck_mysql96i_full_2019-05-22_06-50-01.sql.gz …

As we already stated in earlier articles in this blog
[1 and 2
] it is a good idea to use NULL values properly in MariaDB and MySQL.

One of my Mantras in MariaDB performance tuning is: Smaller tables lead to faster queries! One consequence out of this is to store NULL values instead of some dummy values into the columns if the value is not known (NULL: undefined/unknown).

To show how this helps related to space used by a table we created a little example:

CREATE TABLE big_null1 (
  id INT UNSIGNED NOT NULL …

Precaution: Before you try this out on your production system do a BACKUP first! FromDual Backup Manager can help you with this.

Situation

A MySQL user has delete its InnoDB table files for example like this:

shell> rm -f $datadir/test/test.*

Analysis

We do some analysis first:

mysql> DROP TABLE test;
ERROR 1051 (42S02): Unknown table 'test'

mysql> CREATE TABLE test (id INT) ENGINE = InnoDB;
ERROR 1050 (42S01): Table '`test`.`test`' already exists

The MySQL error log shows us the following …

FreeBSD 9 seems to have some troubles with MySQL 5.5.20. A customer has moved from MySQL 5.0 on Linux to MySQL 5.5 on FreeBSD 9. He experienced a lot of periodic slow downs on the new, much stronger, system which he has not seen on the old Linux box.

This slow downs were also shown in high CPU system time but we could not see any I/O going on.

When we looked into MySQL we have seen many threads in Opening tables state in the MySQL processlist.

The first idea was to increase table_open_cache to 2048 and …

Introduction

Recently a customer claimed that his queries were slow some times and sometimes they were fast.

First idea: Flipping query execution plan caused by InnoDB could be skipped because it affected mainly MyISAM tables.

Second idea: Caching effects by either the file system cache caching MyISAM data or the MyISAM key buffer caching MyISAM indexes were examined: File system cache was huge and MyISAM key buffer was only used up to 25%.

I was a bit puzzled…

Then we checked the table_open_cache …

Abstract:

It is common practice in MySQL table design that fields are declared as NOT NULL but some non-sense DEFAULT values are specified for unknown field contents. In this article we show why this behavior is non optimal an why you should better declare a field to allow NULL values and use NULL values instead of some dummy values.

What we can see often out in the field

Recently we had a discussion with a customer if it makes more sense to store a default value or NULL in InnoDB tables when we do not know …

In our last customer project we had around 600 Gbyte of data in a MySQL database. Because this database consumed a significant amount of our disk space and backups with the InnoDB backup tool took pretty long we wanted to find out if we could get rid of some of the tables.

This application was growing over the last 10 years and it was not clear if some tables are still in use or not.

But how to find out when a table was touched last? MySQL/InnoDB theoretically could know about but does not report this …

Applications occasionally require redesign. However, redesigning an application cannot be done in one step because the application is distributed or several versions of applications must be supported. MySQL 5.0 provides the necessary means to stealthy migrate your data. In a short overview let’s look at what we plan to do: Stealthy Migration (PDF 98.7 kByte).