How To: upgrade from table type maria to aria?


#1

I followed a guide where I updated all of my WordPress database tables to the ‘Maria’ type and after much further reading I get the impression that Maria is now replaced by type ‘Aria’.

Do you have any experience with working with the Aria table type and suggestions as to whether it is a good idea to change or not?

If so, can you suggest a simple solution to change all the table types from Maria to Aria?

*One thing that does concern me is that I had to run a script in order to change to the Maria type. Would I not need to do this every single time I add a new plugin to WordPress?

Or is there a way to autoset the database to only use ‘Aria’ so that when new plugins are installed that Aria is used?

Until very recently I had never done anything other than use the standard mysql database and type installed by WordPress. There are a lot of guides on the net and as always I am sure many of them present bad advice. As you are quite the WordPress and mysql expert, and I didn’t see any articles on rtcamp about Aria, it seemed like a good question to ask

** Here is the ‘script’ that I used to convert to Maria

This will convert from innodb to maria format – do each time plugin tables added? – saved root maria.sh

a small bash script that checks for all tables in a given database for MyMaria tables, alters/repairs them

MYSQL="/usr/bin/mysql"
MYSQLR="/usr/bin/mysqlrepair"
MYSQL_HOST=“localhost”
MYSQL_USER=“my-user”
MYSQL_PASSWD=“my-pass”
MYSQL_SOCKET="/var/run/mysqld/mysqld.sock"
MYSQLCONNECT="$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWD -h $MYSQL_HOST -S $MYSQL_SOCKET -A"
MYSQLREPAIR="$MYSQLR -v -F -u$MYSQL_USER -p$MYSQL_PASSWD -B"

echo "Checking DB "

Use MySQL ‘SHOW DATABASES’

DATABASES="$MYSQLCONNECT --batch -N -e “show databases like ‘your_blog_db’”"

echo "Checking for InnoDB Tables "

Loop through each instance of MySQL and check all databases in that instance

for DATABASE in $DATABASES
do
TABLES="$MYSQLCONNECT --batch -D $DATABASE -N -e “SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA=’$DATABASE’ AND TABLE_TYPE=‘BASE TABLE’ AND ENGINE IN (‘InnoDB’)”"
for TABLE in $TABLES
do
echo “Altering Table : $TABLE”
ALTERTABLES="$MYSQLCONNECT --batch -D $DATABASE -N -e “ALTER TABLE $TABLE ENGINE=Maria”"
echo “Repairing Table: $TABLE”
REPAIR_TABLE=$MYSQLREPAIR $DATABASE --tables $TABLE
done
done

I’m guessing I could just change InnoDB to Maria and Maria to Aria and run it again, thus changing to Aria. Just not sure if this is the best practice or not. Hoping you have some expert advice as this pertains to WordPress, esp very large single instance multi-site installs where single sites can be VERY large. Meaning I have one site with 500k posts and it has serious issues with performance, hence my changing mysql to begin with.


#2

I haven’t used MariaDB before but sometime back we converted from MyISAM to InnoDB in bulk.

Details of process are posted here - http://rtcamp.com/wordpress-nginx/tutorials/mysql/myisam-to-innodb/

Also checkout other articles in - http://rtcamp.com/wordpress-nginx/tutorials/mysql/

They might help!


#3