Buy Percona SupportEmergency? Get 24/7 Help Now!

TokuDB and PerconaFT database file management part 1 of 2

Latest MySQL Performance Blog posts - September 27, 2016 - 7:53am

In this blog post, we’ll look at TokuDB and PerconaFT database file management.

The TokuDB/PerconaFT file set consists of many different files that all serve various purposes. These blog posts lists the different types of TokuDB and PerconaFT files, explains their purpose, shows their location and how to move them around.

Peter Zaitsev blogged on the same topic a few years ago. By the time you read back through Peter’s post and reach the end of this series, you should have some ideas to help you to manage your data set more efficiently.

TokuDB and PerconaFT files and file types:

  • tokudb.environment
    • This file is the root of the PerconaFT file set and contains various bits of metadata about the system, such as creation times, current file format versions, etc.
    • PerconaFT will create/expect this file in the directory specified by the MySQL datadir.
  • tokudb.rollback
    • Every transaction within PerconaFT maintains its own transaction rollback log. These logs are stored together within a single PerconaFT dictionary file and take up space within the PerconaFT cachetable (just like any other PerconaFT dictionary).
    • The transaction rollback logs will “undo” any changes made by a transaction if the transaction is explicitly rolled back, or rolled back via recovery as a result of an uncommitted transaction when a crash occurs.
    • PerconaFT will create/expect this file in the directory specified by the MySQL datadir.
    • PerconaFT maintains a mapping of a dictionary name (example: sbtest.sbtest1.main) to an internal file name (example: _sbtest_sbtest1_main_xx_x_xx.tokudb). This mapping is stored within this single PerconaFT dictionary file and takes up space within the PerconaFT cachetable just like any other PerconaFT dictionary.
    • PerconaFT will created/expect this file in the directory specified by the MySQL datadir.
  • Dictionary files
    • TokuDB dictionary (data) files store actual user data. For each MySQL table there will be:
      • One “status” dictionary that contains metadata about the table.
      • One “main” dictionary that stores the full primary key (an imaginary key is used if one was not explicitly specified) and full row data.
      • One “key” dictionary for each additional key/index on the table.
    • These are typically named: _<database>_<table>_<key>_<internal_txn_id>.tokudb
      PerconaFT creates/expects these files in the directory specified by tokudb_data_dir if set, otherwise the MySQL datadir is used.
  • Recovery log files
    • The PerconaFT recovery log records every operation that modifies a PerconaFT dictionary. Periodically, the system will take a snapshot of the system called a checkpoint. This checkpoint ensures that the modifications recorded within the PerconaFT recovery logs have been applied to the appropriate dictionary files up to a known point in time and synced to disk.
    • These files have a rolling naming convention, but use: log<log_file_number>.tokulog<log_file_format_version>
    • PerconaFT creates/expects these files in the directory specified by tokudb_log_dir if set, otherwise the MySQL datadir is used.
    • PeconaFT does not track what log files should or shouldn’t be present. Upon startup, it discovers the logs in the log dir, and replays them in order. If the wrong logs are present, the recovery aborts and possibly damages the dictionaries.
  • Temporary files
    • PerconaFT might need to create some temporary files in order to perform some operations. When the bulk loader is active, these temporary files might grow to be quite large.
    • As different operations start and finish, the files will come and go.
    • There are no temporary files left behind upon a clean shutdown,
    • PerconaFT creates/expects these files in the directory specified by tokudb_tmp_dir if set. If not, the tokudb_data_dir is used if set, otherwise the MySQL datadir is used.
  • Lock files
    • PerconaFT uses lock files to prevent multiple processes from accessing/writing to the files in the assorted PerconaFT functionality areas. Each lock file will be in the same directory as the file(s) that it is protecting. These empty files are only used as semaphores across processes. They are safe to delete/ignore as long as no server instances are currently running and using the data set.
    • __tokudb_lock_dont_delete_me_environment
    • __tokudb_lock_dont_delete_me_recovery
    • __tokudb_lock_dont_delete_me_logs
    • __tokudb_lock_dont_delete_me_data
    • __tokudb_lock_dont_delete_me_temp

PerconaFT is extremely pedantic about validating its data set. If a file goes missing or unfound, or seems to contain some nonsensical data, it will assert, abort or fail to start. It does this not to annoy you, but to try to protect you from doing any further damage to your data.

Look out for part 2 of this series for information on how to move your log, dictionary, and temp files around correctly.

pt-table-checksum not detecting diff

Lastest Forum Posts - September 27, 2016 - 7:01am
I have a very simple setup: 1 master with 2 replicas. I turned one of the replicas off and changed a row on the master. I ran Code: pt-table-checksum --databases=db_main --user=user --pass='pass' but do not get and diffs. I read the documentation a few times, but can't seem to find what I might be missing.


Unable to show query execution plan from PMM 1.0.4

Lastest Forum Posts - September 26, 2016 - 8:17pm
We've installed PMM 1.0.4 server and client. For some reason, from query analyzer, when we click button "explain" (after gave DB name), it is showing this error:

EXPLAIN failed: Error 1146: Table 'xxxx.PARTITIONS' doesn't exist.

