Category Archives: Databases

Alter mysql tables on the fly, without locking them

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 … Continue reading Alter mysql tables on the fly, without locking them

MariaDB 10.0: How to migrate from InnoDB to TokuDB

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 … Continue reading MariaDB 10.0: How to migrate from InnoDB to TokuDB

Custom Elasticsearch template with custom field mapping

Disclaimer: I am writing this post mostly for me to remember how and what I did to solve wrong field mapping in elasticsearch. Quote from Elasticsearch Dynamic Mapping documentation page When Elasticsearch encounters a previously unknown field in a document, it uses dynamic mapping to determine the datatype for the field and automatically adds the … Continue reading Custom Elasticsearch template with custom field mapping

Marvel indices taking lot of space? Delete indices older than 7 days!

It looks like Marvel is generating some data everyday. Is there a way to reduce the amount of data generated by marvel? The short answer to the above question is Yes! [codesyntax lang=”bash”] curator –host 127.0.0.1 show indices –older-than 30 –time-unit days –timestring ‘%Y.%m.%d’ –prefix .marvel curator –host 127.0.0.1 close indices –older-than 30 –time-unit days … Continue reading Marvel indices taking lot of space? Delete indices older than 7 days!

Kill all mysql queries having query time greater than 1 minute

At this point there are two approaches to achieve this. One is using pt-kill from Percona Toolkit, and the other one is to use a bash script with a lot of pipes :) Why would someone use the second approach? I don’t know, perhaps because there is no Percona Toolkit available. [codesyntax lang=”bash”] for i … Continue reading Kill all mysql queries having query time greater than 1 minute

How to add debug symbols for MariaDB Debian/Ubuntu packages

I don’t know about other distributions, but I know that the debug symbols were stripped from Debian/Ubuntu packages. If there are some crashes reported I won’t be able to fully analyze them. The only way to fix this problem is to build the packages again. [codesyntax lang=”bash”] git clone https://github.com/MariaDB/server.git cd server/ git branch -a … Continue reading How to add debug symbols for MariaDB Debian/Ubuntu packages

mysql hot backup with xtrabackup

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. … Continue reading mysql hot backup with xtrabackup

How to recover the space used by UNDO_LOG in InnoDB tablespaces

The panic started when the monitoring server sent an alert about the storage of one of our MySQL servers – saying that the disk is about to get filled. I realized that a a lot of the disk space was used by the InnoDB’s shared tablespace ibdata1. But wait… I have innodb_file_per_table enabled. So, what … Continue reading How to recover the space used by UNDO_LOG in InnoDB tablespaces