search

fleetdm / fleet

Open-source platform for IT, security, and infrastructure teams. (Linux, macOS, Chrome, Windows, cloud, data center)
https://fleetdm.com
Other
3.11k stars 431 forks source link

Increased load on MySQL From Fleet While Running Query Related To Configuration Profile Delivery #23832

Open nonpunctual opened 3 hours ago

nonpunctual commented 3 hours ago

Fleet version: <!-- Copy this from the "My account" page in the Fleet UI, or run fleetctl --version -->

{
  "version": "4.58.0",
  "branch": "HEAD",
  "revision": "e98f86d0c87979a48e0816e5c55be3602de5b7e5",
  "go_version": "go1.23.1",
  "build_date": "2024-10-17",
  "build_user": "runner"
}

Customer is using a single instance of MySQL for multiple Fleet web apps (each web app has a database.)

šŸ’„ Ā Actual behavior

Configuration Profile - Malwarebytes Protection - HE.mobileconfig

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>PayloadDescription</key>
    <string>System Extensions, Network configurations and Privacy Preferences</string>
    <key>PayloadDisplayName</key>
    <string>Configuration Profile - Malwarebytes Protection - HE</string>
    <key>PayloadIdentifier</key>
    <string>com.malwarebytes.homeexchange.tcc</string>
    <key>PayloadOrganization</key>
    <string>Malwarebytes Protection</string>
    <key>PayloadRemovalDisallowed</key>
    <true/>
    <key>PayloadScope</key>
    <string>System</string>
    <key>PayloadType</key>
    <string>Configuration</string>
    <key>PayloadUUID</key>
    <string>DAE793D0-F368-4664-B06F-C3C5209E042A</string>
    <key>PayloadVersion</key>
    <integer>2</integer>
    <key>PayloadContent</key>
    <array>
        <dict>
            <key>PayloadDescription</key>
            <string>This profile allows Malwarebytes Protection to have Full Disk Access, to ensure full scanning capabilities.</string>
            <key>PayloadDisplayName</key>
            <string>Privacy Settings Whitelist - Malwarebytes Protection</string>
            <key>PayloadIdentifier</key>
            <string>com.malwarebytes.tcc.E18E6763-E6D5-11E9-B185-000C294BA983</string>
            <key>PayloadOrganization</key>
            <string>Malwarebytes</string>
            <key>PayloadType</key>
            <string>com.apple.TCC.configuration-profile-policy</string>
            <key>PayloadUUID</key>
            <string>E18E6763-E6D5-11E9-B185-000C294BA983</string>
            <key>PayloadVersion</key>
            <integer>1</integer>
            <key>Services</key>
            <dict>
                <key>SystemPolicyAllFiles</key>
                <array>
                    <dict>
                        <key>Allowed</key>
                        <true/>
                        <key>CodeRequirement</key>
                        <string>anchor apple generic and identifier "com.malwarebytes.mbam.rtprotection.daemon" and (certificate leaf[field.1.2.840.113635.100.6.1.9] /* exists */ or certificate 1[field.1.2.840.113635.100.6.2.6] /* exists */ and certificate leaf[field.1.2.840.113635.100.6.1.13] /* exists */ and certificate leaf[subject.OU] = GVZRY6KDKR)</string>
                        <key>Comment</key>
                        <string>Allow SystemPolicyAllFiles control for RTProtectionDaemon</string>
                        <key>Identifier</key>
                        <string>com.malwarebytes.mbam.rtprotection.daemon</string>
                        <key>IdentifierType</key>
                        <string>bundleID</string>
                        <key>StaticCode</key>
                        <false/>
                    </dict>
                    <dict>
                        <key>Allowed</key>
                        <true/>
                        <key>CodeRequirement</key>
                        <string>anchor apple generic and identifier "com.malwarebytes.ncep.rtprotection.daemon" and (certificate leaf[field.1.2.840.113635.100.6.1.9] /* exists */ or certificate 1[field.1.2.840.113635.100.6.2.6] /* exists */ and certificate leaf[field.1.2.840.113635.100.6.1.13] /* exists */ and certificate leaf[subject.OU] = GVZRY6KDKR)</string>
                        <key>Comment</key>
                        <string>Allow SystemPolicyAllFiles control for RTProtectionDaemon</string>
                        <key>Identifier</key>
                        <string>com.malwarebytes.ncep.rtprotection.daemon</string>
                        <key>IdentifierType</key>
                        <string>bundleID</string>
                        <key>StaticCode</key>
                        <false/>
                    </dict>
                    <dict>
                        <key>Allowed</key>
                        <true/>
                        <key>CodeRequirement</key>
                        <string>anchor apple generic and identifier EndpointAgentDaemon and (certificate leaf[field.1.2.840.113635.100.6.1.9] /* exists */ or certificate 1[field.1.2.840.113635.100.6.2.6] /* exists */ and certificate leaf[field.1.2.840.113635.100.6.1.13] /* exists */ and certificate leaf[subject.OU] = GVZRY6KDKR)</string>
                        <key>Comment</key>
                        <string>Allow SystemPolicyAllFiles control for EndpointAgentDaemon</string>
                        <key>Identifier</key>
                        <string>/Library/Application Support/Malwarebytes/Malwarebytes Endpoint Agent/EndpointAgentDaemon.app/Contents/MacOS/EndpointAgentDaemon</string>
                        <key>IdentifierType</key>
                        <string>path</string>
                        <key>StaticCode</key>
                        <false/>
                    </dict>
                    <dict>
                        <key>Allowed</key>
                        <true/>
                        <key>CodeRequirement</key>
                        <string>anchor apple generic and identifier "com.malwarebytes.EndpointAgentDaemon" and (certificate leaf[field.1.2.840.113635.100.6.1.9] /* exists */ or certificate 1[field.1.2.840.113635.100.6.2.6] /* exists */ and certificate leaf[field.1.2.840.113635.100.6.1.13] /* exists */ and certificate leaf[subject.OU] = GVZRY6KDKR)</string>
                        <key>Comment</key>
                        <string>Allow SystemPolicyAllFiles control for EndpointAgentDaemon</string>
                        <key>Identifier</key>
                        <string>com.malwarebytes.EndpointAgentDaemon</string>
                        <key>IdentifierType</key>
                        <string>bundleID</string>
                        <key>StaticCode</key>
                        <false/>
                    </dict>
                </array>
            </dict>
        </dict>
        <dict>
            <key>PayloadDisplayName</key>
            <string>Approved System Extensions - Malwarebytes Protection</string>
            <key>PayloadDescription</key>
            <string>Approved System Extensions for Malwarebytes Endpoint Detection and Response</string>
            <key>PayloadIdentifier</key>
            <string>C0112E8A-D776-48B7-A52F-AD47AFA369EB</string>
            <key>PayloadUUID</key>
            <string>C0112E8A-D776-48B7-A52F-AD47AFA369EB</string>
            <key>PayloadOrganization</key>
            <string>Malwarebytes Protection</string>
            <key>PayloadVersion</key>
            <integer>1</integer>
            <key>PayloadEnabled</key>
            <true/>
            <key>PayloadType</key>
            <string>com.apple.system-extension-policy</string>
            <key>AllowUserOverrides</key>
            <true/>
            <key>AllowedSystemExtensions</key>
            <dict>
                <key>GVZRY6KDKR</key>
                <array>
                    <string>com.malwarebytes.edr.helper.ext</string>
                </array>
            </dict>
        </dict>
        <dict>
            <key>FilterSockets</key>
            <true/>
            <key>FilterDataProviderBundleIdentifier</key>
            <string>com.malwarebytes.edr.helper.ext</string>
            <key>FilterDataProviderDesignatedRequirement</key>
            <string>anchor apple generic and identifier "com.malwarebytes.edr.helper.ext" and (certificate leaf[field.1.2.840.113635.100.6.1.9] /* exists */ or certificate 1[field.1.2.840.113635.100.6.2.6] /* exists */ and certificate leaf[field.1.2.840.113635.100.6.1.13] /* exists */ and certificate leaf[subject.OU] = GVZRY6KDKR)</string>
            <key>FilterPackets</key>
            <true/>
            <key>FilterPacketProviderBundleIdentifier</key>
            <string>com.malwarebytes.edr.helper.ext</string>
            <key>FilterPacketProviderDesignatedRequirement</key>
            <string>anchor apple generic and identifier "com.malwarebytes.edr.helper.ext" and (certificate leaf[field.1.2.840.113635.100.6.1.9] /* exists */ or certificate 1[field.1.2.840.113635.100.6.2.6] /* exists */ and certificate leaf[field.1.2.840.113635.100.6.1.13] /* exists */ and certificate leaf[subject.OU] = GVZRY6KDKR)</string>
            <key>FilterType</key>
            <string>Plugin</string>
            <key>FilterGrade</key>
            <string>firewall</string>
            <key>PayloadDescription</key>
            <string>Configures Content Filtering settings for Malwarebytes Endpoint Detection and Response</string>
            <key>PayloadDisplayName</key>
            <string>Web Content Filter - Malwarebytes Protection</string>
            <key>PayloadIdentifier</key>
            <string>C8A8B7E6-8805-48D4-BA7A-C9D80084456E</string>
            <key>PayloadType</key>
            <string>com.apple.webcontent-filter</string>
            <key>PayloadUUID</key>
            <string>C8A8B7E6-8805-48D4-BA7A-C9D80084456E</string>
            <key>PayloadVersion</key>
            <integer>1</integer>
            <key>PayloadEnabled</key>
            <true/>
            <key>PayloadOrganization</key>
            <string>Malwarebytes Protection</string>
            <key>PluginBundleID</key>
            <string>com.malwarebytes.edr.helper</string>
            <key>UserDefinedName</key>
            <string>Malwarebytes Endpoint Detection and Response</string>
        </dict>
    </array>