Is there anything we mis-configured?

Thank you

High Availability at Percona Live Europe 2016

Latest MySQL Performance Blog posts - September 26, 2016 - 12:55pm

This blog will review some of the high availability topics featured at this year’s Percona Live Europe, Amsterdam conference.

The topic of high availability MySQL is always hot, because beyond just being available, you also want efficient database manageability. I’m sure you’ve all seen the video by Frederic Descamps talking about the launch of MySQL InnoDB Cluster (built with group replication, with management executed with the new MySQL Shell). MySQL 8.0 going GA will prove for an exciting time for the MySQL world (though all you early adopters should start trying it now, or right after Percona Live Europe Amsterdam!).

With that, I think that a must attend tutorial is MySQL Group Replication in a nutshell: hands-on tutorial by Frederic Descamps and Kenny Gryp. It competes with MySQL High Availability with Percona XtraDB Cluster 5.7 by  Alok Pathak, Peter Zaitsev and Krunal Bauskar (Percona XtraDB Cluster Team Lead), however, which I think will also be an interesting session (and you’re going to learn about Percona XtraDB Cluster 5.7 here).

The quality of the sessions this year are extremely high, making tutorial day hard to split up – another reason to bring a colleague to the conference to get the best spread! Remember, if you bring three or more in a group, you qualify for the group discounted rate at registration. Back to tutorial day, in the morning you have the choice of seeing me give Best Practices for MySQL High Availability or checkout the ProxySQL Tutorial by David Turner (Uber), Derek Downey (Pythian), and the author himself René Cannaò (Dropbox/ProxySQL). Frankly I think ProxySQL is the new hotness, so you definitely want to be there in the morning (and it will make a good follow on to the Percona XtraDB Cluster tutorial in the afternoon if you’re looking for a “track”).

On Day 1, there are plenty of talks, but my picks focused around high availability would be:

On Day 2, I’d probably check out the following:

So here’s another “track” like post about what’s coming in Amsterdam in about 2 weeks. There is of course, still time to register. Use the FeaturedTalk code to get a discount.

Amsterdam eWeek

Percona Live Europe 2016 is part of Amsterdam eWeek. Amsterdam eWeek provides a platform for national and international companies that focus on online marketing, media and technology and for business managers and entrepreneurs who use them, whether it comes to retail, healthcare, finance, game industry or media. Check it out!

Webinar Wednesday, September 28: Percona Software News and Roadmap Update – Q3 2016

Latest MySQL Performance Blog posts - September 26, 2016 - 12:27pm

Please join Percona founder and CEO Peter Zaitsev for a webinar Wednesday, September 28 at 11 am PDT (UTC-7) where he’ll discuss Percona Software News and Roadmap Update – Q3 2016.

Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL and MongoDB, Percona XtraBackup, Percona Toolkit, Percona XtraDB Cluster and Percona Monitoring and Management.

During this webinar Peter will talk about newly released features in Percona software, show a few quick demos and share with you highlights from the Percona open source software roadmap.

Peter will also talk about new developments in Percona commercial services and finish with a Q&A.

Register for the Percona Software News and Roadmap Update – Q3 2016 webinar here.

Peter Zaitsev, CEO

Peter Zaitsev co-founded Percona and assumed the role of CEO in 2006. As one of the foremost experts on MySQL strategy and optimization, Peter leveraged both his technical vision and entrepreneurial skills to grow Percona from a two-person shop to one of the most respected open source companies in the business. With over 150 professionals in 20 plus countries, Peter’s venture now serves over 3000 customers – including the “who’s who” of internet giants, large enterprises and many exciting startups. The Inc. 5000 added Percona to its list in 2013, 2014 and 2015. Peter was an early employee at MySQL AB, eventually leading the company’s High Performance Group.

A serial entrepreneur, Peter co-founded his first startup while attending Moscow State University where he majored in Computer Science. Peter is a co-author of High Performance MySQL: Optimization, Backups, and Replication, one of the most popular books on MySQL performance. Peter frequently speaks as an expert lecturer at MySQL and related conferences, and regularly posts on the Percona Data Performance Blog. He was also tapped as a contributor to Fortune and DZone, and his recent ebook Practical MySQL Performance Optimization Volume 1 is one of’s most popular downloads. Peter lives in North Carolina with his wife and two children. In his spare time, Peter enjoys travel and spending time outdoors.

XtraDB Cluster -non-locking option to rebuild 2 nodes w/out taking Production down

Lastest Forum Posts - September 26, 2016 - 11:26am
Dear Community,
We have a fairly large cluster that went down due to the hardware problems, and we could recover only one node that is currently running Production Apps.
Few attempts to start the two other nodes resulted in full SST , (using Xtrabackup) and at the end of that process - XtraBackup just hung , and connections on the master node kept creeping up . I assume that is due to the app clients trying to write to the master node, and some of the statement included DDL modifications that created a lock.
We need an advise on the least painful method of rebuilding the two remaining nodes that does not lock the master node.

Appreciate your time,
Elena K.

General Inquiries

For general inquiries, please send us your question and someone will contact you.