DEVTOME.COM HOSTING COSTS HAVE BEGUN TO EXCEED 115$ MONTHLY. THE ADMINISTRATION IS NO LONGER ABLE TO HANDLE THE COST WITHOUT ASSISTANCE DUE TO THE RISING COST. THIS HAS BEEN OCCURRING FOR ALMOST A YEAR, BUT WE HAVE BEEN HANDLING IT FROM OUR OWN POCKETS. HOWEVER, WITH LITERALLY NO DONATIONS FOR THE PAST 2+ YEARS IT HAS DEPLETED THE BUDGET IN SHORT ORDER WITH THE INCREASE IN ACTIVITY ON THE SITE IN THE PAST 6 MONTHS. OUR CPU USAGE HAS BECOME TOO HIGH TO REMAIN ON A REASONABLE COSTING PLAN THAT WE COULD MAINTAIN. IF YOU WOULD LIKE TO SUPPORT THE DEVTOME PROJECT AND KEEP THE SITE UP/ALIVE PLEASE DONATE (EVEN IF ITS A SATOSHI) TO OUR DEVCOIN 1M4PCuMXvpWX6LHPkBEf3LJ2z1boZv4EQa OR OUR BTC WALLET 16eqEcqfw4zHUh2znvMcmRzGVwCn7CJLxR TO ALLOW US TO AFFORD THE HOSTING.

THE DEVCOIN AND DEVTOME PROJECTS ARE BOTH VERY IMPORTANT TO THE COMMUNITY. PLEASE CONTRIBUTE TO ITS FURTHER SUCCESS FOR ANOTHER 5 OR MORE YEARS!

Introduction

My goal in writing up this page, is to be a natural extension of the mysql article. MariaDB is a fork of MySQL by Michael 'Monty' Widenus; the original creator of MySQL.

Why should you care?

Since Oracle now owns MySQL, they have been keeping back more and more features for their enterprise version of MySQL. MySQL still runs well enough, but sometimes, that's just not enough. MariaDB brings many optimisations to the table. See https://mariadb.com/kb/en/mariadb-versus-mysql-features/ for the full list. Most importantly, MariaDB is controlled by the MariaDB foundation, and doesn't rely on the mercy of a huge corporate entity for scraps thrown from the features table.

TokuDB, the killer engine

Starting with MariaDB 5.5.33a, there is support for http://tokutek.com/'s TokuDB engine. This engine seems to share a few structural features with PBXT, but the similarities end there. TokuDB for MariaDB is nothing short of a major breakthrough, and the next step in MySQL/MariaDB evolution. While MariaDB 5.5.33+ doesn't contain all the features of TokuDB, version 10.0 does. Additionally, TokuTek's own version of MariaDB contains all the features, but it can be daunting to install.

Getting MariaDB with TokuDB

Getting MariaDB with the TokuDB engine is simple. The easiest way is to use the repository configuration tool. Adding the MariaDB repository will allow you to use your favorite package manager to install MariaDB.

Upgrading

What if you already have mysql installed, and are using it? Have no fear, MariaDB is a drop-in replacement for MySQL. Since we're focusing on TokuDB, there's a few caveats. The upgrade process will go somewhat like this:

  1. Shut down mysql cleanly. Make a backup of the mysql directory. Put it somewhere safe.
  2. Uninstall mysql. Sometimes it's possible to uninstall it while ignoring dependencies so you don't uninstall many other package. Again, don't worry, MariaDB has a compat package that will make those other programs think you're still running mysql.
  3. Install MariaDB

At this point, we're going to make some assumptions. First, we're going to assume you were using InnoDB as your engine. MYISAM is fine, but it's a crap engine, and if you used full text search in it, that table won't be convertable to TokuDB. So if that's the case, you will need to convert it to the ARIA engine.

You're going to want to edit your my.cnf file and decrease the value of innodb_buffer_pool_size. Remember, don't remove it completely (yet), but give it enough ram to run the conversion. At least 10% of available ram.

Now, TokuDB runs best on SSD drives, but because it uses fractal indexes, it can even squeeze lots of mileage out of regular SATA drives. Regardless, here we assume you have your binary logs in a different set of drives. You're going to want to create something like /mnt/logs/tmp1, where your binary logs are in /mnt/logs/binary-logs. /mnt/logs should be on a separate set of drives. Chown /mnt/logs/tmp1 to the mysql user.

Now add the following config to my.cnf. Note that we assume your DB data to be in /mnt/mysql, although the default is usually /var/lib/mysql. Change as needed:

##TokuDB
tokudb_cache_size = 256M
tokudb_data_dir = /mnt/mysql    #(or /var/lib/mysql)
tokudb_tmp_dir = /mnt/logs/tmp1
tokudb_lock_timeout = 20000
tokudb_row_format = tokudb_small

tokudb_cache_size should be 50% of total ram. tokudb_row_format sets maximum compression. Your mileage may vary, but this gives you monster savings (sometimes up to 60%).

Don't change anything else, and start up mariadb in the same way you'd start up mysql.

Depending on the previous version of mysql you used you may get from a few, to a lot of errors in your mysql log. Make sure none of them are innodb errors.

At this point, you're going to want to convert everything to tokudb (except the mysql, test, info*, and perf* databases). Go ahead and grab this conversion script mysql_convert_table_format.sh

Before you run that script though, you will need to run mysql_upgrade. Once you've run that script, proceed with mysql_convert_table_format. You will want to do something like mysql_convert_table_format -S /mnt/mysql/mysql.sock <database name>

Use mysqlshow to display the list of databases to convert. The mysql_convert_table_format script automatically sets the maximum compression.

Once you're run this script, one by one on each database (you might be able to run it concurrently, but be careful! /mnt/logs/tmp1 must contain enough space for a full table. Make sure you don't run out of space compressing!).

Once everything is compressed, make sure no more InnoDB tables exist. Check by doing:

# find /mnt/mysql -name "*.ibd"

If nothing comes back, you're golden!

Caveats and post/cleanup

There's a potentially nasty situation where when you use an old innodb datafile, upgrade the db, and completely empty the shared table space of innodb by converting everything to TokuDB. InnoDB/XtraDB decides something is wrong, and on your next startup barfs. The fix is easy. Shut down MariaDB after the conversion. Verify again there are no InnoDB files as per above.

Now, go into /mnt/mysql (or /var/lib/mysql) and delete ibdata* and all the ib_logfile* Edit my.cnf again and add

skip-innodb

, but ONLY if you are positive no more innodb tables will be created. If you're not sure, just further decrease the size of the innodb_buffer_pool_size variable in my.cnf. If you go this route, remember, you must still delete the innodb files as mentioned above. They will be re-created automatically at next start-up. If you don't do it, then in the next 2-3 restarts the empty table space of innodb will corrupt. I've seen it every time so far after an upgrade. You must delete ibdata* and all the ib_logfile* files in either case.

While monitoring the MariaDB log file, bring up MariaDB and look for errors. If there are none, you're all set!

Further Resources

The TokuDB User Group is a great place to get specific TokuDB help, while the TokuTek website has User and Quick Setup guides which are invaluable. You can also open a ticket at https://mariadb.com/kb/en/reporting-bugs/.

A Chef recipe for deploying TokuTek's version of MariaDB is available here. It's being used in a ServerTemplate at RightScale. Go ahead and sign up for a free account to test it out!

Software | unix_linux


QR Code
QR Code mariadb (generated for current page)
 

Advertise with Anonymous Ads