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: