Showing entries 1 to 10 of 710
10 Older Entries »
Displaying posts with tag: database (reset)
MySQL 5.7 InnoDB Tablespace

By default for MySQL server, InnoDB Engine is getting used widely due it’s ACID support, optimized read-write performance and for many other reasons which are great significance for the database server.

In this blog post, we are going to cover the InnoDB tablespace and its features like,

  • InnoDB engine tablespaces
  • Tablespace Data Encryption
  • Tablespace related Configuration

InnoDB engine tablespaces System tablespace:  

Common tablespace for MySQL server operations. Apart from the table data storage, InnoDB’s functionality requires looking for table metadata, storing and retrieving MVCC info to support ACID compliance and Transaction Isolation. It contains several types of information for InnoDB objects.

  • Contains:
    Table Data Pages
    Table Index Pages …
[Read more]
Migrating/importing NDB to Cluster Manager w/ version upgrade.

I’ve had some questions from people using MySQL Cluster GPL and wanting to move to using MySQL Cluster Carrier Grade Edition, i.e., they want to use MySQL Cluster Manager, MCM, to make their lives much easier, in particular, upgrading (as well as config change ease and backup history).

All I want to do here is to share with you my personal experience on migrating what’s considered a ‘wild’ NDB Cluster to a MCM managed cluster. It’s just as simple to follow the manual chapter Importing a Cluster into MySQL Cluster Manager so at least you can see how I did it, and it might help someone.

[ If you’re not migrating but just looking for further information on NDB Cluster, and came across this post, please please PLEASE look at the …

[Read more]
Webinar Thursday July 27, 2017: Database Backup and Recovery Best Practices (with a Focus on MySQL)

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Register Now

In the case of a failure, do you know how long it will take to restore your database? Do you know how old the backup will be? In this presentation, we will cover the basics of best practices for backup, restoration and business continuity. Don’t put your company on the line due to bad data retention and backup policies.

[Read more]
PHP and MySQL Without the SQL

Embedding Structured Query Language (SQL) within PHP, or other programming languages, has been problematic for some. Mixing two programming languages together is just plainly not aesthetically pleasing. Especially when you have a declarative language (SQL) mixed with a procedural-object oriented language. But now, with the MySQL XDevAPI PECL extension, PHP developers can now stop mixing the two languages together together. MySQL Document StoreThe MySQL Document Store eliminates the heavy burden for SQL skills. It is designed to be a high speed, schema-less data store and is based on the MySQL JSON data type. This gives you roughly a gigabyte of store in a document format to do with as needed. So you do not need to architect you data before hand when you have no idea how it will evolve. No need to normalize your data. Now behind the scenes …

[Read more]
Using find() with the MySQL Document Store

The VideoThe find() function for the MySQL Document Store is a very powerful tool and I have just finished a handy introductory video. By the way -- please let me have feed back on the pace, the background music, the CGI special effects (kidding!), and the amount of the content. The ScriptFor those who want to follow along with the videos, the core examples are below. The first step is to connect to a MySQL server to talk to the world_x schema (Instructions on loading that schema at the first link above).

\connect root@localhost/world_x

db is an object to points to the world_x schema. To find the records in the countryinfo collection, use db.countryinfo.find(). But that returns 237 JSON documents, too many! So lets cut it down to …

[Read more]
MySQL Document Store Video Series

I am starting a series of videos on the MySQL Document Store. The Document Store allows those who do not know Structured Query Language (SQL) to use a database without having to know the basics of relational databases, set theory, or data normalization. The goal is to have sort 2-3 minute episodes on the various facets of the Document Store including the basics, using various programming languages (Node.JS, PHP, Python), and materializing free form schemaless, NoSQL data into columns for use with SQL.

The first Episode, Introduction, can be found here.

Please provide feedback and let me know if there are subjects you would want covered in the near future.

Jetpants Integration Testing

Tumblr is a big user of MySQL, and MySQL automation at Tumblr is centered around a tool we built called Jetpants. Jetpants does an incredible job making risky operations safe and reliable, even fairly complex tasks like replacing failed master servers, or splitting a shard.

While Jetpants is an incredibly effective and valuable tool for Tumblr’s day-to-day operation, it has remained very difficult to implement a meaningful testing framework. Integration testing at this level is very challenging. In this article I’ll go through these challenges and how we’ve tackled them at Tumblr.

Requirements

Jetpants operates under the assumption you’re managing MySQL daemons on a fully functional host, and that it can:

  • ssh to the target system
  • manage processes via service or systemctl commands
  • copy data around between systems …
[Read more]
How to break MySQL InnoDB cluster

A few weeks ago I started experimenting with MySQL InnoDB cluster. As part of the testing, I tried to kill a node to see what happens to the cluster.

The good news is that the cluster is resilient. When the primary node goes missing, the cluster replaces it immediately, and operations continue. This is one of the features of an High Availability system, but this feature alone does not define the usefulness or the robustness of the system. In one of my previous jobs, I worked at testing a commercial HA system and I've learned a few things about what makes a reliable system.

Armed with this knowledge, I did some more experiments with InnoDB Cluster. The attempt from my previous article had no other expectation than seeing operations continue with ease (primary node …

[Read more]
Multi Tb migration Using mydumper

In this post I will explain how to transfer a multi terabyte size database between two MySQL instances using mydumper, which is a logical backup and restore tool that works in parallel. I will also cover the case where you need to transfer only a subset of data.

Big tables are often problematic because of the amount of time needed to do mysqldump/restore which is single threaded. It is not uncommon for this type of process to take several days.

From MySQL 5.6 and on, we also have transportable tablespaces, but there are some limitations with that approach.

Physical backups (e.g. xtrabackup) have the advantage to be faster, but there are some scenarios where you still need/want a logical backup (e.g migrating to RDS/Aurora/Google Cloud SQL).

Steps Install mydumper/myloader

The first thing …

[Read more]
Improving replication with multiple storage engines

New MariaDB/MySQL storage engines such as MyRocks and TokuDB have renewed interest in using engines other than InnoDB. This is great, but also presents new challenges. In this article, I will describe work that I am currently finishing, and which addresses one such challenge.

For example, the left bar in the figure shows what happens to MyRocks replication performance when used with a default install where the replication state table uses InnoDB. The middle bar shows the performance improvement from my patch.

Current MariaDB and MySQL replication uses tables to transactionally record the replication state (eg mysql.gtid_slave_pos). When non-InnoDB storage engines are introduced the question becomes: What engine should be used for the replication table? Any choice will penalise other engines heavily by injecting a cross-engine transaction with every replicated change. Unless all tables can be migrated to the …

[Read more]
Showing entries 1 to 10 of 710
10 Older Entries »