Large Logs performance hack

NTmatter
New Contributor II

After upgrading to 8.7, I've been running into some issues with the database churning 100% CPU and clients occasionally being unable to properly submit results to the JSS.

Some investigation found that the database was taking upwards of a second to determine the ID of the last log for various computers. All eight DB threads were busy trying to execute some variant of "SELECT MAX(log_id) FROM logs WHERE computer_id=%d;" for various computer_ids.

As a guess, this is probably due to not cleaning out logs often enough (as in "never"). The logs table currently weighs in at roughly 1.1M rows for roughly 150 machines, meaning an average of 7000 rows must be scanned for every MAX(log_id) query.

Just to be clear, the following steps are not sanctioned by JAMF Software, and might brick your JSS install. Don't attempt this unless you are comfortable with restoring from backups :)

Adding an index to the database (and probably invalidating the warranty) seems to have cleared things up nicely:

CREATE INDEX `computer_id_log_id` ON `logs` (`computer_id`, `log_id`);

Queries are now answered almost instantaneously. Unfortunately, the JSS wipes out this extra index every time it starts. As a workaround, it is possible to modify the JSS' database schema to include the extra compound index (also doubly invalidating the warranty). The database schema is kept in the JSS' WebApp directory at WEB-INF/xml/JAMFSoftwareServerDatabaseSchema.xml

Find the logs table, which starts here at line 3513:

<table_name>logs</table_name>

Just before the closing </table>, add in the following composite key:

<composite_key>
                <name>computer_id_log_id</name>
                <column_name>computer_id</column_name>
                <column_name>log_id</column_name>
            </composite_key>

Restart the JSS, and it should have the new index. Connect to your database and run the following:

SHOW INDEX FROM logs;

The computer_id_log_id index should now be present, and your account representative should be sending you an angry e-mail any time soon :)

0 REPLIES 0