Mysql – What are some good methods of structuring a MySQL audit trail to be both immutable, and perform well for usage

auditdatabase-engineinnodbMySQLperformance

In one application, we have an audit table that uses the ArchiveEngine in conjunction with a few triggers to capture 100% of the changes that occur. The upside to this is that 20+ million rows occupies 145MB, not to mention that ArchiveEngine only supports INSERT and SELECT, rendering the trail immutable.

The downside comes when you want to access that data in order to give the user audit information. Since the data is stored in compressed form and there are no indices, selection time can be ridiculous:

mysql> select * from AUDIT_LOG WHERE Table_Name = 'myTable' AND Row_ID = 9024;
+------------+--------+-------------+---------------------+---------------------+----------------+-------------+---------------------+
| Table_Name | Row_ID | Field_Name  | Old_Value           | New_Value           | DB_User        | modified_by | date_modified       |
+------------+--------+-------------+---------------------+---------------------+----------------+-------------+---------------------+
| myTable    |   9024 | Flags       | 0                   | 1                   | user@localhost |         826 | 2011-06-10 22:05:11 |
| myTable    |   9024 | Status      | Unavailable         | Pending             | user@localhost |         826 | 2011-07-08 22:41:45 |
| myTable    |   9024 | Status      | Pending             | Processing          | user@localhost |         826 | 2011-07-08 22:41:49 |
| myTable    |   9024 | Status      | Processing          | Calculated          | user@localhost |         826 | 2011-07-08 22:41:54 |
| myTable    |   9024 | Fee_Paid    | NULL                | 185.00              | user@localhost |         826 | 2011-07-08 22:41:54 |
| myTable    |   9024 | Status      | Calculated          | Approved            | user@localhost |         826 | 2011-07-08 22:47:04 |
| myTable    |   9024 | Approved    | NULL                | 2011-07-08 22:47:06 | user@localhost |         826 | 2011-07-08 22:47:04 |
| myTable    |   9024 | Approved_TZ | America/Yellowknife | America/Vancouver   | user@localhost |         826 | 2011-07-08 22:47:04 |
| myTable    |   9024 | Approved_By | NULL                | 826                 | user@localhost |         826 | 2011-07-08 22:47:04 |
| myTable    |   9024 | Invoice_ID  | NULL                | 2372                | user@localhost |         825 | 2011-10-17 19:32:59 |
+------------+--------+-------------+---------------------+---------------------+----------------+-------------+---------------------+
**10 rows in set (13.54 sec)**

After conversion to InnoDB and adding a PK and indexing Row_ID and Table_Name, this same operation takes roughly 1/10th of a second.

What I'm considering is a leaving the triggers + Archive, and then using a cron job to mirror the Archive in InnoDB.

Are there other ways this has been dealt with?
Any drawbacks or trade-offs that are apparent?

Best Answer

Are there other ways this has been dealt with?

If you use MySQL Replication, you could have a Master/Slave that has the following:

AUDIT_LOG as InnoDB on the Master
AUDIT_LOG as ARCHIVE on the Slave

This would provide fast writing of the AUDIT_LOG on the Master and makes the data available for reads almost instantly. The Slave would have the backup as an ARCHIVE with a small disk footprint.

DO NOT SEND ANY ALTER TABLE SQL AGAINST AUDIT_LOG ONCE REPLICATION IS SET UP. Otherwise, you lose the small disk footprint feature of the ARCHIVE Storage Engine.

Any drawbacks or trade-offs that are apparent?

You must configure InnoDB

with enough RAM to accommodate data and indexes that are frequently queried : What are the main differences between InnoDB and MyISAM?
with tablespaces for table separate from ibdata1 : What is the best way to reduce the size of ibdata in mysql?
to access multiple CPUs (if using MySQL 5.5) : Possible to make MySQL use more than one core?

Related Solutions

Mysql – What are the potential causes for a sudden MySQL slowdown

You could try the following to improve things

skip-host-cache and skip-name-resolve

Please follow these steps:

Step 01) Add the following to /etc/my.cnf

[mysqld]
skip-host-cache
skip-name-resolve

Step 02) service mysql restart

Step 03) Stop using mysql users that have DNS names. Run thus query:

SELECT user,host FROM mysql.user;

Any user that has a DNS name, simply replace it with IP Address (such as 10.85.115.73) or a netblock (such as 10.85.115.%)

You may also want to run this command intermittently (say one an hour in a crontab)

FLUSH HOSTS;

Give it a Try !!!

Mysql – What are some key configuration settings for using an InnoDb table for session storage

You could experiment with innodb_flush_log_at_trx_commit

According to the MySQL Documentation on innodb_flush_log_at_trx_commit

If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

The default value of 1 is required for full ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose up to one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. InnoDB's crash recovery works regardless of the value.

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in your master server my.cnf file.

Caution

Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt the InnoDB database. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix command hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.

Based on this, values other than 1 put InnoDB at risk of losing 1 second's worth of transactions, or a transaction commit's worth of data.

As far as innodb_flush_method, the default is best. I wrote an earlier post to describe the effects of tweeking it.

As for running multiple instances of MySQL, it's OK as along as you have enough memory for the OS and enough memory of DB Connections for the multiple instances.

Best Answer

Related Solutions

Mysql – What are the potential causes for a sudden MySQL slowdown

Mysql – What are some key configuration settings for using an InnoDb table for session storage

Related Question