Log Tables and Size Limits
Novell Cool Solutions: Tip
By Jason Doering
Digg This -
Posted: 31 Aug 2005
A reader asked:
"Anyone know how to manually create a new log table without using the SQL expiration commands? Sometimes my table fills up before the expiration interval, and I want to create one manually without having to redo my expiration time and re-loading lengine."
And here's the reply from Forum expert Jason Doering ...
Lengine will automatically create a new table at startup if the designated table does not exist. In order to manually move the table the way you describe, you must unload lengine so that it will not be writing to the table while you are moving it.If you really want to manually move the log table, here are your steps:
- Stop Auditing: unload lengine
- Open MySQL console: mysql -u auditusr -p
- Enter the auditusr password.
- Select the Audit database: use naudit;
- Move the table: rename table <old table> to <new table>;
- Leave MySQL console: exit
- Start Auditing: load lengine -d
Long term, you are better off allowing for larger tables. MySQL tables default to a 4GB limit, but you can go bigger. See the documentation:
To allow for larger tables with Audit, you must set the AVG_ROW_LENGTH and MAX_ROWS options in the "create table options" field, just above the expiration command field. I use these settings:
Note: The parameters must be entered in the format shown here - no spaces around the "=" and no carriage returns.
This gives me plenty of room for a week's data from my ~250 users. The average row length for a table is shown in the "show table status;" command at the MySQL console. Maximum rows really depends on your user count and auditing selections.
A MySQL table file can grow huge on NetWare. You just need to set the average row size and maximum rows parameters to allow MySQL to tune the internal structures. Before I set these parameters, if lengine filled a table to 4GB, that table became badly corrupted. Since I set the parameters, I have not had any issues. The limit is the maximum size of a single file on NSS.