</dict>
</plist>

Customer has concerns regarding the efficiency of the following query related to MDM profile delivery 

SELECT c.command_uuid, c.request_type, c.command FROM nano_enrollment_queue AS q INNER JOIN nano_commands AS c ON q.command_uuid = c.command_uuid LEFT JOIN nano_command_results r ON r.command_uuid = q.command_uuid AND r.id = q.id WHERE q.id = 'redactedstring' AND q.active = 1 AND (r.status IS NULL OR (r.status = 'NotNow' AND NOT 0)) ORDER BY q.priority DESC, q.created_at LIMIT 1

This query has been identified as being potentially inefficient by CS Infrastructure Engineers & Fleet Engineering and can be the cause of high CPU & RAM usage.

@rfairburn:

this individual query actually runs on the writer. It is possible that it is an immediate select-after-insert and we couldn't wait for replication latency In a multi-tenant situation, I'd probably have my RDS cluster configured with a writer and multiple readers

Customer mentioned they have readers disabled. Engineering mentioned that although this query would not be helped directly by readers, it is possible that having them enabled would reduce total load thereby making this system more performant for heavy loads like this.

See code where query is executed here: https://github.com/fleetdm/fleet/blob/bf6e506c5086bcf768df6f1067283b152579886c/server/mdm/nanomdm/storage/mysql/queue.go#L169

PR for addressing related performance issues: https://github.com/fleetdm/fleet/pull/21247

Internal load testing report: https://docs.google.com/document/d/1KYRxJEIB2Inav0daaXQnIsFI_Lga52uTOJotBEbHCu8/edit?tab=t.0#heading=h.msr2v67rpmdw

šŸ§‘ā€šŸ’» Ā Steps to reproduce

  1. Deploy profile to cause related MDM query to run

šŸ•Æļø More info (optional)

  1. CS infrastructure engineers, Engineering & QA all mentioned possibility of adding indexes for this query to potentially make it more efficient.
  2. Adding readers for the query was also mentioned:

Will attach debug info if possible.

nonpunctual commented 2 hours ago

@jahzielv @gillespi314