The Ultimate Guide to High Availability with MariaDB

Lo Stotage Engine Aria

Stai visualizzando una vecchia versione di questo article. Visualizza la versione più recente.

Lo Storage Engine Aria è compilato per default a partire da MariaDB 5.1 ed è necessario che sia attivo all'avvio di mysqld.

Inoltre, le tabelle interne su disco sono nel formato Aria e non nel formato MyISAM. Questo dovrebbe velocizzare alcune query contenenti GROUP BY e DISTINCT, perché Aria ha un meccanismo di cache più avanzato rispetto a MyISAM. L'inclusione di Aria è una delle differenze tra MariaDB 5.1 e MySQL 5.1.

Nota: Lo Storage Engine Aria era precedentemente chiamato Maria (si veda Rename Maria per ulteriori dettagli sul cambio di nome) e nelle versioni precedenti di MariaDB si chiama ancora Maria.

Vi sono alcune nuove opzioni per CREATE TABLE:

  • TRANSACTIONAL= 0 | 1 : Transazionale significa crash-safe in Aria
  • PAGE_CHECKSUM= 0 | 1 : Se gli indici e i dati devono usare i checksum delle pagine per ulteriore sicurezza
  • TABLE_CHECKSUM= 0 | 1 : Sinonimo di CHECKSUM in MySQL 5.1
  • ROW_FORMAT=PAGE : The new cacheable row format for Aria tables. Default row format for Aria tables and only row format that can be used if TRANSACTIONAL=1. To emulate MyISAM, use ROW_FORMAT=FIXED or ROW_FORMAT=DYNAMIC
  • CHECKSUM TABLE now ignores values in NULL fields. This makes CHECKSUM TABLE faster and fixes some cases where same table definition could give different checksum values depending on row format. The disadvantage is that the value is now different compared to other MySQL installations. The new checksum calculation is fixed for all table engines that uses the default way to calculate and MyISAM which does the calculation internally. Note: Old MyISAM tables with internal checksum will return the same checksum as before. To fix them to calculate according to new rules you have to do an ALTER TABLE. You can use the old ways to calculate checksums by using the option --old to mysqld or set the system variable '@@old' to 1 when you do CHECKSUM TABLE ... EXTENDED;
  • At startup Aria will check the Aria logs and automatically recover the tables from last checkpoint if mysqld was not taken down correctly.

mysqld startup options for Aria

OptionDescriptionDefault value
--aria[=#]Enable or disable Aria plugin. Possible values are ON, OFF, FORCE (don't start if the plugin fails to load).ON
--aria-block-size=# Block size to be used for Aria index pages.8192
--aria-checkpoint-interval=# Interval between automatic checkpoints, in seconds; 0 means 'no automatic checkpoints' which makes sense only for testing.30
--aria-force-start-after-recovery-failures=# Number of consecutive log recovery failures after which logs will be automatically deleted to cure the problem; 0 (the default) disables the feature.0
--aria-group-commit=# Specifies Aria group commit mode. Possible values are "none" (no group commit), "hard" (with waiting to actual commit), "soft" (no wait for commit (DANGEROUS!!!))none
--aria-group-commit-interval=# Interval between commits in microseconds (1/1000000 second). 0 stands for no waiting for other threads to come and do a commit in "hard" mode and no sync()/commit at all in "soft" mode. Option has only an effect if aria_group_commit is used0
--aria-log-dir-path=namePath to the directory where to store transactional logSame as 'datadir'
--aria-log-file-size=# Limit for transaction log size1073741824
--aria-log-purge-type=# Specifies how Aria transactional log will be purged. Possible values of name are "immediate", "external" and "at_flush"immediate
--aria-max-sort-file-size=# Don't use the fast sort index method to created index if the temporary file would get bigger than this.9223372036853727232
--aria-page-checksumMaintain page checksums (can be overridden per table with PAGE_CHECKSUM clause in CREATE TABLE)TRUE
--aria-pagecache-age-threshold=# This characterizes the number of hits a hot block has to be untouched until it is considered aged enough to be downgraded to a warm block. This specifies the percentage ratio of that number of hits to the total number of blocks in the page cache.300
--aria-pagecache-buffer-size=# The size of the buffer used for index blocks for Aria tables. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford.134217720 (= 128M)
--aria-pagecache-division-limit=# The minimum percentage of warm blocks in key cache100
--aria-recover[=#]Specifies how corrupted tables should be automatically repaired. Possible values are "NORMAL" (the default), "BACKUP", "FORCE", "QUICK", or "OFF" which is like not using the option.NORMAL
--aria-repair-threads=# Number of threads to use when repairing Aria tables. The value of 1 disables parallel repair.1
--aria-sort-buffer-size=# The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE.134217728
--aria-stats-method=# Specifies how Aria index statistics collection code should treat NULLs. Possible values are "nulls_unequal", "nulls_equal", and "nulls_ignored".nulls_unequal
--aria-sync-log-dir=# Controls syncing directory after log file growth and new file creation. Possible values are "never", "newfile" and "always").NEWFILE

In normal operations, the only variables you have to consider are:

  • aria-pagecache-buffer-size
    • This is where all data and index are cached. The bigger this is, the faster Aria will work.
  • aria-block-size
    • The default value 8192, should be ok for most cases. The only with a higher value is that it takes long to find a packed key in the block as one has to search roughly 8192/2 to find each key. We plan to fix this by adding a dictionary at the end of the page to be able to do a binary search within the block before starting a scan. Until this is done and key lookups takes too long time even if you are not hitting disk, then you should consider making this smaller.
    • Possible values to try are 2048, 4096 or 8192
    • Note that you can't change this without dumping, deleting old tables and deleting all log files and then restoring your Aria tables. (This is the only option that requires a dump and load)
  • aria-log-purge-type
    • Set this to "at_flush" if you want to keep a copy of the transaction logs (good as an extra backup). The logs will stay around until you execute FLUSH LOGS;

See also: Aria FAQ

Commenti

Sto caricando i commenti......
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.