Tag Archives: innodb

Alter mysql tables on the fly, without locking them

Publicat la 22 January 2019 No comments

If you need to perform real-time ALTER TABLE processes on MySQL (InnoDB, TokuDB) tables, a great tool for the job is the Percona Toolkit.

The Percona Toolkit includes a utility (pt-online-schema-change) to perform such a process, without write-locking tables, or having to manually create a temporary table and triggers to synchronize the data during the process.

[codesyntax lang="bash"]

time pt-online-schema-change --host my-awesome-database.example.net --user=user --password=secret --execute --print --no-drop-old-table --alter "DROP INDEX kenny" D=my-database,t=my-table

[/codesyntax]

[codesyntax lang="bash"]

time pt-online-schema-change --hostmy-awesome-database.example.net --user=user --password=secret --execute --print --no-drop-old-table --alter "add source_id int(10) unsigned DEFAULT NULL"D=my-database,t=my-table

[/codesyntax]

MariaDB 10.0: How to migrate from InnoDB to TokuDB

Publicat la 22 April 2017 No comments

This is only to remember what I did to migrate from InnoDB to TokuDB on a MariaDB cluster.
TokuDB settings are specific to my environment, so please don't ask me what/how/why.

root@dbrfoo:~# mysql -u root -p
MariaDB [(none)]> INSTALL SONAME 'ha_tokudb';
Query OK, 0 rows affected (0.07 sec)

MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| TokuDB             | YES     | Tokutek TokuDB Storage Engine with Fractal Tree(tm) Technology             | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

MariaDB [(none)]> show plugins;
+-------------------------------+----------+--------------------+--------------+---------+
| Name                          | Status   | Type               | Library      | License |
+-------------------------------+----------+--------------------+--------------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL         | GPL     |
| mysql_old_password            | ACTIVE   | AUTHENTICATION     | NULL         | GPL     |
| MEMORY                        | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| MyISAM                        | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| CSV                           | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| MRG_MyISAM                    | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| PERFORMANCE_SCHEMA            | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| InnoDB                        | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| XTRADB_READ_VIEW              | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| XTRADB_INTERNAL_HASH_TABLES   | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| XTRADB_RSEG                   | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_TRX                    | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_LOCKS                  | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_LOCK_WAITS             | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMP                    | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMP_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMPMEM                 | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMPMEM_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMP_PER_INDEX          | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CMP_PER_INDEX_RESET    | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_BUFFER_PAGE            | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_BUFFER_PAGE_LRU        | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_BUFFER_POOL_STATS      | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_METRICS                | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_DEFAULT_STOPWORD    | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_DELETED             | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_BEING_DELETED       | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_CONFIG              | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_INDEX_CACHE         | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_FT_INDEX_TABLE         | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_TABLES             | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_TABLESTATS         | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_COLUMNS            | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_FIELDS             | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_FOREIGN            | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_FOREIGN_COLS       | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_TABLESPACES        | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_SYS_DATAFILES          | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| INNODB_CHANGED_PAGES          | ACTIVE   | INFORMATION SCHEMA | NULL         | GPL     |
| ARCHIVE                       | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| Aria                          | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| FEDERATED                     | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| BLACKHOLE                     | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| FEEDBACK                      | DISABLED | INFORMATION SCHEMA | NULL         | GPL     |
| partition                     | ACTIVE   | STORAGE ENGINE     | NULL         | GPL     |
| TokuDB                        | ACTIVE   | STORAGE ENGINE     | ha_tokudb.so | GPL     |
| TokuDB_trx                    | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so | GPL     |
| TokuDB_lock_waits             | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so | GPL     |
| TokuDB_locks                  | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so | GPL     |
| TokuDB_file_map               | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so | GPL     |
| TokuDB_fractal_tree_info      | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so | GPL     |
| TokuDB_fractal_tree_block_map | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so | GPL     |
+-------------------------------+----------+--------------------+--------------+---------+
54 rows in set (0.00 sec)

MariaDB [(none)]> \q

root@dbrfoo:~# vim /etc/mysql/conf.d/tokudb.cnf

[mariadb]
# See https://mariadb.com/kb/en/how-to-enable-tokudb-in-mariadb/
# for instructions how to enable TokuDB
#
# See https://mariadb.com/kb/en/tokudb-differences/ for differences
# between TokuDB in MariaDB and TokuDB from http://www.tokutek.com/

#plugin-load=ha_tokudb.so

tokudb_cache_size=300GB
tokudb_commit_sync=off
tokudb_fsync_log_period=1000
tokudb_directio=ON
tokudb_disable_slow_alter=ON
tokudb_disable_hot_alter=OFF
tokudb_load_save_space=ON
tokudb_row_format=tokudb_fast


root@dbrfoo:~# /etc/init.d/mysql stop
root@dbrfoo:~# /etc/init.d/mysql start


