Aria Storage Engine Primer

I’m looking into HA MySQL at the moment. With these types of technologies you need to have a crash-safe storage engine in use. MyISAM just won’t cut it. While my long-term goal is to move to a fully-transactional storage engine, for example InnoDB, I’m looking at other possibilities.

Changing to a fully-transactional storage engine may cause some problems with our current applications. For example MyISAM is deadlock-free, while otehr engines are not. Using the Aria storage engine may relieve some of these issues so I can get the HA side of things sorted.

A quick illustration of using the Aria storage engine…

Create a test table..

CREATE TABLE test
(
	id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	value_column VARCHAR(100) NOT NULL
) ENGINE=ARIA ,TRANSACTIONAL=1;

Start a transaction, insert a row, then roll it back…

START TRANSACTION;
 
INSERT INTO test 
(
	value_column
)
VALUES
(
	'Blah, blah, blah, blah, blah!'
);
 
ROLLBACK;

If you execute a “SHOW WARNINGS” you receive the following warning text…

Some non-transactional changed tables couldn't be rolled back

and you’ll see your inserted row is still there…

SELECT *
FROM test;

The explanation is in the Aria FAQ;
“In the current development phase Aria tables created with TRANSACTIONAL=1 are crashsafe and atomic but not transactional because changes in Aria tables can’t be rolled back with the ROLLBACK command. As we will make Aria tables fully transactional in a relatively short time frame we think it’s better to use the TRANSACTIONAL keyword now so that applications don’t need to be changed later.

Tables marked with TRANSACTIONAL=1 will gain more transactional features with each Aria release. We expect these tables to be fully transactional (in the traditional sense) when we reach Aria 2.0.”

So rather than being a fully-fledged transactional engine we should currently only consider Aria to be “Crash-safe MyISAM”.

Relevant server variables;

SHOW VARIABLES LIKE '%aria%';
Variable_name                             Value                
aria_block_size                           8192                 
aria_checkpoint_interval                  30                   
aria_checkpoint_log_activity              1048576              
aria_force_start_after_recovery_failures  0                    
aria_group_commit                         none                 
aria_group_commit_interval                0                    
aria_log_file_size                        1073741824           
aria_log_purge_type                       immediate            
aria_max_sort_file_size                   9223372036853727232  
aria_page_checksum                        ON                   
aria_pagecache_age_threshold              300                  
aria_pagecache_buffer_size                134217728            
aria_pagecache_division_limit             100                  
aria_recover                              NORMAL               
aria_repair_threads                       1                    
aria_sort_buffer_size                     134217728            
aria_stats_method                         nulls_unequal        
aria_sync_log_dir                         NEWFILE              
aria_used_for_temp_tables                 ON

The following command string should be good for copying a MyISAM database to another server ensuring tables use the Aria storage engine with TRANSACTIONAL=1 activated. Ensure the database does not exist on the target server.

mysqldump -h server1 -u root -pSECRET --routines --databases db_name | sed -re 's/^(\) ENGINE=)MyISAM/\1ARIA, TRANSACTIONAL=1/gi' | mysql -h localhost -u root -pSECRET;

Check the engine and create options for each of your tables to ensure they are as expected…

SELECT TABLE_NAME,
		 `ENGINE`,
		 CREATE_OPTIONS
FROM information_schema.tables
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_TYPE = 'BASE TABLE';

Useful links:

Aria Storage Engine MariaDB documentation

Obligatory Wikipedia link

Aria FAQ


Leave a Reply