----------------
150615 14:35:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
150615 14:36:09 mysqld_safe Starting mysqld daemon with databases from /data/db/mysql
150615 14:36:10 [Note] /usr/sbin/mysqld (mysqld 10.0.19-MariaDB-1~wheezy-log) starting as process 20812 ...
150615 14:36:10 [Note] InnoDB: Using mutexes to ref count buffer pool pages
150615 14:36:10 [Note] InnoDB: The InnoDB memory heap is disabled
150615 14:36:10 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
150615 14:36:10 [Note] InnoDB: Memory barrier is not used
150615 14:36:10 [Note] InnoDB: Compressed tables use zlib 1.2.7
150615 14:36:10 [Note] InnoDB: Using Linux native AIO
150615 14:36:10 [Note] InnoDB: Using CPU crc32 instructions
150615 14:36:10 [Note] InnoDB: Initializing buffer pool, size = 100.0G
150615 14:36:16 [Note] InnoDB: Completed initialization of buffer pool
150615 14:36:17 [Note] InnoDB: Highest supported file format is Barracuda.
150615 14:36:20 [Note] InnoDB: 128 rollback segment(s) are active.
150615 14:36:20 [Note] InnoDB: Waiting for purge to start
150615 14:36:20 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.23-72.1 started; log sequence number 38210935651348
150615 14:36:20 [Note] Plugin 'FEEDBACK' is disabled.
150615 14:36:20 [Note] Server socket created on IP: '::'.
150615 14:36:20 [Note] Event Scheduler: Loaded 0 events
150615 14:36:20 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.0.19-MariaDB-1~wheezy-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
----------------

root@dbrfoo:~# mysql -u root -p

MariaDB [(none)]> use xxxxxxxx_ugc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [xxxxxxxx_ugc]> alter table user_data engine=tokudb;
Stage: 1 of 2 'Fetched about 161900000 rows, loading data still remains'   3.49% of stage done

mysql hot backup with xtrabackup

Publicat la 15 September 2015 No comments

I want to backup my databases without downtime... Well, there are couple approaches to this. One of them is to add a standby slave and use LVM snapshot approach. The second approach is to use percona xtrabackup. My problem is that I have some huge databases here, so taking local backups is out of the question.

But just to remain written this is how to do local hot backups.

1. Take the backup
[codesyntax lang="bash"]

time innobackupex --user=user --password=password --no-timestamp --rsync --slave-info --safe-slave-backup /path/mysql

[/codesyntax]

2. At this point the data is not ready to be restored. There might be uncommitted transactions to be undone or transactions in the logs to be replayed. Doing those pending operations will make the data files consistent and it is the purpose of the prepare stage. Once this has been done, the data is ready to be used.

[codesyntax lang="bash"]

time innobackupex --apply-log --use-memory=12G /path/mysql

[/codesyntax]

Since in my case having storing the backup locally (not even temporary) is not an option I need to transport the whole thing to the storage server.
The lame solution is to use NFS... but c'mon... grrrr... no!

The right solution is to stream the data directly to the storage server. To achieve this I am using netcat. Also to gain more performance I am compressing the data on the fly.

1. On the target machine
[codesyntax lang="bash"]

nc -l -p 9999 | qpress -dio | xbstream -x -C /path/to/mysql

[/codesyntax]

2. On the source machine
[codesyntax lang="bash"]

time innobackupex --user=user --password=pass --no-timestamp --slave-info --parallel=$((`nproc`-2)) --safe-slave-backup --stream=xbstream /data/db/mysql | qpress -io something | nc destination 9999

[/codesyntax]

3. On the target machine
Apply the transaction log in order to make the backup consistent.

[codesyntax lang="bash"]

time innobackupex --apply-log --use-memory=120G /path/to/mysql

[/codesyntax]

Note: --use-memory option is used to speed up the process by using more memory (the default is 100MB which is a quite small value).

mysql: preload InnoDB buffer pool

Publicat la 16 June 2015 No comments

Why?! To avoid a lengthy warmup period after restarting the server, particularly for instances with large InnoDB buffer pools.

Things to be added to /etc/mysq/my.cnf:

# Shortened warm-up times with a preloaded InnoDB buffer pool
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON

Basically when MySQL receives the TERM signal will dump the InnoDB buffer pool /data/db/mysql/ib_buffer_pool and it will load it automatically when it will start. However, the buffer pool(s) dump can be done at any time when MySQL is running by doing:

SET GLOBAL innodb_buffer_pool_dump_now=ON;

By analogy the buffer pool(s) can be loaded manually by:

SET GLOBAL innodb_buffer_pool_load_now=ON;

Additionally the progress of the buffer pool(s) dump (although very quick) and load (not so quick) can be monitored by:

SHOW STATUS LIKE 'innodb_buffer_pool_dump_status';
SHOW STATUS LIKE 'innodb_buffer_pool_load_status';

The load operation can be interrupted by:

SET innodb_buffer_pool_load_abort=ON;